DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to estimate index size by rule-of-thumb?

The following formulas yield a quick estimate or calculation of index space usage:

High margin estimate formula:

IndexSize = NumberOfRows * AvgSizeOfIndex * 2 * 1/(FILLFACTOR/100)

Average margin estimate formula:

IndexSize = NumberOfRows * AvgSizeOfIndex * 1.5 * 1/(FILLFACTOR/100)

------------

-- T-SQL create stored procedure for index size calculation
-- Calculating SQL Server index size - sql server index size
CREATE
PROCEDURE sprocIndexSize
                @TableName VARCHAR(256)
AS
  BEGIN
    DECLARE  @IndexSize BIGINT,
             @IndexID   INT
    
    SET @IndexSize = 0
    
    DECLARE curIndex CURSOR  FOR
    SELECT index_id
    FROM   sys.indexes
    WHERE  object_id = OBJECT_ID(@TableName)
    
    OPEN curIndex
    
    FETCH NEXT FROM curIndex
    INTO @IndexID
    
    WHILE (@@FETCH_STATUS = 0)
      BEGIN
        SELECT @IndexSize = sum(avg_record_size_in_bytes * record_count)
        FROM   sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(@TableName),
                           @IndexID,NULL,   'DETAILED')
        
        SELECT TableName = @TableName,
               IndexID = @IndexID,
               IndexSizeMB = CONVERT(DECIMAL(16,1),(@IndexSize / (1024.0 * 1024)))
        
        FETCH NEXT FROM curIndex
        INTO @IndexID
      END
    CLOSE curIndex
    DEALLOCATE curIndex
  END
GO
 
-- Execute store procedure
EXEC sprocIndexSize   'Sales.SalesOrderHeader'
/*
TableName               IndexID     IndexSizeMB
Sales.SalesOrderHeader  1           5.2
Sales.SalesOrderHeader  2           0.7
......

*/

------------

-- SQL index size calculation

USE AdventureWorks;

SELECT IndexName = name,

       IndexSizeMB = CEILING(1.0 * dpages / 128),

       IndexSizeKB = dpages * 8

FROM   sysindexes

WHERE  NAME LIKE '%SalesOrderDetail%'

GO

/* 

IndexName                                             IndexSizeMB  IndexSizeKB

PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID   10              9872

AK_SalesOrderDetail_rowguid                           4               3256

IX_SalesOrderDetail_ProductID                         2               1808

*/

------------

 

-- SQL index size - DMV - Dynamic Management View

SELECT index_id,

       IndexSizeKB = SUM(page_count * 8)

FROM     sys.dm_db_index_physical_stats(db_id(),object_id('Sales.SalesOrderDetail'),

                                        NULL,NULL,'DETAILED')

GROUP BY index_id

ORDER BY index_id

GO

/* Results

index_id  IndexSizeKB

1           9912

2           3280

3           1816

*/

------------

 

-- SQL index size - system procedure

EXEC sp_spaceused 'Sales.SalesOrderDetail'

GO

/* Results

data = index_id 1 (clustered index)

index_size = index_id 2 + 3 (non-clustered indexes)

 

name              rows         reserved  data        index_size  unused

SalesOrderDetail  121317       15440 KB  9872 KB     5240 KB     328 KB

*/

 

-- Rule-of-thumb for AK_SalesOrderDetail_rowguid

SELECT [Rule-of-thumb-KB] = (121317 * 16 * 2 * 1/1) /1024

GO

/*

Rule-of-thumb-KB

3791

*/

 

-- Rule-of-thumb for IX_SalesOrderDetail_ProductID

SELECT [Rule-of-thumb-KB] = (121317 * 4 * 2 * 1/1) /1024

GO

/*

Rule-of-thumb-KB

947

*/

------------

 

Related articles:

SQL Server Indexes

SQL SERVER Size of Index Table A Puzzle to Find Index Size for Each Index on Table

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE