Skip to content

Instantly share code, notes, and snippets.

@jdaigle
Created July 16, 2014 17:12
Show Gist options
  • Save jdaigle/19daf1cd7d1cd4f1a78b to your computer and use it in GitHub Desktop.
Save jdaigle/19daf1cd7d1cd4f1a78b to your computer and use it in GitHub Desktop.
get page count and rows per page in each partition
SELECT TableName = object_name(i.object_id)
, IndexName = i.name
, i.type_desc
, p.data_compression_desc
, [Partitions] = Max(p.partition_number)
, [Rows] = Sum(p.rows)
, [Pages] = Sum(au.data_pages)
, [RowsPerPage] = Sum(p.rows) / Sum(au.data_pages)
FROM sys.indexes AS i
INNER JOIN sys.partitions AS p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units As au
ON p.hobt_id = au.container_id
WHERE object_name(i.object_id) NOT LIKE 'sys%'
AND au.type_desc = 'IN_ROW_DATA'
GROUP BY object_name(i.object_id)
, i.name
, i.type_desc
, p.data_compression_desc
HAVING Sum(au.data_pages) > 1000
ORDER BY rowsPerPage;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment