Can we have a SQL query which will basically help in viewing table and index sizes in SQl Server.
How SQL server maintains memory usage for tables/indexes?
This question is related to
sql-server
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
There is an extended stored procedure sp_spaceused that gets this information out. It's fairly convoluted to do it from the data dictionary, but This link fans out to a script that does it. This stackoverflow question has some fan-out to information on the underlying data structures that you can use to construct estimates of table and index sizes for capcity planning.
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
On SQL 2012 getting this information on a table level has become deliciously simple:
SQL Management Studio -> Right click on Db -> Reports -> Standard Reports -> Disk usage by table !
Enjoy
sp_spaceused gives you the size of all the indexes combined.
If you want the size of each index for a table, use one of these two queries:
SELECT
i.name AS IndexName,
SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.TableName')
GROUP BY i.name
ORDER BY i.name
SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name
The results are usually slightly different but within 1%.
On SQL 2012 getting this information on a table level has become deliciously simple:
SQL Management Studio -> Right click on Db -> Reports -> Standard Reports -> Disk usage by table !
Enjoy
There is an extended stored procedure sp_spaceused that gets this information out. It's fairly convoluted to do it from the data dictionary, but This link fans out to a script that does it. This stackoverflow question has some fan-out to information on the underlying data structures that you can use to construct estimates of table and index sizes for capcity planning.
It’s been a long time since the creation of this post, but I wanted to share my script:
WITH CteIndex
AS
(
SELECT
reservedpages = (reserved_page_count)
,usedpages = (used_page_count)
,pages = (
CASE
WHEN (s.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
)
,s.object_id
,i.index_id
,i.type_desc AS IndexType
,i.name AS indexname
FROM sys.dm_db_partition_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
)
SELECT DISTINCT
DB_NAME(DB_ID()) AS DatabaseName
,o.name AS TableName
,o.object_id
,ct.indexname
,ct.IndexType
,ct.index_id
, IndexSpace = LTRIM (STR ((CASE WHEN usedpages > pages THEN CASE WHEN ct.index_id < 2 THEN pages ELSE (usedpages - pages) END ELSE 0 END) * 8, 15, 0) + ' KB')
FROM CteIndex ct
INNER JOIN sys.objects o ON o.object_id = ct.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , NULL) ps ON ps.object_id = o.object_id
AND ps.index_id = ct.index_id
ORDER BY name ASC
This works for :
There is an extended stored procedure sp_spaceused that gets this information out. It's fairly convoluted to do it from the data dictionary, but This link fans out to a script that does it. This stackoverflow question has some fan-out to information on the underlying data structures that you can use to construct estimates of table and index sizes for capcity planning.
Here is more compact version of the most successful answer:
create table #tbl(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'
select * from #tbl
order by convert(int, substring(data, 1, len(data)-3)) desc
drop table #tbl
This query comes from two other answers:
Get size of all tables in database
How to find largest objects in a SQL Server database?
, but I enhanced this to be universal. It uses sys.objects dictionary:
SELECT
s.NAME as SCHEMA_NAME,
t.NAME AS OBJ_NAME,
t.type_desc as OBJ_TYPE,
i.name as indexName,
sum(p.rows) as RowCounts,
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.objects t
INNER JOIN
sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
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
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
s.NAME, t.NAME, t.type_desc, i.object_id, i.index_id, i.name
ORDER BY
sum(a.total_pages) DESC
;
It’s been a long time since the creation of this post, but I wanted to share my script:
WITH CteIndex
AS
(
SELECT
reservedpages = (reserved_page_count)
,usedpages = (used_page_count)
,pages = (
CASE
WHEN (s.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
)
,s.object_id
,i.index_id
,i.type_desc AS IndexType
,i.name AS indexname
FROM sys.dm_db_partition_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
)
SELECT DISTINCT
DB_NAME(DB_ID()) AS DatabaseName
,o.name AS TableName
,o.object_id
,ct.indexname
,ct.IndexType
,ct.index_id
, IndexSpace = LTRIM (STR ((CASE WHEN usedpages > pages THEN CASE WHEN ct.index_id < 2 THEN pages ELSE (usedpages - pages) END ELSE 0 END) * 8, 15, 0) + ' KB')
FROM CteIndex ct
INNER JOIN sys.objects o ON o.object_id = ct.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , NULL) ps ON ps.object_id = o.object_id
AND ps.index_id = ct.index_id
ORDER BY name ASC
This works for :
This query comes from two other answers:
Get size of all tables in database
How to find largest objects in a SQL Server database?
, but I enhanced this to be universal. It uses sys.objects dictionary:
SELECT
s.NAME as SCHEMA_NAME,
t.NAME AS OBJ_NAME,
t.type_desc as OBJ_TYPE,
i.name as indexName,
sum(p.rows) as RowCounts,
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.objects t
INNER JOIN
sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
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
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
s.NAME, t.NAME, t.type_desc, i.object_id, i.index_id, i.name
ORDER BY
sum(a.total_pages) DESC
;
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
There is an extended stored procedure sp_spaceused that gets this information out. It's fairly convoluted to do it from the data dictionary, but This link fans out to a script that does it. This stackoverflow question has some fan-out to information on the underlying data structures that you can use to construct estimates of table and index sizes for capcity planning.
--Gets the size of each index for the specified table
DECLARE @TableName sysname = N'SomeTable';
SELECT i.name AS IndexName
,8 * SUM(s.used_page_count) AS IndexSizeKB
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS s
ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
WHERE s.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;
SELECT i.name AS IndexName
,8 * SUM(a.used_pages) AS IndexSizeKB
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 a
ON p.partition_id = a.container_id
WHERE i.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;
sp_spaceused gives you the size of all the indexes combined.
If you want the size of each index for a table, use one of these two queries:
SELECT
i.name AS IndexName,
SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.TableName')
GROUP BY i.name
ORDER BY i.name
SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name
The results are usually slightly different but within 1%.
To see a single table's (and its indexes) storage data:
exec sp_spaceused MyTable
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
--Gets the size of each index for the specified table
DECLARE @TableName sysname = N'SomeTable';
SELECT i.name AS IndexName
,8 * SUM(s.used_page_count) AS IndexSizeKB
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS s
ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
WHERE s.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;
SELECT i.name AS IndexName
,8 * SUM(a.used_pages) AS IndexSizeKB
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 a
ON p.partition_id = a.container_id
WHERE i.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;
Here is more compact version of the most successful answer:
create table #tbl(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'
select * from #tbl
order by convert(int, substring(data, 1, len(data)-3)) desc
drop table #tbl
Source: Stackoverflow.com