If my database is 80GB's, how big should my Tempdb files be for a 2-quad core system?

Posted on 2007-10-10
Last Modified: 2008-01-09
My SQL 2005 Database is on Windows 2003 64bit OS with SQL 2005 64bit. My database is 80gigs and I am trying to figure out how to calculate the Tempdb file size. I have a dual Quad core system. I know that that I need an equal amount of files per processor, but I dont know how big they need to be.. i.e. 8 files at 10GB each for the tempdb. Any thoughts.
Question by:malibuboats
    LVL 35

    Expert Comment

    by:David Todd

    Lets start at the very beginning - how big is tempdb at present? How many files?

    While there are white papers to support the idea of a tempdb file per CPU core, the total size should stay about the same.

    That is, if tempdb on one file is 20GB, and because your server is dual quad core, you want that to go to 8 files, then each file should be around 2.5GB. Otherwise you are using space for no advantage.

    Note that different SQL features require different amounts of tempdb.

    Do you have tempdb files on auto-grow?

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    there is no formula that gives you the size of the tempdb in regards to the size of the user db.

    the tempdb size will depend mainly on the usage of GROUP BY, ORDER BY, inline views in large queries, and of course, temporary tables etc...
    you have 2 methods:
    * let the tempdb grow upon usage, and after a while, you can define it's size and restart sql server.
    * estimate the size upon the usage (mainly guesswork), and define it's size and restart sql server, and then apply first method.
    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    <<Any thoughts.>>
    Additionally to the above comments, I would say that people place too much importance on CPU power and not enough on disks (RAID ) and tuning...In other terms, nothing guarantess that increasing CPU is appropriate to size up your db infrastructure...My best advice to you is to analyze the current state of IO to find out the best sizing (tuning)  strategy...

    Just so that you some 200-300Go db's running ok on single and bi dual core...They however have good IO conf behind (multi RAID per db)...

    Hope this helps...
    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    Oh yeah and if you can give tempdb its own RAID conf it will help...

    Author Comment

    The DB now is currently only about 8mb, to me though that does not sound right..
    LVL 35

    Accepted Solution


    If tempDB is currently 8Mb - this does sound a little light - then to answer your question, I'd start with 8 files of 1Mb each.

    I suspect that you may be looking at tempdb shortly after SQL or the server has restarted, and now tempdb is having to be recreated from scratch - ie starts from close to nothing, and grows as needed.

    I'd suggest waiting a day or so, maybe even a week if you have some big processes that only run weekly, and then seeing how big tempdb is.

    If you can set the starting size close to its actual size you achieve a couple of things - you reduce the amount that tempdb has to grow, and by doing that, you reduce the amount that the OS file will fragment. Just keeping the data files defragmented can get better disk throughput ...


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now