Follow

SQL Server TempDB Growth - Best Practices

Created by: Anne Patterson
Created date:
Last Updated date:

Product / Version

 

SummationPro, eDiscovery / All version
 

Issue / Symptoms

 

User interface may experience timeouts and/or the SQL Server query speed will decrease dramatically.
 
Cause

 

This can be caused by the tempdb on SQL Server growing too large.  There are a number of factors that contribute to this state.
 

Resolution

 

Break the tempdb into Microsoft recommended sizes (2GB per core) pre-sized. Rebooting the SQL Server will flush out the tempdb. Accessdata suggests you consult a database administrator to optimize settings for your workload.
 
The SQL script below will break the tempdb into chunks based on the amount of cores:
 
------------------------------------------------------------------------
USE master
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 2097152KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 1048576KB )
GO
 
DECLARE @HTR int, @dflocation nvarchar(max), @PROC nvarchar(max)
SELECT @HTR =hyperthread_ratio
 FROM sys.dm_os_sys_info
 IF (@HTR > 8)
SET @HTR = 8
 SET @dflocation = ( SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation 
FROM master.sys.master_files
WHERE database_id = 2 AND FILE_ID = 1 )
 DECLARE @CNT INT
 SET @CNT = 1
 WHILE (@CNT !=@HTR)
 BEGIN
SET @PROC = N'ALTER DATABASE [tempdb] ADD FILE ( NAME = N''tempdb'+CAST(@CNT as nvarchar(2))+''', FILENAME = N'''+@dflocation+'tempdev'+CAST(@CNT as nvarchar(2))+'.ndf'' , SIZE = 2097152KB , FILEGROWTH = 10%)'
PRINT @PROC
EXEC SP_EXECUTESQL @PROC
SET @CNT =@CNT +1
 END
------------------------------------------------------------------------
 
Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk