x

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?

more ▼

asked Apr 11, 2010 at 06:31 AM in Default

Hill gravatar image

Hill
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Apr 12, 2010 at 06:40 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

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, 2010 at 01:03 PM Hill
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 11, 2010 at 10:00 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

It's indeed meaningless for single table query. I will try to find a better example to demonstrate. I appreciate your help very much!
Apr 12, 2010 at 01:10 PM Hill
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x246
x128

asked: Apr 11, 2010 at 06:31 AM

Seen: 1427 times

Last Updated: Apr 11, 2010 at 06:31 AM