SQL Server Master Database Tables

By: Eric Blinn

Overview

In this section we will cover questions like: what tables are in the master database, can I see the code, can I add my own tables.

What are the key tables in the master database that are important for SQL Server Professionals to know about?

This is a bit of a trick question as there are no tables in master that a SQL Server professional should know about.  There are, however, views in the master database worth learning about – but those are technically stored in mssqlsystemresource and presented in the schema of master and again in each user database.

Although an entire book could probably be dedicated to these dynamic management views (DMVs), here are a few that are commonly queried.

This view has a series of performance counters like those one would read from perfmon on a Windows system. Some are system wide and others pertain only to a single database.  This can be a very convenient alternative to opening perfmon and is often used as a basis for performance monitoring tools.

SELECT * FROM sys.dm_os_performance_counters

This view contains query and query plan information for queries that have executed on the current system.  It shows query stats such as reads, writes, CPU, memory grants, and execution times.

SELECT * FROM sys.dm_exec_query_stats

This next view shows one row for every session open on the SQL Server instance with information about the user, host machine, and login time.

SELECT * FROM sys.dm_exec_sessions

This view contains one row for each database (user and system) data and log file and includes the logical and physical names along with size information.

SELECT * FROM sys.master_files

These next views have index usage information.  The first view includes one row for every rowstore (clustered and non-clustered) index in the system along with information about how often they are read and/or updated.  The second set of views combine to show indexes the optimizer looked for but could not find.  In most cases a table scan was done when these target indexes could not be found.

SELECT * FROM sys.dm_db_index_usage_stats

SELECT * 
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle

Can I see the code for the system objects in the SQL Server master database and if so, how?

These views are based on CLR and cannot be viewed.

Can I change the objects in the SQL Server master database?

These views are all read-only and cannot be changed.

Can I store my own objects in the SQL Server master database?

SQL Server will allow objects to be added to the master database, but that should rarely happen.  It would be ok to add a stored procedure like sp_whoisactive to the master database.  Other objects should be placed in a user database.

It is common to see DBAs add objects like those to support database maintenance to the master database.  It would be a better practice to create a new user database to house those objects.  This is especially true if there is a log table among the objects.