Using the new DTA against the procedure cache

Question

Using the new DTA against the procedure cache

Grant Fritchey

SSC Guru

Points: 398882
More actions
June 27, 2012 at 4:23 am

#258592

I have a general question for everyone.
The new DTA can use the procedure cache as a mechanism for evaluating your queries so that it can suggest indexes, etc. I don't really want to know your opinion of the DTA in general (I think it stinks), but I am interested in knowing whether or not you would choose to run it directly against a production server (assuming you trusted it's output). Unless you first played back a day or so of trace events (new playback software is sweet), you couldn't rely on a dev or qa box having the right queries in cache. So you'd almost have to run it against production. Would you?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Many SQL experts I follow and respect (yourself included) caution against accessing large swaths of the proc cache on a live server. I like the idea of setting up a new environment to test using replay traces, but would think taking a trace for an entire day to get a representative workload into a non-prod setting would be quite expensive in and of itself.

In looking around the net for examples I stumbled on the -n option of the dta command line utility. I do not know what "top" means to dta however.

edit: spelling

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I may depending on the need and with a lot of caution. If I did run it, I would monitor performance and be on top of any issues that arose.

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Thanks guys.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning