Management Studio Reports – Part 2 – Disk Usage Report

Management Studio Reports – Part 2 – Disk Usage Report

Rate This
  • Comments 10

One of my favorite reports is Disk Usage.  It prepares two useful graphs breaking down the MDF and LDF space usage.  It also provides information about disk space used by tables, partitions, and indexes.  This data can also be exported to Excel so you can perform your own calculations.  (To export, right-click on the report and choose Export à Excel)

The second reason I’m blogging about this report is because we’ve received extensive feedback from customers that it does not perform well on mid-high end servers.  There are three main issues:

1)      Heavy queries

One of Dynamic Management Functions that we use is sys.dm_db_index_physical_stats, which “returns size and fragmentation information for the data and indexes of the specified table or view”.  If you have many tables (>100) in a single database this function can take a while to aggregate the data.  There will also be a lot of information that needs to be sorted and grouped.

This is how we call the DMF:

sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'SAMPLED')

According to Books Online, “… the SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap…”.  Running this report on larger databases can cause non-trivial increases in CPU usage, memory consumption, and disk activity.

2)      ReportViewer control rendering

The rendering engine of the ReportViewer control is somewhat slow.  When dealing with large amounts of data, it can take a while for a report to export to Excel.  This issue is out of my team’s hands, but the Reporting Services team knows about the issue and will prioritize it accordingly.

3)      Excel does not like merged cells

When we designed the report, we wanted to make it look visually appealing.  We overlapped and merged some of the cells, which looks great (in my opinion)… but when you save it as an .XLS file and load it with Excel, it can take up to 10 minutes to load!

Great, so now I’ve told you how this report is useful… yet painful in some situations… what now?  Well, I’m going to empower you to write your own reports.  Attached to this blog post is a Visual Studio project that contains both the raw .RDL that we run in Management Studio, as well as a report that I fixed up a little bit that should run with the AdventureWorks sample database.  Feel free to take these reports and run them in your SQL Server 2005 Reporting Services environment.

NOTE: THESE REPORTS WILL NOT RUN IN MANAGEMENT STUDIO.  These reports will run in the ReportViewer control and in your SQL Server 2005 Reporting Services environment.

Help Us Help You
I’m a firm believer that every project that I work on should be validated by our users.  Please answer the following questions if you’d like to improve our future products:

1) Is Management Studio Reports a useful topic?
2) Should I continue releasing reports as .RDL files on this blog?  If so, what report would you like to see next?
3) We’re thinking about re-designing some reports in Management Studio and making some new ones… Which reports do you find useful? Which reports are not useful?  What would your ideal report contain?

If you make something cool and want to help others, or just show it off… add a comment to this blog linking to your site so that others who come here can see what you've done!

-Paul

Attachment: Disk Usage.zip
Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
  • Hi, I didn't step deep into 2005, but I'd like to make some comments.
    1) It's useful, at least the thing I tried to have from 2000.
    2) Of course, they also shed light on .rdl making, thanks!!!
    3) Is it possible to have index usage statistics report (without using profiler)?
  • To answer Paul's questions:
    1. Yes but I would go deeper to DMVs
    2. Yes (all if possible)
    3. Since DMVs are one of the source for reports, having DMVs support I would add more reports for components (Service Boker, Replication, Database Mirroring, CLR etc).
  • Hey Paul,

    This is very useful not only from a dba administrative perspective, but also from an instructional / sample POV.   I would like to see as many of these as possible so they can be integrated into our report web server.  I believe there is real value in creating some data driven or time driven subscriptions.

    Answers:
    1) Yes, absolutely
    2) Hell Yeah!!!  Keep 'em coming!
    3) So many things come to mind:
    a) Audits - logins (successful and failures), auth. type, application used to connect, host connected from, etc...
    b) Security - members of server roles, databases, database roles, explicit table access, mapping a user to all their access or mapping an object to all who can access it
    c) Security History - changes to permissions
    d) Maintenance Plans / SQL Agent Jobs - when and what, history successfule
    e) Alerts History and Notification

    thats enough isn't it???
  • Paul,

    I'd like to see the whole Management Studio Report set released as a package.  There are report packs for Integration Services, Sharepoint, Great Plains, IIS. Why not SQL?

    Also, the Backup and Restore Events report doesn't seem to work. What up with that?

    Go Bucks!!!
  • We are new to SQL2005 and are very used to the taskpad display in SQL2005.
    Running SAP on SQLServer, we have very large databases with very many tables and indexes.
    One of the first things is the bad performance of the Disk Usage Report in SQL2005 Management Studio.
    So I'm happy to have found this BLOG.
    Looking forward to try out the attached report...
  • I do like the reports in SSMS and I do find them useful.  Also, please keep releasing RDL files so that we can run them outside the studio for DBA support.  However, it would be great to be able to integrate our own custom reports in SSMS.

    I can't think of any specific examples, but maybe index usage history for specific procedures so that as data grows or the model changes and we are modifying the procedure, we can see, historically, how that plan might have changed.
  • This blog is a great input for my daily work. Thanks!
    For managing servers I've designed two additional reports to get an overview.

    First there is the top ten of the databases (used/unused space data + log) compared with the information provided by xp_fixeddrives. So I can see the disk space and the biggest databases at first glance.

    My second one is based on all the jobs scheduled on regular daily basis. So I can see which is the best time for a planned downtime or when are to many jobs in the night concurring for cpu.

    I will upload the reports on a german mvp-site and come back to tell you the url.

    Kind regards,
    Christoph
  • Here you can download my additional reports. It includes as a benefit the corrected version of 'Backup and Restore Events'.

    http://www.insidesql.de/component/option,com_remository/Itemid,0/func,fileinfo/id,62/

    The whole (german) article is available here:
    http://www.insidesql.de/beitraege/reporting/cm_reports_im_management_studio.html

    Please leave a comment there if you are satisfied.

    Kind regards,
    Christoph
  • How about a report that returns the same data from the DBCC command in SQL2000 "dbcc memusage(names)"  ?? Is this data available from the DMF already?

  • Is there a way to schedule these report so that in a particular time every day the reports get generated and exported to PDF/xls and saved in a given folder.

Page 1 of 1 (10 items)