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 FROM
msdb.dbo.sysjobs_view WHERE
name = 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)]' AND
category_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) GOTO
QuitWithRollback
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) GOTO
QuitWithRollback
/******
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) GOTO
QuitWithRollback
EXEC @ReturnCode
= msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
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) GOTO
QuitWithRollback
EXEC
@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <>
0) GOTO
QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Enjoy!
Chris
This is handy, ive added it as a metric to red gate monitor. They do have a custom metric on the site but it includes all indexes primary keys etc.
ReplyDeleteGlad you like it Andy! Check out the tables without a Clustered Index and Tables Without a Primary Key posts. They might give you equally as much value :)
DeleteI've updated this script to exclude unused unique indexes and also improved the drop script so that it includes the USE DatabaseName; for each index making it not only re-runable but instantly usable without having to manually change or add the database context for each database.
ReplyDelete