?
Solved

Determining appropriate size for tempdb in SQL 2008

Posted on 2010-11-15
2
Medium Priority
?
793 Views
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.

Thanks.
0
Comment
Question by:goprasad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 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:
http://sqlsolace.blogspot.com/2007/11/moving-tempdb.html
0
 

Expert Comment

by:geepy
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)....

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

752 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