Maintaining Indexes - Microsoft SQL Server 2012 Performance Tuning - page 253

FROM
(
SELECT
partition_id,
index_id,
SUM (used_page_count) AS FilledPage,
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 PageToDeduct
FROM
sys.dm_db_partition_stats
GROUP BY
partition_id
,index_id
) AS InnerTable
GROUP BY
CASE index_id
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'Clusetered Index'
ELSE 'Non-Clustered Index'
END
GO
How it works...
sys.dm_db_partition_stats gives useful information to the DBA, such as the total row
count in each table per partition, used pages, reserved pages for LOB, in-row, and overflow.
Each page consumes 8 KB, so if the total number of pages found for any object is multiplied
by 8, it would give us the total size in KB.
We can get the Used_Page_Count field, which displays information about total pages used
by an object, and if it is a heap or clustered index ( Index_id < 2 ), we remove in_row_
data_page_count , lob_used_page_count , and row_overflow_used_page_count ,
otherwise only the last two fields are removed from total used pages. Multiply those pages
with 8 to get the total used KB (Kilo Bytes).