Management Studio Reports – Part 2 – Disk Usage Report
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
Comments
Anonymous
July 11, 2006
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)?Anonymous
July 12, 2006
The comment has been removedAnonymous
July 19, 2006
The comment has been removedAnonymous
July 21, 2006
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!!!Anonymous
August 23, 2006
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...Anonymous
August 28, 2006
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.Anonymous
September 11, 2006
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,
ChristophAnonymous
September 15, 2006
The comment has been removedAnonymous
October 20, 2006
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?Anonymous
October 16, 2012
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.