Database Tuning Advisor Performance

Question: Some developers are requesting access to Database Tuning Advisor (DTA) on a Production Server. I’m not a big fan of DTA , but I can see how developers will find it useful. The main reason why I resist using DTA is it forces DBAs into lazy thinking.

I strongly believe best practises for set up , capacity planning and monitoring are the cornerstone of a solid SQL Server environment. I’d like to present some reasons to the developers to resist using DTA on Production. Can you give some real-world reasons?

SQL Server Install Checklist

Answer: I agree with your comment on DTA encouraging an approach to performance tuning which may not fix underlying causes. On the other hand, I can see the benefits particularly if used as a tool to learning.   I have seen examples of performance degradation when DTA is in action.

There are some good practises to follow when using DTA. How you apply the best practises, is dependant on the environment you’re managing.

1. Avoid using DTA on Production Servers. If you must use a Production environment , schedule for a low volume time. Ensure only the necessary schemas are used in the Analysis phase. Try and keep the scope of analysis to the required
2. DTA has a feature to use Production Data where it copies metadata and statistics to a Tests server, running most of the load on a Test server.

         For more usage details read Reduce the Production Server Tuning Load

Read More

Hypothetical Indexes (SQL Server DBA)

SQL Server Performance Checklist - SQL Server DBA