NAME : ClusteredIndexAnalyser DESCRIPTION : Identifies each user Table's Clustered index along with the columns that make up that Clustered index. In addition, the column types, their max size in bytes, total index size in bytes, Table Row Count and Total Clustered Index size in kilobytes is calculated to help determine the largest, or most inefficient Clustered indexes. Additionaly whether the Clustered index is unique, the number of non clustered indexes on the table, and another calculation of Potential cost of the CLUSTERED INDEX with the the NONCLUSTERED INDEXES (ClusteredIndexSizeKB * Non Clustered Indexes) since all NONCLUSTERED INDEXES contain the CLUSTERED INDEX.
CREATED BY : CGW DATE : 20/01/2010 MODIFIED : CAJ 12/03/2010
================================================================================ */ SET NOCOUNT ON
------------------------------------------------------------------------ -- Declare Variables ------------------------------------------------------------------------ DECLARE @Iteration INT; -- Variable used as an iterator DECLARE @AddColumnIteration INT; -- An additional Iterator to determine columns of an index DECLARE @Tmp NVARCHAR(MAX); -- Variable used in several areas to temporarily have an SQL statement which can be executed DECLARE @ClusterKeyName SYSNAME; -- The Clustered Key Name (used within the Cursor) DECLARE @SchemaName SYSNAME; -- The Schema Name (used within the Cursor) ** 12/03/2010 CAJ ** DECLARE @TableName SYSNAME; -- The Table Name (used within the Cursor) DECLARE @ClusteredIndexID INT; -- ClusteredIndexID from the ClusteredIndexes TEMP TABLE DECLARE @ClusteredIndexType INT; -- Declaring a constant DECLARE @NonClusteredIndexType INT; -- Declaring a constant
SET @ClusteredIndexType = 1 -- Index Type 1 is a CLUSTERED INDEX SET @NonClusteredIndexType = 2 -- Index Type 2 is a NONCLUSTERED INDEX
------------------------------------------------------------------------ -- Declare #Result Temp Table ------------------------------------------------------------------------ IF OBJECT_ID(N'tempdb..#Result', N'U') IS NOT NULL DROP TABLE #Result;
CREATE TABLE #Result -- Used to iterate through each column on the clustered key ( ColumnName SYSNAME )
------------------------------------------------------------------------ -- Declare Temp Table #ClusteredIndexes being set up to hold Clustered Index info. -- More columns are added to this table later, depending on how many columns reside on the largest composite index ------------------------------------------------------------------------ IF OBJECT_ID(N'tempdb..#ClusteredIndexes', N'U') IS NOT NULL DROP TABLE #ClusteredIndexes;
CREATE TABLE #ClusteredIndexes ( ClusteredIndexID INT IDENTITY(1,1), -- Identity column SchemaName SYSNAME, -- Schema that the Table is on TableName SYSNAME, -- Table that has the CLUSTERED INDEX ClusteredIndexName SYSNAME, -- Name of the CLUSTERED INDEX IsUnique CHAR(1) CONSTRAINT ICClusteredIndexesIsUnique CHECK (IsUnique = 'Y' OR IsUnique = 'N') CONSTRAINT IDClusteredIndexesIsUnique DEFAULT ('Y'), IndexColumnCount INT, -- Columns that exist in the CLUSTERED INDEX NonClusteredIndexCount INT, -- Number of NONCLUSTERED INDEXES that reside on the table IndexSizeInBytesPerRow INT DEFAULT 0, -- Total size of the CLUSTERED INDEX in bytes TableRowCount INT, -- Number of ROWS in the TABLE ClusteredIndexSizeKB INT, -- Number of ROWS multiplied by IndexSize ClustIdxSzTmsByNonClustIdxInKB INT -- CLUSTERED INDEX size mulitplied by all NONCLUSTERED indexes on that TABLE )
------------------------------------------------------------------------ -- Iteration value is determinned by how large the largest composite clustered index is ------------------------------------------------------------------------ SET @Iteration = (SELECT MAX(C.KEY_ORDINAL) FROM SYS.INDEXES I
JOIN SYS.OBJECTS S -- Joining on SYS.OBJECTS to get the TableName ON S.OBJECT_ID = I.Object_ID -- to ensure the
JOIN SYS.INDEX_COLUMNS C ON C.OBJECT_ID = I.Object_ID AND C.INDEX_ID = I.INDEX_ID -- ** 12/03/2010 CAJ **
WHERE I.Type = @ClusteredIndexType AND I.OBJECT_ID NOT IN (SELECT OBJECT_ID FROM SYS.ALL_OBJECTS WHERE TYPE = 'S') -- Not system tables AND S.Type = 'U' AND S.is_ms_shipped = 0)
SET @AddColumnIteration = 1;
------------------------------------------------------------------------ -- Adding the remaining columns to the #ClusteredIndex Table (determinned by largest composite index) ------------------------------------------------------------------------ WHILE (@Iteration >= @AddColumnIteration) BEGIN SET @Tmp = 'ALTER TABLE #ClusteredIndexes ADD Column'+CONVERT(VARCHAR,@AddColumnIteration)+'Name VARCHAR(75), Column'+CONVERT(VARCHAR,@AddColumnIteration)+'Type VARCHAR(25), Column'+CONVERT(VARCHAR,@AddColumnIteration)+'SizeInBytes VARCHAR(25); '
EXEC (@Tmp);
SET @AddColumnIteration = @AddColumnIteration + 1; END
------------------------------------------------------------------------ -- Inserting the List of Clustered Indexes into the #ClusteredIndex Table ------------------------------------------------------------------------ INSERT INTO #ClusteredIndexes (ClusteredIndexName, SchemaName, TableName, IndexColumnCount, IsUnique) SELECT 'ClusteredIndexName' = QUOTENAME(SI.Name), 'SchemaName' = QUOTENAME(SS.Name), 'TableName' = QUOTENAME(SO.Name), 'ColumCount' = IK.IndexColumnCount, 'IsUnique' = CASE WHEN SI.is_unique = 0 THEN 'N' ELSE 'Y' END
FROM SYS.INDEXES SI
JOIN SYS.OBJECTS SO -- Joining on SYS.OBJECTS to get the TableName ON SO.OBJECT_ID = SI.Object_ID
JOIN SYS.SCHEMAS SS -- Joining on SYS.SCHEMAS to get the SchemaName ON SS.Schema_ID = SO.Schema_ID
JOIN ( -- Joining on a Derived view to work out how many columns exist on the clustered index SELECT 'IndexColumnCount' = MAX(KEY_ORDINAL), OBJECT_ID, index_id FROM SYS.INDEX_COLUMNS GROUP BY OBJECT_ID, index_id ) AS IK ON IK.object_id = SI.Object_ID AND IK.index_id = SI.index_id
WHERE SI.Type = @ClusteredIndexType AND SI.OBJECT_ID NOT IN (SELECT OBJECT_ID FROM SYS.ALL_OBJECTS WHERE TYPE = 'S') -- Not system tables AND SO.Type = 'U' AND SO.is_ms_shipped = 0
------------------------------------------------------------------------ -- Creating CLUSTERED INDEX on the Identity column on #ClusteredIndexes TEMP TABLE ------------------------------------------------------------------------ CREATE CLUSTERED INDEX XIE1ClusteredIndexes ON #ClusteredIndexes (ClusteredIndexID)
------------------------------------------------------------------------ -- UPDATING the number of ROWS per TABLE ------------------------------------------------------------------------ UPDATE #ClusteredIndexes SET TableRowCount = ( SELECT 'TableRowCount' = P.Rows FROM SYS.PARTITIONS P
JOIN SYS.TABLES T -- Joining on SYS.TABLES to get the TableName ON P.OBJECT_ID = T.Object_ID
JOIN SYS.SCHEMAS S -- Joining on SYS.SCHEMAS ON T.Schema_id = S.Schema_id
WHERE P.index_id IN (0,1) AND QUOTENAME(S.Name) = C.SchemaName AND QUOTENAME(T.Name) = C.TableName)
FROM #ClusteredIndexes C
------------------------------------------------------------------------ -- UPDATING the number of NONCLUSTERED INDEXES per TABLE ------------------------------------------------------------------------ UPDATE #ClusteredIndexes SET NonClusteredIndexCount = ISNULL(( SELECT 'NonClusteredIndexCount' = COUNT(*) FROM SYS.INDEXES I
JOIN SYS.OBJECTS S -- Joining on SYS.OBJECTS to get the TableName ON S.OBJECT_ID = I.Object_ID
WHERE I.type = @NonClusteredIndexType AND QUOTENAME(S.Name) = T.TableName GROUP BY I.object_id),0)
FROM #ClusteredIndexes T
------------------------------------------------------------------------ -- Iterate through the #ClusteredIndexes Table: -- - Updates the column names for each clustered index along with their correct ordinal position -- - Updating the column types of the Clustered Index -- - Updating the max sizes of each of the columns contained within the Clustered Index -- - Updating the total size of the Clustered Index -- - Updating the Clustered Index Potential Max Size (Clustered index size per row * by rows on table) = ClusteredIndexSizeKB -- - Updating the Clustered Index Potential Max that is attached to the NONCLUSTERED INDEXES (ClusteredIndexSizeKB * Non Clustered Indexes) ------------------------------------------------------------------------ DECLARE cu_PrimaryKeyColumns CURSOR FOR SELECT ClusteredIndexID, ClusteredIndexName, SchemaName, TableName FROM #ClusteredIndexes
OPEN cu_PrimaryKeyColumns
FETCH NEXT FROM cu_PrimaryKeyColumns INTO @ClusteredIndexID, @ClusterKeyName, @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0 BEGIN SET @Iteration = (SELECT IndexColumnCount FROM #ClusteredIndexes -- Determine inner loop by how many columns comprise of the CLUSTERED INDEX WHERE ClusteredIndexID = @ClusteredIndexID)
WHILE (@Iteration > 0) -- inner loop - one iteration per column on the CLUSTERED INDEX BEGIN SET @Tmp = '(SELECT QUOTENAME(C.Name)
FROM SYS.COLUMNS C
JOIN SYS.INDEX_COLUMNS IC ON C.object_id = IC.object_id AND C.column_id = IC.column_id
----------------------------------------------------------------------------------- -- Updating the column names of the Clustered Index ----------------------------------------------------------------------------------- SET @Tmp = ' UPDATE #ClusteredIndexes SET Column'+CONVERT(VARCHAR,@Iteration)+'Name = (SELECT ColumnName FROM #Result) WHERE '+ CONVERT(VARCHAR,@Iteration) + ' = '+CONVERT(VARCHAR,@Iteration)+' AND ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ' '
EXEC (@Tmp);
----------------------------------------------------------------------------------- -- Updating the column types of the Clustered Index ----------------------------------------------------------------------------------- SET @Tmp = ' UPDATE #ClusteredIndexes SET Column'+CONVERT(VARCHAR,@Iteration)+'Type = (SELECT T.Name FROM SYS.TYPES T
JOIN SYS.COLUMNS C ON C.user_type_id = T.user_type_id
WHERE QUOTENAME(C.Name) = Column'+CONVERT(VARCHAR,@Iteration)+'Name AND C.Object_id = OBJECT_ID('''+ @SchemaName + '.' +@TableName+'''))
WHERE Column'+CONVERT(VARCHAR,@Iteration)+'Name = (SELECT Column'+CONVERT(VARCHAR,@Iteration)+'Name FROM #ClusteredIndexes WHERE ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ' ) ' -- ** 12/03/2010 CAJ ** added @SchemaName EXEC (@Tmp);
----------------------------------------------------------------------------------- -- Updating the column sizes of the Clustered Index ----------------------------------------------------------------------------------- SET @Tmp =' UPDATE #ClusteredIndexes SET Column'+CONVERT(VARCHAR,@Iteration)+'SizeInBytes = (SELECT CASE WHEN T.system_type_id = 167 THEN (c.max_length + 2) -- VARCHAR WHEN T.system_type_id = 231 THEN ((c.max_length * 2) + 2) -- NVARCHAR WHEN T.system_type_id = 175 THEN (c.max_length) -- CHAR WHEN T.system_type_id = 239 THEN (c.max_length * 2) -- NCHAR WHEN T.system_type_id = 106 AND (c.precision < 10)THEN 5 -- DECIMAL WHEN T.system_type_id = 106 AND (c.precision < 20 AND c.precision > 9)THEN 9 -- DECIMAL WHEN T.system_type_id = 106 AND (c.precision < 29 AND c.precision > 19)THEN 13 -- DECIMAL WHEN T.system_type_id = 106 AND (c.precision > 28) THEN 17 -- DECIMAL WHEN T.system_type_id = 108 AND (c.precision < 10)THEN 5 -- NUMERIC WHEN T.system_type_id = 108 AND (c.precision < 20 AND c.precision > 9)THEN 9 -- NUMERIC WHEN T.system_type_id = 108 AND (c.precision < 29 AND c.precision > 19)THEN 13 -- NUMERIC WHEN T.system_type_id = 108 AND (c.precision > 28) THEN 17 -- NUMERIC WHEN T.system_type_id = 127 THEN 8 -- BIGINT WHEN T.system_type_id = 56 THEN 4 -- INT WHEN T.system_type_id = 52 THEN 2 -- SMALLINT WHEN T.system_type_id = 48 THEN 1 -- TINYINT WHEN T.system_type_id = 173 THEN (c.max_length) -- BINARY WHEN T.system_type_id = 165 THEN (c.max_length + 2) -- VARBINARY WHEN T.system_type_id = 61 THEN 8 -- DATETIME WHEN T.system_type_id = 58 THEN 4 -- SMALLDATETIME WHEN T.system_type_id = 104 THEN 1 -- BIT WHEN T.system_type_id = 36 THEN 16 -- UNIQUEIDENTIFIER WHEN T.system_type_id = 60 THEN 8 -- MONEY WHEN T.system_type_id = 122 THEN 4 -- SMALLMONEY WHEN T.system_type_id = 62 THEN 4 -- FLOAT WHEN T.system_type_id = 59 THEN 4 -- REAL
-- These DataTypes are not being checked for: image, sql_variant, ntext, timestamp, xml, sysname END
FROM SYS.TYPES T
JOIN SYS.COLUMNS C ON C.user_type_ID = T.user_type_ID
WHERE QUOTENAME(C.Name) = Column'+CONVERT(VARCHAR,@Iteration)+'Name AND C.Object_id = OBJECT_ID('''+ @SchemaName + '.' +@TableName+'''))
WHERE Column'+CONVERT(VARCHAR,@Iteration)+'Name = (SELECT Column'+CONVERT(VARCHAR,@Iteration)+'Name FROM #ClusteredIndexes WHERE ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ')' -- ** 12/03/2010 CAJ ** added @SchemaName
EXEC (@Tmp);
----------------------------------------------------------------------------------- -- Updating the total size of the Clustered Index ----------------------------------------------------------------------------------- SET @Tmp = ' UPDATE #ClusteredIndexes SET IndexSizeInBytesPerRow = IndexSizeInBytesPerRow + Column'+CONVERT(VARCHAR,@Iteration)+'SizeInBytes WHERE Column'+CONVERT(VARCHAR,@Iteration)+'SizeInBytes IS NOT NULL AND ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ' '
EXEC (@Tmp);
DELETE FROM #Result;
SET @Iteration = (@Iteration - 1); -- less one iteration END -- end of Inner Loop once all columns of the CLUSTERED INDEX have been iterated through
----------------------------------------------------------------------------------- -- Updating the Clustered Index Potential Max Size ----------------------------------------------------------------------------------- SET @Tmp = ' UPDATE #ClusteredIndexes SET ClusteredIndexSizeKB = (IndexSizeInBytesPerRow * TableRowCount) / 1024 WHERE ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ' '
EXEC (@Tmp);
----------------------------------------------------------------------------------- -- Updating the Clustered Index Potential Max that is attached to the NONCLUSTERED INDEXES ----------------------------------------------------------------------------------- SET @Tmp = ' UPDATE #ClusteredIndexes SET ClustIdxSzTmsByNonClustIdxInKB = (NonClusteredIndexCount * ClusteredIndexSizeKB) WHERE ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ' AND NonClusteredIndexCount <> 0 '
EXEC (@Tmp);
FETCH NEXT FROM cu_PrimaryKeyColumns INTO @ClusteredIndexID, @ClusterKeyName, @SchemaName, @TableName END
CLOSE cu_PrimaryKeyColumns DEALLOCATE cu_PrimaryKeyColumns
------------------------------------------------------------------------ -- PRINT out CLUSTERED INDEX''s along with other useful information ------------------------------------------------------------------------ PRINT 'The following output shows all USER TABLES CLUSTERED INDEX''s along with the columns that comprise the INDEX.' PRINT 'Other useful information is also output, such as the potential size of the CLUSTERED INDEX according to the datatype''s that' PRINT 'are included in the CLUSTERED INDEX. The total rows per table is calculated, which is then used to muliply against the potential' PRINT 'CLUSTERED INDEX size to output the potential cost of disk space directly relating to the CLUSTERED INDEX.' PRINT 'The total number of NONCLUSTERED INDEXES are also displayed per TABLE. A calculation is taken in regards to the NONCLUSTERED INDEX also' PRINT 'since each NONCLUSTERED INDEX contains the CLUSTERED INDEX within itself therefore adding more expense to the disk space.
' SELECT * FROM #ClusteredIndexes ORDER BY ClustIdxSzTmsByNonClustIdxInKB DESC --ORDER BY ClusteredIndexSizeKB DESC --ORDER BY TableRowCount DESC --ORDER BY IndexColumnCount DESC --ORDER BY IndexSizeInBytesPerRow DESC
------------------------------------------------------------------------ -- PRINT out TABLES that have no CLUSTERED INDEX ------------------------------------------------------------------------ PRINT 'The following TABLE''s have no CLUSTERED INDEX at all (regarded as heaps).
' SELECT QUOTENAME(Name) FROM SYS.TABLES WHERE is_ms_shipped = 0 EXCEPT SELECT TableName FROM #ClusteredIndexes
IF OBJECT_ID(N'tempdb..#ClusteredIndexes', N'U') IS NOT NULL DROP TABLE #ClusteredIndexes; IF OBJECT_ID(N'tempdb..#Result', N'U') IS NOT NULL DROP TABLE #Result;
