SQL Server Quickly Diagnosing Scans

Too often code gets released in production where the access path is a scan, as a DBA these are fairly easy to spot, without looking at a plan, but by simply looking at the amount of reads over a small amount of calls.  The key, no pun intended, or in this case the anti-pun is to watch reads slowly increase over time.  This is because if a query is scanning a table, it will do a consistent amount of reads, for example if a table has 100 data pages in it, then every call will show 100 reads, as data is inserted the table will grow to 101, 102, 103 data pages, and the amount of reads of your query will grow linear. The point is look for linear growth, if a query consistently stays at 15 reads, without growth, it’s using a btree index properly.  Let’s look at an example here.  Below I’ve setup a table so that each row is one datapage.  I’ve intentionally have not indexed FieldTwo, which we’ll query off of to force a scan.  I’ve also made FieldThree 8K, so each addition will add one read in the query

use AdventureWorks2012

go

create table TableOne

(FieldOne int identity(1,1) Primary key,

FieldTwo int,

FieldThree char(8000))

Declare @interval int

set @interval = 0

while @interval < 10000

Begin

insert into TableOne(FieldTwo, FieldThree)

Values (@interval, 'Hello')

set @interval = @interval + 1

end

If we run the following query, notice the amount of reads (10038) and that it uses a scan by examining the messages tab.

set statistics io on

go

select * from TableOne

where FieldTwo = 10

Table 'TableOne'. Scan count 1, logical reads 10038, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The following query loops inserting 1 run, and running the same select query again looking at the messages you can see the linear growth.  I’ve pulled out a lot of the other information provided, so we can simply see the results of the select statement.

Declare @interval int

set @interval = 10001

while @interval < 10100

Begin               

insert into TableOne(FieldTwo, FieldThree)

Values (@interval, 'Hello')

Print 'Running Select Statement'

select * from TableOne

where FieldTwo = 10

set @interval = @interval + 1

end

Table 'TableOne'. Scan count 1, logical reads 10054, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TableOne'. Scan count 1, logical reads 10055, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TableOne'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TableOne'. Scan count 1, logical reads 10056, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TableOne'. Scan count 1, logical reads 10057, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TableOne'. Scan count 1, logical reads 10058, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

.

So let’s fix the query and put an index on this, so we can use a seek. 

create index aaa on tableone(fieldtwo)

select * from TableOne

where FieldTwo = 10

(1 row(s) affected)

Table 'TableOne'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ok we now have our seek and have gotten the reads down to 5.  Great works as it should, but let see what happens as we add data by rerunning the query above, and see if reads change. 

Declare @interval int

set @interval = 20001

while @interval < 20100

Begin               

insert into TableOne(FieldTwo, FieldThree)

Values (@interval, 'Hello')

Print 'Running Select Statement'

select * from TableOne

where FieldTwo = 10

set @interval = @interval + 1

end

Running Select Statement

(1 row(s) affected)

Table 'TableOne'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If you go through all the messages you’ll notice it maintains 5 reads for every select statement, this is because it properly traverses the levels (2 in this case) of the index.  Note the messages still say scan but it’s not a true scan, it actually a range scan which is a good thing.