Unused database indexes found
To ensure quick access to data, like other relational database systems, SQL Server uses indexes to quickly search for data, SQL Server can create different types of indexes, such as clustered index, non-clustered index, XML index, and full text index.
The advantage of Multiple indexes is that if appropriate indexes exist, SQL Server can quickly access data. The disadvantage of too many indexes is that SQL Server has to maintain these indexes, which also requires a cost, in addition, indexes also require additional storage space. Therefore, indexes are a double-edged sword in terms of performance.
Let's take a look at how to identify existing but not used indexes, and then delete them to reduce storage requirements.
We know that the DMVs-dynamic management view is added to SQL Server 2005, allowing you to further explore what happened in SQL Servr, some of which are used to view how indexes are used, we will discuss two DMVs. Note that these views store the accumulated data. Therefore, when the SQL Server resets the status, the counter is set to 0. Therefore, pay attention to these when monitoring the index.
- DMV- sys.dm_db_index_operational_stats
This DMV allows you to view all aspects of the inserted, updated, and deleted index. Basically, it displays a lot of work to maintain the index in data-based modification.
If you return all the columns for a table query, the output may be confusing. Therefore, we focus on a few columns. If you want to know about other columns, you can view Online Books.
- SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
-
- I.[NAME] AS [INDEX NAME],
-
- A.LEAF_INSERT_COUNT,
-
- A.LEAF_UPDATE_COUNT,
-
- A.LEAF_DELETE_COUNT
-
- FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A
-
- INNER JOIN SYS.INDEXES AS I
-
- ON I.[OBJECT_ID] = A.[OBJECT_ID]
-
- AND I.INDEX_ID = A.INDEX_ID
-
- WHERE OBJECTPROPERTY(A.[OBJECT_ID],’IsUserTable’) = 1
The following shows the numbers of inserts, updates, and deletes on each index. Therefore, this indicates that SQL Server has to maintain the index for a lot of work.
- DMV – sys.dm_db_index_usage_stats
This DMV shows how many times the index is used in a user's query. For other columns, see books online.
- SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
-
- I.[NAME] AS [INDEX NAME],
-
- USER_SEEKS,
-
- USER_SCANS,
-
- USER_LOOKUPS,
-
- USER_UPDATES
-
- FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
-
- INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
-
- WHERE OBJECTPROPERTY(S.[OBJECT_ID],’IsUserTable’) = 1
-
- AND S.database_id = DB_ID()
We use seeks, scans, lookups, and updates
- The seeks search refers to The number of times Index Seek occurs on The Index, and seek is The fastest way to access data.
- The scans Scan refers to The number of index scans performed on The index. Scan is to find The data you want from multiple rows of data. Try to avoid scanning.
- The lookups query refers to The number of times The required data is queried from Clustered index or heap.
- The updates update refers to The number of times The index is changed due to data updates.
Identifies unused Indexes
Based on the above output results, we focus on the second query. We can see that this index has not undergone seeks, scans, and lookup, but updates, this means that SQL Server does not use this index when the query is met, but it still needs to be maintained. Remember that the data obtained from DMVs should be reset when SQL Server is restarted, to determine which index should be retained or deleted, make sure that the collected data is long enough.