February 7, 2013 at 8:41 am
Long time lurker...first time poster.
Last night I attended a SQL Server/BI user group meeting and the topic was on indexing. I was surprised to find that the presenter did not touch on the Profiler and the Tuning Advisor. He asked for final questions so I threw it out there, "How much stock do you put into the Profiler and the Tuning Advisor?" I could hear the low rumbling of groans and many DBA's slowly shaking their heads. The presenter paused for a few seconds. He went on to describe all the negatives of the automated tools.
I am in BI Development (SQL Server 2008r2) and have pretty decent success with the Tuning Advisor. I spend a minimum amount of time manually indexing a new DW. After creation of the ETL and the reports, I completely delete the development DW and re-create it from a fresh script. I then turn on the profiler, run the ETL and run all the reports. I then stop the trace and save the .trc file and run it against the tuning advisor. The recommended indexing returned by the Tuning Advisor seems to be pretty spot on. I implement the indexing recommendations in the installation script, delete the DW and re-create a final time before sending off to production. Some questions:
Is the Profiler and Tuning Advisor generally considered taboo by DBAs when it comes to indexing?
Is the Tuning Advisor better for BI development than on production db's that tend to get fragmented?
Do you guys regularly use these tools?
Was I exposed as a noOb for even mentioning the Tuning Advisor?
February 7, 2013 at 8:43 am
Also, I put this in the 2012 section because I was wondering if the Profiler and Tuning Advisor has been significantly improved on the 2012 release.
February 7, 2013 at 11:30 am
If you've had a consistently good experience with the Tuning Advisor, I'd say you're the exception that proves the rule. Mostly, I've seen the DTA giving questionable suggestions or useless suggestions. Occasionally I've seen it make down right dangerous suggestions. Only occasionally have I seen it make a good and useful suggestion. Most of the time I see it miss incredibly obvious and easy to fix issues because it's just not a very sophisticated tool. And, the biggest point for me, it's not maintained by the main development team within SQL Server but seems to be some sort of marketing add-on. So, no, I don't use it. I've tested the 2012 version pretty extensively (had to as part of writing my 2012 book) and it wasn't even remotely better than previous versions.
Now, Profiler, well, let's talk. The Profiler GUI, as a means to directly record information from your servers is problematic. That's because the GUI connects to the server through a different buffering mechanism than the trace events that you can run through T-SQL. That buffering mechanism can actually hurt performance. But, the trace events themselves are a good way to collect data. No question. And the GUI is a good way to display it, especially since you can combine the trace output with Perfmon output.
But, if I were to collect data in 2012, I'd use extended events. They're more lightweight, easier to program, easier to filter, and the 2012 gui is pretty sophisticated.
Are you a n00b? Nah, not at all. Just making different choices, some of which have worked out for you just fine, but don't work out that well for others. Being informed doesn't invalidate your choices, but it does arm you for the future if those choices prove problematic.
"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
February 8, 2013 at 8:04 am
Thanks for the good info Fritchley. I will be sure to give your tuning books a read.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply