Solved

Determining appropriate size for tempdb in SQL 2008

Posted on 2010-11-15
2
791 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 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:
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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
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 walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…

726 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