5/23/2012 6:57:46 PM
Ahmad Osama -
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">
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.
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
Ahmad Osama (Member since: 1/25/2012 1:03:07 PM)
Ahmad Osama is a MCP Database Administrator/Developer, an avid gamer and a chicken lover. Ahmad started his career in the sales industry working as database executive; responsible for report writing, application development and basic database administration. In 2008 he joined World Fashion Exchange as Database Administrator. While in this role he focused on troubleshooting and performance tuning. In 2010 he joined The Perfect Future Technologies and has been awarded as best new comer and expert of the year working as database administrator on one of largest political database in the world.
Ahmad has experience in database administration, SSIS, performance tuning, VLDBs and web development.
When not working on SQL Server, he can be found glued to his Xbox.
View Ahmad Osama 's profile
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
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 ;)
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 comment