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.

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

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: (Drop-Only Option)

Watch out this space for more cool DTA stuff…



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook