Should I use CPU or elapsed time to measure query performance?

Question

During query performance tuning, when comparing the “SQL Server Execution Times” of different runs of a query, should I be comparing the CPU Time or the Elapsed Time, or both?

Answer

CPU time measures the amount of CPU resources used by the query when it ran, measured in milliseconds. Elapsed Time, also measured in milliseconds, refers to the amount of time it took for the query to execute from start to completion.

But which measurement works best for performance tuning?

Of these two measurements, the only one to really watch is CPU Time. This is because CPU Time is a relative consistent measurement of the amount of CPU resources it takes for a query to run, and this is relatively independent of how busy your CPU is. Sure, you probably will see some variation in this number, but it won’t be significant, not as wildly changing as the Elapsed Time, which does vary due to the ever-changing load on your server.

Because the CPU time is relatively consistent, you can use it as a way to help you determine whether the changes you make to your queries, during performance tuning, are actually helping or hurting.




Related Articles :

  • No Related Articles Found
Uncategorized

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |