An Informal Look at Database Performance

  • Comments posted to this topic are about the item An Informal Look at Database Performance

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Great article. It gives me a nice overview and a bunch of very useful links.

    Now I just have to find the time to read them all 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thanks a lot for the great overview !

    One thing I would like to mention additionally: Look a the SQL Server version. In my experience many companies upgrade their SQL Server too rarely, and especially in the MS family of SQL Servers, great improvements in the product during the last 10 years can be an easy gain (like factor 10 improvements or even more in some scenarios). So get rid of that SQL Server 2000 bastard now! (Believe me: They are still out there).

  • mortenmaate (9/8/2010)


    Thanks a lot for the great overview !

    One thing I would like to mention additionally: Look a the SQL Server version. In my experience many companies upgrade their SQL Server too rarely, and especially in the MS family of SQL Servers, great improvements in the product during the last 10 years can be an easy gain (like factor 10 improvements or even more in some scenarios). So get rid of that SQL Server 2000 bastard now! (Believe me: They are still out there).

    Heh... I love the older versions of SQL. That's were I learned all the tricks I know to make stuff work. 😛

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Well done, Timothy. Definitely "brief case" time for this article. Thanks for taking the time to make it so complete.

    The idea of having a baseline and the idea that most of the performance problems are in code are two very important subjects that I try to stress where some stress that hardware is the key. If you buy a server that's twice as fast, the best your code will do is run twice as fast. Tweek the code or rewrite it, and it can run literally 100's of times faster. Why is that important? Heh... look around this forum folks! It's filled with cries for help to make code faster or solve time-outs.

    The other item that people seem to stress is that the server needs to be tuned. Just like buying the proper hardware, tuning the server properly is certainly important but most of the performance problems folks run across just can't be solved by additional tuning of the server. You have to fix the code and this article stresses that.

    The same goes for indexing. Although you can get great gains from proper indexing (even some triangular joins can benefit), you can get even more from your indexes if your code is actually capable of using them. 😉

    [font="Arial Black"]"Tune the Code... that's where the performance is!" [/font]🙂

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Nice job Timothy! Your article certainly covers all the bases, troubleshooting, monitoring, & performance tuning.

    Taking snapshots of a server throughout the day (under normal conditions) is a great idea so that if performance were to degrade, there would be documented evidence to compare the performance degradation snapshot with the normal snapshot.

  • Hi,

    Nice article.

    You can discover a lot more about DMVs in this forthcoming book http://www.manning.com/stirk. Chapter 1 can be downloaded for free and includes scripts for:

    A simple monitor

    Finding your slowest queries

    Find your missing indexes

    Identifying what SQL is running now

    Quickly find a cached plan

    Thanks

    Ian

  • Great job, and a very nice read.

  • mortenmaate (9/8/2010)


    Thanks a lot for the great overview !

    One thing I would like to mention additionally: Look a the SQL Server version. In my experience many companies upgrade their SQL Server too rarely, and especially in the MS family of SQL Servers, great improvements in the product during the last 10 years can be an easy gain (like factor 10 improvements or even more in some scenarios). So get rid of that SQL Server 2000 bastard now! (Believe me: They are still out there).

    That is a very good point to add.

    I know from direct testing that the optimizer works much better in 2005 then it did in 2000. I have not tested the query performance going from 2005 to 2008 or R2, but I would be shocked if it got worse.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Nice overview; I'd like to encourage the addition of looking at SQL Profiler CPU, Reads, Writes, and Duration columns on SQL:BatchCompleted events.

    Not only do these show aggregates, if you do this on a regular basis, you'll get a good, trained idea of when a query is using "too much" of any of them.

  • Nadrek (9/8/2010)


    Nice overview; I'd like to encourage the addition of looking at SQL Profiler CPU, Reads, Writes, and Duration columns on SQL:BatchCompleted events.

    Not only do these show aggregates, if you do this on a regular basis, you'll get a good, trained idea of when a query is using "too much" of any of them.

    This is VERY helpful for anyone who has UDFs in their system too, since it is the ONLY way you can see how bad they really are.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I will add as a minor point that non-clustered index fragmentation (at least the extent kind) usually doesn't matter that much since the seeks tend to be random in nature anyway.

    Nice coverage of many of the things to look out for.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Nice article. Thanks Tim.

    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

Viewing 13 posts - 1 through 13 (of 13 total)

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