karthik M (1/21/2013)
All,I found some wierd thing today when i ran the below two
version of queries.
select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10)
scan count = 10
select * from sub_fund
where sub_fund_id in (1,2,3,4,5,6,7,8,9,10,11)
scan count = 1
how? It should be 11 right.
Not necessarily. Without the execution plan it's a guess, but I'd guess that the first one ran as an index seek. 10 seek operations (for 10 values). The second ran as a scan, a single table scan.
You're better off ignoring the scan count. It is not a count of the number of times a table was scanned. I've seen cases where the scan count was 0 despite the table being accessed. Use the logical reads as a measure of the amount of data read.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability