March 16, 2015 at 8:21 am
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.
March 16, 2015 at 10:27 am
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.
March 16, 2015 at 10:45 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2015 at 11:03 am
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?
March 16, 2015 at 11:06 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2015 at 7:55 am
Thanks for the advice Grant!
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
March 24, 2015 at 3:11 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply