System Databases

Standard Configuration of tempdb (Video)

Jon Seigel

The tempdb system database is used for many purposes from materializing temporary tables to storing row version information for snapshot isolation. The default configuration of tempdb may not perform well for many production workloads. In this video, three important considerations to configure tempdb for optimal performance are discussed, including a more in-depth visualization about why creating more data files can be a big advantage.

Video Resources:

Read more about sys.dm_os_waiting_tasks
Read more about sys.dm_os_wait_stats
Read more about DBCC SQLPERF
sys.dm_os_waiting_tasks query to find PAGELATCH_* waits

An advanced option I didn’t mention in the video is to enable Trace Flag 1118 (note: this trace flag is undocumented), which changes the allocation behaviour in tempdb to not use mixed extents. You can read more about this setting here.

Moving the System Databases (Video)

Jon Seigel

Occasionally after installation, the system databases need to be moved from one location to another. In this video demo, I show you how to accomplish this task.

Key points:

  • Make sure the database engine service account has read/write access to the new file location(s).
  • The paths to the master database files are in the service startup parameters.
  • The paths to the other databases files are changed using ALTER DATABASE … MODIFY FILE.
  • The database files themselves can’t be copied until the database engine service is stopped.

Two things about security which I didn’t mention in the video:

  • When granting permissions on the new data container, the principle for the database engine service account will vary depending on your environment. In my case, it was a local group, but you may need to use a domain group, or a service SID. It should be obvious from the source data container.
  • You may want to grant the Everyone group permission to list the folder contents starting from the root, and remove the inherited permission on each data container. This will allow you to browse to the drive when attaching a database (for example) in Management Studio, yet limit access to only the account that should see the files in each instance folder.

 

What’s in the System Databases?

Jon Seigel

We know they’re important — they sit in that special folder and all — but what do they really do? Why are they special? Let’s take a closer look.
 

master

This database is the gatekeeper of SQL Server.

It contains all the system configuration settings and logins, in addition to other instance-wide objects such as linked servers.

Most importantly, though, it contains all the information needed to find all the other databases in the instance. The SQL Server service is started by default with several command-line parameters, but two of those are the file locations of the master database data file and log file. When SQL Server starts up, it reads the master database to find out where all the databases in the instance are, including all the system databases, and then goes through the recovery process on them.

On a production system, taking regular backups of master is essential.
 

model

As the name suggests, this database is the template from which all new databases are created.

What’s in this database? By default it’s empty, and you can put pretty much anything you want in there — new databases will have those objects in it by default. (Note: this isn’t necessarily a best-practice; I’m just illustrating how it works.) Typically, this database is left empty, and database settings are adjusted to suit the environment. The recovery model is the most common setting changed.

It’s recommended to take backups of this database, even though it’s relatively easy to replace.
 

msdb

This database stores everything having to do with SQL Agent, including all the jobs and their history, schedules, and alerts. It’s also used as a staging area for other features such as Database Mail.

The biggest thing to be aware of with msdb is the size of the job history. Depending on what you have running on your system, this can grow extremely quickly, and can in some cases be very difficult to clean up. Watch the size of this database! A default installation of SQL Server lands this database’s files on the C drive, which is not a drive you want to fill up unexpectedly.

In terms of criticality, this is the second-most important system database behind master, and regular backups are essential on a production system. As mentioned, it’s also good to monitor the size of this database, and keep tabs on what kind of job history is being tracked.

tempdb

This system database is special in that unlike any of the others, it’s recreated from scratch every time the instance starts. Instant data file initialization can improve SQL Server restart time immensely because tempdb gets recreated on start up. Less restart time means higher availability.

Not only is tempdb temporary itself, it’s also responsible for holding temporary objects that get created by user scripts and internal operations. This includes things like temporary tables and table variables, row version information for snapshot isolation, and extra space as needed to satisfy user queries that do big sorts or joins.

While you can’t take backups of tempdb, it’s important for it to be well-tuned to your workload. Tuning tempdb is a topic unto itself, so I won’t discuss that further here.

distribution

This database is used by SQL Server Replication to store information about transactions that have occurred which need to be sent out to subscribers. If Replication is not configured, this database won’t exist in the list. Note also that the name of this database can vary, as it’s user-entered. You can run the following query to find distribution databases in your instance:

SELECT
	name
	FROM sys.databases
	WHERE is_distributor = 1

Resource (mssqlsystemresource)

This is SQL Server’s internal system database, and is hidden from direct viewing. It contains internal objects such as system catalog views and stored procedures that appear in the sys schema in every database.

This database can’t be backed up through SQL Server, and should not be modified.

Although this post certainly isn’t a comprehensive list, hopefully I’ve given you a basic understanding of what’s in the system databases. If you want to learn more, you can read the TechNet articles that go into more detail.