Bug fixed: it now rebuilds indexes, with space in their name.

Basit's SQL Server Tips

In my previous post here, I’ve discussed how we can detect fragmentation in SQL Server databases indexes using dynamic management view function sys.dm_db_index_physical_stats. In this post, I’m sharing my stored procedure which I’ve created a while ago to rebuild fragmented indexes based on the fragmentation level. This stored procedure is based on sys.dm_db_index_physical_stats and resides in master database. This procedure automatically rebuilds indexes if the fragmentation level is above 30% and reorganises indexes if fragmentation is less than 30%.

Code for the procedure is as follow:

Mark this procedure as system object by executing the following command so that this procedure can be accessed from any database:

In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.

Let me know, if you find a bug or…

View original post 6 more words

Advertisements

Detecting and Removing Duplicate Indexes

Indexes are the solution to many performance problems, but with too many indexes on tables can hurt the performance of your database. This is because SQL Server performs extra work in order keep these indexes up-to-date during insert/update/delete operations against tables where these indexes exist. This means that the SQL Server database engine needs more time when updating data in the table, which is based on the number and complexity of these indexes. Having duplicate indexes can even hurt the performance of your database more because SQL Server needs to examine more query plans and the SQL Server database engine needs to maintain, calculate and update these duplicate index statistics. As we know, SQL Server allows us to create indexes with multiple columns, which obviously increases the likelihood of having duplicate indexes in the database, infect we can create up to 999 duplicate indexes on each table inside database.

Check out my article (ie. Detecting and Removing Duplicate Indexes), in which I discussed, what a duplicate index is and how we can find and remove the duplicate indexes from database.

This article is published on SSWUG.org.

Capture SQL Server Deadlocks using Extended Events

Microsoft SQL Server provides a variety of different options to monitor deadlocks. This includes Trace flags 1204 and 1222, SQL Profiler trace events, Extended Events and Event Notifications. Of these options Extended Events are the most efficient, lowest impact method for capturing deadlock information. Extended Events infrastructure is built directly into SQL Server and so can easily be managed with T-SQL.

Check out my article here in which I discussed how we can use Extended Events to capture deadlocks on SQL Server.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Tips For Minimizing Deadlocks in SQL Server

Database servers are configured in such a way that they can service multiple requests from multiple users. Obviously this increases the likelihood of conflicts because multiple processes can request access to same resources at the same time. This type of conflict is also known as block or blocking. Blocking usually resolves itself after the locking process releases the resource for waiting process. Sometimes, blocking creates a more serious condition, called a deadlock or fatal embrace, which occurs when the first process is waiting for the resource that is locked by the second process and the second process, is waiting for the resource that is locked by the first process.

Deadlocks are caused by poor database design, inappropriate isolation level, inefficient code etc. Check out my article here in which I discussed different ways to minimise deadlocks on SQL Server.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Rebuild indexes based on fragmentation – sp_RebuildIndex

In my previous post here, I’ve discussed how we can detect fragmentation in SQL Server databases indexes using dynamic management view function sys.dm_db_index_physical_stats. In this post, I’m sharing my stored procedure which I’ve created a while ago to rebuild fragmented indexes based on the fragmentation level. This stored procedure is based on sys.dm_db_index_physical_stats and resides in master database. This procedure automatically rebuilds indexes if the fragmentation level is above 30% and reorganises indexes if fragmentation is less than 30%.

Code for the procedure is as follow:

USE [master]
GO

SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[sp_RebuildIndex] @FragmentationLevel [int] = 30
	,@SortInTempDB [varchar] (8) = 'OFF'
	,@Online [varchar] (8) = 'OFF'
	,@LOBCompaction [varchar] (8) = 'ON'
	,@ScanMode [varchar] (16) = 'DETAILED'
	,@MinRowsInTable [int] = 1000
	--Stored Procedure: [dbo].[sp_RebuildIndex]
	--Author: Basit A. Farooq
	--Version #: v1.0.0
	--********************
	-- Purpose/Comments
	--********************
	--Stored procedure to rebuild the indexes of database.
	--Indexes will be rebuild based on fragmentation criteria and scan mode.
