blog.sqltreeo.com
Finding Unused Indexes - SQLTreeo
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-5d58166a23b66915933220/] 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 (