Tuning Enovia Smarteam – End Solution

  • Article

[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.