Unanswered: SQL Server 2008 Index Physical Statistics Report
I am trying to open the standard sql server report 'Index Physical Statistics Report' i.e. open management studio --> right click database --> report --> standard database --> Index Physical Statistics Report.
The report opens but displays 'please wait while data is retrieved from the server'. Its been displaying this for 45 minutes now!!
The database against which I need the report is 1TB in size with 8 data files and 1 log file. I don't see any spike on CPU or memory usage , everything seems to be running fine on the server.
SQL profiler doesn't display anything weird.
If I run the same report against another DB which is relatively of very small size I get the outputs immediately (in less than 30 seconds).
Any idea why does this report take too long? is there a simple query I can run to get this information? or can I schedule a job to run this report and place it in a drive as XLS or PDF>
The report is likely hammering the heck out of the disks. The function that the report uses (sys.dm_db_index_physical_stats) will read every page of the table in question (in this case, all of them), then return with a report. You may be able to track the request in sys.dm_exec_requests. The command may show up as "DBCC".
I think the query scope for 'index physical statistics report' is too big. ( unless you want to reorg and rebuild indexes both small and large in size/pages). For large DBs, my query scope is simpler because I only intend to rebuild large indexes which are >30% fragmented and larger than 1000 pages. you can google for the sql script to do this and it should return a faster result than 'index physical statistics report'.