scan and logic read issue

Question

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?

Answer 1

0

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.

answered Apr 12 '10 at 06:40 AM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

I have been aware of the trick --'UNIQUE' constraint. As your mention, the scan count would not increase when seeking only one row. Thank you for your help!

Apr 12 '10 at 01:03 PM Hill

(comments are locked)

Answer 2

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.

scan and logic read issue

Follow this question

Related Questions