Post reply

Performance Problem

  • February 8, 2009 at 2:40 am

    #940118

    I was going to move tempdb when IT manager questioned me about the large size of tempdb!

    our tempdb file is 12GB, and he says it's so strange and there should be a problem.

    I'd like to know if he's right.

  • February 8, 2009 at 9:06 am

    #940160

    peace2007 (2/8/2009)

    I was going to move tempdb when IT manager questioned me about the large size of tempdb!

    our tempdb file is 12GB, and he says it's so strange and there should be a problem.

    I'd like to know if he's right.

    We do a lot of batch and report processing... really heavy duty stuff, too. When we boot the server, we have TempDB start at 12 GB. It's not that strange. A lot of people start out at something smaller and it ends up growing anyway.

    In the realm of tera-byte disk systems, a 9 to 12 GB Temp DB is becoming more common than not whether they start it that way, or that's what it grows to.

    Got resources? Use them. 😉

    --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 8, 2009 at 9:29 am

    #940165

    Jeff Moden (2/8/2009)

    We do a lot of batch and report processing... really heavy duty stuff, too. When we boot the server, we have TempDB start at 12 GB. It's not that strange.

    Indeed. The semi-datawarehouse that I worked on at the bank had a TempDB that was configured for 6 files of 10GB each. We'd regularly get to 70% of that used during the early hours of the morning while the data loads were finishing and the regulatory reports running.

    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 8, 2009 at 5:20 pm

    #940233

    peace2007 (2/8/2009)

    I was going to move tempdb when IT manager questioned me about the large size of tempdb!

    our tempdb file is 12GB, and he says it's so strange and there should be a problem.

    I'd like to know if he's right.

    Starting with SQL Server 2005, tempdb has assumed a larger importance as several new types of objects are now stored there.

    Read all about it in:

    http://technet.microsoft.com/en-ca/library/cc966545.aspx

    If you rely heavily on triggers and/or have turned on optimistic locking, your tempdb use will increase drastically, relative to earlier versions of SQL Server.

    In our shop we typically pre-allocate space for tempdb data files on the order of 50 GB, to ensure our systems will not go down because of one rogue query that we failed to identify during testing.

    One suboptimal query can consume tens of gb of tempdb space. Better to allocate more and be on the safe side than less and have dissatisfied users.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • February 9, 2009 at 4:02 am

    #940365

    Thanks all for your useful comments 🙂

Viewing 5 posts - 61 through 64 (of 64 total)

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