Situation Good index management is key to success. Having too many unused indexes around slows down your system. It is not difficult to find them or defining the criteria for dropping them. Here is how you retrieve them: [crayon-5c511003ea174228722918/] The WHERE clause contains a filter that only shows the indexes for the current databases and only those indexes that are nonclustered, are non unique, is not a constraint and not a primary key. You can adjust that for your situation. Result The result is a list of DROP and CREATE statements that you can copy and execute in your database. The good thing about this is, if you save these results in an Excel file and mark the indexes that you’ve actually dropped, then you have managed your index maintenance. At some point in time, you want to have the original index back and that’s where you can use the CREATE statement. We especially pay attention to the data in the yellow marked area. For example If the userseek, userscan, userlookup is 0 and your sql server is already running for some time, we could drop those indexes. If the userseek, userscan, userlookup is low (<1000) and userupdates is a magnitude of that you could choose those indexes to be dropped the next time you do some index maintenance. You will always have the CREATE statement in your excel. Save your excel file with a date and time of the maintenance you have done and you will build up a nice history. Excel files older than 6 months can be discarded. About us Over the years, SQLTreeo gained precious know-how which we transformed into customer support, managed database services and database solutions. In order to express our knowledge and experiences, we tend to work with anything less than the best-trained staff and that is the key to success and the biggest asset of our company. Thus, all our database management services are controlled by high level skilled and Senior database administrators. We can help you in every aspect of SQL Server.
The post Finding Unused Indexes appeared first on SQLTreeo.
