July 1, 2014 at 8:29 am
Hi all,
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
Thanks for any help!
July 1, 2014 at 8:32 am
sys.indexes
http://msdn.microsoft.com/en-us/library/ms173760.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 1, 2014 at 9:16 am
Thanks. I managed to resolve the issue by importing my csv list into a table, then filtering sys.indexes to remove system indexes and any indexes included in my table, then just created the drop statements from a select statement against the results.
July 2, 2014 at 4:06 pm
You did review the stats SQL Server provides on index usage and missing indexes before you made your changes, right?
Edit: What indexes you think SQL will need/use and what indexes SQL will actually need/use can sometimes be quite different :-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 2, 2014 at 4:41 pm
I have had a look over the stats for some of the indexes, though at the moment it's not hugely relevant to why I was doing this. The database in question is a vendor db and they're looking into application performance issues but essentially said that they wanted additional indexes from the ones they expect to see removed.
Its just being tested on a copy of the production db in any case, so once this is ruled out I can re evaluate what indexes are and are not required, because everything else aside, I did notice a few which have been added previously which were quite large and going unused!
Thanks for the suggestion though! 🙂
July 2, 2014 at 8:32 pm
By far the biggest payback is in correcting clustered indexes if the wrong key column(s) were chosen for it. And that alone often allows very large numbers of other indexes to be removed, while at the same time drastically improving overall performance.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply