[Prior Post in Series]

The result of the tuning exercise is TSQL shown below. The items included are those suggested by Database Engine Tuning Advisor (DTA) evaluated as being prime candidates by the author. The performance improvement expected is at least 15% and may be considerably more. A trace log of your operational SmarTeam should be captured and a final DTA analysis should occur to finish the tuning. Apart from statistics, the author hopes that the number of additional indexes will be small ( < 10% of the table would need additional indexes).

The key changes below are done using patterns that examine the actual tables in your database (which will differ from installation to installation) and dynamically generate conservative indexes on these tables. The main changes are:

  • Putting indexes on tables that appear to have a distinctive pattern.
  • Putting a CLUSTERED Index on tables to define the order that the records are on the hard drives.
    • If a view is a filter of a table, then put the filtering column in the index so all of the records of the view are adjacent on the disk.

A few notes:

  • I removed the /*UNIQUE*/ to eliminate possible conflicts with updates or data scenarios not found in the database I was using.
  • Every table should have at least one index on it.
  • I did not add any primary keys for a variety of reasons, instead I constrained myself to adding non-unique indexes.
  • You may repeatedly execute the code (for example at the end of each month) without creating problems. Only missing indexes are created.
  • At the bottom of this post there is a link to a TSQL file that contains everything ready to run.

CAVAET: This code is supplied ‘AS IS’ and without warranty. Always test code on a recent backup before applying to a production system.

Step #1

The TSQL below applies patterns to the entire database that result in most tables having at least one index. There should be no errors during the execution and the changes should not impact updates or break existing code.

