Link to home
Start Free TrialLog in
Avatar of wasabi3689
wasabi3689Flag for United States of America

asked on

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?
Avatar of wasabi3689
wasabi3689
Flag of United States of America image

ASKER

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
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.
ASKER CERTIFIED SOLUTION
Avatar of x-men
x-men
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial