Give tempdb a chance

If you’re a DBA, you probably secretly wish that you could have one of those cool red and black portrait graphics like those folks at Brent Ozar Unlimited get. But that’s not important right now. You probably also enjoy the privilege of performing the installation of every new SQL Server instance. And if you don’t, well, you can still keep reading, I guess.

Hey, I’m new at this SQL blogging stuff. I’m still working out the kinks.

Anyhow, SQL Server installation leave us with all kinds of defaults that are, well, let’s say sub-optimal. For example, you probably want the ability to remotely utilize the Dedicated Admin Connection, but the default says no can do. You also probably don’t want to be storing query plans for queries only executed once, but by default you most certainly will. And I’m 99% certain you don’t want SQL Server to be able to claim up to 2147483647 MB of available memory, because eventually SQL Server would be writing checks your hardware can’t cash.

(Side note: Admittedly, I thought of typing “cache” instead of “cash”, but I didn’t think it was all that funny. And since this is a new blog I’m pretty much an audience of one, so I gave the pun the thumbs down.)

Back to the subject. . .One default setting I’m always eager to modify is the configuration of the tempdb database files. The initial size of your data file is 8 MB and a single log file of 1 MB, which might have been fine in 1997, but here in the 21st century this is like having desk with 8 square inches of workspace. Compounding this is that the default growth rate is 10%, which means it has to grow 51 times to get to 1 GB. The log file, also with a 10% default growth rate, will have to grow 73 times to get to 1 GB. And growing takes time (especially if you don’t have Instant file Initialization permission configured for your service account).

So unless you’re rolling with “Northwind” or “pubs”, you’re eventually going to find yourself with tempdb files larger than 1 GB. Oh, and it gets worse, because when you restart the SQL Server service you go right back to the 8 MB data and 1 MB log file sizes.

And it gets REALLY worse because you’ve only got one data file, which means you’re going to have resource contention when it comes time to allocate pages in tempdb. Paul Randal has provided a solid explanation. “Bob Dylan” has provided a more entertaining one.

Yes, yes, I know starting in SQL Server 2016 there’s an option to create multiple tempdb files at installation. Huzzah. But I still encounter this configuration problem for two reasons. First, my employer likes to engage in the practice of buying software solutions from other companies, and many of those other companies don’t yet support databases on SQL Server 2016. This is my sad, frowny face.

Second, I have the wonderful experience of working on a team of DBAs, and I’m not being sarcastic. But sometimes – just sometimes – I find that one of my teammates maybe have just clicked “Next” right on through an installation and I eventually discover a tempdb on a 2016 instance that has the classic default configuration. And now this is my really sad, frowny face.

So, in the 10th paragraph, I offer a little SQL sunshine I have scripted and used with much delight. You could probably write this yourself, but that would be inefficient since it’s already written, although I have no doubts someone could improve this since I am not the master of everything.

One final note: if you are using this on a version before SQL Server 2016 then I highly recommend you also turn on Trace Flag 1117, which will ensure that, drive space allowing, your tempdb files all grow synchronously. Which is something you want, since SQL Server will automatically use the file with the most free space. If you have one file with much more free space than the others, well, you’re only going to be using one data file until that file is utilized to a proportionate level of free space as other data files.

And when it comes to using just one data file in tempdb. . .I’m feeling a little déjà vu here.

(NOTE: For some reason the color scheme goes a bit odd near the end of this script due to punctuation. That’s a problem with a plugin, not the SQL.)

/*
This script will create the recommended number of tempdb data files if they do not exist, and resize all files to 1024 MB with 1024 MB growth increments.
*/

SET NOCOUNT ON

DECLARE
 @CPUCount tinyint
 , @TempFileTotal tinyint
 , @FilePath varchar(200)
 , @TempFileCounter tinyint
 , @SQL nvarchar(1000)
SELECT @CPUCount = cpu_count
FROM sys.dm_os_sys_info
IF @CPUCount > 8
 SET @CPUCount = 8
SET @TempFileTotal = @CPUCount + 1 -- all data files + 1 log file

DECLARE @TempFiles table (
 FileId tinyint
 ,Name varchar(20)
 ,FileName varchar(200)
 ,FileResized bit
 )
INSERT @TempFiles (FileId, Name, FileName, FileResized)
SELECT FileId, Name, FileName, 0
FROM tempdb.dbo.sysfiles

SELECT @FilePath = LEFT(filename,
 (CHARINDEX(REVERSE(LEFT(REVERSE(filename),
 CHARINDEX('\', REVERSE(filename)) -1)),
 filename)-1))
FROM @TempFiles
WHERE fileid = 1

SET @TempFileCounter = 1

WHILE @TempFileCounter <= @TempFileTotal BEGIN
 IF EXISTS (select * from @TempFiles where FileId = @TempFileCounter) BEGIN
  SELECT @SQL = 'ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'''+ [name] + ''', SIZE = 1048576KB, FILEGROWTH = 1048576KB)'
  FROM @TempFiles where FileId = @TempFileCounter
 
  EXEC sp_executesql @SQL
  END
 ELSE BEGIN 
  SELECT @SQL = 'ALTER DATABASE [tempdb] ADD FILE ( NAME = N''temp' + convert(varchar(2),(@TempFileCounter-1)) + ''', FILENAME=''' + @FilePath + 'tempdb' + convert(varchar(2),(@TempFileCounter-1)) + '.ndf'', SIZE = 1048576KB , FILEGROWTH = 1048576KB)'
  
  EXEC sp_executesql @SQL
  END
 SET @TempFileCounter = @TempFileCounter + 1
 END

2 thoughts on “Give tempdb a chance

Leave a Reply

Your email address will not be published. Required fields are marked *