HiveBrain v1.2.0
Get Started
← Back to all entries
principlesqlMinor

Using DTA vs. evaluating DMVs?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
dtadmvsusingevaluating

Problem

Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA.

Form How to determine if an Index is required or necessary I have learned, that there are DMV views, which can be used to defer information about actual index usage as well as missing indexes.

The script from Jason Strate only uses the current information from that views, while Fun for the day - Automated Auto-Indexing! saves some information from those views into tables.

DTA on the other side requires planning when to run the profiler, select what to profile and has some impact on performance while running.

My impression is that using DTA as first step tuning tool is simply a waste of time and as its results don't cover the complete database usage, are hard to interpret by novices and can lead to adding too much indexes with negative impact on write performance, while Data collection via DMV requires little preparation and covers nearly the whole usage of the database since the last restart.

My question focuses on which strategy to propose to the management.
I want to focus on evaluation the DMV views in the first step and ignore DTA completely.

Solution

DTA helps for adding indexes but not removing indexes. If you're faced with a database that already has more than, say, 5 indexes per table, the DTA isn't the answer. You'll want to use the DMVs to evaluate usage of existing indexes.

Here's a script with a video tutorial I did a while back:

http://www.toadworld.com/platforms/sql-server/w/wiki/10062.find-indexes-not-in-use.aspx

Context

StackExchange Database Administrators Q#4430, answer score: 4

Revisions (0)

No revisions yet.