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 SQL Server 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.

1_SQL_SERVER_DTA_Reducing_production_server_test_load

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.

2_SQL_SERVER_DTA_Reducing_production_server_test_load

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.

3_SQL_SERVER_DTA_Reducing_production_server_test_load

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.

 
Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

4 Comments on “SQL SERVER DTA: Reducing production server test load.”

  1. Good one, works as a reminder that you can run DTA on test server as it would clone the prod database etc etc… Just one suggetion, I see a BOL link has been referred above. I believe referring to an link over internet (MSDN) would make more sense instead..for example the following link could have been referred here

    http://msdn.microsoft.com/en-us/library/ms190389.aspx

  2. You mean directly pasting this link in a browser would open up the referenced BOL page? Sorry, either I misunderstood something or I didn’t get it right..but anyway..not a big thing to continue the discussion 😉

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

  3. I was using IE9 and It worked on it.. however it doesn’t works with any other browser. I will certainly use MSDN link in future. thanks for pointing out the issue.

Leave a Reply to Prashant Cancel reply

Your email address will not be published. Required fields are marked *