question

Hill avatar image
Hill asked

scan and logic read issue

Hi All,

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?

sql-server-2005performanceindexing
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

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.

Try this...

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hill avatar image Hill commented ·
It's indeed meaningless for single table query. I will try to find a better example to demonstrate. I appreciate your help very much!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.