Posted on 2012-08-26
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
E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
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
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?