AS
BEGIN
	SET NOCOUNT ON;

	--***********
	--Parameters
	--***********
	--@FragmentationLevel [int]
	--Specifies index fragementation level search criteria.
	--@SortInTempDB [varchar](8)
	--Specifies whether to store the sort results in tempdb. The default is OFF.
	--@LOBCompaction [varchar](8)
	--Specifies that all pages that contain large object (LOB) data are compacted.
	--The default is ON
	--@Online [varchar](8)
	--Specifies whether underlying tables and associated indexes are available for queries and
	--data modification during the index operation. The default is OFF.
	--@ScanMode [varchar](16) Is the name of the mode.
	--@ScanMode specifies the scan level that is used to obtain statistics.
	--Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED.
	--The default (NULL) is LIMITED.
	--The DETAILED mode scans all pages and returns all statistics.
	--The SAMPLED mode returns statistics based on a 1 percent sample of all the pages
	--in the index or heap. The modes are progressivelyslower from LIMITED to DETAILED,
	--because more work is performed in each mode.
	--@MinRowsInTable [int]
	--Specifies minimum rows in table for search criteria.
	--********
	--Usage
	--********
	--EXEC [<Database_Name>].[dbo].[sp_RebuildIndex]
	--EXEC [<Database_Name>].[dbo].[sp_RebuildIndex] 30, 'OFF', 'OFF', 'ON', 'LIMITED', 10
	DECLARE @StatementID [int]
		,@SQLStatement [varchar] (max)
		,@ErrorText [varchar] (256)
		,@SQLProcedureName [varchar] (64)

	SET @SQLProcedureName = 'sRebuildIndex'

	BEGIN TRY
		--Validating store procedure parameters
		SET @ErrorText = 'Invalid argument was supplied for @FragementationLevel parameter.' + ' Valid inputs for this parameter should be in the between 1 and 100'

		IF @FragmentationLevel NOT BETWEEN 1
				AND 100
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@FragmentationLevel
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @SortInTempDB parameter.' + ' Valid inputs for this parameter are ON or OFF.'

		IF @SortInTempDB != 'ON'
			AND @SortInTempDB != 'OFF'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@SortInTempDB
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @LOBCompaction parameter.' + ' Valid inputs for this parameter are ON or OFF.'

		IF @LOBCompaction != 'ON'
			AND @LOBCompaction != 'OFF'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@LOBCompaction
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @Online parameter.' + ' Valid inputs for this parameter are ON or OFF.'

		IF @Online != 'ON'
			AND @Online != 'OFF'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@Online
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @ScanMode parameter.' + ' Valid inputs for this parameter are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED.'

		IF @ScanMode != 'LIMITED'
			AND @ScanMode != 'NULL'
			AND @ScanMode != 'SAMPLED'
			AND @ScanMode != 'DETAILED'
			AND @ScanMode != 'DEFAULT'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@ScanMode
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @MinRowsInTable parameter.' + ' Valid inputs for this parameter should be in the between 1 and 1000000000000000'

		IF @MinRowsInTable NOT BETWEEN 1
				AND 1000000000000000
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@MinRowsInTable
					)
		END

		--Creating temporary tables.
		IF OBJECT_ID('Tempdb.dbo.#SQLStatementsStore') IS NOT NULL
			DROP TABLE #SQLStatementsStore

		CREATE TABLE #SQLStatementsStore (
			[SQLStatementID] [int] IDENTITY(1, 1)
			,[SQLstatement] [varchar](1024)
			)

		IF OBJECT_ID('Tempdb.dbo.#PartitionTables ') IS NOT NULL
			DROP TABLE #PartitionTables

		CREATE TABLE #PartitionTables (
			[ObjectId] [int]
			,[SchemaId] [int]
			,[TableName] [varchar](512)
			)

		IF OBJECT_ID('Tempdb.dbo.#Tables') IS NOT NULL
			DROP TABLE #Tables

		CREATE TABLE #Tables (
			[ObjectId] [int]
			,[SchemaId] [int]
			,[TableName] [varchar](512)
			)

		IF OBJECT_ID('Tempdb.dbo.#Index_Physical_Stats') IS NOT NULL
			DROP TABLE #Index_Physical_Stats

		CREATE TABLE #Index_Physical_Stats (
			[ObjectId] [int] NOT NULL
			,[TableName] [varchar](100) NOT NULL
			,[IndexId] [int] NOT NULL
			,[IndexName] [varchar](500) NOT NULL
			,[IndexPartitionNumber] [int] NOT NULL
			,[IndexType] [varchar](500) NOT NULL
			,[PadIndex] [varchar](256) NOT NULL
			,[IndexFillFactor] [int] NOT NULL
			,[StatisticsNoRecompute] [varchar](256) NOT NULL
			,[IgnoreDuplicateKey] [varchar](256) NOT NULL
			,[AllowRowLocks] [varchar](256) NOT NULL
			,[AllowPageLocks] [varchar](256) NOT NULL
			,[AvgFragmentationInPercent] [float] NOT NULL
			,[CountRows] [int] NOT NULL
			)

		INSERT INTO #PartitionTables (
			[ObjectId]
			,[SchemaId]
			,[TableName]
			)
		SELECT DISTINCT t.[object_id]
			,t.[schema_id]
			,t.[name]
		FROM [sys].[partitions] p
		INNER JOIN [sys].[tables] t ON t.[object_id] = p.[object_id]
			AND p.[partition_number] > 1

		INSERT INTO #Tables (
			[ObjectId]
			,[SchemaId]
			,[TableName]
			)
		SELECT t.[object_id]
			,t.[schema_id]
			,t.[name]
		FROM [sys].[tables] t
		WHERE t.[name] COLLATE Latin1_General_CI_AS NOT IN (
				SELECT [TableName]
				FROM #PartitionTables
				)

		INSERT INTO #Index_Physical_Stats (
			[ObjectId]
			,[TableName]
			,[IndexId]
			,[IndexName]
			,[IndexPartitionNumber]
			,[IndexType]
			,[PadIndex]
			,[IndexFillFactor]
			,[StatisticsNoRecompute]
			,[IgnoreDuplicateKey]
			,[AllowRowLocks]
			,[AllowPageLocks]
			,[AvgFragmentationInPercent]
			,[CountRows]
			)
		SELECT dm.[object_id]
			,DB_NAME(DB_ID()) + '.' + s.[name] + '.' + o.[name]
			,dm.[index_id]
			,i.[name]
			,dm.[partition_number]
			,dm.[index_type_desc]
			,[pad_index] = CASE i.[is_padded]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,i.[fill_factor]
			,[statistics_norecompute] = CASE st.[no_recompute]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,[ignore_dup_key] = CASE i.[ignore_dup_key]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,[allow_row_locks] = CASE i.[allow_row_locks]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,[allow_page_locks] = CASE i.[allow_page_locks]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,dm.[avg_fragmentation_in_percent]
			,p.[rows]
		FROM [sys].[objects] o
		INNER JOIN [sys].[indexes] i ON o.[object_id] = i.[object_id]
			AND i.NAME <> 'NULL'
		INNER JOIN [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, @ScanMode) dm ON i.[object_id] = dm.[object_id]
			AND i.[index_id] = dm.[index_id]
			AND dm.[avg_fragmentation_in_percent] >= 5
		INNER JOIN [sys].[schemas] s ON o.[schema_id] = s.[schema_id]
		INNER JOIN [sys].[stats] st ON i.[name] COLLATE Latin1_General_CI_AS = st.[name] COLLATE Latin1_General_CI_AS
			AND o.[object_id] = st.[object_id]
			AND o.[type] COLLATE Latin1_General_CI_AS = 'U'
		INNER JOIN [sys].[partitions] p ON i.[index_id] = p.[index_id]
			AND o.[object_id] = p.[object_id]
			AND p.[rows] > @MinRowsInTable

		--Changing the fillfactor to 100 where fillfactor is 0
		UPDATE #Index_Physical_Stats
		SET IndexFillFactor = 100
		WHERE IndexFillFactor = 0

		-- Building quries to re-build partiton tables indexes partitions with
		-- fragmentation over 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + pt.[TableName] + ' ' + 'REBUILD PARTITION = ' + CONVERT([varchar](10), ips.[IndexPartitionNumber])
		FROM #PartitionTables pt
		INNER JOIN [sys].[indexes] i ON pt.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON pt.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] > @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON pt.[SchemaId] = s.[schema_id]

		-- Building quries to re-build non-partiton tables indexes with
		-- fragmentation over 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + t.[TableName] + ' ' + 'REBUILD WITH (' + ' PAD_INDEX = ' + CONVERT([varchar](8), ips.[PadIndex]) + ', FILLFACTOR = ' + CONVERT([varchar](8), ips.[IndexFillFactor]) + ', SORT_IN_TEMPDB = ' + CONVERT([varchar](8), @SortInTempDB) + ', STATISTICS_NORECOMPUTE = ' + CONVERT([varchar](8), ips.[StatisticsNoRecompute]) + ', ONLINE = ' + CONVERT([varchar](8), @Online) + ', ALLOW_ROW_LOCKS = ' + CONVERT([varchar](8), ips.[AllowRowLocks]) + ', ALLOW_PAGE_LOCKS = ' + CONVERT([varchar](8), ips.[AllowPageLocks]) + ' )'
		FROM #Tables t
		INNER JOIN [sys].[indexes] i ON t.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON t.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] > @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON t.[SchemaId] = s.[schema_id]

		-- Building quries to re-organise partiton tables indexes partitions with
		-- fragmentation between 5 and 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + pt.[TableName] + ' ' + 'REORGANIZE PARTITION = ' + CONVERT([varchar](8), ips.[IndexPartitionNumber])
		FROM #PartitionTables pt
		INNER JOIN [sys].[indexes] i ON pt.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON pt.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] BETWEEN 5
				AND @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON pt.[SchemaId] = s.[schema_id]

		-- Building quries to re-build non-partiton tables indexes with
		-- fragmentation between 5 and 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + t.[TableName] + ' ' + 'REORGANIZE WITH ( LOB_COMPACTION = ' + @LOBCompaction + ' )' AS 'query'
		FROM #Tables t
		INNER JOIN [sys].[indexes] i ON t.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON t.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] BETWEEN 5
				AND @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON t.[SchemaId] = s.[schema_id]

		SELECT @StatementID = MIN(SQLStatementID)
		FROM #SQLStatementsStore

		WHILE @StatementID IS NOT NULL
		BEGIN
			SELECT @SQLStatement = SQLstatement
			FROM #SQLStatementsStore
			WHERE SQLStatementID = @StatementID

			PRINT 'Executing...[' + @SQLStatement + '].'

			EXEC (@SQLStatement)

			DELETE
			FROM #SQLStatementsStore
			WHERE SQLStatementID = @StatementID

			SELECT @StatementID = MIN(SQLStatementID)
			FROM #SQLStatementsStore
		END

		SELECT [ObjectId]
			,[TableName]
			,[IndexId]
			,[IndexName]
			,[IndexPartitionNumber]
			,[IndexType]
			,[PadIndex]
			,[IndexFillFactor]
			,[StatisticsNoRecompute]
			,[IgnoreDuplicateKey]
			,[AllowRowLocks]
			,[AllowPageLocks]
			,[AvgFragmentationInPercent]
			,[CountRows]
		FROM #Index_Physical_Stats

		IF OBJECT_ID('Tempdb.dbo.#SQLStatementsStore') IS NOT NULL
			DROP TABLE #SQLStatementsStore

		IF OBJECT_ID('Tempdb.dbo.#PartitionTables ') IS NOT NULL
			DROP TABLE #PartitionTables

		IF OBJECT_ID('Tempdb.dbo.#Tables') IS NOT NULL
			DROP TABLE #Tables

		IF OBJECT_ID('Tempdb.dbo.#Index_Physical_Stats') IS NOT NULL
			DROP TABLE #Index_Physical_Stats
	END TRY

	BEGIN CATCH
		SELECT ERROR_NUMBER()
			,ERROR_SEVERITY()
			,ERROR_STATE()
			,ISNULL(ERROR_PROCEDURE(), @SQLProcedureName)
			,ERROR_LINE()
			,ERROR_MESSAGE()
	END CATCH
