Running DTA in a production environment?
Posted by decipherinfosys on May 14, 2009
Got a call last night from a client of ours – the system was not performing well – it was not up to the same performance levels as the baseline performance benchmarks that were done for them several months ago. Upon looking into the system and the database, we saw a lot of DTA created stats and indexes. We also saw a lot of recompilations happening in the environment. The client DBA was running DTA every night and accepting all of it’s changes whenever it indicated an improvement in performance. Now, when those indexes and stats were created or modified for the tables, any queries that are going to then touch the same tables that were effected by DTA will undergo recompilation – the recompilation is at the statement level in SQL Server 2005 and above.
It is always recommended to run DTA on a copy of the production enviroment – in a staging environment and then evaluate the benefits and then cautiously select and create the indexes and stats that you want. Never do it blindly. Remember that it is taking a workload or a query into consideration to run through it’s analysis – you know your system better than the DTA – evaluate the recommendations first and if you feel that those are appropriate, then only apply those. With DTA, one also has the option to not copy the production database to the staging environment but rather just copy the necessary meta-data and it also takes into account that the staging server might not be of the same configuration as the production one – you can read more on that in this whitepaper from MSFT – here.