i was suffering from the same problem and i applied the following after searches, testing , and Microsoft consultants' recommendations
- It is highly recommended to have RAID
- Move tempdb to a separate partition
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'T:\Tempdb\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'T:\Tempdb\templog.ldf')
go
- Partition tempdb over multiple files [Number of files=number of Logical CPUs]
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev1', FILENAME = N'T:\Tempdb\tempdev1.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2', FILENAME = N'T:\Tempdb\tempdev2.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3', FILENAME = N'T:\Tempdb\tempdev3.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev4', FILENAME = N'T:\Tempdb\tempdev4.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev5', FILENAME = N'T:\Tempdb\tempdev5.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev6', FILENAME = N'T:\Tempdb\tempdev6.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev7', FILENAME = N'T:\Tempdb\tempdev7.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev8', FILENAME = N'T:\Tempdb\tempdev8.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev9', FILENAME = N'T:\Tempdb\tempdev9.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev10', FILENAME = N'T:\Tempdb\tempdev10.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev11', FILENAME = N'T:\Tempdb\tempdev11.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev12', FILENAME = N'T:\Tempdb\tempdev12.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev13', FILENAME = N'T:\Tempdb\tempdev13.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev14', FILENAME = N'T:\Tempdb\tempdev14.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev15', FILENAME = N'T:\Tempdb\tempdev15.ndf' , SIZE = 2048000KB , FILEGROWTH = 10% ) TO FILEGROUP [PRIMARY]
GO
sp_helpdb tempdb
USE tempdb
GO
DBCC SHRINKFILE (N'tempdev' , EmptyFile)
GO
- Configure Max Degree of Parallelism Option
sp_configure 'show advanced options',1
reconfigure with override
sp_configure 'max degree of parallelism',1
reconfigure with override
- Configure Max Server Memory Option [let some memory space to the OS]
sp_configure 'max server memory (MB)',15360
reconfigure with override
- Partition Data File over multiple files [No of Files = (from .25 to 1)*No of Physical CPUs]
USE [master]
go
sp_helpdb [Test-DB]
GO
ALTER DATABASE [Test-DB] ADD FILE ( NAME = N'Test_Data1', FILENAME = N'D:\data\Test_Data1.ndf' , SIZE = 2048000KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [Test-DB] ADD FILE ( NAME = N'Test_Data2', FILENAME = N'D:\data\Test_Data2.ndf' , SIZE = 2048000KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [Test-DB] ADD FILE ( NAME = N'Test_Data3', FILENAME = N'D:\data\Test_Data3.ndf' , SIZE = 2048000KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [Test-DB] ADD FILE ( NAME = N'Test_Data4', FILENAME = N'D:\data\Test_Data4.ndf' , SIZE = 2048000KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [Test-DB] ADD FILE ( NAME = N'Test_Data5', FILENAME = N'D:\data\Test_Data5.ndf' , SIZE = 2048000KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [Test-DB] ADD FILE ( NAME = N'Test_Data6', FILENAME = N'D:\data\Test_Data6.ndf' , SIZE = 2048000KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [Test-DB] ADD FILE ( NAME = N'Test_Data7', FILENAME = N'D:\data\Test_Data7.ndf' , SIZE = 2048000KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [Test-DB] ADD FILE ( NAME = N'Test_Data8', FILENAME = N'D:\data\Test_Data8.ndf' , SIZE = 2048000KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
USE [Test-DB]
GO
DBCC SHRINKFILE (N'Test_Data' , EmptyFile)
7. Create a job that rebuild most used indexes on daily basis