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