[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 998
  • Last Modified:

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

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.
1 Solution
David ToddSenior DBACommented:

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?

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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...
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Oh yeah and if you can give tempdb its own RAID conf it will help...
malibuboatsAuthor Commented:
The DB now is currently only about 8mb, to me though that does not sound right..
David ToddSenior DBACommented:

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


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now