sql server效能分析–查看錶資料頁數

返回表名、索引名和行數
SELECT object_name(i.object_id) as objectName, i.[name] as indexName, sum(p.rows) as rowCnt
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
WHERE i.object_id = object_id('dbo.Meeting')
AND i.index_id <= 1
GROUP BY i.object_id, i.index_id, i.[name]

返回表的總頁數、使用頁數、資料頁數
SELECT object_name(i.object_id) as objectName, i.[name] as indexName,
sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE i.object_id = object_id('dbo.Meeting')
AND i.index_id <= 1
GROUP BY i.object_id, i.index_id, i.[name]

按頁類型分類統計
SELECT case when grouping(i.object_id) = 1 then '--- TOTAL ---' else object_name(i.object_id) end as objectName,
case when grouping(i.[name]) = 1 then '--- TOTAL ---' else i.[name] end as indexName,
case when grouping(a.type_desc) = 1 then '--- TOTAL ---' else a.type_desc end as pageType,
sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB, (sum(a.data_pages) * 8) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE i.object_id = object_id('dbo.Meeting')
AND i.index_id <= 1
GROUP BY i.object_id, i.[name], a.type_desc with rollup