Back in April 2020, I created an open-source project called "SQL Server Page Allocation Reports". It consisted of a set of SQL queries and some Power BI reports that can be used for visualizing the size and locations of your data and transaction log pages. Well, recently I also added SSMS Custom Reports into the mix. So, it's time to revisit this project and see what's new!
What is this?
In case you're new to this project, let me try to explain what this is all about (if you're already familiar, then you may skip to the next heading).
This project is intended to help SQL Server DBAs understand the contents of their database files using the most universal human language in existence: visuals.
The best use for this collection of reports is for the DBA to quickly and easily understand how their database pages behave, why, and where. It can be useful for understanding the physical impact of various database changes (such as index creations, rebuilds, defragmentation, shrinks, compression, etc.), and also for anticipating the physical impact of such planned changes.
For example, let's say that you have a database file that's 40% unused, you plan on performing a huge index rebuild, and you want to know whether this rebuild will require significant file auto-growth, or would it be able to utilize unused space within the file. By seeing the way pages are currently allocated/used/unused within your file, you could (more or less) predict the answer to your question.
These reports can also be very useful for educational purposes, as part of webinars, demos, presentations, and lectures that touch upon the topic of SQL Server file architecture. For example, for the presenter to demonstrate the physical impacts of an index rebuild or other database operations, or to explain the workings of different page types (GAM, IAM, PFS, etc.).
What's new?
As I said at the beginning of this post, until recently the project only included Power BI reports and some SQL queries that can be run manually. But now, I also added a set of Custom Reports, created in Reporting Services, which you can use directly in SQL Server Management Studio.
Granted, they're not as "versatile" as the Power BI reports, since SSRS lacks the real-time interactivity that Power BI offers. But they could definitely be useful when you don't have the luxury of making new Power BI installations in your production environment. I did try to make them at least semi-interactive by implementing a pseudo-zooming mechanic. It's nothing ground-breaking but it works quite well.
Also, I made a clearer distinction about each report and which minimal SQL Server version it supports, simply by renaming them and placing them in separate projects:
FileAllocationReports_SQL2012
Data File Allocation Map - Overview (SQL2012 and newer)
Data File Allocation Map - Detailed (SQL2012 and newer)
Data Page Consecutive Allocation Map (SQL2016SP2 and newer)
Object Allocation Summary (SQL2016SP2 and newer)
Transaction Log Allocation Map (SQL2008 and newer)
FileAllocationReports_SQL2016
Data File Allocation Map - Overview (SQL2019 and newer)
Data File Allocation Map - Detailed (SQL2019 and newer)
Data Page Consecutive Allocation Map (SQL2016SP2 and newer)
Object Allocation Summary (SQL2016SP2 and newer)
Transaction Log Allocation Map (SQL2016SP2 and newer)
Naturally, the reports supporting the newer versions of SQL Server could offer more accurate results as they utilize newer and better system catalog objects.
Check out the screenshots gallery below:
Where to get it?
It's available for download right here, in this GitHub repository:
You can download the latest release from the "releases" page.
The downloadable assets from the release are differentiated by the minimum supported SQL Server version, and they contain the pre-built RDL files.
In the source code, the relevant SSRS Custom Reports are located in the subfolder called "ssrs-custom-reports".
How to use it?
1. Once you download the latest release relevant to your SQL Server version, extract the zipped archive to someplace on your computer. Please note that this location will be remembered by SSMS when you start using them.
2. Open SSMS and connect to your favorite SQL instance.
3. Right-click on a database and select Reports > Custom Reports...
4. Browse to find the RDL files you unzipped. Start with the report whose name starts with "Data File Allocation Map - Overview" and open it. For example:
5. This will present you with the main data allocation overview report. From there you can "zoom in" to any specific range of pages in your data files by clicking on it, and "move" to the ranges left or right by clicking on the blue-ish arrows on the sides (after you zoom in).
6. Once you zoom in "close enough" then you'd be presented with the "Detailed" report which also includes a breakdown by objects and indexes.
There are a few more reports in the archive, used for different purposes:
Object Allocation Summary
This is a summary report showing a breakdown for a specific object (i.e. a table), based on its different indexes and data page types.
This report only works when right-clicking on a specific table or index.
Data Page Consecutive Allocation Map
This is a database-level report that shows the largest consecutive data pages, whether they're empty or in use. Click on such a range to see a detailed breakdown of it.
In the SQL 2016SP2 and newer version of the report, you can also see a data map just below the bar chart:
Hover over a rectangle to see its page range in the tooltip, and click on it to see the detailed allocation report for this range.
Transaction Log Allocation Map
This is a simple database-level report used for visualizing Transaction Log VLFs (Virtual Log Files).
You can use it to see how many VLFs you have in your database, what are their sizes, offset, and what's their status (active/unused).
Conclusion
You may notice that there's also a TEMPLATE.rdl file for each of the versions. That's just something small you could use to create your own Custom Reports for SSMS. It contains the basic parameters supported by SSMS, some basic elements, a header, and a footer for the report.
Anyways, I hope you'll find this useful. I believe these Custom Reports are more accessible and easier to use than their Power BI counterparts, even though each has its advantages and disadvantages.
Don't forget that this is an open-source project in GitHub, and as such, you can feel free to submit issues if you detect bugs or want to submit feature requests.
Enjoy!