March 12, 2007 at 4:35 pm
Hi,
Just keen to here people's perspectives on where it is best to run the tuning advisor. I have a workload file, but am cautious to just start up the tuning advisor on the production machine in case it causes undue load.
Is it very heavy in terms of resource usage?
Would people suggest running it on a seperate machine and just point it to the database or run it on a seperate machine with the database replicated to another instance to allow the changes/interogation to be done on this replicated database (or is this a bit of an overkill).
Keen on people's thoughts.
Thanks
Troy
March 12, 2007 at 4:44 pm
Read BOL topic "Reducing the Production Server Tuning Load "
http://msdn2.microsoft.com/en-us/library/ms190389.aspx
Tuning a large workload can create significant overhead on the server that is being tuned. The overhead results from the many calls made by Database Engine Tuning Advisor to the query optimizer during the tuning process. You can eliminate this overhead problem if you use a test server in addition to your production server.
MohammedU
Microsoft SQL Server MVP
March 14, 2007 at 6:52 am
generally best to use a test server, unless you have quiet times with no user activity then you could use your real box. It's like most things don't run on a busy prod box unless you really have to.
I have to admit I don't really use these tools , but i have dropped individual queries into the advisor - but i do it on a test box having captured the sql from the production box. I find it can be useful for a quick gain on a complex query and doesn't make too bad a job of the suggestions. As always be wary of doing everything it suggests.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 14, 2007 at 7:09 pm
yeah I have been wary of taking these things as gospel too - thanks for the link and the thoughts guys!
Cheers
February 23, 2010 at 2:03 pm
How can a user run Database Tuning Advisor without having db_owner previalges ?
February 24, 2010 at 11:57 am
Tara-1044200 (2/23/2010)
How can a user run Database Tuning Advisor without having db_owner previalges ?
I think you should start a new thread with this question.
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
February 25, 2010 at 6:54 am
Be VERY careful with the 'recommendations' that DTA spits out at you. They can truly devastate your database. WAY too many indexes and WAY too many INCLUDED columns. I spent hundreds of hours 2 years ago cleaning up a database for a client that went hog-wild with DTA...it wasn't pretty. I deleted two thirds of all indexes they had with imperceptable reduction in read performance but MASSIVE improvements in both concurrency AND DML performance. Oh, I cut the size of the indexes by about 70% too. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply