Post reply

Urgent performance problems

  • February 20, 2007 at 1:40 am

    #690585

    I keep pointing out that the sql performance memory recommendations are not complete. As with all things it just depends, but the /3gb switch can give problems. Read Ken Henderson's Guru's guide to internals, this explains how it all works.

    I agree with Geoff, tuning is usually about 10% hardware and 90% application. I know it seems easier to throw hardware, blame the server, blame sql server - but it's usually the application that needs looking at - this is what I do for a living ( tuning ) and in nearly all cases it's the application ( including indexes ) that needs attention. The main hardware issue I run up against is the use of Raid 5 .

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 20, 2007 at 12:57 pm

    #690735

    oops! sorry Jeff , slip of the brain.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 20, 2007 at 7:15 pm

    #690787

    Heh... at least it sounds the same... I've been called worse

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

  • February 20, 2007 at 7:24 pm

    #690789

    I love a good discussion, especially when the likes of Colin and Jeff add their knowledge to the banter. I'm always learning something from them. Keep it up guys !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • February 20, 2007 at 8:31 pm

    #690801

    Sam,

    You took a good first step by defragging the DB.  Now, about that "slow query" and "insufficient memory" problem... "slow" normally means "resource hog" for CPU, IO, and Memory.  If it's not too large, you should post it and let us see if we can identify some key areas to help you with. 

    If it is large, check it for a couple of really bad things...

    1. (Sorry folks) Cursors
    2. Unfiltered cross joins
    3. Unfiltered triangular joins (half a cross join and normally has somecolumn < someothercolumn or somecolumn > someothercolumn).  Some folks will mistakenly use such a thing to create a running count or a running total... works fine for less than a couple thousand rows or in carefully filtered sub-queries in the SELECT list... but starts choking really bad above 10K rows for the same count.
    4. Correlated sub-queries... especially in the WHERE clause (a sub query that references a table that's not in the subquery).
    5. UPDATES that use a join that don't have the target of the update in the FROM clause of the update.
    6. Correlated sub-queries in UPDATES.
    7. Non-sargeable WHERE/ON clauses (have column names in formulas)... destroys any chance of Index Seeks.
    8. Look for joins to views and then go look at the code for the views.  Lot's of folks write, well... shear crap for views in that they return too many columns and have all the problems I just mentioned (except for the updates, of course) I just mentioned.
    9. Look for views of views.  Very bad "Bozo No-No" if they are used incorrectly because lot's of folks write these in such a manner as to cause every row in the inner view to materialize before the outer view can use it.  The only thing worse than a view of a view is a self join of a view... both views must materialize their rows which will consume much memory.
    10. Look for hidden code like "Triggers" and "functions"... If poorly written, these will also kill performance... big time.
    11. Look for "dumb" Clustered Indexes... those are Clustered Indexes that will cause simple inserts to reorganize data in the table.  A highly transactional table should probably not have a Clustered Index but, since the default for a Primary Key is Clustered, you may have one that you don't actually want.
    12. Check the execution plan... see what's happening... anywhere there is a pencil thin line is an area of possible concern.  Also, look for large row counts.

    There's a couple million other things you should look for... but that's what I would look for first.

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

Viewing 5 posts - 16 through 19 (of 19 total)

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