April 11, 2013 at 6:01 am
Hello,
I have been running the DBTA on a database to get some recommendations, I have the results, nothing has been applied.
Afterwards I ran the script below that I got from the web, I first ran it in dev (where DBTA had not been run) to test it, I then ran it in live to compare the output. The script appears to have also listed all the indexes listed in the DBTA results as well. I have checked to make sure these were not applied in live, why have all the dta indexes also been listed? Where are they and how can I prevent them from being listed? I only want to list the indexes currently in use. After the code I have a another question.
SET NOCOUNT ON;
DECLARE @First[smallint]
,@Last[smallint]
,@IsUnique[smallint]
,@HasNonKeyCols[char](1)
,@TableName[varchar](256)
,@IndexName[varchar](256)
,@IndexType[varchar](13)
,@IndexColumns[varchar](1000)
,@IncludedColumns[varchar](1000)
,@IndexColsOrder[varchar](1000)
,@IncludedColsOrder[varchar](1000)
DECLARE @Indexes TABLE ([RowNo][smallint] IDENTITY(1, 1)
,[TableName][varchar](256)
,[IndexName][varchar](256)
,[IsUnique][smallint]
,[IndexType][varchar](13))
DECLARE @AllIndexes TABLE ([RowNo][smallint] IDENTITY(1, 1)
,[TableName][varchar](256)
,[IndexName][varchar](256)
,[IndexType][varchar](13)
,[KeyColumns][varchar](512)
,[NonKeyColumns][varchar](512)
,[KeyColumnsOrder][varchar](512)
,[NonKeyColumnsOrder][varchar](512)
,[IsUnique][char](1)
,[HasNonKeyColumns][char](1))
IF OBJECT_ID('Tempdb.dbo.#Temp') IS NOT NULL
DROP TABLE #Temp
SELECT o.[object_id] AS [ObjectID]
,OBJECT_NAME(o.[object_id]) AS [TableName]
,i.[index_id] AS [IndexID]
,i.[name] AS [IndexName]
,CASE i.[type]
WHEN 0 THEN 'Heap'
WHEN 1 THEN 'Clustered'
WHEN 2 THEN 'Non-Clustered'
WHEN 3 THEN 'XML'
ELSE 'Unknown'
END AS [IndexType]
,ic.[column_id] AS [ColumnID]
,c.[name] AS [ColumnName]
,ic.[is_included_column] [IncludedColumns]
,i.[is_unique] AS [IsUnique]
INTO #Temp
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
AND o.type = 'U' AND i.index_id > 0
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id
AND i.object_id = ic.object_id
INNER JOIN sys.columns c
ON c.column_id = ic.column_id
AND c.object_id = ic.object_id
INSERT INTO @Indexes
SELECT DISTINCT
[TableName]
,[IndexName]
,[IsUnique]
,[IndexType]
FROM #Temp
SELECT @First = MIN([RowNo]) FROM @Indexes
SELECT @Last = MAX([RowNo]) FROM @Indexes
WHILE @First <= @Last
BEGIN
SET @IndexColumns=NULL
SET @IncludedColumns=NULL
SET @IncludedColsOrder=NULL
SET @IndexColsOrder=NULL
SELECT @TableName = [TableName]
,@IndexName = [IndexName]
,@IsUnique = [IsUnique]
,@IndexType = [IndexType]
FROM @Indexes
WHERE [RowNo] = @First
SELECT @IndexColumns = COALESCE(@IndexColumns + ', ', '') + [ColumnName]
FROM #Temp
WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 0
ORDER BY [IndexName], [ColumnName]
SELECT @IncludedColumns = COALESCE(@IncludedColumns+ ', ', '') + [ColumnName]
FROM #Temp
WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 1
ORDER BY [IndexName], [ColumnName]
SELECT @IndexColsOrder = COALESCE(@IndexColsOrder + ', ', '') + [ColumnName]
FROM #Temp
WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 0
SELECT @IncludedColsOrder = COALESCE(@IncludedColsOrder + ', ', '') + [ColumnName]
FROM #Temp
WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 1
SET @HasNonKeyCols = 'N'
IF @IncludedColumns IS NOT NULL
BEGIN
SET @HasNonKeyCols = 'Y'
END
INSERT INTO @AllIndexes ([TableName]
,[IndexName]
,[IndexType]
,[IsUnique]
,[KeyColumns]
,[KeyColumnsOrder]
,[HasNonKeyColumns]
,[NonKeyColumns]
,[NonKeyColumnsOrder] )
SELECT @TableName
,@IndexName
,@IndexType
,CASE @IsUnique
WHEN 1 THEN 'Y'
WHEN 0 THEN 'N'
END
,@IndexColumns
,@IndexColsOrder
,@HasNonKeyCols
,@IncludedColumns
,@IncludedColsOrder
SET @First = @First + 1
END
SELECT 'Listing All Indexes' AS [Comments]
SELECT [TableName]
,[IndexName]
,[IndexType]
,[KeyColumns]
,[HasNonKeyColumns]
,[NonKeyColumns]
,[KeyColumnsOrder]
,[NonKeyColumnsOrder]
,[IsUnique]
FROM @AllIndexes
SELECT 'Listing Duplicate Indexes' AS [Comments]
SELECT DISTINCT
a1.[TableName]
,a1.[IndexName]
,a1.[IndexType]
,a1.[KeyColumns]
,a1.[HasNonKeyColumns]
,a1.[NonKeyColumns]
,a1.[KeyColumnsOrder]
,a1.[NonKeyColumnsOrder]
,a1.[IsUnique]
FROM @AllIndexes a1
JOIN @AllIndexes a2
ON a1.[TableName] = a2.TableName
AND a1.[IndexName] <> a2.[IndexName]
AND a1.[KeyColumns] = a2.[KeyColumns]
AND ISNULL(a1.[NonKeyColumns], '') = ISNULL(a2.[NonKeyColumns], '')
WHERE a1.[IndexType] <> 'XML'
SET NOCOUNT OFF;
Is it bad practice to have a non clusted index on top of a clusted index?
Thanks for any help.
Regards,
D.
April 11, 2013 at 6:20 am
Duran i guess you'd have to show us an example or two from the results of the query, instead of the query itself.
for example, maybe the recommendation is an index with INCLUDE columns, but we can't see what you were seeing, so everything would be wild guesses.
Lowell
April 11, 2013 at 8:21 am
Hello Lowell,
Thanks for getting back, here is an example of what came back, as well as these the indexes that are actually in the table also came up, but mixed in with these.
_dta_index_Assigned_Tests_9_29959183__K3_K2_1040 Non-Clustered
_dta_index_Assigned_Tests_9_29959183__K3_K2_4_5_6_7_8_10 Non-Clustered
_dta_index_Assigned_Tests_9_29959183__K3_K2_4_5_6_7_8_10_8337 Non-Clustered
_dta_index_Assigned_Tests_9_29959183__K3_K2_9 Non-Clustered
What do you think? Is the script picking the indexes out of a cache rather than taking the information from the database?
Regards,
D.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply