January 13, 2011 at 10:21 am
>>For the foreseeable future, the best answer is, get someone in who can tune your structures and your queries.
Not true Grant!! I can ALWAYS make time for some performance tuning consulting! I LOVE MY JOB!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 13, 2011 at 10:26 am
TheSQLGuru (1/13/2011)
>>For the foreseeable future, the best answer is, get someone in who can tune your structures and your queries.Not true Grant!! I can ALWAYS make time for some performance tuning consulting! I LOVE MY JOB!! 😎
Ha!
I didn't mean you wouldn't be around. I meant that they might not be able to afford 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
January 13, 2011 at 12:08 pm
Koen (da-zero) (1/13/2011)
Grant Fritchey (1/13/2011)
Koen (da-zero) (1/12/2011)
TheSQLGuru (1/12/2011)
I am curious what you mean by "advisor" here. Hopefully you do NOT mean Database Tuning Advisor!! Avoid that like the plague!!
Just out of curiosity, why? I haven't really worked with it before, so I'm a bit in the dark here.
Because it's very frequently wrong, and not just a little wrong. Not only can it suggest bad or redudant or even useless indexes, it can miss really obvious indexes too. It's primarily a marketing tool, not a performance tuning tool. You can use it, but never, ever, trust it.
If it is such a crappy product, it is quite a shame that Microsoft pushes the use of the DTA through the certification exam. In those questions, using the DTA is portrayed is a best practice.
I think, like many tools, knowledge of and proper use of the tool is a huge factor in whether or not the tool is useful.
If you run the same 20 processes every day as part of your normal business, but you create a workload that only captures some (pick any number less than 20, the lower it is the worse it is) of those and then you use the results to change your indexes, obviously it is going to smell to high heaven because you will be asked to delete indexes you actually need.
If, however, your DTA workload actually captures the vast majority of your processes, especially ones that are MISSING needed indexes, it can make a huge improvement on a database that has little or no tuning. Remember who this tool is targeting, probably the vast majority of DBA's who have been thrust into the job and don't really know where to start from a tuning perspective. Obviously someone who understands how to tweak the database because of their SQL experience plus knowledge of the business intelligence will have a better chance of doing a great job than the DTA will. But that is the minority I can assure you.
AND using the DTA can even help a seasoned professional to identify something they might have missed. I like to run it just to see what it finds to help me hunt down the 1 or 2% of things that might slip by for whatever reason.
Remember, Microsoft continues to gain market in many products because, even though they may not always have the best builtin tools or best performing product (in the minds of some), they usually make their products friendly to those with less experience.
DTA will (likely) never take the place of an experienced DBA. Thankfully. But it can be useful so I would not say "avoid it like the plague" 🙂 I have seen some fairly dramatic results using it but there is always room for the "guru" to improve on it's recommendations.
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
January 13, 2011 at 1:28 pm
Peter Trast (1/13/2011)
Koen (da-zero) (1/13/2011)
Grant Fritchey (1/13/2011)
Koen (da-zero) (1/12/2011)
TheSQLGuru (1/12/2011)
I am curious what you mean by "advisor" here. Hopefully you do NOT mean Database Tuning Advisor!! Avoid that like the plague!!
Just out of curiosity, why? I haven't really worked with it before, so I'm a bit in the dark here.
Because it's very frequently wrong, and not just a little wrong. Not only can it suggest bad or redudant or even useless indexes, it can miss really obvious indexes too. It's primarily a marketing tool, not a performance tuning tool. You can use it, but never, ever, trust it.
If it is such a crappy product, it is quite a shame that Microsoft pushes the use of the DTA through the certification exam. In those questions, using the DTA is portrayed is a best practice.
I think, like many tools, knowledge of and proper use of the tool is a huge factor in whether or not the tool is useful.
If you run the same 20 processes every day as part of your normal business, but you create a workload that only captures some (pick any number less than 20, the lower it is the worse it is) of those and then you use the results to change your indexes, obviously it is going to smell to high heaven because you will be asked to delete indexes you actually need.
If, however, your DTA workload actually captures the vast majority of your processes, especially ones that are MISSING needed indexes, it can make a huge improvement on a database that has little or no tuning. Remember who this tool is targeting, probably the vast majority of DBA's who have been thrust into the job and don't really know where to start from a tuning perspective. Obviously someone who understands how to tweak the database because of their SQL experience plus knowledge of the business intelligence will have a better chance of doing a great job than the DTA will. But that is the minority I can assure you.
AND using the DTA can even help a seasoned professional to identify something they might have missed. I like to run it just to see what it finds to help me hunt down the 1 or 2% of things that might slip by for whatever reason.
Remember, Microsoft continues to gain market in many products because, even though they may not always have the best builtin tools or best performing product (in the minds of some), they usually make their products friendly to those with less experience.
DTA will (likely) never take the place of an experienced DBA. Thankfully. But it can be useful so I would not say "avoid it like the plague" 🙂 I have seen some fairly dramatic results using it but there is always room for the "guru" to improve on it's recommendations.
Sorry, but I have a COMPLETELY different perspective. I have spent more consulting hours in the last several years cleaning up DTA-caused problems than every other performance-related topic save one (that being scalar UDFs, which wins by a large margin). I have had more than one client make their system essentially unusable due to DTA usage, primarily from it's propensity to INCLUDE very large percentages of the table's columns in MULTIPLE (often very similar) indexes. A typical example would find 10-15 indexes on a table with 20-40 columns with each index averaging 40-60% of all columns. Extreme examples include one table with 62 indexes that each averaged 68% of all columns on the table.
Having said that, it is clearly very much to my advantage for users to use DTA (and scalar UDFs!!) since I get paid good money to be "The Wolf" and clean up their problems. But I would still rather they come up with other ways to harm their system - and there are a nearly infinite array of possibilities there!! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply