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.
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.
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
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
thanks Prashant.. this also works over internet.. try it.
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
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.