I have a question. Please run the script as below
and output is:
My question is why the scan count is 0 but logical reads is 2 for query 2. How to read pages without scanning table? what's the relationship between them?
asked Apr 11 '10 at 06:31 AM in Default
To add to Fatherjack - the reason you don't have a scan count is that there is no scan (table or index) performed. But yes this is pretty meaningless for single table queries.
The 2nd query is satisfied by the UNIQUE index, so the row locator will point to the row you require. There is only one row, as you have specified the index as UNIQUE.
Change the first index to UNIQUE too:
and then run your 2 queries, you will get a scan count of 0 for both queries.
I dont think your example is sufficient to effectively demonstrate the details presented by StatisticsIO.
Firstly, take a look at the Actual Execution Plan - the select statements are identical. Scan count isnt a whole lot of use when there is only one table, more meaning can be derived from it when analysing a query with several joins.
If we place you query against AdventureWorks database with a different table:
then the results for STATISTICS IO and Actual Execution Plan are identical.
In short, I dont think there is anything wrong with your query.
answered Apr 11 '10 at 10:00 AM