When working with SQL Server and Oracle, it’s important to understand the different background processes that make up these technologies. These processes handle various tasks such as writing data to disk, logging transactions, managing system resources, and scheduling jobs. In this article, we will explore the similarities and differences between these background processes in SQL Server and Oracle.

Data Writer Processes

In SQL Server, there are three processes responsible for writing data to disk: the LAZY WRITER, EAGER WRITER, and CHECKPOINT process. The LAZY WRITER ensures there are free pages in the buffer pool by removing infrequently used pages. The EAGER WRITER writes dirty data pages associated with non-logged operations. The CHECKPOINT process flushes dirty pages to disk to keep the time to recover low.

In Oracle, there is only one process, the DATABASE WRITER, that handles writing data to disk. It writes modified buffers to disk and can be configured to handle high data modification loads.

Logging Transaction Processes

In SQL Server, the LOG WRITER process flushes log records to disk. The transaction log files are circular, and old entries are overwritten when no longer needed. To backup the transaction log, the “BACKUP LOG” command is used.

In Oracle, the LOG WRITER process writes data in the redo log buffer to the redo logs on disk. The redo logs are also circular, and automatic archiving copies them to another storage device. The ARCHIVER process handles the backup of redo logs.

System Related Processes

In SQL Server, the SIGNAL HANDLER process handles instance startup and shutdown activities. The TASK MANAGER starts databases and internal SQL Server tasks. The RESOURCE MONITOR adjusts memory levels, the LOCK MONITOR detects blocking scenarios, the GHOST CLEANUP removes deleted index records, and the TRACE QUEUE TASK monitors active transactions.

In Oracle, the SERVER MONITOR handles startup, shutdown, and recovery steps. The PROCESS MONITOR manages user processes, cleans up failed processes, and registers the database instance with the network listener.

Job Scheduling Processes

In SQL Server, the SQL Server Agent Windows Service handles job scheduling. It can execute various administrative tasks defined in the sysjob* tables.

In Oracle, the JOB QUEUE processes handle job scheduling. The JOB QUEUE COORDINATOR selects jobs from the system JOB$ table and spawns JOB QUEUE SLAVE processes to execute them.

Other Processes

SQL Server has background processes like the Extended Event processes for XEvent processing and the Service Broker processes for handling startup and executing tasks. Oracle has the RECOVERER process for recovering distributed transaction failures.

Summary

In summary, SQL Server and Oracle have different background processes that handle various tasks. Understanding these processes is crucial for optimizing performance and troubleshooting issues in both technologies.