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.

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

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

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