March 8, 2010 at 4:50 pm
I would like to run a workload file on the Database Tuning Advisor(DTA) for getting tuning recommendations.
There are some batch jobs which are taking more time than usual to run. Thought of using the DTA for the same. I am not comfortable running on the production server.
Could you kindly advise if you have run it on a test server to get the recommendations. I came across some links on books online.
Also, while running the DTA, how can I make sure that I would only get the recommendations and the recommendations will NOT get implemeted during the DTA process. Would clicking 'Keep All Existing PDS' on Tuning options of DTA be sufficient for this.
M&M
March 8, 2010 at 5:03 pm
mohammed moinudheen (3/8/2010)
I would like to run a workload file on the Database Tuning Advisor(DTA) for getting tuning recommendations.There are some batch jobs which are taking more time than usual to run. Thought of using the DTA for the same. I am not comfortable running on the production server.
Could you kindly advise if you have run it on a test server to get the recommendations. I came across some links on books online.
Also, while running the DTA, how can I make sure that I would only get the recommendations and the recommendations will NOT get implemeted during the DTA process. Would clicking 'Keep All Existing PDS' on Tuning options of DTA be sufficient for this.
Use DTA with extreme caution. Not all indexes and statistics suggested are viable options. I would certainly start with running this in a test environment first. Also, I would recommend having a backup of the database before running DTA. This will permit you to more quickly recover back to before you ran the DTA.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 8, 2010 at 5:09 pm
CirquedeSQLeil (3/8/2010)
Use DTA with extreme caution. Not all indexes and statistics suggested are viable options. I would certainly start with running this in a test environment first. Also, I would recommend having a backup of the database before running DTA. This will permit you to more quickly recover back to before you ran the DTA.
Jason,
Does it mean, when we run the DTA, it would directly make the recommendations to the database rather than just reporting to us the recommendations. This is kind of risky right.
M&M
March 8, 2010 at 5:14 pm
The risk comes in the indexes that it may propose to remove. Any indexes or stats that are recommended are easy to spot and remove.
As for immediately implementing the recommendations, If you select "Keep my existing PDS" it will keep them. It will not implement the new PDs until you select "Apply Recommendations." Thus you can view the recommendations and not implement anything.
Here is a more in-depth article on the subject.
http://www.simple-talk.com/sql/database-administration/fine-tuning-your-database-design-in-sql-2005/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 8, 2010 at 5:23 pm
Thanks for sharing the article link.
I also came across another URL on BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/bb95ecaf-444a-4771-a625-e0a91c8f0709.htm
which describes using DTA against a test server instead of directly on production.
By any chance, have you tried this before.
M&M
March 8, 2010 at 5:27 pm
Yes, I have used DTA in the past. I only use it to see possible recommendations. I do not typically implement changes directly from DTA though. I check the recommendations and test them to see if the recommendation is valid or not. It's a long iterative process.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply