Taking Advantage of SQL Server Tools

  • Comments posted to this topic are about the item Taking Advantage of SQL Server Tools

    Brad M. McGehee
    DBA

  • 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]

  • 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.
  • 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)

  • 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.

  • 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

  • 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

  • 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

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

  • 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

  • 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!!!

  • 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

  • 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

    /Niels Grove-Rasmussen

  • 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

  • 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.

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

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