At a customer, I found out their tempdb was not as it should be. The server uses 4 CPUs, each with 2 cores. Common consensus seems to be that for every core, you ought to have 1 file in the tempdb. Their tempdb was fitted with 4 files.
The other recommendation is to make sure all files in the tempdb are equally large. This is because SQL Server uses a sort of a round-robin approach, but will favour files with more free space than others. So by ensuring all files are the same size, you prevent SQL Server from ending up sending most of its workload into the largest file.
As it turns out, out of those 4 files, 3 were sized at 20 GB, and the fourth one was 25.9 GB.
Based on my recommendation, we grew their drive (In this case, the T-partition) to ensure we'd have sufficient amounts of space to host 8 files of 30 GB each, as well as a transaction log @ 15 GB, and additional space available just in case (the server hosts a data warehouse, so is used to heavy bulky queries).
The script we used to grow the initial 4 files are:
Obviously, we ran this for all 4 files involved.
And here's the script we used to add 4 additional files (again, changing the name for different files):
I know that allowing for FILEGROWTH might not be what every expert would recommend, but I'd hate to see the client end up with a tempDB unable to grow if it'd for some reason need it. And as said, we ensured there's room for growth on the drive.
Finally, should you want to go a-pokin' around and check up on your tempdb, the below script might be of interest to you. It will basically list all current devices the tempdb is made up of, as well as give you an overview of the currently configured filesize, which might be different from what you see in the GUI.
The client server is due to reboot tonight, courtesy of the WannaCry malware outbreak, so I'm looking forward to a happy server with a lot less PAGELATCH waits tomorrow morning
The other recommendation is to make sure all files in the tempdb are equally large. This is because SQL Server uses a sort of a round-robin approach, but will favour files with more free space than others. So by ensuring all files are the same size, you prevent SQL Server from ending up sending most of its workload into the largest file.
As it turns out, out of those 4 files, 3 were sized at 20 GB, and the fourth one was 25.9 GB.
Based on my recommendation, we grew their drive (In this case, the T-partition) to ensure we'd have sufficient amounts of space to host 8 files of 30 GB each, as well as a transaction log @ 15 GB, and additional space available just in case (the server hosts a data warehouse, so is used to heavy bulky queries).
The script we used to grow the initial 4 files are:
Code:
ALTER DATABASE tempdb
MODIFY FILE (name = tempdev, SIZE = 30GB)
;Obviously, we ran this for all 4 files involved.
And here's the script we used to add 4 additional files (again, changing the name for different files):
Code:
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev5, FILENAME = 'T:\TempDB\tempdb5.mdf', SIZE = 30GB, FILEGROWTH = 512MB);I know that allowing for FILEGROWTH might not be what every expert would recommend, but I'd hate to see the client end up with a tempDB unable to grow if it'd for some reason need it. And as said, we ensured there's room for growth on the drive.
Finally, should you want to go a-pokin' around and check up on your tempdb, the below script might be of interest to you. It will basically list all current devices the tempdb is made up of, as well as give you an overview of the currently configured filesize, which might be different from what you see in the GUI.
Code:
SELECT
[name] AS [logical_name]
, [size] * 8 / 1024 AS [size_in_MB]
FROM
master.sys.master_files
WHERE
database_id = 2
;The client server is due to reboot tonight, courtesy of the WannaCry malware outbreak, so I'm looking forward to a happy server with a lot less PAGELATCH waits tomorrow morning
Last edited: