Every SQL Server instance relies heavily on tempdb to function. Because of this it is important to properly setup and configure tempdb. If you work on a system with a sub optimal tempdb setup then you may have to move files. Read on to see a technique for getting this job done.
Review tempdb Configuration Best Practices
Since tempdb is used by all databases on the instance it is imperative to properly configure it. I wrote about how to set up smarter configurations in my SQL Server Environmental Diagnostics Guide.
The basic guidelines are:
- Each tempdb data file should be the same initial size
- Autogrowth to tempdb files should be an explicit value in MB instead of a percentage. Choose a reasonable value based on the workload. Ex. 64MB, 128MB, 256MB, etc.
- The number of tempdb files should be 1 per logical processor core up to 8. At that point the performance should be monitored and if more tempdb files are needed they should be added in sets of 4.
- Ideally the tempdb files are sized up to the max they will need and never have to autogrow.
- Use trace flags 1117 and 1118 for versions of SQL Server < 2016. In SQL Server 2016 these trace flags are defaults.
- Trace flag 1117: when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow together
- Trace flag 1118: Removes most single page allocations on the server, reducing contention on the SGAM page. TLDR; no more mixed extents – use the whole page.
There is a lot to unpack here. Pretty much everything that could be wrong is wrong. You may think that things are not this bad in the wild but you would be wrong.
- The background is that there are about 40 logical CPU cores. I have no idea why there are 60+ tempdb data files because that is way too many. Not only are there diminishing returns to adding more files unnecessarily but it can actually hurt performance. Start with 8 and monitor.
- Every tempdb file has a different initial size. This is really bad because the round robin and proportional fill algorithms that SQL Server uses to allocate tempdb files depends on the files growing together. Paul Randal has a very interesting post about this.
- Autogrowth are set to percentages. This is much harder to manage than using concrete MB. The math changes the larger you get: 10% of 1GB is much different than 10% of 1TB.
Shake the horror off and let us see about what we can do to improve the situation.
Move and Configure tempdb
My tiny VM lab has 2 CPU cores. Let’s look at how we can move tempdb from where it currently resides to another location. We will reconfigure it along the way.
Alternatively you can query:
--how many logical CPU cores
select scheduler_id, cpu_id, status, is_online
where status = 'VISIBLE ONLINE';
Here is our starting location:
select * from sys.master_files where database_id = DB_ID(N'tempdb');
Now let’s put the files somewhere else and change the initial size along with the autogrowth.
--Move tempdb files to a different location
declare @sql nvarchar(max) = ''; --initialize because we cannot concatenate NULL with anything
declare @newPath varchar(128) = 'G:\MSSQL\tempdb\'; --ENTER NEW LOCATION
declare @sizeMB int = 128; --ENTER INITIAL SIZE IN MB
declare @autogrowthMB int = 64; --ENTER AUTOGROWTH IN MB
select @sql += N'alter database tempdb modify file (name = [' + f.[name] + '],
filename = ''' + @newPath + f.[name]
+ case when f.[type] = 1 then '.ldf' else '.mdf' end + ''''
+ ', size = ' + cast(@sizeMB as nvarchar) + 'mb'
+ ', filegrowth = ' + cast(@autogrowthMB as nvarchar) + 'mb'
+ char(13) + char(10)
from sys.master_files f
where f.database_id = DB_ID(N'tempdb');
exec sp_executesql @sql;
I usually run it first with just the print statement and not the subsequent execution. That way I can give it a sanity check before letting it rip.
PROTIP: leave a print for variables to help debugging
NOTE: you must restart SQL Server services to see the change in tempdb!
The old files will still be here: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA
Since nothing is pointing to them you can simply delete the files from this location.
Removing Files from tempdb
If you need to remove some tempdb files there is a trick to it. SQL Server will not let you remove the file unless it is emptied.
Now the file is safe to remove:
alter database tempdb remove file tempdev2;
If you check the file location you will see that it is no longer there.
Thanks for reading!
if you liked this then you might like Migration – How To Move SQL Server Database Files to a New Location