tempdb questions

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?
wasabi3689Asked:
Who is Participating?
 
x-menIT super heroCommented:
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
 
wasabi3689Author Commented:
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
 
wasabi3689Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.