Can you determine an estimated execute time of an SQL statement

Question

Can you determine an estimated execute time of an SQL statement

Resender

SSCertifiable

Points: 7443
More actions
March 31, 2014 at 3:33 am

#291558

OK, I gotten a call in which I need to run 6 update statement on a staging environment,but before running I need to give them an estimate of how long it would take to run these statements.
That makes me wonder is it possible to get a 'predicted' estimated execution time based on an sql statement.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Short of running them in a similar environment and timing them, no.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 233821 More actions · Answer 2

sys.dm_exec_requests has a percent complete column and also an estimated finish time, but only for a set of specific commands. You'll need to capture time stats in a similar environment setup and use this to gauge completion estimates

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

It is very difficult to find out as same query on the prod take different time to execute as it depends on lot of factors like:

1. Any other application is using server resources?

2. Howmany active connections on the server?

3. Load on the server? (Resource availability)

4. Index fregmentation?

5. Query plan? etc.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Even if you do tests to determine an approximate run time, you can still hit resource contention or blocking issues that cause that time to change. This is a tough spot overall in our ability to quantify the behavior of SQL Server.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning