[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Determining appropriate size for tempdb in SQL 2008

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

608 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