SET STATISTICS IO Results
SET STATISTICS IO information is displayed at the bottom of the output results. Here is it again:
Table ‘Order Details’. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 9.
Some of this information is very useful, and others not. Let’s take a look at each and learn what they mean.
Scan Count: Refers to the number of times that tables referenced in the query have been accessed. In our example, the table in our query was only accessed 1 time. For queries that don’t include joins, this information is not too helpful. But if your query has one or more joins, then this information may be of use to you.
For example, an outer table of a nested loop would have a Scan Count of 1. But for an inner table, the Scan Count refers to the number of times through the nested loop that the table was accessed. As you might imagine, the smaller the Scan Count for the inner table, the less resources it uses and the faster the performance of the query. As you tune a query with joins, you may want to pay attention to the Scan Count, watching to see if it is increasing or decreasing as you tune the query.
Logical Reads: This is the most useful piece of data provided by the SET STATISTICS IO or the SET STATISTICS TIME commands. As you probably know, before SQL Server can do anything with data, that data must be in its data cache. In addition, you probably know that when SQL Server reads data from the data cache, it reads it in 8K pages.
So what is a logical read? A logical read is the number of pages SQL Server had to read from the data cache in order to produce the results specified by your query. SQL Server will never read any data pages more or less than it needs when executing a query. Because of this, the exact same number of logical reads will always occur when the same query runs against the exact same data on SQL Server.
Why is knowing the number of logical reads SQL Server takes to execute a query important to your query tuning? Because it is the one thing that never changes from one execution of a query to another execution of the same query. And because of this, it is the perfect gauge to use when you are query tuning to determine if you are succeeding or failing.
As you tune a query, if the number of logical reads goes down, then you know that your query is using less server resources and will result in increased performance. And if the number of logical reads goes up, then whatever you are doing to “tune” your query is hurting the performance of your query. The fewer the logical reads performed by a query, the more efficient it is, and the faster it will perform, assuming all other things are held equal.
Physical Reads: What I am about to tell you here may be a little confusing, but it should eventually make sense once you think about it. First of all, a physical read refers to when SQL Server must go to disk to get data pages it needs to put into the data cache before it can execute the query. As I mentioned above, all data must be in the data cache before SQL Server can execute a query. Whenever SQL Server begins to execute a query, the first thing it does is to check and see if the data pages it needs are in the data cache. If they are, great. But if they are not, then SQL Server must go to disk and then move the pages it needs from disk into the data cache.
As you can imagine, it takes a lot more server resources for SQL Server to perform a physical read than a logical read. Ideally, for best performance, we should do our best to avoid physical reads when we can.
Now here is the part that is confusing. You should ignore physical reads when performance tuning your queries, instead focusing only on logical reads. How can this be, I just said that physical reads were much more resource intensive than logical reads?
This is a true statement, but the number of physical reads SQL Server uses to execute a query cannot be reduced by query tuning. Reducing physical reads is also an important task for the DBA, but it is a focus on whole server tuning, not query tuning. When you tune queries, you have no control over the size of the data cache or how busy the server is, or whether or not the data your query needs is in the data cache or on disk. The only thing you can directly control through query tuning is the number of logical reads that are required to produce the results you need.
Because of this, you can safely ignore the Physical Read data provided by the SET STATISTICS IO command. (Side Note: One way to help reduce physicals reads, and speed up your SQL Server, is to ensure your server has plenty of physical RAM.)
Read-Ahead Reads: Like physical reads, this data won’t be of use to you when query tuning. This value tells you the number of physical page reads that SQL Server performed as part of its read-ahead mechanism. To help optimize its performance, SQL Server reads physical data pages ahead of when it thinks your queries might need the data. The pages that are read-ahead may or may not be used, based on how well SQL Server guessed what your needs were.
In my example, notice that the read-ahead pages were 9, that the physical reads was 1, and that the logical reads was 10. Do these numbers add up? Yes they do. Here’s what happened when I ran the query on my server. First of all, SQL Server started to check to see if the data pages it needed to fulfill the query were in the data cache. About this same time, SQL Server was smart enough to figure out that they were not, and then the read-ahead mechanism kicked in and read the first 9 data pages out of the 10 it needed from disk and placed them in the data cache. When SQL Server checked to see if all 10 data pages were in the cache, it found that 9 of them were already there (because of the read-ahead mechanism), but one was missing. Because of this, SQL Server then had to retrieve the last single database and place it in the data cache. Once all of the data pages were in the cache, then SQL Server was able to process the query. SQL Server is smarter than you thought, isn’t it?
So What Does All This Mean to You?
As I said at the beginning of this article, it is important when performance tuning queries that you have some scientific basis in which to know whether or not your tuning efforts are paying off. The problem, as you remember, is that SQL Servers are often dynamic, and using total query run time as your sole measurement if a particular query you are tuning is performing better or worse, is not a fair way to judge your efforts.
A better way is to compare more scientific values, such as the number of logical reads or the CPU time it takes a query to run. So when you are performance tuning queries, you may want to begin using the SET STATISTICS IO and the SET STATISTICS TIME commands to provide you with the solid data you need in order to know exactly how successful your query tuning efforts really are.
]]>