Let us start with humor! 

I think we the series on various reports, we come to a logical point. We covered all the reports at server level. This means the reports we saw were targeted towards activities that are related to instance level operations. These are mostly like how a doctor diagnoses a patient. At this point I am reminded of a dialog which I read somewhere:

Patient: Doc, It hurts when I touch my head.

Doc: Ok, go on. What else have you experienced?

Patient: It hurts even when I touch my eye, it hurts when I touch my arms, it even hurts when I touch my feet, etc.

Doc: Hmmm …

Patient: I feel it hurts when I touch anywhere in my body.

Doc: Ahh … now I get it. You need a plaster to your finger John.

Sometimes the server level gives an indicator to what is happening in the system, but we need to get to the root cause for a specific database. So, this is the first blog in series where we would start discussing about database level reports. To launch database level reports, expand selected server in Object Explorer, expand the Databases folder, and then right-click any database for which we want to look at reports. From the menu, select Reports, then Standard Reports, and then any of database level reports.

In this blog, we would talk about four “disk” reports because they are similar:

1. Disk Usage
2. Disk Usage by Top Tables
3. Disk Usage by Table
4. Disk Usage by Partition

Disk Usage

This report shows multiple information about the database. Let us discuss them one by one.  We have divided the output into 5 different sections.

Section 1 shows the high level summary of the database. It shows the space used by database files (mdf and ldf). Under the hood, the report uses, various DMVs and DBCC Commands, it is using sys.data_spaces and DBCC SHOWFILESTATS.

Section 2 and 3 are pie charts. One for data file allocation and another for the transaction log file. Pie chart for “Data Files Space Usage (%)” shows space consumed data, indexes, allocated to the SQL Server database, and unallocated space which is allocated to the SQL Server database but not yet filled with anything. “Transaction Log Space Usage (%)” used DBCC SQLPERF (LOGSPACE) and shows how much empty space we have in the physical transaction log file.

Section 4 shows the data from Default Trace and looks at Event IDs 92, 93, 94, 95 which are for “Data File Auto Grow”, “Log File Auto Grow”, “Data File Auto Shrink” and “Log File Auto Shrink” respectively. Here is an expanded view for that section.

If default trace is not enabled, then this section would be replaced by the message “Trace Log is disabled” as highlighted below.

Section 5 of the report uses DBCC SHOWFILESTATS to get information. Here is the enhanced version of that section. This shows the physical layout of the file.

In case you have In-Memory Objects in the database (from SQL Server 2014), then report would show information about those as well. Here is the screenshot taken for a different database, which has In-Memory table. I have highlighted new things which are only shown for in-memory database.

The new sections which are highlighted above are using sys.dm_db_xtp_checkpoint_files, sys.database_files and sys.data_spaces. The new type for in-memory OLTP is ‘FX’ in sys.data_space.

The next set of reports is targeted to get information about a table and its storage. These reports can answer questions like:

1. Which is the biggest table in the database?
2. How many rows we have in table?
3. Is there any table which has a lot of reserved space but its unused?
4. Which partition of the table is having more data?

Disk Usage by Top Tables

This report provides detailed data on the utilization of disk space by top 1000 tables within the Database. The report does not provide data for memory optimized tables.

Disk Usage by Table

This report is same as earlier report with few difference.

1. First Report shows only 1000 rows
2. First Report does order by values in DMV sys.dm_db_partition_stats whereas second one does it based on name of the table.

Both of the reports have interactive sort facility. We can click on any column header and change the sorting order of data.

Disk Usage by Partition

This report shows the distribution of the data in table based on partition in the table.

This is so similar to previous output with the partition details now. Here is the query taken from profiler.

SELECT
row_number() OVER (ORDER BY a1.used_page_count DESC, a1.index_id) AS row_number
,      (dense_rank() OVER (ORDER BY a5.name, a2.name))%2 AS l1
,      a1.OBJECT_ID
,      a5.name AS [schema]
,       a2.name
,       a1.index_id
,       a3.name AS index_name
,       a3.type_desc
,       a1.partition_number
,       a1.used_page_count * 8 AS total_used_pages
,       a1.reserved_page_count * 8 AS total_reserved_pages
,       a1.row_count
FROM sys.dm_db_partition_stats a1
INNER JOIN sys.all_objects a2  ON ( a1.OBJECT_ID = a2.OBJECT_ID) AND a1.OBJECT_ID NOT IN (SELECT OBJECT_ID FROM sys.tables WHERE is_memory_optimized = 1)
INNER JOIN sys.schemas a5 ON (a5.schema_id = a2.schema_id)
LEFT OUTER JOIN  sys.indexes a3  ON ( (a1.OBJECT_ID = a3.OBJECT_ID) AND (a1.index_id = a3.index_id) )
WHERE (SELECT MAX(DISTINCT partition_number)
FROM sys.dm_db_partition_stats a4
WHERE (a4.OBJECT_ID = a1.OBJECT_ID)) >= 1
AND a2.TYPE <> N'S'
AND  a2.TYPE <> N'IT'
ORDER BY a5.name ASC, a2.name ASC, a1.index_id, a1.used_page_count DESC, a1.partition_number


Using all of the above reports, you should be able to get the usage of database files and also space used by tables.

I think this is too much disk information for a single blog and I hope you have used them in the past to get data. Do let me know if you found anything interesting using these reports in your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)