Post reply

Index with zero seeks and thousands of scans

  • October 29, 2012 at 9:38 am

    #147441

    I have reviewed several of the blogs and posts and have a simple question about index seek vs. scan ratio

    I have one particular index (IX_9 in attachment) that has zero seeks and 600K scans since that last refresh. So based on what I am seeing this index is always scanned completely when it is touched and of course gets all of the updates. I have disabled IX_22 since and saw a jump in my query efficiency and want to see if this addition index could possibly be a problem.

    Is this index slowing down my look ups?

    Thanks and my apologies in advance if this question has already been asked and answered, I just have not found it yet 😉

    Be a well of knowledge . . . Not a fire hose. Those who appreciate your knowledge will come to you with a bucket and get what they need. "Drenching" others with your knowledge however, will cause them to look elsewhere.

  • October 29, 2012 at 10:05 am

    #1553787

    i don't see index_id = 1 , which would be the clustered index.

    I see index_id 0, which means the table is a heap, and all the downsides that entails.

    what columns are involved in index#9?

    we'd have to start looking at individual queries as to why you are getting seeks vs scans;

    my first guess, assuming you are expecting a SEEK based on a query you know about may be non-SARG-able queries, which requires a conversion, whether explicit (using a myFunction(ColumnName) or Implicity, like IntegerColumn = '42' of the data before it can compare the value in a parameter.

    If you can provide a bit more details, we could help a little better.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 29, 2012 at 11:01 am

    #1553814

    Thanks for the prompt response.

    After taking a leap and disabling said index, all subsequent queries to this table slowed down tremendously.

    After rebuilding (and thus enabling) this index, the queries sped up.

    I am currently examining the stored procs to see which records are being hit.

    The ones that I have isolated to use the indexes to a degree but still have a large percentage of the cost for RID lookups. I will did deeper and update you soon.

    Again, much thanks

    Be a well of knowledge . . . Not a fire hose. Those who appreciate your knowledge will come to you with a bucket and get what they need. "Drenching" others with your knowledge however, will cause them to look elsewhere.

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

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