Neil McDonald's Dynamics CRM Blog

Checking SQL table sizes

leave a comment »

Sqlsize

I wanted a way to find which tables were using the most storage in my SQL databases. The stored procedure sp_spaceused will show this data for a single table, but I wanted to view every table at once. The below SQL query will run against any SQL 2005 database and show you it’s table sizes in descending order. As expected, the ActivityMimeAttachment table in my CRM database was pretty huge!


SELECT sys.schemas.[name] AS [Schema],
sys.tables.name AS [Table],
COALESCE([Row Count].[Count], 0) AS [Rows],COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],
COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]
FROM sys.tables
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
LEFT OUTER JOIN (SELECT object_id,SUM(rows) AS [Count]
FROM sys.partitions
WHERE index_id < 2
GROUP BY object_id) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECT sys.indexes.object_id,SUM(CASE WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Data Pages] ON [Data Pages].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECT sys.indexes.object_id,SUM(a.used_pages - CASE
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Index Pages] ON [Index Pages].object_id = sys.tables.object_id
ORDER BY [Data Bytes] desc

Written by neilmcd

July 19, 2010 at 6:07 pm

Posted in Misc

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: