SQL Server Management Studio Standard Reports – Index Usage Statistics: [DatabaseName]

I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today we’re in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears, click the Standard Reports, and then select the title at the top of this post.

This report is very useful – you can use it to tune your system, plan your space layout and your maintenance. It shows information about the indexes on your tables, the types they are, and how often they are accessed. If you want more information about indexes in general, check here. Indexes are accessed in multiple ways – there are scan operations, which read the index from the top to the bottom, seek operations, which look for a specific entry or a range of entries, lookup operations, which look for a specific entry and so on. In addition to the way the index is used, the type of index is important as well. This is a great reference to read about which query type will use which type index.

The first band in the report groups the information by the Table Name:

Column	Description
Index Name	The name of the index. System-naThe last timestamp of the user seek activitymed indexes usually start with either IX_ or PK_
Index Type	Whether the index is CLUSTERED, which means it is physically arranged in the order defined in the index, or UNCLUSTERED, meaning that the index is not physically arranged by the order defined in the index. You can have one clustered index per table. Read this to find out more
# User Seeks	The number of seek operations on the index caused by user activity
# User Scans	The number of scan operations on the index caused by user activity
# User Updates	The number of updates on the index caused by user activity
Last User Seek Time	The last timestamp of the user seek activity
Last User Scan Time	The last timestamp of the user scan activity
Last User Lookup Time	The last timestamp of the user index lookup activity
Last User Update Time	The last timestamp of update operations caused by user activity
# System Seeks	The number of seek operations on the index caused by system activity
# System Scans	The number of scan operations on the index caused by system activity
# System Updates	The number of updates on the index caused by system activity
Last System Seek Time	The last timestamp of the system seek activity
Last System Scan Time	The last timestamp of the system scan activity
Last System Lookup Time	The last timestamp of the system lookup activity
Last System Update Time	The last timestamp of update operations caused by system activity