Post reply

Table scan got increased by almost 100%

  • April 22, 2009 at 1:17 am

    #133077

    HI...

    I added nonclustered index to table column which was taking lot of reads.

    After adding the index logical reads become minimal.

    But lots of table scan happening...

    before adding index

    ------------------

    Scan count 2, logical reads 8928

    and after adding index

    ---------------------

    Scan count 96, logical reads 192

    plz help me....

  • April 22, 2009 at 2:05 am

    #981988

    Scan count != Table scan.

    Without seeing the exec plan, I can't say for sure but I'm guessing that you now have a nonclustered index seek and around 90 bookmark lookups. The IO stats for the query after the index look very good. Is it faster?

    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

  • April 22, 2009 at 2:23 am

    #981998

    Its become bit faster after adding index..

    plz find the attached file..

  • April 22, 2009 at 2:37 am

    #982004

    Plan looks pretty good.

    The scan count does not indicate the number of times the table was scanned. In fact, it's a pretty hard to interpret number as different query operators log different numbers of 'scans'

    Your IO is low and the query is faster. That's what's important.

    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

  • April 22, 2009 at 2:49 am

    #982009

    Hi Gail,

    If I execute the same query in some good server, then will table scans get reduced..?

  • April 22, 2009 at 3:14 am

    #982024

    You don't have table scans, that's what I'm trying to say.

    The Scan count does not count the number of table scans. It should be the number of times the object is touched at all, but it's not accurate even at that

    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

  • April 22, 2009 at 3:28 am

    #982029

    Thanks Gail............:-)

    Thanks for ur reply..

Viewing 7 posts - 1 through 6 (of 6 total)

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