Unused database indexes found

Source: Internet
Author: User

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.

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

 
 
  1. SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
  2.  
  3. I.[NAME] AS [INDEX NAME], 
  4.  
  5. A.LEAF_INSERT_COUNT, 
  6.  
  7. A.LEAF_UPDATE_COUNT, 
  8.  
  9. A.LEAF_DELETE_COUNT 
  10.  
  11. FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A 
  12.  
  13. INNER JOIN SYS.INDEXES AS I 
  14.  
  15. ON I.[OBJECT_ID] = A.[OBJECT_ID] 
  16.  
  17. AND I.INDEX_ID = A.INDEX_ID 
  18.  
  19. 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.

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

 
 
  1. SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
  2.  
  3. I.[NAME] AS [INDEX NAME], 
  4.  
  5. USER_SEEKS, 
  6.  
  7. USER_SCANS, 
  8.  
  9. USER_LOOKUPS, 
  10.  
  11. USER_UPDATES 
  12.  
  13. FROM SYS.DM_DB_INDEX_USAGE_STATS AS S 
  14.  
  15. INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
  16.  
  17. WHERE OBJECTPROPERTY(S.[OBJECT_ID],’IsUserTable’) = 1 
  18.  
  19. 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.