Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Taking Advantage of SQL Server Tools Expand / Collapse
Author
Message
Posted Saturday, March 6, 2010 1:52 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
Comments posted to this topic are about the item Taking Advantage of SQL Server Tools

Brad M. McGehee
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #878216
Posted Saturday, March 6, 2010 4:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 7,042, Visits: 12,971
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
Post #878245
Posted Saturday, March 6, 2010 5:21 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
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.

Post #878246
Posted Saturday, March 6, 2010 6:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 36,793, Visits: 31,251
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #878253
Posted Sunday, March 7, 2010 1:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:13 PM
Points: 33,100, Visits: 15,210
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
Post #878358
Posted Sunday, March 7, 2010 7:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:35 PM
Points: 21,340, Visits: 15,015
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #878395
Posted Sunday, March 7, 2010 7:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:35 PM
Points: 21,340, Visits: 15,015
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #878397
Posted Sunday, March 7, 2010 7:27 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:35 PM
Points: 21,340, Visits: 15,015
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #878398
Posted Monday, March 8, 2010 4:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 9, 2010 10:17 AM
Points: 5, Visits: 12
I think that one would now have to add powershell to that list.
Post #878506
Posted Monday, March 8, 2010 7:36 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 15,558, Visits: 27,932
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #878606
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse