SQL 2012 Failover Cluster Pt. 9: TempDB

One of the most important aspects of SQL performance is TempDB. Applications can use TempDB as a scratch space, and in most cases you should not rely on just a single TempDB file. Current best practices is to create 1/4-1/2 the number of TempDB files that you have processor cores. You don’t really want to go above 8 files, in most cases.

Another important aspect of TempDB is their size and file growth. SQL will proportionally use the TempDB databases based on their size. So it’s important to create all TempDBs with the same size and not allow for autogrow. Size them properly up front to accommodate your application requirements. If you do need to grow them in the future, grow all of them equally. All TempDBs will use a single “centralized” log file, so you won’t see multiple log files.

Just to conserve disk space the script below creates four TempDB files at 5MB each. Increase all TempDB sizes equally for your environment. I’d probably go with 1-5GB per file, if the SQL server will be under light usage and only supporting an application like vCenter.

The script renames the SA account, as a security protection measure. This is optional, but recommended. It also enables DAC, which can be used for remote administration during dire SQL server problems to possibly repair the situation and get SQL server back into production.

In addition to TempDB, the script makes other minor changes to system database sizes and auto-growth. I won’t bore you with all the details, but please consult your local DBA for system DB settings specific to your environment. In the absence of a DBA the settings below are a reasonable start for a small environment.

Blog Series

Configure TempDB and More

1. To run the script open SQL Studio, create a new query (click “New Query”) then paste in the code below. Or save the script to a .sql file and double click on it. Execute the script and it should run successfully.

/* Configures TempDB and other settings */
/* Derek Seaman, derekseaman.com */

/* Configure TempDB */

USE master;
GO
ALTER DATABASE tempdb
ADD FILE
(
 NAME = tempdev2,
 FILENAME = 'F:\TempDBData\tempdb2.mdf',
 SIZE = 5MB,
 FILEGROWTH = 0
);

ALTER DATABASE tempdb
ADD FILE
(
 NAME = tempdev3,
 FILENAME = 'F:\TempDBData\tempdb3.mdf',
 SIZE = 5MB,
 FILEGROWTH = 0
);

ALTER DATABASE tempdb
ADD FILE
(
 NAME = tempdev4,
 FILENAME = 'F:\TempDBData\tempdb4.mdf',
 SIZE = 5MB,
 FILEGROWTH = 0
);

ALTER DATABASE tempdb
MODIFY FILE
(
 NAME = tempdev,
 SIZE = 5MB,
 FILEGROWTH = 0
);

ALTER DATABASE tempdb
MODIFY FILE
(
 NAME = templog,
 SIZE = 2048MB,
 FILEGROWTH = 256MB
);

/* Configure other system databases */

GO
ALTER DATABASE model
MODIFY FILE
(
 NAME = modeldev,
 SIZE = 10MB,
 FILEGROWTH = 10MB
);

ALTER DATABASE model
MODIFY FILE
(
 NAME = modellog,
 SIZE = 10MB,
 FILEGROWTH = 10MB
);
GO

ALTER DATABASE master
MODIFY FILE
(
 NAME = master,
 SIZE = 10MB,
 FILEGROWTH = 10MB
);

ALTER DATABASE master
MODIFY FILE
(
 NAME = mastlog,
 SIZE = 10MB,
 FILEGROWTH = 10MB
);
GO

ALTER DATABASE msdb
MODIFY FILE
(
 NAME = msdbdata,
 SIZE = 25MB,
 FILEGROWTH = 10MB
);

ALTER DATABASE msdb
MODIFY FILE
(
 NAME = msdblog,
 SIZE = 25MB,
 FILEGROWTH = 10MB
);
GO
/* Allow advanced configuration options */

USE master;
GO
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

/* Rename SA Account */

ALTER LOGIN sa WITH NAME = [Matrix];

/* Enable DAC */

EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

Summary

TempDB may be often overlooked by non-DBA people installing SQL server. Even with VMs using just 1-2 cores I would still configure a few TempDB files. Why? If the SQL server grows and you add more DBs, vCPUs, etc. you probably won’t remember to add more TempDB files.

What’s next? Configuring SQL RAM and setting up email notifications in Part 10.