wasabi3689
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.MSSQLSER VER\MSSQL\ DATA
templog
F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER VER\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.m df? and 16 tempdb1.ndf ...tempdb16.ndf
or I just need 16 tempdev1.mdf...tempdev16.m df but only one tempdb.ndf?
Also, all the separate tempdb should be equal in size, correct?
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.MSSQLSER
templog
F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
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.m
or I just need 16 tempdev1.mdf...tempdev16.m
Also, all the separate tempdb should be equal in size, correct?
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.MSSQLSER
templog
F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
to
tempdev
K:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER
templog
K:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSER