SQL Server: Query to Find Duplicate Indexes.
Below script will return you database wise list of duplicate indexes on your server. If you want to use this specific database, please add condition to filter database name in the query with SYS.DATABASES. This script is the based on the script that I posted here on one of my previous post. Using this script you can identify the duplicate indexes and drop those, I hope it will be helpful.
SQL Server allows you to create many non-clustered indexes (999 non-clustered indexes in SQL Server 2008 or later versions), so technically you can create more than one non-clustered index on the same columns(s) with a different name. Recently I was evaluating one of database environment where we have hundreds of databases, this server houses databases for one of the major litigation product, so technically all the databases has same architecture but different set of data. I was evaluating our environment for the regular evaluation of indexes and its utilization and did reanalyses that databases has few duplicate indexes. Actually admin section of the application provides the ability to create indexes (may be for temporary use) from the UI and it was created by application users unintentionally without understating the impact of having these indexes on the major tables with millions of records.
I also noticed the overlapping index and I'll discuss about that in my next post.
Below script will return you database wise list of duplicate indexes on your server. If you want to use this specific database, please add condition to filter database name in the query with SYS.DATABASES. This script is the based on the script that I posted here on one of my previous post. Using this script you can identify the duplicate indexes and drop those, I hope it will be helpful.
USE master
GO
DECLARE
@DBName VARCHAR(100),
@DBCount INT,
@IndexCount INT,
@SQL VARCHAR(8000) = '',
@Columns varchar(2000) = '',
@IncludedColumns varchar(2000) = '',
@IndexName VARCHAR(1000) = ''
DECLARE @tblDBList
TABLE (DBName VARCHAR(100))
DECLARE
@tblIndexList TABLE (IndexName
VARCHAR(1000))
CREATE TABLE #tblIndexListSingleDB (DataBaseName VARCHAR(100), IndexName VARCHAR(1000), ColumnName VARCHAR(100), TableName VARCHAR(100), IndexType VARCHAR(100) , IsIncluded BIT)
CREATE TABLE #tblIndexListAllDB (RecordID INT Identity(1,1), DataBaseName VARCHAR(100), TableName VARCHAR(100), IndexName VARCHAR(1000), ColumnName VARCHAR(1000),
IncludedColumns VARCHAR(2000), IndexType VARCHAR(100))
INSERT INTO @tblDBList SELECT
name FROM SYS.DATABASES
--optional condition if you wish to use to this query on
limited database.
-- WHERE Name Like '%database name%'
-- WHERE database_id BETWEEN 5 AND 20
SELECT
@DBCount = COUNT(*) FROM @tblDBList
WHILE @DBCount
> 0
BEGIN
SELECT TOP 1 @DBName =
DBName FROM @tblDBList
SET @SQL = @SQL + ' INSERT INTO #tblIndexListSingleDB (DataBaseName, IndexName,
ColumnName, TableName, IndexType, IsIncluded) ' +
CHAr(13)
SET @SQL = @SQL + ' SELECT ' + '''' + @DBName + '''' + ' AS DataBaseName, IND.NAME
AS IndexName, COL.NAME AS ColumnName, T.Name AS TableName, IND.TYPE_DESC AS
IndexType , IC.IS_INCLUDED_COLUMN AS IsIncluded ' + CHAR(13)
SET @SQL = @SQL + ' FROM ' + @DBName + '.SYS.INDEXES IND ' + CHAR(13)
SET @SQL = @SQL + ' INNER JOIN ' +
@DBName + '.SYS.INDEX_COLUMNS
IC ON IND.OBJECT_ID = IC.OBJECT_ID AND IND.INDEX_ID = IC.INDEX_ID ' + CHAR(13)
SET @SQL = @SQL + ' INNER JOIN ' +
@DBName + '.SYS.COLUMNS
COL ON IC.OBJECT_ID = COL.OBJECT_ID AND IC.COLUMN_ID = COL.COLUMN_ID ' + CHAR(13)
SET @SQL = @SQL + ' INNER JOIN ' +
@DBName + '.SYS.TABLES
T ON IND.OBJECT_ID = T.OBJECT_ID ' + CHAR(13)
SET @SQL = @SQL + ' ORDER BY T.NAME, IND.NAME, IND.INDEX_ID, IC.INDEX_COLUMN_ID
' + CHAR(13)
EXEC(@SQL)
PRINT (@SQL)
INSERT INTO @tblIndexList SELECT
IndexName FROM #tblIndexListSingleDB GROUP BY IndexName
SELECT
@IndexCount = COUNT(*) FROM
@tblIndexList
WHILE
@IndexCount > 0
BEGIN
SELECT TOP 1 @IndexName =
IndexName FROM @tblIndexList
SELECT
@Columns = @Columns +
ColumnName + ', '
FROM
#tblIndexListSingleDB
WHERE IndexName = @IndexName AND
IsIncluded != 1 ORDER
BY ColumnName
IF LEN(@Columns) > 1
SET
@Columns = LEFT(@Columns, LEN(@Columns) - 1)
SELECT
@IncludedColumns = @IncludedColumns + ColumnName + ', '
FROM
#tblIndexListSingleDB
WHERE IndexName = @IndexName AND
IsIncluded = 1
ORDER BY ColumnName
IF LEN(@IncludedColumns) > 1
SET
@IncludedColumns = LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1)
INSERT INTO #tblIndexListAllDB (DataBaseName,
TableName, IndexName,
ColumnName, IncludedColumns, IndexType)
SELECT
DataBaseName, TableName,
IndexName, @Columns,
@IncludedColumns, IndexType
FROM #tblIndexListSingleDB
WHERE
IndexName = @IndexName
GROUP BY DataBaseName,
TableName, IndexName,
IndexType
DELETE FROM @tblIndexList WHERE
IndexName = @IndexName
SELECT
@IndexCount = COUNT(*) FROM
@tblIndexList
SET
@Columns = ''
SET
@IncludedColumns = ''
END
DELETE FROM @tblDBList WHERE
DBName =
@DBName
SELECT
@DBCount = COUNT(*) FROM @tblDBList
SET @SQL = ''
DELETE FROM #tblIndexListSingleDB
END
SELECT T1.DataBaseName, T1.TableName, T1.IndexName, T1.ColumnName, T1.IncludedColumns
FROM #tblIndexListAllDB T1
JOIN (SELECT DataBaseName, TableName,
ColumnName, IncludedColumns FROM #tblIndexListAllDB GROUP
BY DataBaseName,
TableName, ColumnName,
IncludedColumns HAVING COUNT(*) > 1) T2
ON T1.DataBaseName = T2.DataBaseName AND T1.TableName = T2.TableName AND T1.ColumnName = T2.ColumnName AND T1.IncludedColumns = T2.IncludedColumns
--SELECT * FROM
#tblIndexListAllDB
DROP TABLE #tblIndexListSingleDB
DROP TABLE #tblIndexListAllDB
먹튀사이트 잡는 고릴라의 먹튀검증 통과 메이저토토사이트 안전놀이터 추천 훌륭한 먹튀검증을 통한 안전토토사이트를 추천합니다 고릴라만의 검증 시스템은 특별합니다 전세계적인 먹튀검증을 인전받은 최고의 메이저사이트 추천을 합니다 자세한 내용은 내 웹 사이트를 방문하십시오 먹튀검증.
ReplyDelete