Scenario:
You are working as SQL Server DBA or developer, You are working with storage estimation projection. You would like to estimate storage space for clustered index with given number or rows.
Solution:
Below script can be used to estimate storage size for cluster indexes. You can provide number of rows you would like to have in table on which clustered index is created.
--Drop and Create Temp Tables
IF Object_id(N'tempdb..#Index_Space_Used') IS NOT NULL
BEGIN
DROP TABLE #Index_Space_Used
END
IF Object_id(N'tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
CREATE TABLE #Index_Space_Used
(
Table_Name VARCHAR(100),
Num_Index_Pages INT
)
/*----------------------------------------
1:Build the List of Tables with Rows for which Estimation have to be done
------------------------------------------*/
;
WITH CTE_TableNames
AS (SELECT 'Table1' AS TableName,
12800 AS Num_Rows
UNION ALL
SELECT 'Table2' AS TableName,
479232 AS Num_Rows
UNION ALL
SELECT 'Table3' AS TableName,
360448 AS Num_Rows)
/*----------------------------------------
2:Get Storage Size for Fixed Column and Variable Columns
------------------------------------------*/
,
CTE_DataTypes
AS (SELECT TABLE_NAME,
TABLE_SCHEMA,
COLUMN_NAME,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CASE
WHEN Data_Type = 'Int' THEN 4
WHEN DATA_TYPE = 'BigInt' THEN 8
WHEN DATA_TYPE = 'TinyInt' THEN 1
WHEN DATA_TYPE = 'SmallInt' THEN 2
WHEN DATA_TYPE = 'Bit' THEN 1
WHEN DATA_TYPE = 'Money' THEN 8
WHEN DATA_TYPE = 'SmallMoney' THEN 4
WHEN DATA_TYPE = 'SmallDateTime' THEN 4
WHEN DATA_TYPE = 'DateTime' THEN 8
WHEN DATA_TYPE = 'Date' THEN 3
WHEN DATA_TYPE = 'Real' THEN 4
WHEN DATA_TYPE = 'Float'
AND NUMERIC_PRECISION <= 24 THEN 4
WHEN DATA_TYPE = 'Float'
AND NUMERIC_PRECISION >= 25 THEN 8
WHEN DATA_TYPE IN ( 'Decimal', 'Numeric' )
AND NUMERIC_PRECISION <= 9 THEN 5
WHEN DATA_TYPE IN ( 'Decimal', 'Numeric' )
AND NUMERIC_PRECISION BETWEEN 10 AND 19 THEN 9
WHEN DATA_TYPE IN ( 'Decimal', 'Numeric' )
AND NUMERIC_PRECISION BETWEEN 20 AND 28 THEN 13
WHEN DATA_TYPE IN ( 'Decimal', 'Numeric' )
AND NUMERIC_PRECISION BETWEEN 29 AND 38 THEN 17
WHEN DATA_TYPE = 'Time'
AND DATETIME_PRECISION IN ( 0, 1, 2 ) THEN 3
WHEN DATA_TYPE = 'Time'
AND DATETIME_PRECISION IN ( 3, 4 ) THEN 4
WHEN DATA_TYPE = 'Time'
AND DATETIME_PRECISION >= 5 THEN 5
WHEN DATA_TYPE = 'DateTime2'
AND DATETIME_PRECISION < 3 THEN 6
WHEN DATA_TYPE = 'DateTime2'
AND DATETIME_PRECISION IN( 3, 4 ) THEN 7
WHEN DATA_TYPE = 'DateTime2'
AND DATETIME_PRECISION > 4 THEN 8
WHEN DATA_TYPE = 'DateTimeoffset'
AND DATETIME_PRECISION < 3 THEN 8
WHEN DATA_TYPE = 'DateTimeoffset'
AND DATETIME_PRECISION IN( 3, 4 ) THEN 9
WHEN DATA_TYPE = 'DateTimeoffset'
AND DATETIME_PRECISION > 4 THEN 10
ELSE CHARACTER_OCTET_LENGTH --This is going to cover CHAR,NCHAR,VARCHAR,NVARCHAR Columns
END AS SIZE_IN_BYTES
FROM INFORMATION_SCHEMA.COLUMNS),
CTE_Num_Cols
AS (SELECT TD.Table_NAME,
Count(TD.COLUMN_NAME) Num_Cols,
Sum(SIZE_IN_BYTES) AS SIZE_IN_BYTES,
TN.Num_Rows
FROM CTE_DataTypes TD
INNER JOIN CTE_TableNames TN
ON TD.TABLE_NAME = TN.TableName
GROUP BY TD.Table_NAME,
TN.Num_Rows),
NullBitMap_RowSize
AS (SELECT Table_Name,
Num_Rows,
Num_Cols,
Size_In_Bytes,
2 + ( ( Num_Cols + 7 ) / 8 ) AS NullBitMap,
Size_In_Bytes + ( 2 + ( ( Num_Cols + 7 ) / 8 ) ) + 4 AS Row_Size,
Size_In_Bytes + ( 2 + ( ( Num_Cols + 7 ) / 8 ) ) + 4 + 1--(for row header overhead of an index row)
+ 7--(for the child page ID pointer)
AS Index_Row_Size
FROM CTE_Num_Cols),
Rows_Per_Page
AS (SELECT TABLE_NAME,
Num_Rows,
Num_Cols,
SIZE_IN_BYTES,
NullBitMap,
Row_Size,
8096 / ( Row_Size + 2 ) AS Rows_Per_Page,
Index_Row_Size,
8096 / ( Index_Row_Size + 2 ) AS Index_Rows_Per_Page
FROM NullBitMap_RowSize),
Free_Rows_Per_Page
AS (SELECT TABLE_NAME,
Num_Rows,
Num_Cols,
SIZE_IN_BYTES,
NullBitMap,
Row_Size,
Rows_Per_Page,
Ceiling(8096 * ( ( 100 - 95 ) / 100.0 ) / ( Row_Size + 2 )) AS Free_Rows_Per_Page,
Index_Row_Size,
Index_Rows_Per_Page
FROM Rows_Per_Page),
Num_Leaf_Pages
AS (SELECT TABLE_NAME,
Num_Rows,
Num_Cols,
SIZE_IN_BYTES,
NullBitMap,
Row_Size,
Rows_Per_Page,
Free_Rows_Per_Page,
Ceiling(Num_Rows / ( Rows_Per_Page - Free_Rows_Per_Page )) AS Num_Leaf_Pages,
Index_Row_Size,
Index_Rows_Per_Page
FROM Free_Rows_Per_Page),
Leaf_Space_Used
AS (SELECT TABLE_NAME,
Num_Rows,
Num_Cols,
SIZE_IN_BYTES,
NullBitMap,
Row_Size,
Rows_Per_Page,
Free_Rows_Per_Page,
Num_Leaf_Pages,
8192 * Num_Leaf_Pages AS Leaf_Space_used_In_Bytes,
Ceiling(( 8192 * Num_Leaf_Pages ) / 1024) AS Leaf_Space_used_in_KB,
Index_Row_Size,
Index_Rows_Per_Page,
Ceiling(1 + Log (Index_Rows_Per_Page) * ( Num_Leaf_Pages / Index_Rows_Per_Page )) AS Non_leaf_Levels
FROM Num_Leaf_Pages)
SELECT *
INTO #Temp
FROM Leaf_Space_Used
--Calculate the number of non-leaf pages
DECLARE @TableName VARCHAR(100)
DECLARE Table_Cursor CURSOR FOR
SELECT Table_Name
FROM #Temp
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Cnt INT
DECLARE @Num_Leaf_Pages INT
DECLARE @Non_Leaf_Levels INT
DECLARE @Index_Row_Per_Page INT
DECLARE @VarSUM INT
SET @VarSUM=0
SELECT @Num_Leaf_Pages = Num_Leaf_Pages,
@Non_Leaf_Levels = Non_Leaf_Levels,
@Index_Row_Per_Page = Index_Rows_Per_Page
FROM #Temp
WHERE TABLE_NAME = @TableName
WHILE ( @Non_Leaf_Levels <> 0 )
BEGIN
BEGIN TRY
SET @VarSUM=@VarSUM + ( Ceiling(Cast(@Num_Leaf_Pages AS NUMERIC(38, 0)) / Power(Cast(@Index_Row_Per_Page AS NUMERIC(38, 0)), @Non_Leaf_Levels)) )
END TRY
BEGIN CATCH
END CATCH
SET @Non_Leaf_Levels=@Non_Leaf_Levels - 1
END
INSERT INTO #Index_Space_Used
VALUES (@TableName,
@VarSUM)
FETCH NEXT FROM Table_Cursor INTO @TableName
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
SELECT T.*,
ISU.Num_Index_Pages,
T.Leaf_Space_used_in_KB
+ Ceiling(((8192*ISU.Num_Index_Pages)/1024)) AS Clustered_Index_Size_in_KB
FROM #Temp T
INNER JOIN #Index_Space_Used ISU
ON T.TABLE_NAME = ISU.Table_Name