END
GO

Mark this procedure as system object by executing the following command so that this procedure can be accessed from any database:

USE [master]
GO

EXEC sys.sp_MS_marksystemobject sp_RebuildIndex;
GO

In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.

Let me know, if you find a bug or want me add any other feature.

Determine Index Fragmentation in a SQL Server Database

Index fragmentation can adversely affect query response time. When accessing data through an index, SQL Server must read each page in the specified range to retrieve the indexed values. If the index is highly fragmented, SQL Server may have to search many more pages, and possibly levels, to get this information. This results in poor performance and causes your application to respond slowly.

We can use system function sys.dm_db_index_physical_stats to detect fragmentation in specific index, all indexes in a table or indexed view, or all indexes in databases, or all indexes in all databases. The column avg_fragmentation_in_percent returns the percentage of fragmented data.

Although there is no hard and fast rule, a common recommendation is to keep the index fragmentation below 10 percent if possible. Following query can be used to identify indexes in the current database that have more than 5% fragmentation:

-- Ensure a USE statement has been executed first.

SELECT dm.[object_id]
	,DB_NAME(DB_ID()) + '.' + s.[name] + '.' + o.[name]
	,dm.[index_id]
	,i.[name]
	,dm.[partition_number]
	,dm.[index_type_desc]
	,[pad_index] = CASE i.[is_padded]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,i.[fill_factor]
	,[statistics_norecompute] = CASE st.[no_recompute]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,[ignore_dup_key] = CASE i.[ignore_dup_key]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,[allow_row_locks] = CASE i.[allow_row_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,[allow_page_locks] = CASE i.[allow_page_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,dm.[avg_fragmentation_in_percent]
FROM sys.objects o
INNER JOIN sys.indexes i ON o.[object_id] = i.[object_id]
	AND i.[name] = 'NULL'
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') dm ON i.[object_id] = dm.[object_id]
	AND i.[index_id] = dm.[index_id]
	AND dm.[avg_fragmentation_in_percent] >= 5
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.stats st ON i.[name] = st.[name]
	AND o.[object_id] = st.[object_id]
	AND o.[type] = 'U'

Microsoft recommends reorganizing index if an index fragmentation is between 5-30% and rebuilding index if the index fragmentation is over 30%. For more details about reorganize and rebuild Indexes refer to Microsoft Books Online at http://msdn.microsoft.com/en-us/library/ms189858.aspx.

Find unused indexes using sys.dm_db_index_usage_stats

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 TableName operation) 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.

Syntax:

-- Ensure a USE statement has been executed first.
SELECT * FROM sys.dm_db_index_usage_stats
GO

Example:

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

Output:

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.