It is 10 PM, do you know where your pages are?

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: