SQL Server Performance Optimization-duplicate Index

Read navigation

1.Overview

2. What is duplicate index?

3. Search for duplicate Indexes

4. Delete duplicate Indexes

5. Summary

 

1.Overview

Many people know that indexing has advantages and disadvantages in the database. Like other mainstream commercial databases, SQL Server allows repeated index creation on one column. Because SQL Server does not limit the number of duplicate indexes, it only limits the maximum number of duplicate indexes that can be created on a table in the database. This increases the possibility of duplicate indexes in the database. If duplicate indexes exist on the columns of the table, the database performance may be significantly impaired, because SQL Server must maintain each duplicate index separately. In addition, when SQL Server optimizes query statements, the query optimizer also considers this problem, which leads to a series of performance problems. You need to understand what fact duplicate indexes, how to find them, and how to remove them.

 

2. What is duplicate index?

Assume that a table named Test_Table has four columns (Col1, Col2, Col3, Col4)

CREATE TABLE Test_TableCREATE TABLE Test_Table(    Col1 int NOT NULL PRIMARY KEY,    Col2 varchr(30) NOT NULL,    Col3 varchr(30) NOT NULL,    Col4 varchr(30) NOT NULL,)

 

1) create different types of indexes in the primary key column

   1: CREATE UNIQUE CLUSTERED INDEX IX1 ON Test_Table(Col1); 

   2:  

   3: CREATE INDEX IX2 ON Test_Table(Col1); 

2) create indexes of the included columns in different sequence in non-primary key columns

   1: CREATE INDEX IX3 ON Test_Table (Col4) 

   2: INCLUDE (Col2, Col3); 

   3:  

   4: CREATE INDEX IX4 ON Test_Table (Col4) 

   5: INCLUDE (Col3, Col2); 

3) create an Index containing columns in the same sequence on non-primary key columns

   1: CREATE INDEX IX5 ON Test_Table (Col4) 

   2: INCLUDE (Col2, Col3); 

   3:  

   4: 

Create unique index IX6 ON Test_Table (Col4)

   5: INCLUDE (Col2, Col3); 

4) create indexes of different sequence in different non-primary key columns

   1: CREATE INDEX IX7 ON Test_Table (Col3, Col2); 

   2:  

   3: 

Create index IX8 ON Test_Table (Col3, Col2 );

The duplicate indexes must be updated when you perform DML operations (insert, update, and delete.

 

3. Search for duplicate Indexes

Generally, no one creates duplicate indexes. Sometimes you create an index without knowing it. Sometimes, the new index does not check whether the current column already exists. So how can we expose them?

1) when SQL Server Management Studio (SSMS) is used, but SQL Server has many databases and there are large tables and indexes in the database, SSMS is not a quick method.

2) UseSp_helpindexSearch for duplicate Indexes

3) using the SQL Server System Directory, you can use and develop scripts on the SQL Server database to find duplicate indexes. This is a convenient and flexible way.

SQL System directory:

A.Sys. indexes: Includes each row of an index or heap of a table object (such as a table, view, or table value function)

B.Sys. objects: Each user-defined Schema-scope object created in the database corresponds to a row in the table.

C.Sys. index_columns: Each column of the sys. indexes index or unordered table (HEAP) corresponds to a row.

D.Sys. columns: Returns each row of a column containing a column object (such as a view or table ).

The following table contains the column object type:

A) Table value Assembly function (FT)

B) inline Table value SQL function (IF)

C) Internal table (IT)

D) system table (S)

E) Table value SQL function (TF)

F) User table (U)

G) view (V)

One way is to list the tables on which all indexes are located, the number of times they are scanned, the number of times they are updated, the size in memory, and the useful information for us.

