Created
August 1, 2025 16:36
-
-
Save MarkPflug/dfd2fe44f28a1495b2e2c364be4c943d to your computer and use it in GitHub Desktop.
A query that provides roughly the same data as "Disk usage by table", but with some extra info about lob usage
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| a3.name AS [schemaname], | |
| a2.name AS [tablename], | |
| a1.rows as row_count, | |
| coalesce(a1.reserved, 0) * 8 AS reserved, | |
| a1.data * 8 AS data, | |
| a1.rodata * 8 AS rodata, | |
| a1.lobdata * 8 AS lobdata, | |
| (CASE WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data THEN (a1.used + ISNULL(a4.used, 0)) - a1.data - a1.lobdata ELSE 0 END) * 8 AS index_size, | |
| (CASE WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used ELSE 0 END) * 8 AS unused | |
| FROM | |
| ( | |
| SELECT | |
| ps.object_id, | |
| SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], | |
| SUM (ps.reserved_page_count) AS reserved, | |
| SUM ( | |
| CASE WHEN (ps.index_id < 2) | |
| THEN (ps.in_row_data_page_count + ps.row_overflow_used_page_count) | |
| ELSE (row_overflow_used_page_count) | |
| END | |
| ) AS data, | |
| SUM (ps.lob_used_page_count) AS lobdata, | |
| SUM (ps.row_overflow_used_page_count) AS rodata, | |
| SUM (ps.used_page_count) AS used | |
| FROM sys.dm_db_partition_stats ps | |
| GROUP BY ps.object_id) AS a1 | |
| LEFT JOIN | |
| (SELECT | |
| it.parent_id, | |
| SUM(ps.reserved_page_count) AS reserved, | |
| SUM(ps.used_page_count) AS used | |
| FROM sys.dm_db_partition_stats ps | |
| JOIN sys.internal_tables it | |
| ON (it.object_id = ps.object_id) | |
| WHERE it.internal_type IN (202,204) | |
| GROUP BY it.parent_id) AS a4 | |
| ON (a4.parent_id = a1.object_id) | |
| INNER JOIN sys.all_objects a2 | |
| ON a1.object_id = a2.object_id | |
| INNER JOIN sys.schemas a3 | |
| ON a2.schema_id = a3.schema_id | |
| WHERE | |
| a2.type <> N'S' and | |
| a2.type <> N'IT' | |
| order by reserved desc |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment