Overview
In this section, we will cover questions like what tables are in the master database, can I see the code, and can I add my own tables.
Explanation
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 the 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_countersThis 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_statsThis 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_sessionsThis 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_filesThese 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 combines to show the 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_handleCan 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.
Eric has been a SQL Server DBA and Architect in the legal, software, transportation, and insurance industries for over 10 years. Currently he is the Sr Data Architect for Squire Patton Boggs, a leading provider of legal services with 47 offices in 20 countries.
Eric is a 2018-2019 Idera Ace and has co-authored 2 Idera Whitepapers.
He has been a presenter at PASS Summit, IT/DevConnections, SQLSaturdays, the in.sight transportation conference, and the Ohio North SQL Server User’s Group.
- MSSQLTips Awards: Author of the Year Contender – 2021, 2022 | Trendsetter (25+ tips) – 2021
