Database Tuning Advisor Performance

15 January,2017 by Jack Vamvas

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.

         DTA_on_testFor more usage details read Reduce the Production Server Tuning Load

Read More

Hypothetical Indexes (SQL Server DBA)

SQL Server Performance Checklist - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Database Tuning Advisor Performance


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer