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
------------------------------------------------------------------------