August 13, 2014 at 7:57 am
Hi ,
I need help on indexes, please view the attached file and let me know which index Should we keep in our database and which one needs to be removed.
PLEASE It's urgent... (( attached file is showing stats of index usages based on sys.dm_db_index_usage_stats DVM))
I really appreciate your quick response.
August 13, 2014 at 8:08 am
From a single glance, very dangerous to guess.
To be sure, you need to record the values from that DMV on a regular basis for at least a month and preferably a full business cycle (including year end). The DMV is only valid since last server start (which I have no idea when it was).
Why is this urgent anyway?
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
August 13, 2014 at 8:21 am
Yah, you are correct and thanks for the quick reply, these indexes were added to the one of our customer’s database that is having issue in performance in May. So, don’t worry about that we didn't restart our SQL sever since than.
Further, we are in a hurry and reason of this urgency is we need to make decision and then add or drop these indexes which we have created only on the customers database (based on the usage) but they are not part of our MAIN Database. So, just to sync the schema Index wise AND to release the latest version of new Database “Which have no schema changes in indexes ” its urgent.
Please have a look at the file and guide me .Thanks for the reply and help once again.
August 13, 2014 at 8:34 am
Former post content:
Attachment is no good...
Current post content:
Never mind... I did it wrong...
Sorry, folks....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 13, 2014 at 8:34 am
From a quick glance, drop them all and then do a proper index analysis and tuning exercise that doesn't involve creating everything the missing index DMV says. You've got lots and lots of duplicate indexes in that list that shouldn't have been created in the first place.
In many places you have one index which is a subset of another. In other places you have created completely duplicate indexes (exact same set of columns). I hope that the columns are in different orders (the spreadsheet doesn't say). If not, those indexes are a waste of space and resources.
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
August 13, 2014 at 8:55 am
Thanks for the reply Gila , please view the attached create index script of all these indexes , you can create them in any of your test db or at least have a look at those. Also view the script which I execute on the Customer database in order to find the Index usage, I should have share those in the first post but please review them then you will have the clear picture.
August 13, 2014 at 9:06 am
You definitely have redundant indexes. Here's just one example
CREATE INDEX [missing_index_133_132_PhoneLink] ON [LP_LIve_Data].[dbo].[PhoneLink] ([CustomerID]) INCLUDE ([ID], [VendorID], [PhoneID], [PhoneTypeID], [DefaultPhone], [LocationID], [ProgramID]);
CREATE INDEX [missing_index_135_134_PhoneLink] ON [LP_LIve_Data].[dbo].[PhoneLink] ([CustomerID]);
You, or whoever created these, really need to go through these indexes, check for duplicates or redundant indexes and decide on a case-by-case basis whether that index should even exist, based on the stats and on your knowledge of how the system works. This goes double if the indexes were created by running a script over the missing index DMVs, which is what it looks like. Missing indexes are suggestions, should never be created blindly.
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
August 13, 2014 at 9:12 am
Yah I know we have some redundant indexes which was not created by me but one of the person from development team have created those indexes using missing index DMVs on the LIVE customer database BLINDLY :crying: without consulting me and now they are asking me to do my analysis on them . Weird isn’t it?
but a part form that I am trying my best to help them HELP which is required from you is that if you'll look at the Index ( missing_index_41_40_LineItems ) then it has 34327 seek, 707 scan and 4070 lookups. What do you suggest that I should add it to my db or not??
I mean I am really confuse on the STATS that how many Seek , scan are ok to add that specific index in the DB or if an index have 11438 lookups but less or no seek and scan then should I DROP that index or not??
Please suggest anything any one?? please help 🙁
August 13, 2014 at 9:35 am
please view my latest reply and help me please 🙁
August 13, 2014 at 9:44 am
Sana4u (8/13/2014)
HELP which is required from you is that if you'll look at the Index...
"Required"?
The only people who can "require" me to do some work are my manager and my paying clients.
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
August 13, 2014 at 9:59 am
🙁 ok
August 13, 2014 at 9:59 am
I was just asking help :crying:
August 13, 2014 at 12:33 pm
Sana4u (8/13/2014)
but a part form that I am trying my best to help them HELP which is required from you is that if you'll look at the Index ( missing_index_41_40_LineItems ) then it has 34327 seek, 707 scan and 4070 lookups. What do you suggest that I should add it to my db or not??I mean I am really confuse on the STATS that how many Seek , scan are ok to add that specific index in the DB or if an index have 11438 lookups but less or no seek and scan then should I DROP that index or not??
Please suggest anything any one?? please help 🙁
So you want to know if you should drop an index that is getting used quite a bit?
Seeks, scans, lookups all indicate that the index is in use.
But my curiosity revolves around you asking to add that index to your database. How are you figuring that the index is being used like that and then asking whether or not to add it to your database? Isn't it already there?
Is it because somebody named the index as "missingindex" and didn't do a decent job of giving it a name and description?
Evaluate your workload like Gail said. That takes a lot of time - it's not something you can do in a matter of seconds.
Review the indexes and remove the duplicate indexes - waste of space and resources.
Determine the useful indexes based on how much they cost to maintain (updates, writes) versus their use benefit (reads, lookups, scans, seeks). Keep the ones with an extreme benefit and toss the ones with all cost and no benefit. And then tune from there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply