I have a question. Please run the script as below
CREATE TABLE tbl_temp( id int, name1 nvarchar(20), name2 nvarchar(20)) CREATE INDEX name1 ON tbl_temp(name1) CREATE UNIQUE INDEX name2 ON tbl_temp(name2) INSERT INTO tbl_temp VALUES (1,'a','a') DECLARE @name nvarchar(20) SET @name='a' SET STATISTICS IO ON --query 1 SELECT 1 FROM tbl_temp WHERE name1 = @name --query 2 SELECT 1 FROM tbl_temp WHERE name2 = @name
and output is:
Table 'tbl_temp'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tbl_temp'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
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?
Answer by Kev Riley ·
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:
CREATE UNIQUE INDEX name1 ON tbl_temp(name1)
and then run your 2 queries, you will get a scan count of 0 for both queries.
Answer by Fatherjack ·
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:
USE AdventureWorks GO dbcc freeproccache SET STATISTICS IO ON DECLARE @name nvarchar(20) SET @name='gustavo' --query 1 SELECT 1 FROM Person.Contact WHERE FirstName = @name --go --DECLARE @name nvarchar(20) --SET @name='gustavo' --query 2 dbcc freeproccache SELECT 1 FROM Person.Contact WHERE FirstName = @name SET STATISTICS IO OFF
then the results for STATISTICS IO and Actual Execution Plan are identical.
In short, I dont think there is anything wrong with your query.