Post reply

ScanCount Difference

  • September 20, 2013 at 6:27 am

    #298121

    See below query, i have read this scenario at many place but couldnt collect it .Please explain Why the ScanCount is different

    CREATE TABLE ScanCount (Id INT IDENTITY(1,1),Value CHAR(1))

    INSERT INTO ScanCount (Value ) VALUES ('A') ,('B'),('C'),('D'), ('E') , ('F')

    CREATE UNIQUE CLUSTERED INDEX ix_ScanCount ON ScanCount(Id)

    SET STATISTICS IO ON

    --Unique clustered Index used to search multiple value

    SELECT * FROM ScanCount WHERE Id IN(1,2,3,4,5,6)

    --Unique clustered Index used to search multiple value

    SELECT * FROM ScanCount WHERE Id BETWEEN 1 AND 6

    drop table ScanCount

    (6 row(s) affected)

    Table 'ScanCount'. Scan count 6, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (6 row(s) affected)

    Table 'ScanCount'. 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.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 20, 2013 at 6:47 am

    #1651893

    Ignore the scan count. It's not the number of times the table has been scanned. It's not consistent.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 20, 2013 at 7:17 am

    #1651915

    GilaMonster (9/20/2013)

    Ignore the scan count. It's not the number of times the table has been scanned. It's not consistent.

    thanks for reply.

    But is this difference because of IN and BECAUSE clause ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 20, 2013 at 8:23 am

    #1651943

    Have a look at the execution plans if you want to see how the two queries are run differently. Ignore the scan count, it does not give you particularly useful information.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply