Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Sean Smith,
I've always been wary about dealing with indexes and indexing strategies. As I learn more and more about the subject I realize how little I actually know. On the surface indexing can appear quite simple and straightforward. But the reality is that indexing requires a great deal of knowledge, can differ from environment to environment, and is basically an art form.
A while back I was tasked with investigating performance issues within an application driven by a SQL Server database solution. It ended up coming down to improper indexing, but in the course of the investigation I started to notice other, unrelated, indexing issues. As I dug further I needed more and more data to properly assess each and every index in the database (I won't go into further details but the design was a disaster to say the least). By the time I had finished my investigation and applied my fixes I was left with a fairly robust script that lists out many details regarding each and every index contained within a database.
Below is a listing of all the fields in the output. It's somewhat overwhelming at first, but depending on what one column's output is will determine what additional columns you'll likely want to look at. I will try to explain how I evaluate the data though I won't go through all of the columns nor will I go into any extensive details as you should already have a decent understanding of indexes if you're going to be using the script for any type of troubleshooting and / or architectural restructuring.
NOTE: I do not claim to be an indexing expert in any way, shape, or form. I simply want to make available to others what I've learned as well as to share some code which might come in handy for individuals who find themselves in a situation similar to the one I experienced. Also, be very careful when playing around with indexes. What seems like a good idea / change could have an extremely negative impact!
Some Things To Keep In Mind / Look For Right Off The Bat:
Some Final Notes On The Script:
Any friendly feedback is always welcome. Enjoy!
Column and Row Total
Index on BIT column
Change the formula / other aggregated formula on the SSAS Total Column
Valid columns indicating column invalid