Creating a scalable tempdb for your cloud servers

When deploying to the cloud one of the hardest challenges is to get tempdb to scale in the same way as your virtual server.

Why is this, well as cloud machines are scalable and thus can change configurations during their lifecycle the number of tempdb files should be able to scale accordingly, so the amount of tempdb files becomes a dynamic value. Luckily for us, this parameter changes only after a shut down and restart of your virtual machine, so if we had a script that could adjust our tempdb needs every time the configuration changes we could deploy this and be more at ease.

This script does exactly that, if you place this code in a sql job that runs on startup, the script will adjust the tempdb settings accordingly every time you scale your cloud server.

I made the most important parameters completely adjustable while retaining maximum automation.

I did follow the best practices as much as possible which means that there will be an equal amount of tempdb files and cpu cores with a maximum of 8. This setting is not a parameter but can easily be adjusted when needed.

SET NOCOUNT ON;
/*

Script om tempdb te resetten op de Z:\ Drive van een (Amazon)VM

1. 1. Step wordt de tempdb gereset naar 1 file, en geschrinked naar 10GB

Dit is belangrijk omdat dan zowel de fileproperies als de filesettings correct geplaatst worden

1. Daarna worden de overblijvende TempDB files effectief gewist via commandline (Om residu te voorkomen)
2. Files worden opnieuw aangemaakt met de correcte size

*/
DECLARE @CMD nvarchar(200)
DECLARE @TempDBLocation nvarchar(200)
DECLARE @FilesizeInMB nvarchar(10)
DECLARE @FileGrowthInMB nvarchar(10)
DECLARE @TempDBPrimFile sysname
DECLARE @TempDBSecFile sysname
DECLARE @TempDBFileName varchar(2000)
DECLARE @Fileid int
DECLARE @Debug bit
DECLARE @Execute bit
DECLARE @AlwaysCleanUp bit
DECLARE @NumberOffFiles tinyint
DECLARE @NumberOffExistingFiles tinyint
SET @TempDBLocation=’Z:\’ –Location of tempdb, including the last \ sign
SET @FilesizeInMB=’1024′ — Filesize of the created files in MB
SET @FileGrowthInMB=’64’ — Filegrowth of the newly created files in MB
SET @Debug=0 –Set to 1 If you want the script to print the statements
SET @Execute=1 –Set to 1 If you want the script to execute the statements
SET @AlwaysCleanUp=0 –Set to 1 If you want the script to clean up the files on every reboot
SELECT @NumberOffFiles=CASE WHEN cpu_count < 8 THEN cpu_count ELSE 8 END FROM [sys].[dm_os_sys_info]
USE [tempdb];
Select @TempDBPrimFile=Name from sys.sysfiles where fileid=1
Select @NumberOffExistingFiles=MAX(fileid)-1,@Fileid=MAX(fileid)+1 from sys.sysfiles where groupid=1
IF SUBSTRING(@TempDBLocation,LEN(@TempDBLocation),1)<>’\’
BEGIN
PRINT ‘———————————————————-‘
PRINT ‘Error, Invallid location’
PRINT ‘———————————————————-‘
Goto Invalid
END
PRINT ‘———————————————————-‘
PRINT ‘Script Executing with the following parameters:’
PRINT ‘———————————————————-‘
PRINT ‘@TempDBLocation=’+@TempDBLocation
PRINT ‘@NumberOffFiles=’+CAST(@NumberOffFiles as varchar(5))
PRINT ‘@NumberOffExistingFiles=’+CAST(@NumberOffExistingFiles as varchar(5))
PRINT ‘@FilesizeInMB=’+@FilesizeInMB
PRINT ‘@FileGrowthInMB=’+@FileGrowthInMB
PRINT ‘@Debug=’+CAST(@Debug as char(1))
PRINT ‘@Execute=’+Cast(@Execute as char(1))
PRINT ‘@AlwaysCleanUp=’+CAST(@AlwaysCleanUp as char(1))
PRINT ‘———————————————————-‘
PRINT ‘Part 1 Resetting Tempdb to default settings on ‘+@TempDBLocation
PRINT ‘———————————————————-‘
IF (@AlwaysCleanUp=1 or @NumberOffExistingFiles<>@NumberOffFiles)
BEGIN
WHILE @NumberOffExistingFiles > 1
BEGIN
BEGIN TRY
SELECT @Fileid=MAX(fileid) from sys.sysfiles where groupid=1 and fileid < @Fileid
SELECT @TempDBSecFile=Name from sys.sysfiles where fileid=@Fileid
SET @CMD=’ALTER DATABASE [tempdb] REMOVE FILE [‘+@TempDBSecFile+’]’
IF @Execute=1 exec sp_executesql @CMD
IF @Debug=1 PRINT @CMD
END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH
BEGIN TRY
SELECT @TempDBFileName=[filename] from sys.sysfiles where fileid=@Fileid
SET @CMD=’DEL ‘+@TempDBFileName+’ /Q’
IF @Execute=1 exec xp_cmdshell @CMD
IF @Debug=1 PRINT ‘EXEC xp_cmdshell ‘+@CMD
END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH
set @NumberOffExistingFiles=@NumberOffExistingFiles-1
END
PRINT ‘———————————————————-‘
PRINT ‘Finished Part 2 removing residual files on ‘+@TempDBLocation
PRINT ‘———————————————————-‘
BEGIN TRY
SET @CMD = ‘USE [tempdb]; DBCC SHRINKFILE (N”’+@TempDBPrimFile+”’ , ‘+CAST(@FilesizeInMB*1024 as varchar(100))+’)’
IF @Execute=1 exec sp_executesql @CMD
IF @Debug=1 PRINT @CMD
END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH
PRINT ‘———————————————————-‘
PRINT ‘Part 3 Recreating files with correct filesize on ‘+@TempDBLocation
PRINT ‘———————————————————-‘
WHILE @NumberOffFiles > 1
BEGIN
BEGIN TRY
SET @CMD=’USE [master]; ALTER DATABASE [tempdb] ADD FILE ( NAME = N”tempdev0’+CAST(@NumberOffFiles as char(1))+”’, FILENAME = N”’+@TempDBLocation+’tempdev0’+CAST(@NumberOffFiles as char(1))+’.ndf” , SIZE = ‘+@FilesizeInMB+’MB , FILEGROWTH = ‘+@FileGrowthInMB+’MB )’
IF @Execute=1 exec sp_executesql @CMD
IF @Debug=1 PRINT @CMD
END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH
SET @NumberOffFiles=@NumberOffFiles-1
END
END
ELSE
PRINT ‘———————————————————-‘
PRINT ‘Had nothing to do’
Invalid:
PRINT ‘———————————————————-‘
PRINT ‘End of Script’
PRINT ‘———————————————————-‘