Tuesday, June 14, 2011

Database Tuning Without Impacting Production Servers

Database Tuning Engine Advisor has a feature which allows you to offload analysis to a test SQL server.  Here’s how it works:
             create a test SQL Server
             create a workload from the production SQL Server
             create an XML input file that references the workload and the name of the test server
             launch dta on the production server using the input file

From the msdn article:
The production server will use the information in the XML file to locate the test server and offload statistics and other data necessary for analysis. This technique allows the optimization to be based on the production server's hardware configuration, while reducing the processing required by the production server.   Also, storing a workload in a table negatively impacts performance of the production server, so you should always store the workload in a file when creating a SQL Server Profiler trace on a busy production server.

No comments:

Post a Comment