TSQL Get Index Size Per Table – 如何使用TSQL 來查詢每一個Table上 每一個Index 佔用的空間大小

今天想和大家分享如何使用TSQL 來查詢每一個Table上 每一個Index 佔用的空間大小
這個可以令大家明白Database 的空間有多小放在Index上
明白更多..應該有幫助吧

解決方法
我們可以使用以下的Query 來查詢 每一個Table上 每一個Index 佔用的空間大小

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)',
8 * SUM(a.used_pages) / 1024.00 AS 'Indexsize(MB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

TSQL Get Individual Index SizeHope you find it useful

Leave a Reply