Understanding Query Durations

Kendra Little explains some of the intricacies behind query durations:

I typically look at the ‘CPU time’ metric when tuning instead of ‘elapsed time’ (duration). This can work well for tuning because you’re measuring how much more efficient you made the  query in terms of CPU cycles.

But ‘CPU time’ isn’t perfect, and it can get a little weird for reporting results to users, because:

  • If the query uses parallelism, CPU time can be higher than the duration — which may make the query seem “slower” than it actually is to anyone reading a report

  • ‘elapsed time’ includes all the time that it takes to display the results in Management Studio, which is probably a different duration than it would take to return the results to an application server. If you’re just returning a few rows, this may be negligible– but once it gets into the thousands of rows, it can be very noticeable.

Moral of the story:  also use SQL Sentry Plan Explorer…

Related Posts

Tabs And Spaces In SSMS

Wayne Sheffield continues his SQL Server Management Studio Tips & Tricks series: Regardless of whether you like to use tabs or spaces, this is where you go to configure your settings. The first part of the screen controls the indenting options. If “None” is selected, then the next line will start at the beginning of the […]

Read More

Remote DAC And Vulnerability Assessments

Max Vernon points out a SQL Server Management Studio Vulnerability Assessment check which seems somewhat incomplete: Certainly, you’d want to ensure the port for the DAC is not available to the Internet, but hopefully if you’re reading this blog you already know how silly it would be to open SQL Server to the Internet. Assuming […]

Read More


September 2016
« Aug Oct »