Is a non clustered index getting always scanned useful?

  • Hi All

    I created a NC index as suggested by missing index DMV(of course I don't create them blindly). This one seemed to be a useful index but I now see from index usage stats that it only got scanned 50 times in 4 days.No seeks, no lookups. So is it a good idea keeping such index.The table on which this index is created is used more for reads and less for writes. But yes, there are significant writes, so I won't like to keep it if it is not helpful in reads. Because I pay a price in terms of writes if I keep it.

    Pls suggest.

    thanks

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • I'm not a fan of missing index reports unless I have really drilled in deep to the queries touching a table. You need to look at the queries accessing the table. If the SELECT statement includes columns not in the index either as part of the key or part of the INCLUDE clause then the query has to do lookups to get the additional columns. Lookups are expensive so SQL will decide whether its more efficient to use the non-clustered index with lookups or just do table scans. A scan could be a good thing is the query needs to look at all rows and a small number of columns that are all included in the index.

  • Without seeing the queries involved, I'm just guessing.

    But, it is possible that eve if it's just getting scanned, it's still useful. Let's say your clustered index consists of 1000 pages and your non-clustered index consists of 500. If the optimizer recognizes that either one could satisfy the query, but both are going to be scans, it's going to scan the smaller index because that will have a lower cost for the query.

    I'm not saying that's the case here. I don't know anything of the situation to make that kind of assessment.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I'm surprised that discussions of whether or not to keep an index seldom include a look at what is generating the query using the index and how important speed is in that context. I would think that if the query is run by a person looking something up while a customer is waiting on the phone, then speed is very important and you'd keep the index. If the index is used only by a report running in the middle of the night and the users of that report don't care if the report takes 5 seconds or 5 minutes to run, then you're better off dropping the index and not taking the performance hit on inserts and updates.

    Thoughts?

  • dan-572483 (3/16/2015)


    I'm surprised that discussions of whether or not to keep an index seldom include a look at what is generating the query using the index and how important speed is in that context. I would think that if the query is run by a person looking something up while a customer is waiting on the phone, then speed is very important and you'd keep the index. If the index is used only by a report running in the middle of the night and the users of that report don't care if the report takes 5 seconds or 5 minutes to run, then you're better off dropping the index and not taking the performance hit on inserts and updates.

    Thoughts?

    Well yeah. That's why I said, without seeing the queries and how they're used, I'm just guessing.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks for the advice Grant!

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Just to add my 2 pence, did you know that you can query the dmv's and retrieve all the queries where the plans include operations on the index. So you should actually get the queries and see why they are scanning that index.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

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

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