/****Method 1: Estimate the
index size by using sp_spaceused.
The calculation is based on the
statistics in sys.partitions and sys.allocatin_units, and the page count.
Use this method, yolu can find
the combined size of all indexes in each table.
For the StoreContact table in
the AdventureWorks databse, the index_size is 120KB from this method.
From SSMS, the properties of
the table shows this table has 37KB data and 117KB indexes.
*/
USE
AdventureWorks
GO
EXEC sp_MSforeachtable @command1
= "EXEC sp_spaceused
'?'"
/***Method 2: Estimate the
index size by using sys.dm_db_index_physical_stats and the page_count column
This method can estimate the
size of each individual index.
Page_count=Total number of
index or data pages (Remember, a page is 8K)
·
For an
index,it is the total number of index pages in the current level of the b-tree
in the IN_ROW_DATA allocation unit.
·
For a
heap, it is the total number of data pages in the IN_ROW_DATA allocation unit.
·
For
LOB_DATA or ROW_OVERFLOW_DATA allocation units, it is the total number of pages
in the allocation unit.
Index in the table StoreContact Size in KB
------------------------------------- --------------
AK_StoreContact_rowguid 32
IX_StoreContact_ContactID 24
IX_StoreContact_ContactTypeID 24
PK_StoreContact_CustomerID_ContactID
48
Notice the total (128KB) based
on this method is larger than that based on sp_spaceused, even both estimates
are based on page.
The reason for that is the
current method counts the pages at both the leaf and root level.
For instance, the PK index has
5 pages at the leaf-node and one page at the root, adding up to
6 pages or 48KB. If you like to
limit to the leaf level, add the WHERE clause.
*/
SELECT i.name AS
IndexName ,
SUM(page_count
* 8) AS IndexSizeKB -- A page is 8K
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
--WHERE
avg_fragmentation_in_percent<>0 --AND
i.object_id=OBJECT_ID('Sales.StoreContact')
GROUP BY i.name
ORDER BY i.name
/****Method 3: Estimate the
index size by using sys.dm_db_index_physical_stats and and the number of rows
This method also uses
sys.dm_db_index_physical_stats,
but it estimaes the index size
via avg_record_size_in_bytes and the number of rows
What is average record size in
bytes?
·
For an
index, the average record size applies to the current level of the b-tree in
the IN_ROW_DATA allocation unit.
·
For a
heap, it is the average record size in the IN_ROW_DATA allocation unit.
·
For
LOB_DATA or ROW_OVERFLOW_DATA allocation units, it is the average record size
in the complete allocation unit.
·
NULL when
mode = LIMITED.
Index in the table StoreContact Size in KB
------------------------------------ --------------
AK_StoreContact_rowguid 18
IX_StoreContact_ContactID 7
IX_StoreContact_ContactTypeID 10
PK_StoreContact_CustomerID_ContactID 32
Total is 67KB, much less than
that by the other two methods.
*/
SELECT [DatabaseName] ,
[ObjectId] ,
[ObjectName] ,
[IndexId] ,
[IndexDescription] ,
CONVERT(DECIMAL(16, 0), ( SUM([avg_record_size_in_bytes]
* [record_count]) / ( 1024.0 ) )) AS
[IndexSize(KB)] ,
[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
