Need some help understanding IO Statistics

Question

I have a query that has a very costly INDEX SEEK operation in the execution plan. In order to track down the cause i set IO STATISTICS on and ran it. In the problem section it gave the following statistics:

Table '#TempStudents_Enrollment2_________________________________________________________________000000004D5F'. Scan count 0, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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.

Table '#TempRace2________________________________________________________________________________000000004D58'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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.

Table 'RefRace'. Scan count 120, logical reads 240, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'RefFedEnctyRaceCatg'. Scan count 18, logical reads 36, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#43B0BA0F'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#42BC95D6'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#41C8719D'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#40D44D64'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#LEA2_____________________________________________________________________________________000000004D56'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#39332B9C'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#School2__________________________________________________________________________________000000004D57'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#GenderKey________________________________________________________________________________000000004D5A'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#LangAcqKey_______________________________________________________________________________000000004D5B'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#TransferCatKey___________________________________________________________________________000000004D5C'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#ResCatKey________________________________________________________________________________000000004D5D'. Scan count 1, logical reads 29164, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'RPT_SnapShot_1_4_StuPgm_Denorm'. Scan count 2344954, logical reads 4992518, physical reads 16, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#3FE0292B'. Scan count 1, logical reads 2344954, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'RPT_SnapShot_1_4_StuEnrlmt_Denorm'. Scan count 20, logical reads 87679, physical reads 0, read-ahead reads 87425, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#GradeKey_________________________________________________________________________________000000004D59'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What should I look for in here when i'm looking to improve the performance? The line with over 2 million for the Scan count looked suspicious to me but I really don't know. Does anyone see anything here that i should look into in more detail?

Answer 1

The Source: MS SQL Server 2008 R2 Unleashed

Scan Count The scan count value indicates the number of times the corresponding table was accessed during query execution. The outer table of a nested loop join typically has a scan count of 1. The scan count for the inner tables typically reflects the number of times the inner table is searched, which is usually the same as the number of qualifying rows in the outer table. The number of logical reads for the inner table is equal to the scan count multiplied by the number of pages per lookup for each scan. Note that the scan count for the inner table might sometimes be only 1 for a nested join if SQL Server copies the needed rows from the inner table into a work table in cache memory and reads from the work table for subsequent iterations (for example, if it uses the Table Spool operation). The scan count for hash joins and merge joins is typically 1 for both tables involved in the join, but the logical reads for these types of joins are usually substantially higher.

Logical Reads The logical reads value indicates the total number of page accesses necessary to process the query. Every page is read from cache memory, even if it first has to be read from disk. Every physical read always has a corresponding logical read, so the number of physical reads will never exceed the number of logical reads. Because the same page might be accessed multiple times, the number of logical reads for a table could exceed the total number of pages in the table.

Physical Reads The physical reads value indicates the actual number of pages read from disk. The value for physical reads can vary greatly and should decrease, or drop to zero, with subsequent executions of the query because the data will be loaded into the data cache by the first execution. The number of physical reads will also be lowered by pages brought into memory by the read-ahead mechanism.

Read-Ahead Reads The read-ahead reads value indicates the number of pages read into cache memory using the read-ahead mechanism while the query was processed. Pages read by the read-ahead mechanism will not necessarily be used by the query. When a page read by the read-ahead mechanism is accessed by the query, it counts as a logical read, but not as a physical The read-ahead mechanism can be thought of as an optimistic form of physical I/O, reading the pages into cache memory that it expects the query will need before the query needs them. When you are scanning a table or index, the table’s index allocation map pages (IAMs) are looked at to determine which extents belong to the object. An extent consists of eight data pages. The eight pages in the extent are read with a single read, and the extents are read in the order that they are stored on disk. If the table is spread across multiple files, the read-ahead mechanism attempts parallel reads from up to eight files at a time instead of sequentially reading from the files.read.

Answer 2

It seems there is a rather costly index scan going on there: Table 'RPT_SnapShot_1_4_StuPgm_Denorm'. Scan count 2344954, logical reads 4992518.

Answer 3

You already executed "set statistics IO on". In "Query" menu turn on "Include Actual Execution Plan" and "Include Client Statistics". Run your query/procedure. In "messages" tab look for highest "Logical reads" number, memorize that table. In "Execution plan" tab, look for table you found in step before (usually has highest Cost percentage associated in the plan). If it is "Scan" (table scan or index scan), you are missing appropriate index, or appropriate index has no good statistics. If it is "Seek", then rows you are seeking are wide scattered in blocks. You have to bring them physically together by creating CLUSTERED index on column you seek. That is VERY efficient method. Not many people is aware of what clustered indexes are. Spend some time studying on them. By default, Sql server creates primary key that is clustered, and most people leave it that way. And in many cases it can lead to performance degradation. You need clustered index to physically group rows together by column(s) you build your clustered index on. You can have just one clustered index per table. Clustered index does not have to be unique, does not have to be PK, can consist of more than one column. You can rewrite the query, e.g. replace exists with IN and vice-versa, or replace exists with table join. There is no "fastest" method of join. If there would be one, all other types would be automatically converted to that fastest one. It depends on the data, available indexes, amount of ram etc.

Always measure, do not assume. Measuring is only truth. For how much you managed to cut down logical reads, that much you succeeded to optimize the query. Other optimisations would be database-wide level done by DBA (memory cache, parallel processes, storage system, examining wait events, etc).

Answer 4

The only figure worth worrying about is "Logical Reads". Physical Reads will depend on how much data is currently cached, which could change every time the query is run.

Scan count is sometimes telling, but isn't really worth focusing on.

Edit: See some more discussion of these results in this post here. What I meant by "telling" is that the scan count can sometimes be a "flag" indicating that SQL is retrieving data from that table inefficiently. But when it comes to trying different versions of your query while you optimize, I pay much more attention to the improvement I can make in Logical Reads.