SET NOCOUNT ON DECLARE @CMD nvarchar(max) DECLARE @Schema nvarchar(max) DECLARE @Table nvarchar(max) -- STEP A DECLARE PK_Cursor CURSOR FOR Select o.Table_Schema, O.Table_Name FROM Information_Schema.Columns O JOIN Information_Schema.Columns C ON O.Table_Name=C.Table_Name AND O.Table_Schema=C.Table_Schema JOIN Information_Schema.Tables T ON O.Table_Name=T.Table_Name AND O.Table_Schema=T.Table_Schema WHERE O.Column_Name='Object_ID' AND C.Column_Name='Class_ID' AND T.Table_Type='BASE TABLE' ORDER BY T.Table_Name OPEN PK_Cursor; FETCH NEXT FROM PK_Cursor INTO @Schema,@Table WHILE @@FETCH_STATUS = 0 BEGIN SET @CMD='CREATE /*UNIQUE*/ CLUSTERED INDEX [PK_' +@TABLE+'] ON ['+@Schema+'].[' +@Table+']([Object_ID] ASC,[Class_ID] 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 END; CLOSE PK_Cursor; DEALLOCATE PK_Cursor; -- STEP B DECLARE PK_Cursor CURSOR FOR Select o.Table_Schema, O.Table_Name FROM Information_Schema.Columns O JOIN Information_Schema.Columns C ON O.Table_Name=C.Table_Name AND O.Table_Schema=C.Table_Schema JOIN Information_Schema.Columns D ON O.Table_Name=D.Table_Name AND O.Table_Schema=D.Table_Schema JOIN Information_Schema.Columns E ON O.Table_Name=E.Table_Name AND O.Table_Schema=E.Table_Schema JOIN Information_Schema.Tables T ON O.Table_Name=T.Table_Name AND O.Table_Schema=T.Table_Schema WHERE O.Column_Name='CN_PROJECT_REFERENCE' AND C.Column_Name='OBJECT_ID' AND D.Column_Name='FILE_TYPE' AND E.Column_Name='FILE_NAME' AND T.Table_Type='BASE TABLE' ORDER BY T.Table_Name OPEN PK_Cursor; FETCH NEXT FROM PK_Cursor INTO @Schema,@Table WHILE @@FETCH_STATUS = 0 BEGIN SET @CMD='CREATE /*UNIQUE*/ CLUSTERED INDEX [PK_' +@TABLE+'] ON ['+@Schema+'].['+@Table +']([CN_PROJECT_REFERENCE] ASC, [OBJECT_ID] ASC, [FILE_TYPE] ASC,[FILE_NAME] ASC,[DIRECTORY] 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 END; CLOSE PK_Cursor; DEALLOCATE PK_Cursor; -- STEP C DECLARE PK_Cursor CURSOR FOR Select o.Table_Schema, O.Table_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.Column_Name='OBJECT_ID' AND T.Table_Type='BASE TABLE' AND O.Table_Name NOT IN ( SELECT Table_Name FROM Information_Schema.Columns WHERE Column_Name='CLASS_ID') ORDER BY T.Table_Name OPEN PK_Cursor; FETCH NEXT FROM PK_Cursor INTO @Schema,@Table WHILE @@FETCH_STATUS = 0 BEGIN SET @CMD='CREATE /*UNIQUE*/ CLUSTERED INDEX [PK_' +@TABLE+'] ON ['+@Schema+'].[' +@Table+']([Object_ID] 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 END; CLOSE PK_Cursor; DEALLOCATE PK_Cursor; -- STEP D DECLARE PK_Cursor CURSOR FOR Select o.Table_Schema, O.Table_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.Column_Name='CLASS_ID' AND T.Table_Type='BASE TABLE' AND O.Table_Name NOT IN ( SELECT Table_Name FROM Information_Schema.Columns WHERE Column_Name='OBJECT_ID') ORDER BY T.Table_Name OPEN PK_Cursor; FETCH NEXT FROM PK_Cursor INTO @Schema,@Table WHILE @@FETCH_STATUS = 0 BEGIN SET @CMD='CREATE /*UNIQUE*/ CLUSTERED INDEX [PK_' +@TABLE+'] ON ['+@Schema+'].[' +@Table+']([Class_ID] 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 END; CLOSE PK_Cursor; DEALLOCATE PK_Cursor; -- STEP E DECLARE PK_Cursor CURSOR FOR Select Table_Schema, Table_Name FROM Information_Schema.Columns where Column_Name='FILE_NAME' AND ORDINAL_POSITION=1 ORDER BY Table_Name OPEN PK_Cursor; FETCH NEXT FROM PK_Cursor INTO @Schema,@Table WHILE @@FETCH_STATUS = 0 BEGIN SET @CMD='CREATE /*UNIQUE*/ CLUSTERED INDEX [AKFN_' +@TABLE+'] ON ['+@Schema+'].[' +@Table+']([FILE_NAME] ASC)' IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name='AKFN_'+@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 END; CLOSE PK_Cursor; DEALLOCATE PK_Cursor;

Step #2: Tables without a Clustered Index

The TSQL below lists the tables lacking a clustered index.

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')

I did not have time to do analysis of each of these tables and resorted to type-casting table design. Typically keys are the first column(s) of a table; I used the following code to create a clustered index on the first column of all tables lacking a clustered index. You may wish to hand build clustered indexes for the above tables before running the next code sample.

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;

 

Step #3

After doing the above, I had a teleconference call with Mirek Tokarz and Ken Palmer of Langen Packaging Group and Marc Young at xML Solutions to discuss the results. They indicated that many of the regular production queries used two columns [CN_ID],[Description]. These queries were likely not captured by my trace log.

First, I wanted to check if they occur concurrently in any table, which I did by executing (with no rows returned):

select C1.Table_Name from information_Schema.Columns C1 JOIN information_Schema.Columns C2 ON C1.Table_Name=C2.Table_Name where C1.column_Name='DESCRIPTION' AND C2.column_Name='CN_ID'

I wrote the following code to create indices on these two columns.

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 T.Table_Type='BASE TABLE' AND O.Column_Name in ('CN_ID','DESCRIPTION') 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 INDEX [CI_'+@TABLE+'_'+@ColName+'] ON ['+@Schema+'].['+@Table+'](['+@ColName+'] ASC)' IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE Name='CI_'+@Table+'_'+@ColName) 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;

Step #4

I decided not to include other indices recommended by the DTA because my trace file was not large.  The recommended approach would be to capture a portion of a regular production day as a trace log and process it through DTA. If the number of indexes is less than 10% of the tables in the database, then apply all of them at this point or do further analysis

Step #5

Adding statistics gets a little more complex than adding indexes. In my post on statistics, I used the DTA’s XML as an input. In the TSQL below I hard coded the column sets that would be applied to simplify the code. I removed the [Class_Id] and [Object_ID] from the statistics because I know that these columns are always in at least one index.

DECLARE @CMD nvarchar(max),@ColName nvarchar(max), @Table nvarchar(max),@LastTable nvarchar(max),@StatID nvarchar(max),@InClause nvarchar(max) DECLARE @ColCnt int,@ColIdx int DECLARE @Xml xml SET @Xml='<dta> <statistics InCol="[CLASS_ID],[ROOT_OBJECT_ID],[ROOT_CLASS_ID]" StatId="2_3_4" ColCnt="3" InClause="''CLASS_ID'',''ROOT_OBJECT_ID'',''ROOT_CLASS_ID''" /> <statistics InCol="[COL_ORDER]" StatId="2" ColCnt="1" InClause="''COL_ORDER''" /> <statistics InCol="[OBJECT_ID],[CLASS_ID],[OBJECT_ID1],[CLASS_ID1],[OBJECT_ID2],[CLASS_ID2]" StatId="1_2_3_4_5_6" ColCnt="6" InClause="''OBJECT_ID'',''CLASS_ID'',''OBJECT_ID1'',''CLASS_ID1'',''OBJECT_ID2'',''CLASS_ID2''" /> <statistics InCol="[OBJECT_ID],[CLASS_ID],[CLASS_ID1],[OBJECT_ID1],[CLASS_ID2],[OBJECT_ID2]" StatId="1_2_17_18_19_20" ColCnt="6" InClause="''OBJECT_ID'',''CLASS_ID'',''CLASS_ID1'',''OBJECT_ID1'',''CLASS_ID2'',''OBJECT_ID2''" /> <statistics InCol="[OBJECT_ID],[CLASS_ID],[OBJECT_ID1],[OBJECT_ID2]" StatId="1_2_9_11" ColCnt="4" InClause="''OBJECT_ID'',''CLASS_ID'',''OBJECT_ID1'',''OBJECT_ID2''" /> <statistics InCol="[CLASS_ID],[REQUIERED],[PROJECTION_ID]" StatId="1_12_28" ColCnt="3" InClause="''CLASS_ID'',''REQUIERED'',''PROJECTION_ID''" /> <statistics InCol="[OBJECT_ID],[CN_SECTION],[CN_VALUE_1],[CN_VALUE_2],[CN_VALUE_3],[CN_VALUE_4]" StatId="1_2_3_4_5_8" ColCnt="6" InClause="''OBJECT_ID'',''CN_SECTION'',''CN_VALUE_1'',''CN_VALUE_2'',''CN_VALUE_3'',''CN_VALUE_4''" /> <statistics InCol="[OBJECT_ID],[CLASS_ID],[CN_CONTACT_TYPE],[CN_INDUSTRY_TYPE]" StatId="1_2_11_12" ColCnt="4" InClause="''OBJECT_ID'',''CLASS_ID'',''CN_CONTACT_TYPE'',''CN_INDUSTRY_TYPE''" /> <statistics InCol="[OBJECT_ID],[CLASS_ID],[CN_ID],[CREATION_DATE]" StatId="1_2_3_5" ColCnt="4" InClause="''OBJECT_ID'',''CLASS_ID'',''CN_ID'',''CREATION_DATE''" /> <statistics InCol="[OBJECT_ID],[CLASS_ID],[OBJECT_ID1],[CLASS_ID1],[OBJECT_ID2],[CLASS_ID2]" StatId="1_2_3_4_5_6" ColCnt="6" InClause="''OBJECT_ID'',''CLASS_ID'',''OBJECT_ID1'',''CLASS_ID1'',''OBJECT_ID2'',''CLASS_ID2''" /> <statistics InCol="[OBJECT_ID],[CN_MADE_FROM]" StatId="1_9" ColCnt="2" InClause="''OBJECT_ID'',''CN_MADE_FROM''" /> <statistics InCol="[OBJECT_ID],[CN_MADE_FROM]" StatId="1_6" ColCnt="2" InClause="''OBJECT_ID'',''CN_MADE_FROM''" /> <statistics InCol="[OBJECT_ID],[CLASS_ID],[CN_PROJECT_ID],[TDM_RESTRICTED]" StatId="1_2_3_13" ColCnt="4" InClause="''OBJECT_ID'',''CLASS_ID'',''CN_PROJECT_ID'',''TDM_RESTRICTED''" /> <statistics InCol="[USR_OBJECT_ID],[GRP_OBJECT_ID],[CNTX_OBJECT_ID],[CNTX_CLASS_ID]" StatId="8_9_10_11" ColCnt="4" InClause="''USR_OBJECT_ID'',''GRP_OBJECT_ID'',''CNTX_OBJECT_ID'',''CNTX_CLASS_ID''" /> <statistics InCol="[OBJECT_ID],[CLASS_ID],[LOGIN],[FIRST_NAME]" StatId="1_2_3_10" ColCnt="4" InClause="''OBJECT_ID'',''CLASS_ID'',''LOGIN'',''FIRST_NAME''" /> </dta>' CREATE TABLE #Temp (Incol varchar(max),StatId varchar(max), ColCnt Int, InClause varchar(max)) INSERT INTO #Temp SELECT node.value('./@InCol','varchar(max)'), node.value('./@StatId','varchar(max)'), node.value('./@ColCnt','int'), node.value('./@InClause','varchar(max)') FROM @Xml.nodes('//statistics') as Ref(node) CREATE Table #temp3(TName varchar(max), InCol varchar(max), StatId varchar(max) ) SET @CMD='' DECLARE P2_Cursor CURSOR FOR Select InCol,ColCnt,StatId,InClause FROM #Temp ORDER BY InCol,ColCnt OPEN P2_Cursor; FETCH NEXT FROM P2_Cursor INTO @ColName,@ColCnt,@StatId, @InClause WHILE @@FETCH_STATUS = 0 BEGIN IF Len(@CMD) > 0 SET @CMD=@CMD+' UNION ' SET @CMD=@CMD+'Select Table_Name,'''+@ColName +''',''STATS_''+Table_Name+''_''+'''+@StatID+''' FROM Information_Schema.Columns JOIN Sys.Objects ON NAME=Table_Name AND Objects.Type=''U'' WHERE COLUMN_Name in ('+@InClause+') GROUP BY TABLE_NAME HAVING Count(1)=' +Cast(@ColCnt as varchar(11)) FETCH NEXT FROM P2_Cursor INTO @ColName,@ColCnt,@StatId,@InClause END CLOSE P2_Cursor; DEALLOCATE P2_Cursor; CREATE TABLE #Temp4 (TName sysname,Cols nvarchar(max), StatId sysname) INSERT INTO #Temp4 EXEC (@Cmd) DECLARE P3_Cursor CURSOR FOR Select TName,Cols,StatId FROM #temp4 OPEN P3_Cursor; FETCH NEXT FROM P3_Cursor INTO @Table,@ColName,@StatId WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS(SELECT 1 FROM Sys.Stats where Name=@STATID) BEGIN SET @CMD='CREATE STATISTICS ['+@STATID+'] ON ['+@Table +'] ('+REPLACE(REPLACE(@ColName,'[OBJECT_ID],',''),'[CLASS_ID],','')+')' EXEC(@CMD) END FETCH NEXT FROM P3_Cursor INTO @Table,@ColName,@StatId END CLOSE P3_Cursor; DEALLOCATE P3_Cursor; DROP TABLE #Temp DROP TABLE #Temp4 DROP TABLE #Temp3

I did not have time to evaluate several variations so the above is definitely “As Is” and may be improved further.

Summary

The goals of this series of posts are twofold:

  • Generate a TSQL script that would improve the performance on any SmartTeam installation by applying patterns.
    • SmarTeam can drop indices when the design is customized, so this script becomes a part of the post-customization process.
  • Show by example an approach for tuning performance using DTA that should result in better performance than the standard approach for this ISV product.

Feel free to email me the results of DTA after doing the above (Export the results as XML and write the recommendation as SQL) if you wish me to provide comments on your specific situation. Results submitted may be used for future posts in this series.

I have placed the complete TSQL above into a single file [here], if you wish to download it.