SQL SERVER DTA: Reducing production server test load.

This feature creates a copy of a database to be tuned on a specified test server and runs the tuning process on that test server. The recommendations suggested can then be applied to production server as appropriate.  Only database and object structure is copied including statistics. It doesn’t copies data. Thus, it saves performance issues on production server because of tuning process and it can also be used for copying complete database structure on to test servers if need be.

This feature is only configurable from dta command line utility. The implementation is very easy. It requires an XML file as input. The xml is shown below.

<?xml version="1.0" encoding="utf-16" ?>
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/dta">
  <DTAInput>
    <Server>
      <Name>Ahmad-Pc\SQL2012</Name>
      <Database>
        <Name>DoodleDB2012</Name>
      </Database>
    </Server>
    <Workload>
      <File>D:\SQLServerGeeks\Blogs\DTA\DTA_Offline_Tuning\QueriesToTune.sql</File>
    </Workload>
    <TuningOptions>
      <TestServer>Ahmad-PC\SQL2005</TestServer>
      <FeatureSet>IDX</FeatureSet>
      <Partitioning>NONE</Partitioning>
      <KeepExisting>NONE</KeepExisting>
      <RetainShellDB>YES</RetainShellDB>
    </TuningOptions>
  </DTAInput>
</DTAXML>

The xml is self explanatory. Specify the Production server, workload to tune and test server and it’s done. The node  “RetainShellDB” tells dta not to drop the test database created on test server. Once xml file is ready; fire up below dta command.  

"C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\dta" -S Ahmad-PC\SQL2012 -E -ix "D:\SQLServerGeeks\Blogs\DTA\DTA_Offline_Tuning\xmlinput.xml" -if "D:\SQLServerGeeks\Blogs\DTA\DTA_Offline_Tuning\QueriesToTune.sql" -s TuningSession4 –u

The switches used in above commands are

-S: Server Name against which tuning is to be done

-ix: input xml file

-if: Work load to be tuned

-s: Session Name

-u: open up GUI

The output from the above command is shown in below image.

Though the feature is not configurable from GUI, however –u switch opens up the GUI interface as shown below. The output can even be stored in an xml file by specifying –ox switch. 

The dta command passes all the required parameters to GUI and the moment I hit, “Start Analysis” button, DTA creates a copy of DoodbleDB2012 at Ahmad-PC\SQL2005 i.e. the test instance and then  tunes the specified workload against it and gives back the recommendations as shown below.

Yes, it is this simple. It saves production server from the tuning load and can also help in moving database structure without even scripting all objects.  As it copies statistics also, the test database can also be used to analyze and tune queries without copying actual data and thus saves storage cost at test server.

References: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_4deptrbl/html/bb95ecaf-444a-4771-a625-e0a91c8f0709.htm

Watch out this space for other cool DTA features.

If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks