SQL Server DTA: Finding Unused Indexes

This is another good feature of DTA. There are lot of different scripts available over internet to find unused indexes, however why not use the SQL Servers very own DTA to figure this out. It’s similar to tuning up any other workload only the tuning option to be selected is “Evaluate Utilization of Existing PDS only”.  It doesn’t works along with Tuning option “Keep all existing PDS” and it should not be selected.

Let’s see how it’s done. I will use the below the query as workload to SQL Server DTA.

SELECT emp.firstname, 
       emp.lastname, 
       addr.addline1 
FROM   tblemployee emp 
       JOIN tbladdress addr 
         ON emp.empid = addr.empid 
WHERE  emp.firstname LIKE 'a%'
       AND addr.pobox LIKE 'PO%'

Let’s create below indexes with reference to the above query.

CREATE NONCLUSTERED INDEX [ix_Addr1] 
  ON [dbo].[tblAddress] ( [pobox] ASC, [empid] ASC ) 
  include ([Addline1]) WITH (sort_in_tempdb = OFF, drop_existing = OFF, online = 
OFF) ON [PRIMARY] 
go 
CREATE NONCLUSTERED INDEX [ix_Addr2] 
  ON [dbo].[tblAddress] ( [pobox] ASC, [empid] ASC ) 
  include ([Addline1], [ZipCode]) WITH (sort_in_tempdb = OFF, drop_existing = 
OFF, online = OFF) ON [PRIMARY] 
CREATE NONCLUSTERED INDEX [ix_Emp1] 
  ON [dbo].[tblEmployee] ( [firstname] ASC ) 
  WITH (pad_index = OFF, statistics_norecompute = OFF, sort_in_tempdb = OFF, 
drop_existing = OFF, online = OFF, allow_row_locks = ON, allow_page_locks = ON) 
ON [PRIMARY] 
go 
CREATE NONCLUSTERED INDEX [ix_Emp2] 
  ON [dbo].[tblEmployee] ( [firstname] ASC ) 
  include ( [lastname]) WITH (pad_index = OFF, statistics_norecompute = OFF, 
sort_in_tempdb = OFF, drop_existing = OFF, online = OFF, allow_row_locks = ON, 
allow_page_locks = ON) ON [PRIMARY]

Once this is done, the DTA can be setup in 2 easy steps as shown below.

Step 1- Under the “General” tab set the below option.

1_SQL_Server_DTA_Finding_Unused_Indexes

Step 2- Under the “Tuning Options” tab select the options as shown in below.

2_SQL_Server_DTA_Finding_Unused_Indexes

Hit “Start Analysis” and get recommendations as shown below.

3_SQL_Server_DTA_Finding_Unused_Indexes

As per DTAs recommendations, indexes ix_Addr2 and ix_Emp1 aren’t being used and can be dropped.

Analyze the result and figure out that which indexes can actually be dropped. An index being used for a monthly report might come up in DTAs recommendations, however it can’t be dropped.

References: http://msdn.microsoft.com/en-us/library/ms174215(v=sql.105).aspx (Drop-Only Option)

Watch out this space for more cool DTA stuff…

 
Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

3 Comments on “SQL Server DTA: Finding Unused Indexes”

  1. dba’s should be aware, these tools can create blocking when evaluating indexes, in my experience with large very busy systems, these tools should not be used on production systems.

  2. The DTA is only tuning on the workload that you provide and the recommendations are based on that only. Unless that is the ONLY workload on the server then the recommendations from DTA hold true but if there are other workloads then applying the DTA recommendations will hurt any other workloads running on your production system.

Leave a Reply

Your email address will not be published. Required fields are marked *