SQL 2014 Always-on AG Pt. 7: TempDB

2014-09-22_17-13-13 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. If the number of vCPUs is less than 8, then configure an equal number of TempDB files as you have assigned vCPUs. If the number of vCPUs exceeds 8, then start with 8 TempDB files and look for contention for in-memory allocation (PAGELATCH_XX). Scale up the number of TempDB files in increments of four until contention is eliminated.

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. A general TempDB sizing rule of thumb is 1% to 10% of your total database size. Your transaction log file size needs to be of sufficient size to hold all the transactions between backups. This is probably daily, unless you are running a large database with more frequent backups.

The script also 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

SQL 2014 Always-on AG Pt. 1: Introduction
SQL 2014 Always-on AG Pt. 2: VM Deployment
SQL 2014 Always-on AG Pt. 3: Service Accounts
SQL 2014 Always-on AG Pt. 4: Node A Install
SQL 2014 Always-On AG Pt. 5: Unattended Node B
SQL 2014 Always-on AG Pt. 6: Cluster Configuration
SQL 2014 Always-on AG Pt. 7: TempDB
SQL 2014 Always-on AG Pt. 8: Max Mem & Email
SQL 2014 Always-on AG Pt. 9: SQL Maintenance
SQL 2014 Always-On AG Pt. 10: AAG Setup
SQL 2014 Always-On AG Pt. 11: File Share Witness

Configure TempDB and More

1. Copy the script below to your first SQL server. 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. Be sure to change the file paths to match your system. The script will fail if they are incorrect.

2014-09-22_17-19-05

 

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

/* Configure TempDB */

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

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

ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev4,
FILENAME = 'J:\TempDB\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

2. Repeat the configuration process on the other SQL node.

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. Coming up in Part 8 we will be configuring memory and email alerting. Then yes finally configuring AlwaysOn Availability Groups.

Print Friendly, PDF & Email

Related Posts

Subscribe
Notify of
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Arndt
April 21, 2017 1:52 am

Hello,

Thank you for the information to install the AllwaysOn Cluster.

One thing to mention: a second or third SQL datafile should have the extention .ndf
This ist the document from microsoft: https://docs.microsoft.com/en-us/sql/relational-d

Kind regards