SQL SERVER – Generate Report for Index Physical Statistics – SSMS

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.

SQL SERVER - Generate Report for Index Physical Statistics - SSMS indexphys1

SQL SERVER - Generate Report for Index Physical Statistics - SSMS indexphys2

  • 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.

SQL SERVER - Generate Report for Index Physical Statistics - SSMS indexphys3

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.

SQL SERVER - Generate Report for Index Physical Statistics - SSMS indexphys4
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.

SQL SERVER - Generate Report for Index Physical Statistics - SSMS indexphys5

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.

Update: As Per Paul Randal this is un-killable and lots of IO, be careful about using it.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Out of the Box – Activity and Performance Reports from SSSMS
Next Post
SQL SERVER – FIX : ERROR : 4214 BACKUP LOG cannot be performed because there is no current database backup

Related Posts

10 Comments. Leave new

  • Just a thought, when you wrote:
    “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.”

    I guess you really meant to say:
    ” I will NOT rebuild my indexes just because this report is recommending it.”

    I know, obvious, but anyway, just to be correct.

    Great site btw, helped me lots of times!

    //Richard

    Reply
  • Venky Subramaniam
    December 1, 2010 10:25 am

    Hi Pinal,

    Is there anyway one can find when and if re-indexing/re-organization was done on the tables in a database? Assume that there is no scheduled job that does this activity (which I can derive from the job’s history), how can I find this information. I have looked at the DMVs and catalog views but none of them (unlike Oracle) to the best of my knowledge has any record of the indexing process having occurred on (a) table(s)…

    Any hint or suggestion would be helpful
    Thanks,
    Venky

    Reply
  • hi sir,

    i run this report and found out that there are some tables need to be rebuilt and reorganize.

    i did the “operations recommended” thing which is rebuild and reorganize indexes.

    after running the task (1. check db integrity, 2. reorganize, 3. rebuild and 4. update statistics takes 26 hours) i re-run the report again and in “operations recommended” i still see the rebuild and reorganize…

    whole database (rebuild and reorganize) : not specific table/s.

    questions are:

    1. is this report accurate?
    2. why did i see the rebuild, reorganize again in the operations recommended column?
    3 what do you mean by: “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.”

    thanks…

    josh

    Reply
  • When I try and access the index reports, I receive the error “Incorrect syntax near ‘(‘” Any idea what this error is all about and how to correct it?

    Reply
    • Post the actual code you have used

      Reply
      • Thank you. I subsequently figured out that our database is set to run under campatibility mode of 2000 and reports are only supported at 2005 or higher. Now I just have to find time to figure out the implications of switching to 2005 or 2008. :o)

  • Diyyala Satish
    August 5, 2011 3:46 pm

    Hi ,

    I have rebulid index on entire database,Update statistics after generate report it show rebuild/reorganise

    Reply
  • I looked at this but I remember back that there was a query that you can run, (DBCC?) that you can run that gave suggestions on what columns to index, or am I thinking wrong

    Reply
  • When I try to run Index Statistic report and Index physical statistic it give me error. Error: Incorrect syntax near ‘)’.
    What might be the error.

    Reply
  • I’m using SSMS 2016. I’m comparing this standard report Index Physical Statistics to the a query result from the function sys.dm_db_index_physical_stats. The results don’t match up. Any idea why? I’m using ‘Detailed’ method on the function. Could the standard report be using another method?

    Reply

Leave a Reply

Menu