Shulou(Shulou.com)05/31 Report--

This article shows you how to understand the MSSQL database background process, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Similar to Oracle database, Microsoft database product MSSQL also has many background processes to ensure the efficient and normal operation of the database system. Because MSSQL uses a thread model, it should be called background thread, so we will call it background process for the sake of ease of understanding. Let's take a brief look at it:

Similar to Oracle database, Microsoft database product MSSQL also has many background processes to ensure the efficient and normal operation of the database system. Since MSSQL uses a thread model, it should be called background thread, so we will call it background process for the sake of ease of understanding. Let's take a brief look at it:

1. Process related to data writing

1) Lazy writer: this process is used to ensure that there is enough free memory in the system buffer (buffer pool). The process periodically scans buffered memory pages to find infrequently used memory pages and moves them out of the buffer. During this period, dirty page data is written to disk and the buffer is removed.

2) Eager writer: similar to lazy writer, this process is also responsible for writing dirty pages in the buffer to disk, but the memory pages it writes are mainly related to non-non-logged (e.g. bulk insert,select into) operations, during which new pages are allowed to be read and written in parallel.

3) Checkpoint: this process periodically scans buffers to find dirty pages in a specific database and writes these dirty pages to disk. By creating a point in time before confirming that all dirty pages are written to disk, you can reduce the time required for the most recent database recovery. Users can request a checkpoint operation by submitting a checkpoint command, or the system can automatically generate a checkpoint operation according to the consumed log space or time. In addition, some events in the system will also lead to the occurrence of a checkpoint, such as adding or removing data files or log files, instance shutdown and so on. When a checkpoint occurs, the process writes out the dirty pages in the buffer to disk, regardless of whether the transaction has been committed or not.

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

2. Transaction log related processes

1) Log writer: this process is responsible for brushing the transaction log from the buffer to the disk log file. In pre-MSSQL2016 versions, there was only one log writer process per instance, so the process was responsible for all database log buffer in the instance. The process writes log buffer to disk through asynchronous IO. When a user commits a transaction, the process blocks the user session until the relevant database log buffer data is brushed out to disk. At the same time, the process can continue the work of other database log buffer. Many events in the MSSQL system will trigger the log writer process to write the contents of the log buffer to the disk, such as: 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 units can vary, ranging from 512 to 64k, which, unlike the data buffer write process, must be the minimum data page size.

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

3. The system monitors and manages related processes.

1) Signal handler: this process is mainly responsible for starting and shutting down MSSQL instances.

2) Task manager: this process will also participate in the startup process of the MSSQL instance, which is mainly used to start all databases related to the instance. In addition, the process is responsible for starting certain tasks within MSSQL, as well as monitoring instance service processes and startup times.

3) Resource monitor: this process is mainly responsible for monitoring memory usage and status, and adjusts MSSQL-related buffers when necessary, and automatically enters the 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 threshold, as well as resolving deadlocks.

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

6) Trace queue task: this process is responsible for monitoring trace file and rowset providers. For trace files, the process brushes the data out to disk files every 4 seconds; for rowsets, suppliers that do not receive any events are closed for more than 10 minutes.

4. Job scheduling related processes

SQLServer Agent: strictly speaking, this is not a background process, but a windows service, which is responsible for scheduling and executing various jobs.

5. Other related processes

1) XE Timer and XE Dispatcher: these are Extended Event-related processes that periodically send collected data asynchronously from the buffer to the destination.

2) BRKR EVENT HNDLR and BRKR TASK: these events are service agent (Service Broker) related processes. The former is mainly responsible for handling all startup and shutdown events of the service agent; the latter is one of many processes that perform internal service agent tasks.

We can query the information related to the backend process of MSSQL instance through the following SQL:

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%'

The above content is how to understand the MSSQL database background process, have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.