MS SQL tempdb - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

MS SQL tempdb

Author

Topic

rosetulip
Starting Member

15 Posts

Posted - 2012-08-27 : 02:15:38

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?

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.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-27 : 04:34:24

What makes yoou think tempdb is the issue.
Usually deadlocks are caused by poor designed code and you shoould probably look at that.
Look at the statements that are causing the deadlock and see if you cn change them. Minimise the duration of transactions - which coukd mean accumulating data to be updated then performing the update.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-27 : 04:52:08

Have you identified the code with the problem? When you do, analyse the order of the transactions? Can you apply more BEGIn TRANS..COMMIT TRANS around the sub steps, to release locks?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources