Measuring SQL Server Resources Are Key to Easing Query Tuning Problems
When you execute a query on SQL Server, various server resources are used. One of those resources is a specific amount of CPU time. If you run the same query over and over again–and assuming the database has not changed–the CPU resources needed to run the query will remain very close to the same from execution to execution (usually less than 500 milliseconds or less of variation from run to run). I am not talking about the amount of time it takes for a query to run from beginning to end, but the amount of CPU resources needed by the query to run. The amount of time that it takes a query to run to completion will vary based on how busy the server is.
Another resource that SQL Server needs is IO. Whenever you run a query, SQL Server must retrieve data from the data cache (logical reads), and if the data it needs is not in the data cache, then it must read it from disk (physical reads).
As must be obvious from this discussion, the more CPU and IO resources that a query needs to run, the slower the query will perform. So another way to describe the task of query tuning is that you want to rewrite your queries in such a way as that they use less CPU and IO resources. If you accomplish this, then the performance of your query will have improved.
If you think of query tuning in terms of reducing the server resources it uses, (rather than in terms of how long it takes for a query to run) the easier it is to measure whether or not the actions you take to tune your query are actually helping or hurting, especially on a busy server whose resource usage is changing all the time. Let’s find out how we can measure these resources so we can tell if our queries are using more or less resources as we tune them.