Microsoft SQL Server Page Allocation Reports

This open-source project aims to visualize your SQL Server data and transaction log file contents. It is currently built with Power BI, but there are plans for implementation in other technologies as well (you’re welcome to contribute!).

What is this good for?

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 files 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.

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 database operations.

Data File Reports

You can use these reports to easily see which pages are reserved for which objects, and which pages are not used.

Page Allocation (Compact)

This report displays your biggest continuous USED / EMPTY pages in your data file.

Each bar in this report represents a continuous range of pages, and its height represents the number of pages in that range.

Compact Page Allocation Screenshot
Compact Page Allocation screenshot

Allocation by Object

This report summarizes the data utilization of your database objects.

You can use this report to drill-through to the detailed page report.

Allocation by Object Screenshot
Allocation by Object screenshot

Page Allocation (Detailed)

This report displays your data file’s contents per each data page.

It shows you the page allocation type (DATA / INDEX / LOB / IAM / EMPTY / etc.), and also the object and index it belongs to.

Detailed Page Allocation Screenshot
Detailed Page Allocation screenshot

Transaction Log Reports

Transaction Log Contents

This report displays your transaction log file’s contents, highlighting the active / non active VLFs.

Transaction Log Detailed Screenshot
Transaction Log Contents screenshot

Future Plans

Additional plans for future development:

  • Additional report formats besides Power BI (e.g. Qlik, Reporting Services, Power Pivot, etc.)
  • More report types and improvements

License & Contribution

This is an open-source project released under the MIT license.

You are more than welcome to contribute by forking this project and making improvements and adding features.

If you wish to develop these reports for a new reporting platform, please create a separate folder for it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.