Taking Advantage of SQL Server Tools

Question

Taking Advantage of SQL Server Tools

Brad McGehee

SSCertifiable

Points: 5282
More actions
March 6, 2010 at 1:52 pm

#139299

Comments posted to this topic are about the item Taking Advantage of SQL Server Tools
Brad M. McGehee
DBA

Viewing 15 posts - 1 through 15 (of 19 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

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 SSC Guru Points: 1004313 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

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[/url]
Learn Extended Events

james-888817 Valued Member Points: 63 More actions · Answer 8

I think that one would now have to add powershell to that list.

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

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

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

Gary Varga SSC Guru Points: 82166 More actions · Answer 10

I think that this extends to developers too. Maybe not all of the items on the the list but then again maybe so.

As for PowerShell, that's a me too. Hopefully, PowerShell will reduce the number of interfaces required to be learnt (although experience says that it will just add one to the list).

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

ken-467489 Old Hand Points: 317 More actions · Answer 11

Brad,

As always a hit on the nose. I find that these tools are very powerful especially when you need to debug something. Im not sure if it was intended to be included the the Management studio, but I would have broken out SSIS by it'self with all of the power it has to schedule and maintain the database.

Thanks again

Ken

NielsGrove Say Hey Kid Points: 710 More actions · Answer 12

I do agree that PowerShell should be on the list, and I would like to point out that SQL Server PowerShell (sqlps) should be on the list.

I tend to use sqlps before sqlcmd as I then can do more complex tasks in a much simpler way.

/Niels Grove-Rasmussen

WayneS SSC Guru Points: 95460 More actions · Answer 13

I don't know about DAC being on a list of tools to master. Agree with others about DTA not being on it... I've seen DTA (and the missing indexes dmv's) suggest new indexes, where adding a column to an existing index would handle the query... I think all they know is to add a new one.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Nadrek SSC-Insane Points: 20039 More actions · Answer 14

I think Perfmon is missing in the list; I find it a useful tool for watching, in particular, disk and network IO.

A packet sniffer of choice can also be very useful; not all issues manifest at the SQL Server level except as "It's slow!".

A big +1 on Profiler.