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.