Have you ever wondered how to get detailed information about your SQL Server databases? In this blog post, we will explore the database level reports in SQL Server that can provide valuable insights into your database usage and storage.
When it comes to monitoring and diagnosing issues in SQL Server, server level reports can only provide a high-level overview. To get to the root cause of specific database issues, we need to dive deeper into the database level reports.
To access the database level reports, follow these steps:
- Expand the selected server in Object Explorer.
- Expand the Databases folder.
- Right-click on the database for which you want to view reports.
- Select Reports, then Standard Reports, and choose any of the available database level reports.
In this blog post, we will focus on four “disk” reports:
- Disk Usage
- Disk Usage by Top Tables
- Disk Usage by Table
- Disk Usage by Partition
The Disk Usage report provides multiple information about the database. Let’s discuss each section of the report:
Section 1: High-Level Summary
This section shows the space used by the database files (mdf and ldf). It utilizes various DMVs and DBCC commands, such as sys.data_spaces and DBCC SHOWFILESTATS.
Section 2 and 3: Pie Charts
These pie charts display the data file allocation and transaction log file usage. The “Data Files Space Usage (%)” chart shows the space consumed by data, indexes, allocated to the SQL Server database, and unallocated space. The “Transaction Log Space Usage (%)” chart shows the empty space in the physical transaction log file.
Section 4: Default Trace Data
This section utilizes the Default Trace and looks at Event IDs 92, 93, 94, and 95, which correspond to “Data File Auto Grow,” “Log File Auto Grow,” “Data File Auto Shrink,” and “Log File Auto Shrink” respectively. It provides insights into the automatic growth and shrink events of the database files.
Section 5: File Layout
This section uses DBCC SHOWFILESTATS to display the physical layout of the file. It also includes information about In-Memory Objects if they exist in the database.
The next set of reports focuses on individual tables and their storage:
- Disk Usage by Top Tables
- Disk Usage by Table
- Disk Usage by Partition
These reports provide detailed data on the utilization of disk space by tables within the database. They can answer questions like:
- Which is the biggest table in the database?
- How many rows do we have in a table?
- Is there any table with a lot of reserved space but is unused?
- Which partition of the table has more data?
By utilizing these database level reports, you can gain valuable insights into your database usage and storage. Whether you need to optimize disk space, identify performance bottlenecks, or troubleshoot issues, these reports can provide the necessary information.
Remember, knowledge is power, and understanding your database’s usage patterns is crucial for maintaining a healthy and efficient SQL Server environment.
I hope you found this blog post informative and that you will explore these database level reports in your own environments. Feel free to share any interesting findings or insights you discover!