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’s new?
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.
Where to get it?
It’s available for download right here, in this GitHub repository:
github.com/MadeiraData/mssql-data-allocation-report
You can download the latest release from the “releases” page.
If you want to learn more about this project, please check out the full post published at the Madeira Data Solutions blog:
It is 10 PM, do you know where your pages are? | Madeira Data Solutions Blog