SQL SERVER – Generate Report for Index Physical Statistics – SSMS

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Fri, 26 Mar 2010 01:30:54 +0000 Indexed on 2010/03/26 1:33 UTC
Read the original article Hit count: 1384

Few days ago, I wrote about SQL SERVER – Out of the Box – Activity and Performance Reports from SSSMS (Link). A user asked me a question regarding if we can use similar reports to get the detail about Indexes. Yes, it is possible to do the same. There are similar type of reports are available at Database level, just like those available at the Server Instance level. You can right click on Database name and click Reports. Under Standard Reports, you will find following reports.

  • Disk Usage
  • Disk Usage by Top Tables
  • Disk Usage by Table
  • Disk Usage by Partition
  • Backup and Restore Events
  • All Transactions
  • All Blocking Transactions
  • Top Transactions by Age
  • Top Transactions by Blocked Transactions Count
  • Top Transactions by Locks Count
  • Resource Locking Statistics by Objects
  • Object Execute Statistics
  • Database Consistency history
  • Index Usage Statistics
  • Index Physical Statistics
  • Schema Change history
  • User Statistics

Select the Reports with name Index Physical Statistics. Once click, a report containing all the index names along with other information related to index will be visible, e.g. Index Type and number of partitions.

One column that caught my interest was Operation Recommended. In some place, it suggested that index needs to be rebuilt. It is also possible to click and expand the column of partitions and see additional details about index as well. DBA and Developers who just want to have idea about how your index is and its physical statistics can use this tool.

Click to Enlarge

Note: Please note that I will rebuild my indexes just because this report is recommending it. There are many other parameters you need to consider before rebuilding indexes.

However, this tool gives you the accurate stats of your index and it can be right away exported to Excel or PDF writing by clicking on the report.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Filed under: Pinal Dave, SQL, SQL Authority, SQL Index, SQL Optimization, SQL Performance, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, SQL Utility, T SQL, Technology

© SQL Authority or respective owner

Related posts about Pinal Dave

Related posts about sql