Post reply

Erratic SP Performance

  • February 29, 2012 at 8:22 am

    #1453196

    Jeff Moden (2/28/2012)

    With that thought in mind and taking a shot in the dark, what are the initial size and growth settings of TempDB?

    tempdev: initial size 8MB, autogrowth by 10% (unrestricted growth).

    templog: initial size 1MB, autogrowth by 10% (unrestricted growth).

    These are the defaults IIRC.

    Update:

    I found the tempdb files to have grown to the following:

    tempdev: 480.23MB

    templog: 82.31MB

    Dave MasonSeminole County, FL

  • February 29, 2012 at 10:19 am

    #1453291

    Ouch.

    So tempDB is reaching almost 500MB in 1MB chunks (well, to start with). Not pretty. Set the default size of TempDB to something in the general vecinity of the size you found it at. I'd probably go 500MB, 100MB

    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

  • March 7, 2012 at 7:38 am

    #1456004

    I ended up opening a support ticket with Microsoft. During initial correspondence with the MS rep, it came to light that SP1 was not installed on the server (Win Server 2008 R2). After installing the SP, performance has been quite good. 4 - 5 days afterwards, I rebooted the server again. Hopefully, performance will remain good...

    UPDATE:

    Just to clarify, I installed SP1 for the OS. (The Sql 2008 R2 instance already had SP1 installed.)

    Dave MasonSeminole County, FL

  • March 7, 2012 at 9:54 am

    #1456112

    There's 5 CU's to SP1 already, something else to look into. We've been debating whether to (and when to) start moving to the latest CU.

  • March 8, 2012 at 6:46 pm

    #1456914

    Dave Mason (3/7/2012)

    I ended up opening a support ticket with Microsoft. During initial correspondence with the MS rep, it came to light that SP1 was not installed on the server (Win Server 2008 R2). After installing the SP, performance has been quite good. 4 - 5 days afterwards, I rebooted the server again. Hopefully, performance will remain good...

    UPDATE:

    Just to clarify, I installed SP1 for the OS. (The Sql 2008 R2 instance already had SP1 installed.)

    That's good but you still need to fix the growth and initial size settings of TempDB. 😉

    --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)
    Intro to Tally Tables and Functions

  • March 9, 2012 at 1:10 pm

    #1457369

    Jeff Moden (3/8/2012)

    That's good but you still need to fix the growth and initial size settings of TempDB. 😉

    Yes, I fixed that on my "problem" server, and on two other production servers as well.

    Thanks, Jeff and Gail!

    Dave MasonSeminole County, FL

Viewing 6 posts - 16 through 20 (of 20 total)

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