SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Taking Advantage of SQL Server Tools


Taking Advantage of SQL Server Tools

Author
Message
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
SSChasing Mays
SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)SSChasing Mays (620 reputation)

Group: General Forum Members
Points: 620 Visits: 730
Comments posted to this topic are about the item Taking Advantage of SQL Server Tools

Brad M. McGehee
DBA
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10275 Visits: 13559
I'm not sure if Database Engine Tuning Advisor should be on that list:

I think DTA sometimes (mostly?) is misguiding in terms of indexing. I'd rather study execution plans or use some scripts to find missing/unused indexes.
When DTA is used to optimize design structure then I think there is something wrong in general, not only related to SQL Server...
I'm not sure how helpful DTA would be in terms of partitioning, since I never tried.

I agree with the remaining list though. Even if BIDS and myself aren't friends yet.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Steve Cullen
Steve Cullen
Right there with Babe
Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)

Group: General Forum Members
Points: 745 Visits: 1226
You should add SQL debugger (part of SSMS) to that list. I've found most DBAs don't appreciate the value.

Converting oxygen into carbon dioxide, since 1955.


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85276 Visits: 41078
I agree that DTA can be more of a hindrance in the hands of someone inexperienced in the art of determining proper indexing. It will (many times) suggest indexes where the first column has super low cardinality which causes a huge number of reads on INSERTs especially when the Insert causes the index to split to "make room" to the point where timeouts are actually caused.

I can't speak much about the Debugger but it's been my experience with other Debugger's that it's no substitute for some good ol' horse-sense-due-to-experience. In fact, I see them as sometimes being detrimental because people will sometimes use them as a crutch instead of learning from them.

I agree that having tools are good and every DBA should strive to learn as much about them as they can... if, for nothing else, to learn of the caveates they have and the damage they can cause when used incorrectly.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61962 Visits: 19101
I think it makes sense to spend some time learning some of these, especially the ones that you use on a regular basis. Those you ought to have a lot of familiarity with and understand how to use them.

In terms of some of the performance tools, like Profiler, I think it makes sense to expect someone to know how to use them, and to continue to learn about them over time. They may not be regularly used, but when they are needed, you shouldn't fumble too often.

Jeff brings up a good point as well in terms of not necessarily depending on these tools, but learning more about your craft after time. That's part of being a professional. Understanding what makes sense, what is appropriate, and then when to use it. And, of course, how to use those tools.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32046 Visits: 18551
lmu92 (3/6/2010)
I'm not sure if Database Engine Tuning Advisor should be on that list:



I agree with this assessment. This tool can be useful, and can be very detrimental. This is a tool that should come with a warning label.:-)



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32046 Visits: 18551
Jeff Moden (3/6/2010)


I agree that having tools are good and every DBA should strive to learn as much about them as they can... if, for nothing else, to learn of the caveates they have and the damage they can cause when used incorrectly.


This applies not only to the packaged tools that come with SQL Server. There are also a great many more tools available from Microsoft, the forums, and the community at large. Find your toolset, learn it and incorporate it well. Not every DBA needs a Miter Saw, and many don't need a framing nailer. Find the right tool for the job and keep it in your toolset.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32046 Visits: 18551
Steve Jones - Editor (3/7/2010)
I think it makes sense to spend some time learning some of these, especially the ones that you use on a regular basis. Those you ought to have a lot of familiarity with and understand how to use them.

...

Jeff brings up a good point as well in terms of not necessarily depending on these tools, but learning more about your craft after time. That's part of being a professional. Understanding what makes sense, what is appropriate, and then when to use it. And, of course, how to use those tools.


You said it quite nicely.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

james-888817
james-888817
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 12
I think that one would now have to add powershell to that list.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39418 Visits: 32630
Excellent list and great set of suggestions. I'd suggest an exchange, already covered by others. Yanke the DTA and put PowerShell up in its place.

The DTA is just way to shaky in its performance to be truly useful. I've had to run it during Premier Support calls with Microsoft and the suggestions have either been useless or harmful. I'm sure there are instances where it helps, but I haven't been able to see them. And that, despite having to write about it for my book. I tried and tried to get it to suggest useful indexes, and on queries that clearly could have benefited from an index. Queries that had missing index information in them, the DTA just couldn't find a good index. It truly stinks.

More and more I'm finding uses for PowerShell, specifically with SMO, for management & development on SQL Server machines. I'd say that should be one tool that gets added to the standard DBA toolbelt.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search