{"id":794,"date":"2010-02-04T00:00:00","date_gmt":"2010-02-04T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/simple-query-tuning-with-statistics-io-and-execution-plans\/"},"modified":"2026-06-02T16:12:22","modified_gmt":"2026-06-02T16:12:22","slug":"simple-query-tuning-with-statistics-io-and-execution-plans","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/simple-query-tuning-with-statistics-io-and-execution-plans\/","title":{"rendered":"SQL Server STATISTICS IO and Execution Plans: Query Tuning Lessons with Logical Reads, Physical Reads, and Plan Analysis"},"content":{"rendered":"\n

SET STATISTICS IO is one of the two essential SQL Server query-tuning diagnostics (the other is the execution plan). When enabled for a session, it returns per-object I\/O statistics for every query – scan count (number of times each index was accessed), logical reads (pages read from the buffer cache), physical reads (pages read from disk), read-ahead reads (pages prefetched), and LOB variants of those for large-object data. <\/b><\/p>\n\n\n\n

Interpreting these numbers in combination with the execution plan is how you identify which queries are expensive, which specific objects are driving the expense, and whether the expense comes from I\/O volume, non-cached reads, or large-object access. <\/b><\/p>\n\n\n\n

This article teaches the skill in three lessons: <\/b>

(1) breaking down each STATISTICS IO metric with working examples of what the numbers mean; <\/b>

(2) dissecting an execution plan alongside STATISTICS IO output, connecting the plan operators to the I\/O statistics;<\/b>
<\/b>
(3) a real-world tuning example showing how a query’s STATISTICS IO and plan together diagnose the performance problem and validate the fix. <\/b><\/p>\n\n\n\n

This article is<\/strong> beginner-friendly with executable examples – no prior query-tuning experience assumed, SQL Server basics sufficient.<\/b><\/p>\n\n\n\n

Introduction<\/h2>\n\n\n\n
\n

\u00a0I once worked at a large financial institution where we had a policy in place that went …<\/p>\n

“All procedures that are to be promoted must have their execution plan and STATISTICS IO data attached with the promote form, for review by DBA group”.<\/i><\/b><\/p>\n

\u00a0A policy that, at first, might seem an intolerable imposition turned out to be a great habit to foster. We, the DBA group, could easily identify poorly performing queries, make notes and return it to the developers for them to modify.\u00a0 \u00a0The developers soon realized that they learned better techniques over time by studying the execution plans and STATISTICS IO data; \u00a0<\/p>\n

In this article, I’d like to pass on what I learned about these two simple sources of information from SQL Server, because they are specifically designed to assist the developer to speed up the performance of queries:<\/p>\n

You need the outputs from both together to make best use of them. In this article, I’ll take a real example of a large SQL query to illustrate how they will help solve the puzzle, and, hopefully, provide a nice clear picture rather than the jumbled puzzle pieces.<\/p>\n

STATISTICS IO provides detailed information about the impact that your query has on SQL Server.\u00a0 It tells you the number of logical reads (including LOB), physical reads (including read-ahead and LOB), and how many times a table was scanned.\u00a0 This information helps you to establish whether or not the choices made by the optimizer are as efficient as possible at the time.\u00a0<\/p>\n

This is powerful information to have when used together with the Execution plan. \u00a0Sometimes, for example, you’ll run a query and find that the Execution plan displays an index being used, yet STATISTICS IO shows that the index is doing 10 million logical reads.\u00a0 At that point, you can re-evaluate the index choice and make sure there is no better way to write the query to use a more efficient index (less IOs).\u00a0 The ideal solution is to use the least number of logical reads to perform your operation.\u00a0 The fewer the logical reads, the faster the response and the lesser the impact on the Server.<\/p>\n

Lesson 1: Breakdown of ‘STATISTICS IO’<\/h1>\n

STATISTICS IO can be set as an option when you execute a query. A message is sent via the connection that made a query , telling you the cost of the query in terms of the actual number of physical reads from \u00a0the disk and logical reads from memory, by the query. In SQL Server Management Studio, it will appear with the result of the query in the results pane\u00a0 (under the messages tab, if you are using the grid)<\/p>\n

\"934-StatsIO.jpg\"<\/figure>\n

\u00a0<\/p>\n

STATISTICS IO<\/b> helps you to understand how your query performed by telling you what actually happened.\u00a0 This shows you the IO that was incurred for each object, including the number of times it read a given object, the amount of logical\/physical IO, and the order of access.\u00a0<\/p>\n

What Statistics IO means<\/h2>\n

I took a single line from the output of STATISTICS IO<\/b> and it is shown below.\u00a0 We will take each phrase in the order in which they appear from left to right, though this is not necessarily the order that you will concentrate on, once you begin your tuning.\u00a0 I’ll explain that later.\u00a0<\/p>\n

Table 'T023_RepStatement'. Scan count 208450, logical reads 716751, physical reads 1421, read-ahead reads 996, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/pre>\n
Scan count (208,450)<\/h3>\n
This number tells us that the optimizer has chosen a plan that caused this object to be read repeatedly\u00a0 \u00a0\u00a0This number is used as a gauge later on in the process and you will see what object it is being scanned when I go over the execution plan. This number does not change unless you alter the query.<\/p>\n
Logical Reads (716751)<\/h3>\n
This number tells us the actual number of pages read from the data cache.\u00a0\u00a0 This is the number to focus on because it does not change unless you change the actual query structure or index structures.\u00a0 Most common changes are the joins within the WHERE clause, parameter values, or \u00a0index structures.<\/p>\n
Physical Reads (1421)<\/h3>\n
This is the number of pages actually read from the disk.\u00a0 These are the pages that weren’t already in cache and so it is an interesting figure to monitor as it has a direct effect on the performance of the query.\u00a0 SQL Server does all of its work within its caches.\u00a0 If there is a requested page that is not in cache, it will read it from disk and place it in cache, then use that page.\u00a0 If you were to run your query multiple times in a row, you would see your physical reads decrease and ultimately become 0 (so long as there is enough room in memory to store all of the pages required).\u00a0 Because the physical reads change based upon memory pressure and not query design, I tend to ignore this figure.<\/p>\n
Read-Ahead Reads (996)<\/span><\/h3>\n
This number tells us how many of the physical reads were satisfied by SQL Servers ‘Read-ahead’ mechanism.\u00a0 This is directly tied to physical reads, so if there are no physical reads, you will have 0 for Read-Ahead reads.\u00a0 I ignore this just like I ignore the Physical Reads.\u00a0 This number will fluctuate as pages are swapped in\/out of memory.\u00a0\u00a0 Although this is considered a type of physical read and whether or not SQL Server will do a physical read is based upon if the page exists in memory or not, index fragmentation will affect this number.<\/p>\n
LOB Logical Reads (0)<\/h3>\n
We are not reading in any Large Objects (text<\/b>, ntext,<\/b> image<\/b>, varchar(max)<\/b>, nvarchar(max) <\/b>and varbinary(max))<\/b> in this particular example so this number will be 0.\u00a0 \u00a0\u00a0The query used later on in this document does not request any Large Objects so this number is 0.\u00a0 Should the query you are tuning at the time request large objects, you will see this number grow.\u00a0 Pay attention to this number, just like the Logical Reads<\/b> show above.<\/p>\n
LOB Physical Reads (0)<\/h3>\n
This is the number of physical reads the server performed to fetch the necessary pages to satisfy the query.\u00a0 Again, being physical we have no control over this.\u00a0 Ignore it.<\/span><\/p>\n
LOB Read-Ahead Reads (0)<\/span><\/h3>\n
This represents the number of physical reads satisfied by the Read-Ahead mechanism.\u00a0 Nothing you can affect, without tuning the physical server, nothing to look to tune.<\/p>\n
The STATISTICS IO Output<\/h2>\n
Here is part of a real query that I’ve chosen because it represents the reality of what has to be tackled in the working day of a DBA or developer. The real query is over 700 lines long so I’ll just show the tail of the query from the ‘JOIN’ clause onwards.<\/p>\n
SELECT  \n----a lot of code that isn't directly relevant to this article ----\nfrom (select getdate() as mdate) as d,\n\u00a0\u00a0\u00a0\u00a0 HCPGlobalDW.dbo.T002_ConfirmedSalesDetail o\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0inner join RepManagement.dbo.T023_RepStatement cl on (cl.C023_deliveryacct=o.C002_AddressNumberShipTo)-- and cl.c023_statement=o.C002_AddressNumber)\n\u00a0\u00a0\u00a0\u00a0 inner join dbo.T017_ISBNRange e on ( e.C017_SellingCompany=o.C002_SellingCompany and e.C017_ISBN=o.C002_SecondItemNumber and e.C017_MisCompanyCode=o.C002_MisCompanyCode)\nwhere\u00a0 o.C002_PackFlag in ('C','')\n\u00a0 and o.C002_DataType='SL'\n\u00a0 and exists (select 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 from HCPGlobalMasterFilesDB.dbo.T008_JDEProductMaster,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Repmanagement.dbo.T521_OrgGrouping\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where C521_GroupID=C023_SellingGroupNo \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and C521_TAPCd=C008_DivisionCode\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and C521_TARCd=C008_ProductGroup\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and (C521_TACCd='' or C521_TACCd=C008_Category)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 and (C521_TasCd='' or C521_TasCd=C008_ProgramCode)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and C521_RowStatus='1'\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and C008_ISBN=e.C017_ISBN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 and e.C017_SellingCompany=C008_SellingCompany)\n<\/pre>\n
.\u00a0 Listed below is the actual output of the STATISTICS IO request from executing this query..<\/p>\n
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  \n\u00a0  \nTable 'T023_RepStatement'. Scan count 208450, logical reads 716751, physical reads 1421, read-ahead reads 996, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  \n\u00a0  \nTable 'T002_ConfirmedSalesDetail'. Scan count 1, logical reads 3959, physical reads 2, read-ahead reads 3952, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  \n\u00a0  \nTable 'T017_ISBNRange'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  \n\u00a0  \nTable 'T521_OrgGrouping'. Scan count 5, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  \n\u00a0  \nTable 'T008_JDEProductMaster'. Scan count 1, logical reads 4, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n<\/pre>\n
Key Items of STATISTICS IO<\/h2>\n
Now that we have gone over each item that the STATISTICS IO<\/b> provides, we’ll home in on what I call the key items<\/u>, those that you can effect by \u00a0tuning your query.\u00a0 The items are the scan count and the \u00a0logical reads.\u00a0 I use these numbers in conjunction with the Execution plan output.\u00a0 Now that you understand what each item within the STATISTICS IO <\/b>output represents, I will now dig into the Execution plan<\/b> of the above query.\u00a0 You might be thinking to yourself “Why do that now?\u00a0 We just started with the STATISTICS IO p<\/b>art”.\u00a0 The answer is that you cannot properly alter any of the key items\u00a0 (scan count and logical IO) without an understanding of Execution plans. \u00a0You need to use both outputs together.<\/p>\n
Lesson 2: Execution plan Dissection<\/h1>\n
Now it’s time to take a Execution plan apart.\u00a0 This will be your most important guide for identifying and fixing problem queries \u00a0You will begin to see the how you can affect the figures being returned by STATISTICS IO if you determine the best index and join usage .\u00a0 Here is the Execution plan that corresponds to the STATISTICS IO output shown above.\u00a0 To properly fit onto the page it’s hard to see the individual components.\u00a0 Click on it to see it full-size\u00a0 Since the output of the Execution plan is too large to display clearly on the page, I will break it into individual sections.<\/p>\n
\"934-image002.png\"<\/a><\/figure>\n
\u00a0<\/p>\n
Start with the right-most top piece, since the end result of the Execution plan will be on the left.\u00a0<\/p>\n
\"934-image004.png\"<\/figure>\n
\u00a0<\/p>\n
There are two things that you ought to keep in mind when you start inspecting the output. You will want to check the main access points (table scan, index scan, index seek, type of index) and the little % below each step.\u00a0 All steps must add up to 100% and you will find, as you reduce one step, then another step will surely increase.\u00a0 It’s the nature of the beast and you just want to make each step as efficient as you can<\/p>\n
Taking a look at the execution fragment above, you will see that we are accessing two tables via an index seek. This is a more efficient way \u00a0to use an index than an index scan. The execution plan output \u00a0gives you the fully-qualified ‘database.owner.object.index’ syntax for each object.\u00a0 If you mouse-over one of the images you will see something like this:<\/p>\n
\"934-image006.png\"<\/figure>\n
\u00a0<\/p>\n
This gives a great deal of information about how<\/b> SQL Server is accessing that index, and about what it is expecting to retrieve.\u00a0 If you are stumped as to the reason why an index might be scanning \u00a0instead of seeking, or why SQL Server is doing a table scan instead of using an index, this is where you go.<\/p>\n
Let’s take a moment to dissect the information presented by the image above using the highlighted sections as guides..\u00a0 The first piece of information is the type of index access, which in this case is a seek.\u00a0 This means that SQL Server knows exactly where to start looking for the information within the index; \u00a0much the same way if you were looking up my last name in the telephone book.\u00a0 You could go straight to ‘Richberg’ and then look at each listing until you found ‘Josef’.\u00a0<\/p>\n
The next highlighted section is Actual Number of Rows<\/b>, which is exactly as it sounds.\u00a0 SQL Server found 4 records that match all of the criteria given\u00a0 I find this very useful when trying to determine how effective a given index is or specific criteria are\u00a0 The Predicate <\/b>section is next.\u00a0 This shows you the all the pieces this index uses to qualify a row.\u00a0 It will show you any known values, value ranges, and joins to other tables (and on what columns).\u00a0 You will see this on an Index Seek <\/b>and an Index Scan<\/b>.\u00a0 This is an area I use to help determine if you index is being use as effectively as possibly.\u00a0<\/p>\n
You might look at this and notice ‘I am missing a join to table X’ or ‘I shouldn’t be joining to table y’.\u00a0 You may not see this section at all.\u00a0 That would mean the index is being scanned in its entirety and there are no criteria to restrict the rows at this point.\u00a0 The Output List<\/b> shows you what columns the index will be returning.\u00a0 If the index can satisfy all of the columns requested, it is considered a ‘Covered’ index, otherwise the index will need to get the additional columns from the underlying structure (clustered index if it exists or the table). \u00a0\u00a0The final section is Seek <\/b>Predicates, which shows the actual columns, values, and criteria (<,>,=) used to satisfy the seek.\u00a0 If this where an Index Scan<\/b> you would not see the Seek Predicates<\/b> section. I can tell by reading this section, I am looking for C521_RowStatus='1' and C521_TASCD > [Expr1167] and C521_TASCD<[Expr1168].<\/code>\u00a0 I would have to go back to the query to see what the actual values of [Expr1167] and [Expr1168] <\/code>are, but I would know where to look, because it would be near > and <.\u00a0 \u00a0.\u00a0<\/p>\n
Understanding how and why SQL Server determines particular paths between objects to satisfy your query and ultimately provide your data, you can tune your queries and database structures to be more efficient in their use of resources, which is the ultimate goal in tuning.<\/p>\n
The next set of objects is below.<\/p>\n
Here you can see that \u00a0T017_ISBNRange<\/b> is accessed via a clustered index seek and that T002_ConfirmedSalesDetail<\/b> is access via a non-clustered index seek.\u00a0 They are joined together by a Nested Loop.\u00a0 The output of that is then joined to the output of the non-clustered index seek of T023_RepStatement<\/b>I would like you to take note as we walk through the graphical execution plan how each step from the right connects to objects on the left and each object from the bottom connects upwards towards the top.\u00a0 The sequence logically moves from left to right, bottom to top and eventually towards, in this case a select<\/b>.\u00a0 Continuing, the output of all 3 indexes is joined again to a non-clustered index seek on T008_JDEProductMaster<\/b>.\u00a0 This is shown in the image below. \"934-image011.jpg\"<\/p>\n
\u00a0We are now at the end of the diagram.\u00a0 A nested loop is used to join the result coming from T008_JDEProductMaster<\/b> and that of the nested loop below it, shown by the orange arrow.\u00a0 The result of that output is joined with the results of other operations below, shown by the fatter line pointed to by the blue arrow.\u00a0\u00a0 A final hash match is used, taking up 9% of the total cost, then Sort<\/b> (I have a few group by<\/b>s in the query creating the need for a sort) and then the Select<\/b>, which is the result, being sent back to the calling application.\u00a0 Another piece of useful information can be gleaned by clicking on the lines between each set.<\/p>\n
\"934-image012.png\"<\/figure>\n
\u00a0<\/p>\n
This information tells me that there are 208,453 qualifying rows that are the result of joining T017_ISBNRange<\/b> and T002_ConfirmedSalesDetail<\/b>.\u00a0 If there is a significant difference between the estimated and actual figures, check the statistics of your indexes as they might be stale. \u00a0The other pieces of information, estimated row size and estimated data size can only be modified if you adjust your join columns or select statement.\u00a0 \u00a0\u00a0<\/p>\n
Lession 3: Into the Wild Blue Yonder (Real World Example)<\/h1>\n
I am going to switch up gears a little bit to show you how you can<\/p>\n