In SQL Server Mangement Studio there are some built-in reports, but they do not cover everything you might want to know about your server, databases, tables, indexes or statistics. In addition to the built-in reports, I will create some useful custom reports for SQL Server 2012, and my 7:th report is the ”Index usage” report. This report will show information about:
- How the indexes have been used (in percentage) since last restart
- When was the Last scan
- When was the Last seek
- When was the lookup
- When was the update
- How many rows and pages there are in the index
This statistics will give you a hint of how frequently used the indexes are. As you only benefit from the read operations of an index, you’ll need to know if you have any reads or if the cost of the updates are too high. You can also get a clue if you have choosen the right clustered index, as you can see how many percent seeks you have, compared to the lookups.
Download SQL Server 2012 Management Studio Report at: SQLServiceMonitor_Index_usage.zip
I will post all my custom reports at this link : www.sqlservice.sesvstartssmscustom_reports.aspx
If you have any feature requests, feel free to send me a tweet.