?
Solved

tempdb questions

Posted on 2012-08-26
3
Medium Priority
?
643 Views
Last Modified: 2012-09-04
Hi,

I have a database thrown out many deadlock recently. It seems it use a lot of tempdb. I have several questions about tempdb.

So far the tempdb is located

tempdev
E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

templog
F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data

They are auto growth.

I see a lot of activities going on on tempdb. But, I am not able to catch them. So, how do I capture the activities like what queries used in tempdb?

It is a good idea to split tempdb into muitl, how? sliting tempdb means only spiting log or data? I google some articles, it seems saying Generally you want to ensure you have as many tempdb data files as there are CPU's or vCPU's.
You'll only need one log file. Make sure both data and log files are not set to the standard autogrowth...
Is this correct?

My next question is how to creating Multiple Files?

I am using the following query to do that

USE master;
GO
ALTER DATABASE [tempdb]
ADD FILE ( NAME = 'tempdev1',
FILENAME = 'f:\tempdb1.ndf' ,
SIZE = 8192KB , FILEGROWTH = 10%)

If I want to separate into 16 tempdb, does it mean I need tempdev1.mdf...tempdev16.mdf? and 16 tempdb1.ndf ...tempdb16.ndf

or I just need 16 tempdev1.mdf...tempdev16.mdf but only one tempdb.ndf?
Also, all the separate tempdb should be equal in size, correct?
0
Comment
Question by:wasabi3689
  • 2
3 Comments
 

Author Comment

by:wasabi3689
ID: 38335340
I have another question

How to move the SQL Server tempdb to new Drive ? For example, I have a new drive K, Do I just need to change like below??

from
tempdev
E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

templog
F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data

to

tempdev
K:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

templog
K:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data
0
 

Author Comment

by:wasabi3689
ID: 38335463
Final question about tempdb


For better performance, is this correct?
This ‘tempdb’ is used for temp table...etc. Ideally this should not be in same drive as data-drive.
0
 
LVL 18

Accepted Solution

by:
x-men earned 2000 total points
ID: 38335901
have as many tampdb datafiles as PHYSICAL CPU CORES (hyperthreading does not count) to a max of 8 (if you have 16 cores, start with 8 tempdb data files, and test increments of 4 untill reach the best performance)

The files should be in a seperate HDD Controler (or at least a seperate disk)

The files should have the same size and growth params.

Example:
ALTER DATABASE tempdb
      MODIFY FILE (NAME = 'tempdev', NEWNAME = 'tempdev1', FILENAME = 'D:\SQL_TEMPDB\tempdev1.mdf', SIZE = 2GB, FILEGROWTH = 10%)
      GO
ALTER DATABASE tempdb
      MODIFY FILE (NAME = 'templog', FILENAME = 'D:\SQL_TEMPDB\templog.ldf', SIZE = 1GB, FILEGROWTH = 10%)
      GO
ALTER DATABASE tempdb
      ADD FILE (NAME = 'tempdev2',FILENAME = 'D:\SQL_TEMPDB\tempdev2.ndf' , SIZE = 2GB, FILEGROWTH = 10%)
      go
ALTER DATABASE tempdb
      ADD FILE (NAME = 'tempdev3',FILENAME = 'D:\SQL_TEMPDB\tempdev3.ndf' , SIZE = 2GB, FILEGROWTH = 10%)
      go
ALTER DATABASE tempdb
      ADD FILE (NAME = 'tempdev4',FILENAME = 'D:\SQL_TEMPDB\tempdev4.ndf' , SIZE = 2GB, FILEGROWTH = 10%)
      go
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

862 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