Tuning Enovia SmarTeam - Indexes - Conclusion

[Prior Post in Series] [Next Post in Series]

In this part, I will deal with the fact that many remaining tables do not have clustered indexes on them. In my humble opinion, ANY reasonable order of the disk will perform better than a random order, so putting a clustered index on every table is a must-do.

The pattern is simple, if a table does not have a clustered index, then put a clustered index on the first column. The typical behavior of database designers is to put the key indexes at the start of each table, so we are assuming this is true in this solution.

To obtain the list of tables lacking indexes, use this code snippet.

 SELECT O.NAME FROM Sys.Objects O 
WHERE  O.type='U'
AND O.Name NOT IN (
SELECT O.Name FROM Sys.Objects O 
JOIN Sys.Indexes I 
ON I.Object_Id=O.Object_Id 
AND O.type='U' 
AND I.type_desc='CLUSTERED')

You may wish to manually add some indexes (based on your domain knowledge) before proceeding further.

Code Solution

The solution below grabs the first column only. A more advanced approach would be to walk each table and build the index from the leading columns that are integer values.

 SET NOCOUNT ON
DECLARE @CMD nvarchar(max)
DECLARE @Schema nvarchar(max)
DECLARE @Table nvarchar(max)
DECLARE @ColName nvarchar(max)

DECLARE PK_Cursor CURSOR FOR
Select o.Table_Schema, O.Table_Name, O.Column_Name
    FROM Information_Schema.Columns O
    JOIN Information_Schema.Tables T
        ON O.Table_Name=T.Table_Name
        AND O.Table_Schema=T.Table_Schema 
    WHERE O.Ordinal_Position=1
        AND T.Table_Type='BASE TABLE'
        AND O.Table_Name NOT IN (
    SELECT O.Name FROM Sys.Objects O 
        JOIN Sys.Indexes I 
            ON I.Object_Id=O.Object_Id 
            AND O.type='U' 
            AND I.type_desc='CLUSTERED')
    ORDER BY O.Column_NAME

OPEN PK_Cursor;
FETCH NEXT FROM PK_Cursor INTO @Schema,@Table,@ColName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CMD='CREATE CLUSTERED INDEX [PK_'
    +@TABLE+'] ON ['+@Schema+'].['+@Table+'](['+@ColName+'] ASC)'
    IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name='PK_'+@Table)
    BEGIN TRY
        EXEC (@CMD)
    END TRY
    BEGIN CATCH
        SET @CMD='Error: '+@CMD
        Print @CMD
    END CATCH
    FETCH NEXT FROM PK_Cursor INTO @Schema,@Table,@ColName
END;
CLOSE PK_Cursor;
DEALLOCATE PK_Cursor;

Summary

In this post we looked at loose-ends: tables lacking any clustered indexes. We solved this problem by adding a clustered index using the first column. A lot more analysis could be done on this database, but I ran out of time (after an apparent doubling of performance).