The secret SQL Server techniques that they don't want you to know Part 1

Seriously Microsoft don't want you to know this because they bury the value deep inside the Knowledge Base where no one can find it.  

When you build a new SQL Server or take on the support of an existing one, check the configuration of the temp db. How many do you have? 

Best practice is to match the number of temp db to the CPU cores in the Server. This will help avoid paging in memory of Latches. These are locks in memory.

Below is an existing SQL Server, it is over loaded right now and has only one temp db. I have monitored the number of Latch Wait/sec. You can see the maximum is 74 per second with an average of 5 per second. The longer a latch has to wait the longer queries will take to run. This means the longer users have to wait for data.

Below the same SQL Server as above is showing a maximum wait time 9ms, if we have 74 latches waiting at 9ms each then that is 666ms wait time to clear them all. That is over half a second. This needs to be reduced or the users of the databases are going to start to have a poor experience.

The Microsoft article that discusses this situation can be read here. 

I will outline next what you should do to avoid this situation with an existing SQL Server or a new build.

On the SQL Server open Task Manager and select CPU. You can now look at the Virtual Processors to determine how many CPU cores you have.

Next increase the size of the existing temp db.

The below TSQL command will increase the temp db to 8GB in size.

USE [master]; 

GO 

alter database tempdb modify file (name='tempdev', size = 8GB);

GO

Now you will have a fixed size temp db at 8GB. You want to make sure the temp db file is set to NOT auto grow. SQL will assign paging to temp db by the size of the file so you want to have the same size temp db for each file to make sure a round robin occurs.

The below TSQL command will add a further three temp db to the SQL Server

USE [master];

GO

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'F:\Tempdbs\tempdev2.ndf' , SIZE = 8GB , FILEGROWTH = 0);

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3', FILENAME = N'F:\Tempdbs\tempdev3.ndf' , SIZE = 8GB , FILEGROWTH = 0);

ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev4', FILENAME = N'F:\Tempdbs\tempdev4.ndf' , SIZE = 8GB , FILEGROWTH = 0);

GO

In this example Server we have 4 CPU cores, therefore I have added three more temp db files to the Server and I have located them on the F:\Tempdbs folder.

Check the Windows Explorer file location for the files as they are created. Note they should all be 8GB in size as specified.

You have now created the correct relationship of temp db to CPU Cores for your Server.