sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table.
What if you need to capture size of individual index on a table?
It is very easy to find out some basic details of any table using the following Stored Procedure.
USE AdventureWorks2012
GO
EXEC sp_spaceused [HumanResources.Shift]
GO
Above query will return following resultset
The above Stored Procedure provides basic details such as rows, data size in table, and Index size of all the indexes on the table.
If we look at this carefully, a total of three indexes can be found on the table HumanResources.Shift.
USE AdventureWorks2012
GO
SELECT *
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Shift')
GO
The above query will give result with query listing all the index on the table.
We need a query that will return an additional column in the above listed query and it should contain the size of the index.
we will have three different sizes, which should add up to a total of 40 KB as shown in earlier query, where the total size is displayed.
I have done some good amount of testing of both the script and they both are really giving accurate results 99.9% of the time. There were couple of instances where it provided incorrect results but for the most of the time, it just worked like charm. Again, it may be a bit of rounding up logic and I will not hold it against the script.
Only Index names and sizes for a table
DECLARE @OBJECT_NAME VARCHAR(255) = 'HumanResources.Shift';
DECLARE @temp TABLE
(
indexID BIGINT,
objectId BIGINT,
index_name NVARCHAR(MAX),
used_page_count BIGINT,
pages BIGINT
)
--Insert into temp table
INSERT INTO @temp
SELECT
P.index_id,
P.OBJECT_ID ,
I.name,
SUM (used_page_count),
SUM (
CASE
WHEN (p.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
)
FROM sys.dm_db_partition_stats P INNER JOIN sys.indexes I ON I.index_id = P.index_id AND I.OBJECT_ID = P.OBJECT_ID
WHERE p.OBJECT_ID = OBJECT_ID(@OBJECT_NAME)
GROUP BY P.index_id, I.Name, P.OBJECT_ID;
SELECT index_name INDEX_NAME,
LTRIM (STR ((CASE WHEN used_page_count > pages THEN (used_page_count - pages) ELSE 0 END) * 8, 15, 0) + ' KB') INDEX_SIZE
FROM @temp T
GO
The result is like this
All Index information for a table
DECLARE
@objname NVARCHAR(776),
@id INT,
@dbname sysname
SELECT
@objname = 'HumanResources.Shift',
@dbname = ISNULL(PARSENAME(@objname, 3),DB_NAME()),
@id = OBJECT_ID(@objname)
SELECT
i.*,
CASE
WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages)
ELSE 0
END * 8 indexsize
FROM sys.indexes i
INNER JOIN (
SELECT
OBJECT_ID,
index_id,
SUM (used_page_count) usedpages,
SUM (
CASE
WHEN (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
)pages
FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = @id
GROUP BY OBJECT_ID, index_id
) ps ON i.index_id = ps.index_id
WHERE i.OBJECT_ID = @id
GO
The result is like this
All Indexes in database
Uses sys.indexes and sys.dm_db_partition_stats DMF to calculate the size of individual index on a table.
SELECT i.[name] AS IndexName
,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER 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]
GO
The result is like this
All tables and all related Indexes in database
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)'
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
The result is like this
All Object names, Index descriptions, Index sizes and fragmentation percentages in database
Uses sys.dm_db_index_physical_stats and sys.dm_db_partition_stats to calculate the size of individual index on a table. This query is more reliable as compared to first query because it uses DMFs
SELECT [DatabaseName]
,[ObjectId]
,[ObjectName]
,[IndexId]
,[IndexDescription]
,CONVERT(DECIMAL(16, 1), (SUM([avg_record_size_in_bytes] * [record_count]) / (1024.0 * 1024))) AS [IndexSize(MB)]
,[lastupdated] AS [StatisticLastUpdated]
,[AvgFragmentationInPercent]
FROM (
SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName'
,OBJECT_ID AS ObjectId
,Object_Name(Object_id) AS ObjectName
,Index_ID AS IndexId
,Index_Type_Desc AS IndexDescription
,avg_record_size_in_bytes
,record_count
,STATS_DATE(object_id, index_id) AS 'lastupdated'
,CONVERT([varchar](512), round(Avg_Fragmentation_In_Percent, 3)) AS 'AvgFragmentationInPercent'
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'detailed')
WHERE OBJECT_ID IS NOT NULL
AND Avg_Fragmentation_In_Percent <> 0
) T
GROUP BY DatabaseName
,ObjectId
,ObjectName
,IndexId
,IndexDescription
,lastupdated
,AvgFragmentationInPercent
The result is like this
I hope this information is useful for you