ViewSELECT sch. name + '. '+ t. name AS [Table Name], I. name AS [Index Name], I. type_desc, ISNULL (user_updates, 0) AS [Total Writes], ISNULL (user_seeks + user_scans + user_lookups, 0) AS [Total Reads], s. last_user_seek, s. last_user_scan, s. last_user_lookup, ISNULL (user_updates, 0)-ISNULL (user_seeks + user_scans + user_lookups), 0) AS [Difference], p. reserved_page_count * 8.0/1024 as SpaceInMBFROM sys. indexes AS I WITH (NOLOCK) LEFT OUTER JOIN sys. dm_db_index_usage_stats AS sWITH (NOLOCK) ON s. object_id = I. object_id AND I. index_id = s. index_id AND s. database_id = db_id () AND objectproperty (s. object_id, 'isusertable') = 1 INNER JOINsys. tablesAS tWITH (NOLOCK) ON I. object_id = t. object_id INNER JOINsys. schemasAS schWITH (NOLOCK) ON t. schema_id = sch. schema_id left outer join sys. dm_db_partition_statsAS pWITH (NOLOCK) ON I. index_id = p. index_id and I. object_id = p. object_idWHERE (1 = 1) -- and isnull (user_updates, 0)> = ISNULL (user_seeks + user_scans + user_lookups), 0) -- display all constraints including unused constraints -- and isnull (user_updates, 0)-ISNULL (user_seeks + user_scans + user_lookups), 0)> 0 -- only show the indexes used -- AND I. index_id> 1 -- Non-first index -- AND I. is_primary_key <> 1 -- not defined as the primary key -- AND I. is_unique_constraint <> 1 -- not UniqueConstraints order by [Table Name], [index name]

 

Another method is to search for duplicate indexes based on columns.

View/* after executing this script, the index will be displayed in three reports.
See the following:
1. list key information about all indexes and constraints
2. List potential redundant indexes of a table
3. List potential reverse indexes of a table
*/
-- Create a table that stores index information
DECLARE @ AllIndexes TABLE (
[Table ID] [int] not null,
[Schema] [sysname] not null,
[Table Name] [sysname] not null,
[Index ID] [int] NULL,
[Index Name] [nvarchar] (128) NULL,
[Index Type] [varchar] (12) not null,
[Constraint Type] [varchar] (11) not null,
[Object Type] [varchar] (10) not null,
[AllColName] [nvarchar] (2078) NULL,
[ColName1] [nvarchar] (128) NULL,
[ColName2] [nvarchar] (128) NULL,
[ColName3] [nvarchar] (128) NULL,
[ColName4] [nvarchar] (128) NULL,
[ColName5] [nvarchar] (128) NULL,
[ColName6] [nvarchar] (128) NULL,
[ColName7] [nvarchar] (128) NULL,
[ColName8] [nvarchar] (128) NULL,
[ColName9] [nvarchar] (128) NULL,
[ColName10] [nvarchar] (128) NULL
)
-- Load the index information to the following statement
Insert into @ AllIndexes
([Table ID], [Schema], [Table Name], [Index ID], [Index Name], [Index Type], [Constraint Type], [Object Type]
, [AllColName], [ColName1], [ColName2], [ColName3], [ColName4], [ColName5], [ColName6], [ColName7], [ColName8],
[ColName9], [ColName10])
SELECT o. [object_id] AS [Table ID], u. [name] AS [Schema], o. [name] AS [Table Name],
I. [index_id] AS [Index ID]
, CASE I. [name]
WHEN o. [name] THEN '** Same as Table Name **'
ELSE I. [name] END AS [Index Name],
CASE I. [type]
WHEN 1 THEN 'clustered'
WHEN 0 THEN 'heap'
WHEN 2 THEN 'nonclustered'
WHEN 3 THEN 'xml'
ELSE 'unknon' end as [Index Type],
CASE
WHEN (I. [is_primary_key]) = 1 THEN 'Primary key'
WHEN (I. [is_unique]) = 1 THEN 'unique'
ELSE ''end AS [Constraint Type],
CASE
WHEN (I. [is_unique_constraint]) = 1
OR (I. [is_primary_key]) = 1
THEN 'constraint'
WHEN I. [type] = 0 THEN 'heap'
WHEN I. [type] = 3 THEN 'xml Index'
ELSE 'index' end as [Object Type],
(Select coalesce (c1. [name], '') FROM [sys]. [columns] AS c1 inner join [sys]. [index_columns] AS ic1
ON c1. [object_id] = ic1. [object_id] AND c1. [column_id] = ic1. [column_id] AND ic1. [key_ordinal] = 1
WHERE ic1. [object_id] = I. [object_id] AND ic1. [index_id] = I. [index_id]) +
CASE
WHEN INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 2) is null then''
ELSE ',' + INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 2) END +
CASE
WHEN INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 3) is null then''
ELSE ',' + INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 3) END +
CASE
WHEN INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 4) is null then''
ELSE ',' + INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 4) END +
CASE
WHEN INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 5) is null then''
ELSE ',' + INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 5) END +
CASE
WHEN INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 6) is null then''
ELSE ',' + INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 6) END +
CASE
WHEN INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 7) is null then''
ELSE ',' + INDEX_COL ('[' + u. [name] + ']. ['+ o. [name] + ']', I. [index_id], 7) END +
CASE