A sound indexing strategy is paramount to performance in an OLTP system. Not having the correct indexes in place can cause unnecessary reads placing additional overhead on, among other things the IO subsystem. Having said that indexes that are in place but never used cause unnecessary writes during insert and update operations, as well as having to be maintained by expensive REORGANIZATION and REBUILD operations. This is a lot of unnecessary work SQL Server has to do for little to no additional benefit. The key with indexing is to cover as many queries in your workload with as few small indexes as possible, which is by no means an easy task.
Below is a script I use to report on unused indexes, the definition of unused indexes in this case uses the sys.dm_db_index_usage_stats DMV where the user_seeks + user_scans + user_lookups = 0.
I exclude Index ID’s 0 and 1 (Heaps and Clustered Indexes respectively) I also exclude Primary Keys. Now as with any production environment I would never just blindly drop the indexes without investigation always test the effects in a development, QA environment first preferably with a full regression test to get a better picture of the impact and make a more educated decision.
There are a few things that will need changing;
· @notify_email_operator_name=N'Chris'
· SET @EmailProfile = ''DBA''
· SET @EmailRecipient = ''Chris@SQLServer365.co.uk''
You can also obviously change the schedule accordingly to suit your needs.
/*
-----------------------------------------------------------------
Get unused indexes and the SQL Statement to drop them
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
USE [msdb]
GO
IF EXISTS (SELECT job_id FROMmsdb.dbo.sysjobs_view WHEREname = N'Alert - UnusedIndexes')
EXEC msdb.dbo.sp_delete_job @job_name = N'Alert - UnusedIndexes', @delete_unused_schedule=1
GO
BEGIN TRANSACTION
DECLARE@ReturnCode INT
SELECT@ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' ANDcategory_class=1)
BEGIN
EXEC@ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <>0) GOTOQuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC@ReturnCode = msdb.dbo.sp_add_job @job_name=N'Alert - UnusedIndexes',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job runs every Monday morning at 07:33 and will email Chris@SQLServer365.co.uk if there are any indexes that are unused (0 reads) in any user database.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Chris', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <>0) GOTOQuitWithRollback
/****** Object: Step [Get Unused Indexes] Script Date: 11/27/2012 14:42:49 ******/
EXEC@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get Unused Indexes',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-- Set database context
USE master;
GO
-- Declare variables
DECLARE @EmailProfile VARCHAR(255)
DECLARE @EmailRecipient VARCHAR(255)
DECLARE @EmailSubject VARCHAR(255)
DECLARE @IndexUsageStats INT
DECLARE @IndexCount INT
-- Get Index Usage History Stats Age
SET @IndexUsageStats = (SELECT DATEDIFF(DAY, sd.crdate, GETDATE()) AS days_history
FROM sys.sysdatabases sd
WHERE sd.[name] = ''tempdb'');
-- Set variables
SET @EmailProfile = ''DBA''
SET @EmailRecipient = ''Chris@SQLServer365.co.uk''
-- Drop temporary table if exists
IF OBJECT_ID(''tempDB.dbo.#UnusedIndexes'') IS NOT NULL
DROP TABLE #UnusedIndexes;
-- Create Temporary Table
CREATE TABLE #UnusedIndexes
(
DatabaseName VARCHAR(255) ,
TableName VARCHAR(1000) ,
IndexName VARCHAR(1000) ,
IndexID INT ,
TotalWrites BIGINT ,
TotalReads BIGINT ,
[Difference] BIGINT ,
DropScript VARCHAR(4000)
);
INSERT INTO #UnusedIndexes
EXEC sp_msforeachdb ''USE [?];
IF ''''?'''' NOT IN (''''master'''', ''''model'''',''''msdb'''', ''''tempdb'''', ''''ReportServer'''', ''''ReportServerTempDB'''', ''''Distribution'''')
BEGIN
SELECT DB_NAME(DB_ID()) ,
OBJECT_NAME(ddius.[object_id]) ,
i.name AS [IndexName] ,
i.index_id ,
user_updates AS [TotalWrites] ,
user_seeks + user_scans + user_lookups AS [TotalReads] ,
user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference] ,
''''USE '''' + ''''['''' + DB_NAME(DB_ID()) + ''''];'''' + '''' IF EXISTS (SELECT 1 FROM sys.indexes WHERE [object_id] = '''' + ''''object_id('''' + + '''''''''''''''' + ''''['''' + SCHEMA_NAME(o.[schema_id]) + ''''].'''' + ''''['''' + OBJECT_NAME(ddius.[object_id]) + '''']'''' + '''''''''''''''' + '''')'''' + '''' AND name = '''' + '''''''''''''''' + i.NAME + '''''''''''''''' + '''')''''
+ '''' DROP INDEX '''' + ''''['''' + i.name + '''']'''' + '''' ON '''' + ''''['''' + SCHEMA_NAME(o.[schema_id]) + ''''].'''' + ''''['''' + OBJECT_NAME(ddius.[object_id]) + ''''];''''
FROM sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]
AND i.index_id = ddius.index_id
INNER JOIN sys.objects AS o WITH ( NOLOCK ) ON i.[object_id] = o.[object_id]
WHERE OBJECTPROPERTY(ddius.[object_id], ''''IsUserTable'''') = 1 -- Only user tables
AND ddius.database_id = DB_ID() -- Current Database
AND ( user_seeks + user_scans + user_lookups ) = 0 -- 0 Reads
AND i.index_id > 1 -- Exclude clustered indexes and heaps
AND i.is_primary_key = 0 -- Exclude primary keys
AND i.is_unique = 0 -- Exclude unique indexes
ORDER BY [TotalReads] ASC;
END
''
-- Check for unused indexes
IF EXISTS ( SELECT 1
FROM #UnusedIndexes)
BEGIN
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML = N''<style type="text/css">''
+ N''.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} ''
+ N''.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} ''
+ N''body {font-family: Arial, verdana;} ''
+ N''table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} ''
+ N''td{background-color:#F1F1F1; border:1px solid black; padding:3px;} ''
+ N''th{background-color:#99CCFF; border:1px solid black; padding:3px;}''
+ N''</style>'' + N''<table border="1">'' + N''<tr>''
+ N''<th>DatabaseName</th>''
+ N''<th>TableName</th>''
+ N''<th>IndexName</th>''
+ N''<th>IndexID</th>''
+ N''<th>TotalWrites</th>''
+ N''<th>TotalReads</th>''
+ N''<th>Difference</th>''
+ N''<th>DropScript</th>''
+ N''</tr>''
+ CAST(( SELECT td = DatabaseName,
'''',
td = TableName,
'''',
td = IndexName,
'''',
td = IndexID,
'''',
td = TotalWrites,
'''',
td = TotalReads,
'''',
td = [Difference],
'''',
td = DropScript,
''''
FROM #UnusedIndexes
FOR
XML PATH(''tr'') ,
TYPE
) AS NVARCHAR(MAX)) + N''</table>'';
-- Count Indexes
SELECT @IndexCount = COUNT(1) FROM #UnusedIndexes;
-- Set subject
SET @EmailSubject = ''ALERT - '' + CAST(@IndexCount AS VARCHAR(100)) + '' Unused Indexes found on '' + @@SERVERNAME + '' - have not been used for '' + CAST(@IndexUsageStats AS VARCHAR(100)) + '' days''
-- Email results
EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,
@recipients = @EmailRecipient, @subject = @EmailSubject,
@body = @tableHTML, @body_format = ''HTML'';
END
GO',
@database_name=N'master',
@flags=12
IF (@@ERROR <> 0 OR @ReturnCode <>0) GOTOQuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <>0) GOTOQuitWithRollback
EXEC@ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monday at 07:33',
@enabled=1,
@freq_type=8,
@freq_interval=2,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20121123,
@active_end_date=99991231,
@active_start_time=73300,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <>0) GOTOQuitWithRollback
EXEC@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <>0) GOTOQuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Enjoy!
Chris
