A High Level Comparison Between Oracle and SQL Server – Part II
Since SQL Server is a Windows application, not surprisingly the rules of VAS also applies to it. When SQL Starts, its VAS sees the database engine and the buffer pool residing inside it. The buffer pool is SQL Server’s data cache: it is composed of 8 KB buffers and holds the data pages from data files. This is the equivalent of Oracle’s buffer cache. SQL Server database engine in the VAS comprises of the SQLSERVR.EXE application, various DLL libraries, thread structures etc.
The buffer pool size is limited by two components: the physical RAM available to the machine and the VAS available to SQL. If the buffer pool grew and grew and ultimately consumed all the VAS, SQL would fail with unexpected and critical errors. To protect against it, SQL Server ensures the buffer pool leaves some VAS memory as “reserved”. Also, SQLOS memory manager ensures the buffer pool is available for other SQL internal requirements. Pages from the buffer pool can be assigned for caching connection data, SQL optimizer and most importantly, for the procedure cache. Since the pages assigned to these components are unavailable for storing data, these are known as stolen pages.
The schematic diagram showing the components of SQL Server VAS is shown below.
We mentioned the resource monitor as one of the components of SQLOS. One of the things resource monitor does is checking and listening for operating system notifications of low memory conditions. If the resource monitor detects any low memory situation, it logs a message in a structure called the ring buffer and broadcasts this message to the SQL engine so that all SQL related components can reduce their memory usage. The memory manager component of SQLOS also monitors the virtual and physical memory available and responds to memory pressures through a notification mechanism known as memory clerks.
When it comes to executing tasks (e.g. query compilation, execution and so on), SQLOS makes use of the scheduler mechanism. A scheduler is an abstraction of a single CPU in SQL Server. To give an example, a SQL Server machine with two quad-core CPUs will see eight schedulers available. Each scheduler will have a number of worker threads associated with it. It is these threads that individual components of a task in SQL Server are assigned to. Example of a task can be a query running against the database. This task can be divided into sub-tasks like parsing, compiling, generation of execution plan etc. Each of these sub-tasks will be carried out by individual threads.
When SQL Server starts, a finite number of threads are created. The total number of threads created will depend on the number of CPUs available to SQL Server and their architecture (x86 or x64). For an x86 based machine with 4 CPUs or less, the number of threads created will be 256. For every additional CPU over 4, extra 8 threads will be created. For an x64 machine with 4 CPUs or less, the initial number of threads will be 512. Each additional CPU above 4 will see an extra 16 threads being created. So the more CPU power the machine has, the more threads are available for SQL.
As users connect to a SQL Server system and submits tasks, each task is assigned to a scheduler. Before SQL 2005, the tasks were assigned on a round robin fashion. From SQL 2005, SQLOS takes care of this assignment: tasks are assigned to schedulers that are least busy.
Like Oracle, it is also possible to see some of the background processes running in SQL Server. If you execute the sp_who2 command against a database instance, you will see a number of background processes:
Of these, the CHECKPOINT process is fairly self-explanatory. The LOG WRITER process is equivalent to the Oracle LGWR process: it is responsible for writing database changes to the transaction logs. The LAZY WRITER process is the equivalent of the Oracle DBWn process: its job is to write the modified data pages from the buffer pool to the data files. One thing to note is that unlike Oracle, there is only one lazy writer process writing to the data files. Other examples of background processes are those that involve SQLOS: the scheduler monitor, the deadlock monitor, the resource monitor etc.
Transactional Consistency and Point-in-time Recovery
Both Microsoft SQL Server and Oracle has built-in mechanisms for protecting user transactions. The idea behind transactional consistency is that as changes are made to the data, the data blocks are not directly updated in the disk files. They are actually updated in the server’s memory in a place called the buffer cache. There is another place in memory called the log buffer that is used for continuously recording the changes applied to the data. Contents of this memory area are written to separate disk files frequently. There are two distinct background processes that write the contents of the buffer cache and the log cache to disk files; however the log buffer is flushed to disk far more frequently than the buffer cache.
Now, if a user successfully commits a transaction, the changes may not be written to the data files on the disk immediately. However, there will be a record of the change in the log buffer and the contents of the log buffer would be hardened to a special kind of file before the commit successful signal is sent to the user.
As we have seen before, SQL Server calls this log file the Transaction Log. Oracle calls it the Redo Log. In SQL Server terms, the area in memory that holds the changes applied to the data is known as the Log Buffer, Oracle calls it the Redo Buffer. Despite the naming differences, the functionality of the log files remains the same: if the server crashes unexpectedly, the database server will examine the contents of this file when it restarts. If it finds there are committed transactions present in the log file but not in the data files, it will apply those changes to the data files so that they would reflect the committed changes. If there are records in the log file that shows a transaction being incomplete or rolled back, it will roll back those changes in the data files as well. The first process is called the redo phase and the second is known as undo.
SQL Server maintains one transaction log for every database it hosts. The database transaction log can have one or more transaction log files assigned to it. The transaction log files are created during database creation time and more files can be added later. For Oracle, the concept of database encompasses all the physical data files and the logical tablespaces it hosts. Oracle’s redo log is created as a set of files that capture the changes made to all the tablespaces. An Oracle database will need to have at least two redo log files for its operation. There can be more than two redo log files, but two is the minimum.
One notable difference between SQL Server transaction logs and the Oracle redo logs is that transaction log files are not grouped into any logical entities. Redo log files on the other hand are assigned to two or more redo log groups. Every Oracle database will have at least two redo log groups and each group will have one or more log files assigned to it. The log files in each group are known as the group’s members.
Oracle writes the redo entries from its log buffer into one redo group at a time. As the redo entries are written to a redo log group, each member of the group is updated at the same time. Having more than one redo log file in a group is used for fault tolerance and it is called multiplexing. Once a log group fills up with redo records, Oracle will start writing into the next redo group. This is called log switching. Once that group is filled up, the writes will proceed to the next group and so on. Once all the redo log groups are filled up (whether there are two or more of these), Oracle will wipe off the first redo log group in the chain and start writing there again.
A SQL Server database’s transaction log is
also written to in a sequential manner. However the log is not cleared
automatically unless the database is running in simple recovery mode or the
transaction log is backed up. If the logical transaction log for a database
becomes full and the underlying log file(s) are unable to grow, the database
becomes unavailable for any user activity. However if a transaction log backup
is taken, SQL Server allows the backed up entries in the log to be overwritten
with new transactions. In this sense, SQL’s logical transaction log works in a
“wrap-around” fashion. Another point to note is that a SQL Server transaction
log file can be configured to grow automatically when more space is required.
Oracle’s redo log files are created with a pre-defined size and they do not
grow beyond that size unless you manually change it.