Skip to content

Category: System database

Visualization of database sessions 1, 2, 3, and latch waits connecting to a central TEMPDB node
SQL Server 0

Usage of Tempdb and Tempdb Contention

SQL Server has few system databases which was used to store the medata and information related to SQL server which will require to function the SQL server smoothly. Tempdb is one of the important System database.

What is Tempdb?

Tempdb is a system database. It is used to store the temporary objects and intermediate results which created at the time of query executions.

When SQL services stops tempdb gets cleared and when SQL services starts again tempdb always starts as a clean copy.

Best practices for Tempdb

  1. Always keep tempdb on fast and dedicated storage.
    Ex. On Azure IaaS server there is drive called as Temporary storage, it is fast dedicated. we can use that drive for tempdb.
  2. Use multiple same size dta files to reduce allocation contention.
    Ex. 1 file per CPU core upto 8 files.
  3. Pre-size data and log files to avoid frequent autogrowth. Also, keep autogrowth increaments resonable.
    Ex. 512 MB for data files and 256 MB for log files.
  4. All databases on the same instance share or use same Tempdb.

Basic but important points about Tempdb

  1. Tempdb database is minimally logged.
  2. we can not backup tempdb database.
  3. If tempdb gets full, queries starts failing or slowdown drastically.

Usage of Tempdb

Tempdb is used to store the user created temporary objects like local temp tables #temp and global temp tables ##temp, table-valued variables and temporary stored procedures.
It is used to perform operations like sorts, hashes, spools, worktables and intermediate result sets when an operation can not fit in a memory.
Data related row versions for features like Read-Committed Snapshot Isolation, Snapshot Isolation, online index rebuilds, MARS and some triggers.
Also, it acts as a temporary storage for certain metadata structures, cursors and some internal objects.

What if Tempdb is getting full?

  1. Check why tempdb is not able to grow.
    – Confirm that the tempdb files have autogrowth turned ON.
    – Check and confirm that disks are not full and files are not hitting their maxsize.
    If disks are full, add some space or pre-grow tempdb data and log files to safe size.

2. Identify the queries and sessions which are burning the tempdb space.

3. Take action on culprit.
– If you get long running or runaway query, reconsider killing it carefully.
– If maintainancr job or any other job is the cause, reschedule it outside of peak hours.

4. Once the cause is resolved shrink the tempdb files.

5. If there is emergency then restart the SQL services.

What is Tempdb Contention?

In SQL Server Tempdb contention occurs when multiple sessions and queries complete for access to shared allocation pages like PFS, GAM and SGAM causing latch waits and performance bottlenecks.

When you execute DMV like sys.dm_exec_requests you will see high waits on PAGEIOLATCH_*, PAGELATCH_* or LATCH_*. This occurs especially during heavy temp table or sort operations. Queries slow down under concurrency.

Below are the types of Contention

Allocation Contention

Sessions queue for PFS, GAM, SGAM pages during object creation in tempdb data files.

Metadata Contention

Overloaded access to tempdb system tables(Ex.sys.objects) from many temp table creations.

Solution to Tempdb Contention issue

Add multiple equally sized tempdb data files(1 per logical CPU upto 8, then in multiples of 4) to spread load via round robbin allocation.
For SQL server 2019 and above versions, enable memory optimized tempdb metadata to eliminate metadata issues.

Solution for Tempdb Contention issue
Solution for Tempdb Contention issue

Check all related blogs

By Tushar Kothawade Posted on

System database 0

Resource Database

Till now you are aware about the 4 main system databases. However, there is one more important, but hidden system database is exists. Yes, it is called as Resource Database or mssqlsystemesource database.

Today, we will explore this hidden database in SQL Server.

  • This is hidden and read only system database.
  • It does not contain user data or user metadata.
  • It contains all the system objects that are included with SQL Server.
  • SQL Server system objects like sys.objects are resides in this database, however, they logically appear in the sys schema of all the databases

Physical Properties of Resource Database

  • Below are the physical file names of the resource database
    • mssqlsystemresource.mdf
    • mssqlsystemresource.ldf
  • Below is the location of these files.
    <drive>;\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\
  • Each instance has only one associated mssqlsystemresource.mdf file.
  • Changing the location of resource database files is not at all recommended.
  • The Id of this Database is always 32767.
  • Other important values associated with this database are as below
    • Version Number
      SELECT SERVERPROPERTY('ResourceVersion');
      GO
    • Last time the database was updated
      SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
      GO

Backup and Restore of Resource Database

  • We can not backup this database using SQL server like other system or user databases.
  • If you want to backup, you can take the backup of the resource database files directly like file backup.

Please check below Microsoft article for further information.

Resource Database

Check all related articles

By Tushar Kothawade Posted on

SQL Server 2

MSDB Database-Usage, Features

MSDB Database is one of the important system database.

The MSDB database serves SQL server Agent by handling jobs. It also supports other features such as Service Broker and Database Mail. SQL Server automatically maintains the history of Backup and Restore within MSDB Tables. It includes information like the name of the user that performed the backup. It also includes the time of the backup. It details the device or path where the backup is stored.

Backup events for all databases are recorded even if they were created with custom application or third party tools.
Because of all these reasons Microsoft always recommends to keep the MSDB files in fault tolerant storage space.
By default, recovery model of this database is set to Simple.

Roles:

MSDB is the only system database that has pre-defined database roles besides the regular fixed roles.

MSDB Database Roles

Below are the database roles and their use.

Group NameDatabase Role NameRemarks
Database MailDatabaseMailUserRoleOnly members of this database role can send mails through SQL Server.
Integration Services Rolesdb_ssisadmin
db_ssisltduser
db_ssisoperator		Only members of this database role can work with SSIS packages.
Data Collectordc_operator
dc_admin
dc_proxy		Used to implement the Data Collector Security.
Policy-Based ManagementPolicyAdministratorRoleOnly members of this database role can manage a SQL Server instance Policies.
Server GroupServerGroupAdministratorRole
ServerGroupReaderRole		Only members of these database roles can administer and use registered server groups.
SQL Server Agent Fixed Database RolesSQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole		Only members of one of these database roles can use SQL Server Agent.
Multiserver EnvironmentTargetServersRoleUsed to work with a Multiserver Environment.
Server UtilityUtilityCMRReader
UtilityIMRWriter
UtilityIMRReader		Used to work with the Server Utility.
MSDB database roles with their use

Restrictions

We have many restrictions while working with this database. We cannot perform below activities on this database.

  1. We cannot drop MSDB database.
  2. We cannot drop the “Guest” user from this database.
  3. As it is one of the system databases, we cannot enable Change Data Capture (CDC) on this database.
  4. This database cannot participate in the database mirroring solution.
  5. We cannot remove the primary file group, primary data file or log file from the this database.
  6. Renaming of the database or primary file group is not possible for this database.
  7. MSDB database’s default collation is the MSSQL instance collation and cannot be changed.
  8. We cannot take MSDB database offline.
  9. We cannot change the primary file group to READ_ONLY.

Check all related articles

By Tushar Kothawade Posted on