Previous | Table of Contents | Next |
Another useful tool for analyzing query performance is the statistics io option. With this tool, you can determine how much disk and memory work is performed to resolve your query. This can help you assess whether the server is making correct choices. Well look at some output from statistics io now.
set statistics io on select count(*) count, sum(qty) total sold from sales where stor_id between F and #147;Gz and title_id like BI2% go
Note: Unlike showplan, statistics io is an execution-time option. You can include it in a batch and even wrap it around the particular query you are interested in:select count(*) from titles where title_id like A% set statistics io on select count(*) count, sum(qty) total sold from sales where stor_id between F and Gz and title_id like BI2% set statistics io off select count(*) from titles where title_id like A% goThis will report statistics for the second query, but not for the first or last.
count total sold ----------- ----------- 188 160224 Table: sales scan count 1, logical reads: 567, physical reads: 31, read ahead reads: 350 (1 row affected)
In addition to the query results, the server returns information about disk and memory reads performed when executing the query. You can compare these values to your expectations to determine whether SQL Server is finding the most effective way to resolve the query.
Table: sales
In a multitable query, there will be one statistics line per table. This element of the output allows you to figure out which table is being reported.
scan count 1,
In a single-table query, the table will be scanned only once. In multitable queries, SQL Server selects outer and inner tables. The inner tables are scanned one time per row in the outer table.
logical reads: 567, physical reads: 31,
Logical reads are reads from cache; physical reads are from disk. If a required page is not found in cache, SQL Server performs a physical read to place it in the cache and then a logical read to retrieve it from cache. So there is a logical read for every page required, whether that page was originally found in cache or not.
Tip: In my experience, its most useful to try to reduce logical reads when troubleshooting queries. In general, as this value goes down, query time goes down. The problem is that too many outside factors influence whats in cache. If you work to minimize physical reads (which are more time-consuming than logical reads), you may find that a sudden increase in activity on the server will bump all your data out of cache, with a sudden surge in physical reads again.As a programmer, in particular, the place where you can have the greatest impact is on logical, not physical reads. Focus on that, and your query performance will improve.
read ahead reads: 350
Read-aheads are used by SQL Server when it needs to retrieve a page from disk, but it expects to need additional pages in the same 16KB extent. SQL Server launches a task on a separate thread to perform a 16KB block read. Read-aheads are most effective when SQL Server is running on a system with multiple processors, but the efficiency of 16KB I/Os can also dramatically improve performance on large tables.
Now that you know how to get information about your query, and how to read that information, what can you do with it? Lets look one more time at our sample query and consider our options.
select count(*) count, sum(qty) total sold from sales where stor_id between F and Gz and title_id like BI2%
With normal optimization, as you saw earlier, SQL Server chooses to use the clustered index. If you wanted to override that choice, you could try forcing the use of the nonclustered index by adding the name of the index after the table name in the FROM clause of the query.
select count(*) count, sum(qty) total sold from sales (index = titleidind) where stor_id between F and Gz and title_id like BI2%
STEP 1 The type of query is SELECT Scalar Aggregate FROM TABLE sales Nested iteration Index : titleidind STEP 2 The type of query is SELECT count total sold ---------- ------------ 188 160224 (1 row(s) affected) Table: sales scan count 1, logical reads: 1524, physical reads: 440, read ahead reads: 43
It worked: the optimization plan uses the nonclustered index, titleidind. Although the result is the same, notice how the cost of the query has increased in both logical and physical reads. The server was also less able to take advantage of read aheads. The choice of the clustered index was correct here.
Just to be complete, lets force a table scan, too, by putting a zero after the table name:
select count(*) count, sum(qty) total sold from sales (0) where stor_id between F and Gz and title_id like BI2%
STEP 1 The type of query is SELECT Scalar Aggregate FROM TABLE sales Nested iteration Table Scan STEP 2 The type of query is SELECT count total sold ----------- ----------- 188 160224 (1 row(s) affected) Table: sales scan count 1, logical reads: 4536, physical reads: 159, read ahead reads: 2415
Again, the query returned the correct results, but the table scan method cost about 4,000 additional logical reads and substantially more read-aheads. The index selection made by the optimizer was the correct one for this query.
Previous | Table of Contents | Next |