Alright, up to the last of the changes I’ve made to the index analysis query that I had planned. This makes the fifth post in the series on this query.
The previous posts in this complete series are:
A while back I was using the index analysis query at a client, I couldn’t find the most recent version of the query. It can happen when you make a tweak at one client, and then another at a different client. Soon there were a few versions floating around and the one that is the most “correct” is became harder to identify.
To help prevent this, I had re-written the query as a stored procedure. That way it can be dropped into the master database. By being in the master database and having the sp_ prefix, then the procedure can be accessed from any database on the SQL Server instance.
Below are two links that can be used to build the stored procedure. One for SQL Server 2005 and the other for SQL Server 2008/R2.
sp_IndexAnalysis for SQL Server 2005
sp_IndexAnalysis for SQL Server 2008+
If you have any comments or suggestions, please leave them as a comment. I consider this a work in progress to help provide a method to get good information on indexes with very little effort.