MSSQL database background process (thread)

Similar to the Oracle database, the Microsoft database product MSSQL also has many background processes to ensure the efficient and normal operation of the database system, because MSSQL is the threading model, should be called the background thread, for everyone to understand, we call it a background process, the following we will briefly look at:

1. Data Write related process

1) Lazy Writer: This process is used to ensure that there is sufficient free memory in the system buffer pool, which periodically scans the buffered memory pages to discover infrequently used memory pages and moves them out of the buffer, during which the dirty page data is written out to disk and the buffer is removed.

2) Eager Writer: Similar to lazy writer, the process is also responsible for writing out the dirty pages in the buffer to disk, but the memory pages it writes out are mainly related to non-log (non-logged, for example: bulk insert,select into) operations, during That allows read-write new pages to occur in parallel.

3) Checkpoint: The process periodically scans the buffer to discover dirty pages for a particular database and writes out the dirty pages to disk by creating a point in time that confirms that all dirty pages are written out to disk before this can reduce the time required for the most recent database recovery. The user can request a checkpoint by submitting the checkpoint command, or the system can automatically generate a checkpoint based on the consumed log space or time, in addition, when certain events in the system cause checkpoints to occur, such as: adding or removing data files or log files, closing instances, and so on. When a checkpoint occurs, the process writes out the dirty pages in the buffer to disk, and writes out to disk regardless of whether the related transaction has been committed.

It is worth mentioning that the three background processes are asynchronous, that is, they can do the IO operation while doing other work, and later to check the completion of the previous IO.

2. Transaction log related processes

1) Log Writer: This process is responsible for flashing the transaction log from the buffer to the disk log file. Prior to the MSSQL2016 version, each instance had only one log writer process, so the process was responsible for all the database log buffer in the instance. The process completes the write of log buffer to disk through asynchronous IO, when the user submits a transaction, the process blocks the user session until the relevant database log buffer data is brushed out to disk completion, and the process can continue the work of the other database log buffer. Many events in the MSSQL system trigger the log writer process to write out the contents of the log buffer to the disk, for example: The session commits the current transaction, the log buffer is full, checkpoint, and so on.

In addition, when the process writes Lredo records from log cache/buffer to disk log files, the write-up unit is changeable, ranging from 512~64k, which, unlike data buffer writing process, must be the data page size.

2) Backup log: Although this process is strictly not a background process, because it is used to complete the "Backup log ..." command issued by the user, but it is closely related to the transaction log.

3. System monitoring and management related processes

1) Signal handler: This process is primarily responsible for the startup and shutdown of MSSQL instances.

2) Task Manager: The process also participates in the startup process of the MSSQL instance, which is used primarily to launch all databases related to the instance. In addition, the process is responsible for initiating certain tasks within MSSQL, as well as monitoring instance service processes and startup times.

3) Resource Monitor: This process is primarily responsible for monitoring the use and status of the memory plane and, if necessary, adjusting the MSSQL-related buffers and automatically enters an idle state when no user requirements are detected.

4) Lock Monitor: This process is responsible for monitoring scenarios where the blocking time exceeds the system-related thresholds and is responsible for resolving the deadlock problem.

5) Ghost cleanup: The process wakes up periodically and checks for index entries in all indexes that have been marked for deletion, and then physically removes the index entries.

6) Trace Queue task: The process is responsible for monitoring trace files and rowset providers. For a trace file, the process brushes the data out to the disk file every 4 seconds, and for the rowset, it will close the provider for more than 10 minutes without receiving any events.

4, Job scheduling related processes

SQL Server Agent: Strictly speaking, this is not a background process, but a Windows service, which is responsible for the scheduling and execution of various jobs.

5. Other related processes

1) XE Timer and XE Dispatcher: These are the extended events (Extended event) related processes that are responsible for periodically sending the collected data from the buffer to the destination asynchronously.

2) Brkr event HNDLR and Brkr TASK: These events are Service Broker-related processes. The former is primarily responsible for handling all startup and shutdown events of the service agent, which is one of the many processes that perform internal service proxy tasks.

We can use the following SQL to query the MSSQL instance background process related information:

Select T.os_thread_id,r.session_id,r.status,r.command
From Sys.dm_os_threads t inner join Sys.dm_os_workers W on t.thread_address=w.thread_address
INNER JOIN sys.dm_exec_requests R on W.task_address=r.task_address
where r.status like '%background% ';

MSSQL database background process (thread)