By: Ben Snaidero | Comments (1) | Related: > SQL Server and Oracle Comparison
Problem
As I mentioned in a previous tip, that compared datatypes between SQL Server and Oracle, given that I've worked with both technologies I constantly find myself thinking in terms of one and comparing it to something in the other. This tip will give an overview of the different background processes that make up both technologies and will highlight the similarities and differences between them.
Solution
Although almost all of what is outlined below applies across all versions of each technology, for the purposes of this comparison we are going to focus on SQL Server 2008 R2 and Oracle 10g Release 2. An example of an exception to this would be the processes that handle extended events would not apply to SQL Server 2000 as this functionality was not available in that release.
It's safe to say that because these are two different technologies the architecture could be quite different between them. That said, since it's a database and were dealing with data they both must read/write data and also log these transactions so let's first look at the group of processes that perform these operations.
Data Writer processes in Oracle vs. SQL Server
In SQL Server there are 3 processes that handle writing data to disk, the LAZY WRITER, EAGER WRITER, and the CHECKPOINT process. All three of these processes are asynchronous. The LAZY WRITER's main job is to ensure there are free pages in the buffer pool. It will scan the buffer pool looking for infrequently used pages and remove them from the cache. If a page is dirty (contains modified data) it will first flush the changes to disk before removing it from the cache. The EAGER WRITER performs basically the same function however it only writes dirty data pages to disk that were associated with non-logged operations (i.e. bulk inserts). The CHECKPOINT processes main responsibility is to keep the time to recover low. It does this by periodically scanning the buffer pool looking for dirty pages that need to be flushed to disk. One important thing to note with the checkpoint process is that pages are written to disk whether they are committed or not. If the change is rolled back a subsequent checkpoint will move this change to disk.
There is only one Oracle process than handles writing data to disk and that is the DATABASE WRITER. This processes sole purpose is to write modified buffers to disk. On systems that do a large amount of data modification you can configure multiple database writer processes to handle the extra load.
Although it does not write modified data buffers to disk Oracle also has a CHECKPOINT process. It's main function is to update the datafile headers with the details of each checkpoint so that the buffer pool and disk are kept in sync in case recovery is needed.
Logging transactions processes in SQL Server vs. Oracle
The LOG WRITER is the process within SQL Server that is responsible for flushing all log records to disk. The physical transaction log file(s) are internally broken up into virtual log files which are written to in a circular fashion. That is, if we've reached the end of the physical file and the virtual logs at the start of the file are no longer needed, i.e. they've been backed up if we are in full recovery mode or the old transactions are complete if we are in simple mode, then the log writer wraps around and starts reusing the virtual logs at the start of the file. In order to backup the transaction log in SQL Server we need to run the TSQL "BACKUP LOG" command. Although this is not really a background process as it just runs under a regular user connection, I thought it was important to mention as we will see below how this is handled in Oracle.
In Oracle there is a similar LOG WRITER process which is responsible for redo log buffer management. This process writes all the data in the redo log buffer to the redo logs on disk since the last time it wrote. Similar to SQL Server this buffer and redo log files are circular and old entries are overwritten when they are no longer needed. Note: Before they can be overwritten these redo logs need to be backed up. The process responsible for backing up the redo logs is the ARCHIVER process. When automatic archiving is enabled this process wakes up after any log switch and copies the redo log to another storage device. The other small difference between Oracle and SQL Server is that the Oracle LOG WRITER process writes synchronously to the mirrored redo log files, in SQL Server the LOG WRITER is only writing to a single transaction log file.
Oracle and SQL Server System related processes
The next logical group of processes that we are going to look at are the tasks that handle the monitoring/management of the overall system. The following processes handle things like system startup and shutdown, memory management, user process management, etc...
Let's first take a look at SQL Server and the tasks involved in startup/shutdown activities. The process responsible for the instance tasks around this is the SIGNAL HANDLER process. This is really it's only function and after an instance startup is complete it simply waits for a shutdown signal. Also involved in the startup is the TASK MANAGER background process which starts all the databases that are part of the SQL Server instance. In addition to this responsibility it also handles the startup of different internal SQL Server tasks as well as monitoring things like the server process id of the SQL instance as well as uptime. There are a few more SQL Server background process that monitor other aspects of the instance. The RESOURCE MONITOR process main function is to monitor the memory levels and make any adjustments to the cache as necessary, when this task detects there are no user-initiated requests being processed it enters and idle state. The LOCK MONITOR process monitors exactly what you would think. It is responsible for detecting any blocking scenarios that are longer than the 'blocked process threshold' value as well as detecting and resolving any deadlocks. Another process that I would put in this category is the GHOST CLEANUP process. While it doesn't really monitor server activity it's responsibility is to periodically wake up and checked for any index records (both clustered index data page and non-clustered index leaf page) that have been marked as deleted and it physically removes them. The last process that would fall into this category is the TRACE QUEUE TASK process. This process is running anytime there is an active transaction on the server. It also monitors the trace, file and rowset providers. For file providers it flushes them every 4 seconds and for rowset providers it closes any provider that has not received an event for 10 minutes.
While SQL Server used 6 processes to carry out the above activities Oracle has two main processes that perform these functions. First the SERVER MONITOR process handles most of the startup and shutdown activities, including any recovery steps required during startup. It's also responsible for cleaning up any temporary segments that are no longer needed as well as coalescing any free extents in tablespaces. The PROCESS MONITOR process does exactly what you would suspect in that in handles pretty much everything that has anything to do with a user process. This includes any recovery required when a user process fails. When this happens it cleans up the buffer cache as well as any resources the process was using (i.e.. locks, memory, etc...). The PROCESS MONITOR also has the extra responsibility of registering the database instance with the network listener process.
While I guess you could fit them into the category of system related processes I think it makes more sense to look at the processes involved in the area of job scheduling separately. These are the processes that are involved in running any scheduled tasks within our database systems.
Job scheduling processes in SQL Server and Oracle
When it comes to SQL Server it is not really a background process that handles this scheduling, it's the SQL Server Agent Windows Service. This service can execute a wide variety of administrative tasks through the jobs that are defined in the sysjob* tables and stored in the msdb database. Anything from simple SQL statements and stored procedure calls to SSIS packages to operating system and PowerShell commands can be run by using this service.
On the Oracle side it is a background processes that handle this scheduling. The JOB QUEUE processes handle the scheduling and execution of the tasks that are defined. First, it is the responsibility of the JOB QUEUE COORDINATOR process to select the jobs that need to be run. These jobs are stored in the system JOB$ table. Once found it dynamically spawns a JOB QUEUE SLAVE process to execute the job which is a set of PL/SQL statements or procedures. Using PL/SQL gives you the ability to run operating system commands just as we do using the SQL Server Agent.
Other background processes - SQL Server vs. Oracle
There are a few background processes that are part of SQL Server that really don't have a direct counterpart in Oracle. The first of these would be the Extended Event background processes, XE TIMER and XE DISPATCHER. These processes are responsible for XEvent processing which entails periodically collecting data from the buffers and sending it to each target asynchronously. Another one is the Service Broker related processes, BRKR EVENT HNDLR and BRKR TASK. The first is the main event handler thread which takes care of all the startup and shutdown events for Service Broker. The second is one of possibly several background processes that is used to execute the internal service broker tasks. This would include transmitting and receiving messages as well as other asynchronous network operations.
Within Oracle there is one other background process worth mentioning, the RECOVERER process. This process is responsible for the recovery of any failures in regards to distributed transactions. Once it can re-establish a connection to all the database servers involved in the distributed transaction it will remove any pending transaction table rows from each database involved in the in-doubt transaction.
Summary - SQL Server vs. Oracle Processes
The following table summarizes the background process mappings described above:
| SQL Server | Oracle |
|---|---|
| Data Writer Processes | |
|
LAZY WRITER |
DATABASE WRITER |
| Logging Transaction Processes | |
|
LOG WRITER |
LOG WRITER |
| System related processes | |
|
SIGNAL HANDLER |
SERVER MONITOR |
| Job Scheduling processes | |
|
SQL SERVER AGENT |
JOB QUEUE COORDINATOR |
| Other processes | |
|
XE TIMER |
N/A |
|
N/A |
RECOVERER |
Next Steps
- Read more details on:
- Other tips comparing SQL Server and Oracle:
About the author
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
