A critical part of database design and management is index design. Index design involves balancing space requirements and the resource overhead required to keep indexes up-to-date compared to the performance improvements resulting from the indexes. You can either use dynamic management functions (DMFs) and views (DMVs) or Database Engine Tuning Advisor to identify index needs based representative server activity.
With the release of SQL Server 2005 and later releases, Microsoft introduced set of new dynamic management view and function that helps database administrators (DBA) and developers to identify potential index candidates based on query history.
Checkout the part-3 of my three part article series on index-related dynamic management views and function here, in which I discussed these index-related dynamic management views (DMVs), which are useful to identify potential indexes for your queries based representative server activity.
This article is published on SSWUG.org.
SQL Server is able to service requests from a large number of concurrent users. When SQL Server is servicing requests from many customers, there is a strong possibility that conflicts arise because different processes access the same resources at the same time. A conflict in which a process waits a release the resource is a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it.
Checkout my tip (i.e. Different techniques to identify blocking in SQL Server) in which I discussed various techniques for troubleshooting and resolving blocks in SQL Server. This tip is published on MSSQLTips.com.
The SQL Server maintains all indexes defined against a table regardless of their usage. Index maintenance can cause significant amounts of CPU and I/O usage, which can be detrimental to performance in a write-intensive system. With this in mind, it makes sense to identify and remove any indexes that are not being used as they are a pointless drain on resources.
Even though there are several different methods in SQL Server which you can use to identify unused indexes but since SQL Server 2005 onwards the most common way to monitor unused indexes is to use
sys.dm_db_index_usage_stats DMV. As the name suggests this DMV returns the information that is tracked about index usage from SQL Server cache.
SQL Server caches the following information for each index (for user queries and system queries):
- User Seeks – The number of times index has been used in a seek operation (either looking up a single row, or doing a range scan) along with the time of the last seek.
- User Scans – The number of times index has been used in a scan operation (e.g. a
SELECT * FROM TableNameoperation) along with the time of the last scan.
- User Lookups – The number of times index has been used in a lookup operation (this means a bookmark lookup – where a non-clustered index does not fully cover a query and additional columns must be retrieved from the base table row) along with the time of the last lookup.
- User Updates – The number of times index has been used in an update operation (this counts inserts, updates, and deletes) along with the time of the last update.
This information helps us to identify and remove the unused index accurately because every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Using this DMV carefully removes the risks associated with dropping useful indexes.
-- Ensure a USE statement has been executed first. SELECT * FROM sys.dm_db_index_usage_stats GO
Following query helps you to find all unused indexes within database using
sys.dm_db_index_usage_stats DMV. I took the approach of finding the indexes that have a lot of updates to them but don’t have any user scans/seeks/lookups which means we are only inserting into the indexes and our queries are not really using them:
-- Ensure a USE statement has been executed first. SELECT u.* FROM [sys].[indexes] i INNER JOIN [sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID) LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID) AND i.[index_id] = u.[index_id] AND u.[database_id] = DB_ID() --returning the database ID of the current database WHERE o.[type] <> 'S' --shouldn't be a system base table AND i.[type_desc] <> 'HEAP' AND i.[name] NOT LIKE 'PK_%' AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0 AND u.[last_system_scan] IS NOT NULL ORDER BY 1 ASC
Key considerations when using sys.dm_db_index_usage_stats:
- SQL Server cache is flushed whenever you restart SQL Server instance, and is not persisted across instance restarts.
- All cache entries for indexes in a database are removed when the database status is changed to read_only.
- Rebuilding an index clears the stats from SQL Server cache for the index involved.
- It is important to make sure that index monitoring is performed over a representative time period. If you only monitor indexes during specific time frames you may incorrectly highlight indexes as being unused. The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations.