This is a useful query to get the total size of tables and indexes. It gives similar information to the standard report in SQL Server for top tables. (That's where I plagiarised the original query from.) However, I find it really useful to be able to get the information in a query that you can adapt. This query shows the compression factor for the first partition of the table.
with ps as
(SELECT
object_id,
MIN(partition_id) first_partition_id,
SUM (CASE
WHEN (index_id<2) THEN row_count
ELSE 0
END)
AS [rows],
SUM (reserved_page_count) AS reserved,
SUM (CASE
WHEN (index_id < 2) THEN (in_row_data_page_count+lob_used_page_count +row_overflow_used_page_count)
ELSE (lob_used_page_count+row_overflow_used_page_count)
END) AS data,
SUM (used_page_count) AS used
FROM sys.dm_db_partition_stats
GROUP BY object_id)
,ps2 as
(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
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id)
SELECT --TOP 1000
a3.name AS SchemaName,
a2.name AS TableName,
CASE a2.type
WHEN 'U' THEN 'User'
WHEN 'S' THEN 'System'
WHEN 'IT' THEN 'Internal'
ELSE a2.type
END
AS TableType,
ps.rows as [RowCount],
(ps.reserved + ISNULL(ps2.reserved,0))* 8 AS Reserved_KB,
ps.data * 8 AS Data_KB,
CASE
WHEN (ps.used + ISNULL(ps2.used,0)) > ps.data
THEN (ps.used + ISNULL(ps2.used,0)) - ps.data
ELSE 0
END * 8
AS Index_KB,
CASE
WHEN (ps.reserved + ISNULL(ps2.reserved,0)) > ps.used
THEN (ps.reserved + ISNULL(ps2.reserved,0)) - ps.used
ELSE 0
END * 8
AS Unused_KB,
p.data_compression_desc FirstPartitionCompression
FROM ps
LEFT OUTER JOIN ps2
ON (ps2.parent_id = ps.object_id)
INNER JOIN sys.all_objects a2
ON ( ps.object_id = a2.object_id )
INNER JOIN sys.schemas a3
ON (a2.schema_id = a3.schema_id)
LEFT OUTER JOIN sys.partitions p
ON p.partition_id=ps.first_partition_id
--WHERE a2.type <> N'S'
--and a2.type <> N'IT' order by 5 desc
No comments:
Post a Comment