Solved

Determining appropriate size for tempdb in SQL 2008

Posted on 2010-11-15
2
777 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
2 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
Comment Utility
>> 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
Comment Utility
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
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 tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

763 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

10 Experts available now in Live!

Get 1:1 Help Now