Post reply

Performance Advice

  • February 15, 2011 at 4:12 am

    #246695

    Hi All,

    I have two problems:

    1. I have an SQL 2000 SERVER, which uses all of the 8 processors, Memory of 12223(MB). Reports are run through out the day as well as bespoke queries which results for the server to run very slow. I was wondering what I should change in order to make the performance up to speed please.

    2. Updatestats for each server job that runs every two weeks but for the past month the job seems to be executing but not finishing so I have to end up canceling the job.

    I would be very grateful if someone advice me on my problems.

    Thank you

  • February 15, 2011 at 4:21 am

    #1286953

    What about the other maintenance jobs?

    Are they running fine?

    Rebuild/reorg ?

    M&M

  • February 15, 2011 at 4:22 am

    #1286955

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 15, 2011 at 4:57 am

    #1286968

    mohammed moinudheen (2/15/2011)

    What about the other maintenance jobs?

    Are they running fine?

    Rebuild/reorg ?

    All the other jobs seem to be fine for now. The indexes are dropped on a daily and recreated as part of an overnight job, because of this I believe that I have to run updatestats but I haven't run it for the past month because it keeps failing. Would you suggest that I have a Server that runs all the reports as a last resort?

  • February 15, 2011 at 5:01 am

    #1286970

    The only stats you'll need to update are the column statisics (ones not associated with indexes) as the ones that are associated with indexes will be recreated with the index.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 15, 2011 at 5:01 pm

    #1287391

    tt-615680 (2/15/2011)

    Reports are run through out the day as well as bespoke queries which results for the server to run very slow.

    There's a high probability that nothing you do to the server will help. Performance is usually in the code, or not. Some indexing can certainly help but I've see a whole lot of reporting code that uses non-SARGable predicates which prevents proper use of indexes and also has many other problems including "accidental Cross Joins" (ie: many-to-many joins) typical of "all in one queries", joined aggregated views, views of views, scalar and multi-line table valued functions, functions of functions, RBAR procs, Triangular Joins, and a wealth of other performance "sins".

    Even a "bad" server can look good if the code is written with performance in mind.

    --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 6 posts - 1 through 5 (of 5 total)

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