Query Completion Time Estimate

Thunderstorms can be a dangerous temptation to a fisherman. Like King David gazing upon Bathsheba as she bathed, a fisherman looks toward the ominous, incoming clouds and sees opportunity. When a storm rolls into an area, it causes a big drop in barometric pressure. This is the weight of the atmosphere pushing down on everything, including the water. When that wieght drops quickly, it causes a huge rise in fish activity. I’ve caught some great fish right before a storm strikes. I can remember being right outside a bay on a lake up north as a storm rolled in. We hadn’t caught much all day, but a wall of thick black clouds was approaching quickly. I managed to hook into a nice sized walleye using a Lindy Rig, but lost it right before I could get it in the net. After that we had to get off the water. The key is to be smart about the situation. The lightening that accompanies these storms can be fatal, and out in the middle of a lake is not a good place to be. Fishing right before a storm is a balancing act of staying long enough to hook into some fish but leaving before conditions become dangerous.
Storm Northern
Similarly, as Database Administrators we are often forced to perform tasks within some tight windows. Software updates can involve both schema and data changes that need to be deployed (and possibly rolled back, if the software update is unsuccessful) within a short timeframe. Moving databases from one location to another is another task that may need to be done within a small window of time. One very interesting challenge that I encountered during my first few months as a DBA was a significant table change on a very large production table. This table held over a billion rows of detail records, and had an integer for the primary key/identity field. A little less than a month before it happened, we noticed we were running out of integer values for this field. We needed to change the field to a bigint, but this change required a significant outage.

It is helpful to have a way to measure progress when performing changes that take a long time. I found this query which has been helpful in estimating the time required to do some longer running tasks. I’ve found it works well for database backups and restores.

SELECT  percent_complete ,
        CONVERT(NUMERIC(10, 2), estimated_completion_time / 1000 / 60) AS Estimated_Minutes_Remaining ,
        start_time ,
        session_id ,
        status ,
        command ,
        sql_handle ,
        database_id ,
FROM    sys.dm_exec_requests
WHERE   command <> 'TASK MANAGER'
        AND status <> 'background'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s