Determining appropriate size for tempdb in SQL 2008

Posted on 2010-11-15
Last Modified: 2012-05-10
Hello experts,

I have to allocate storage in SAN for tempDB that can cater for 3 instances  hosted on a single server.
First Instance - Total size of mdf's of DBs are 300 GB
Second Insance - Total size of mdf's of all DB's are 30 GB
Third Instance - Totla Size of mdf's of all DB's are 1 GB
SQL VER: 2008
OS: W2K8 R2
Please advise as to how I can arrive at sizing tempDB space.

Question by:goprasad
LVL 57

Accepted Solution

Raja Jegan R earned 500 total points
ID: 34142808
>> First Instance - Total size of mdf's of DBs are 300 GB
Second Insance - Total size of mdf's of all DB's are 30 GB
Third Instance - Totla Size of mdf's of all DB's are 1 GB

You need to get clarified on one thing that Tempdb is independent for instances and not shared and hence First Instance would have it own tempdb, Second would have its own tempdb and so on.

And you forgot to mention about the no. of processors available in your Server. Say if you have 8 cores ie., 2 Quad core processors, you need to set affinity mask using sp_configure command to force those instances to utilize the allocated processors only.

For your above scenario, I would split affinity mask in this ratio

First Instance - 4 cores
Second Instance - 2 Cores
Third instance - 1 Core
OS and other applications - 1 Core

Tempdb should be split into separate files as that of the no. of cores available for that instance. Hence

First Instance - Tempdb needs to be splitted into 4 files with 4 GB each
Second Instance - Tempdb needs to be splitted into 2 files with 1 GB each
Third Instance - Tempdb can be a single file with 1 GB size

More details on why to split tempdb here:

Expert Comment

ID: 34148104
In Addition zu rrjegan17 (you need one tempdb per instance), the main sizing requirements for TempDB is the size of a single transaction on your database times the number of concurrent transactions minus size of RAM on your machine
whenever you run an SQL statement, SQL Server tries to process it in RAM. As soon as RAM is exhausted (because the intermediate size of a result is too big, e.g. you're performaing a table scan on a really big table and need the result for further processing in the query), the data is written to tempdb.
So in a OLTP database where the only select statements retrieve a simple and small part of the data, the tempdb can be fairly small. But for a Data Warehouse, the TempDB can be quite big, becaus many temporary results have to be written to it.

So a sizing can only be given if you watch the tempdb of your existing db. Just set it to a small size and let it grow automatically. Then run it for some days (make sure every query that will b e executed on the data has run at least once) and look how much the tempdb has grown.
I personally havent found a  rule of thumb yet; I have had TempDBs exceeding the size of the databases itself (on highly parallel systems) and beeing quite small (on dev machines where no big data is stored)....


Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

831 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