Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2014
    Posts
    5

    Unanswered: SQL Server 2008 Index Physical Statistics Report

    Dear Members,

    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>


    Thanks,

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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".

  3. #3
    Join Date
    Mar 2014
    Posts
    5
    thank you MCrowley, the report took almost 2 hours to run.

  4. #4
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    hi,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •