Jun 032008

SQL Server’s memory usage often leads to confusion, particularly when AWE memory is involved.

When the amount of memory available to SQL Server is under 3-4GB, SQL Server (or any other 32-bit application) can reside entirely in the Virtual Address Space (VAS), which is a virtual 32 bit address space allowing each process to use up to 4 GB of memory – 2 GB for user-mode and 2 GB for the system (kernel mode).  This virtual address space is then managed by Windows’ memory management to map it into real memory, and SQL Server can use up to 2 GB of memory. 

A 32 bit machine with 4 GB of RAM can use the /3gb switch in boot.ini to allow user mode (and thus SQL Server) VAS to use 3 GB, limiting the kernel mode to 1 GB.

If you look at Task Manager, you can see exactly how much memory SQL Server is using when SQL Server is entirely in VAS.

However, what happens when your machine has more than 3 GB of memory?  The answer is AWE, or Address Windowing Extensions.  AWE allows the operating system to map in a window of the extra memory, up to the limit of the operating system, which is 64 GB for Windows Server 2003, Datacentre Edition.  The SQL Server process will still require around 256 MB of memory to implement AWE, but the buffer and procedure cache can then be stored in AWE-mapped memory.  When looking in Task Manager, SQL Server will only show the 256 MB of memory, and the AWE-resident memory is hidden.  The best way to determine SQL Server’s AWE memory usage is via the SQLServer: Memory Manager – Total Server Memory (KB) Perfmon counter.

To turn on AWE, there are three steps:

  1. Add the /pae switch to boot.ini to allow Windows 2000/2003 to access more than 4 GB of memory
  2. Grant “Lock Pages in Memory” to the user account that runs the SQL Server process
  3. Set the configuration setting “AWE Enabled” to 1.

Note that AWE requires 2 GB of kernel VAS to address AWE memory above 16 GB, so using the /3gb switch in boot.ini will limit AWE to 16 GB.

Under Windows 2000, AWE will only be enabled if the available physical memory is greater than the user mode virtual address space.  When SQL Server starts, it will lock either the amount of memory specified in the configuration setting “Max Server Memory”, or the amount of free physical memory, minus 256 MB.

This AWE mapped pool cannot be swapped out of physical memory.  Also, the value of “Min Server Memory” is not used, as the AWE allocated by SQL Server is never decreased in Windows 2000.

Under Windows Server 2003, SQL Server will always try to use AWE if it is enabled, and SQL Server can dynamically manage its AWE memory usage.  Upon startup, it will allocate the amount of memory needed for the startup workload, and from there it will grow as needed, assuming memory is available.  It will continue growing up to the value specified in “Max Server Memory”.  If the server comes under memory pressure, SQL Server 2005 will release memory if possible, down to the point specified in “Min Server Memory”.

The 32-bit versions of Windows Server 2003 support the following maximum memory limits: Standard Edition 4 GB, Enterprise Edition 32 GB, and Datacentre Edition 64 GB.

It is always recommended to set a value for “Max Server Memory” to ensure that other processes running on the server have some memory available.  Leaving ~2 GB is a good setting, for under 32 GB total server memory, ~4 GB if 64 GB total server memory.  Additionally, when multiple instances of SQL Server are running on the one machine, “Max Server Memory” is necessary to prevent one instance from using too much memory and starving the other.

AWE is not necessary on a 64-bit machine, as the operating system’s memory limits are the limits (a 64 bit memory address can point to 16,777,216 GB of memory). However, it is recommended that the “Lock Pages in Memory” permission is granted to the user account that runs the SQL Server process, as this will prevent SQL Server’s memory from getting paged out to disk.

Jun 022008

Once you have a database mirror running, it is imperative to monitor the performance of the mirror, and in the case of high performance (asynchronous) mirrors, how far behind the mirror is compared to the principle.

With high safety (synchronous) mirroring, performance is critical, as all transactions must be committed on the mirror before they can be committed on the principle.  On a high performance mirror, a delay can mean the mirror isn’t as up to date as service levels require, and the transaction log can fill up as transactions cannot be backed up until they have been applied on the mirror.

There are different tools for monitoring mirroring:

  1. Database Mirroring Monitor (SQL Server Management Studio)
  2. Perfmon Counters
  3. Catalog Views

Database Mirroring Monitor

To launch the Database Mirroring Monitor, right click on the database in SSMS, select "Tasks", then "Launch Database Mirroring Monitor".   You then need to register your mirrored database by connecting to either the principle or the mirror, and selected the mirrored database.  You can then view the current statistics of the mirror, including previous snapshots of the mirror’s status.

Perfmon Counters

The primary drawback to the Database Mirroring Monitor is that you must be watching it to ensure there’s no problems.  The Perfmon Counters provided by the SQL Server: Database Mirroring object provide similar information that can be found in the Database Mirroring Monitor, but with all the benefits of Perfmon counters, such as registering alerts, and having a performance baseline.  The counters available are:

Name Description
Bytes Received/sec The number of bytes sent to the other server, per second
Bytes Sent/sec The number of bytes received from the other server, per second
Log Bytes Received/sec The number of bytes from the log received from the principle, per second
Log Bytes Sent/sec The number of bytes from the log sent to the mirror, per second
Log Send Queue KB The number of bytes in the log that has not been sent to the mirror
Pages Sent/sec The number of transaction log pages sent per second
Receives/sec The number of mirroring messages received per second
Redo Bytes/sec The number of bytes of log rolled forwards per second on the mirror
Redo Queue KB The number of bytes of transaction log that remains to be applied to the mirror to roll it forwards
Send/Receive Ack Time  
Sends/sec The number of mirroring messages sent per second
Transaction Delay The delay while waiting for the mirror to commit a transaction

For a high safety mirror, Transaction Delay is probably the most important counter, as it monitors how long transactions are taking to be committed on the mirror.  If this counter increases, then there is a performance problem somewhere in either the network or on the mirror.  Meanwhile, users on the principle will notice a decrease in performance when committing transactions.

For a high performance mirror, there are two perspectives that need to be viewed – the status of the principle, and the status of the mirror.  The main counter on the principle is the Log Send Queue KB, which shows how much log has not been sent over to the mirror.  However, this value could increase when a lot of log traffic occurs in a short period of time, such as during an index rebuild.  If this counter is combined with a drop in the number of Log Bytes Sent/sec, a problem can be identified.  As long as the log is still being sent at a rapid rate, a small delay can be expected, particularly if the network cannot keep up with the amount of log traffic being generated by the principle.  Note that a large Log Send Queue KB is dangerous, as this data has not been sent to the mirror, and could be lost if the principle goes down.

From the mirror’s perspective, Redo Queue KB is the premier counter.  This counter shows how many KB have been received by the mirror, but not yet applied to the database.  Again, this value can grow if the mirror cannot keep up with the number of transactions being sent through the network, for example, during index rebuilds.  However, this number is important as it shows how far behind the mirror is to the principle.  All this data is already on the mirror, but not applied, so the transactions up to this point are still safe.

Catalog Views

There are four views related to mirroring.  They are:

  1. sys.database_mirroring
  2. sys.database_mirroring_endpoints
  3. sys.database_mirroring_witnesses
  4. sys.dm_db_mirroring_connections

These views show information about the mirroring setup, but not a lot about the current status of how the mirror is operating. 

sys.database_mirroring contains a row for each mirror, including the current state of the mirror.

sys.dm_db_mirroring_connections has information about how much traffic the connection has experienced, and the last time that it had traffic, but viewing this is more appropriate for checking the configuration of the mirror, not constant monitoring.


Perfmon counters are the way to go to monitor a database mirroring implementation.  Having a proper benchmark of your mirroring statistics is vital to be able to set accurate thresholds for the counter alerts, and periodic review of these thresholds is recommended.

Jun 012008

Last week I had to set up a mirror while an existing log shipping solution was in place.  The aim was to have the mirror as an asynchronous mirror (possibly upgraded to synchronous in future, depending on performance), but still have the log shipping solution as an offsite backup.

To initialise a mirror, you need to have the database restored to a point where the current transaction log on the principle is able to be applied.  As log shipping is constantly taking backups, it is necessary to constantly restore each log backup to keep the un-activated mirror ready to synchronise.  In this situation, we manually copied the log backups over to the mirror, and allowed our log shipping scripts to automatically apply any log backups it found.

When ready to turn on the mirror, we paused the log backup job, turned on the mirror, and allowed the two to synchronise.  After that, we turned the job back on, and it took backups as normal.

The one thing to watch out for when doing log shipping with mirroring is that only transactions that have been applied to both the principle and the mirror will be backed up.  This means that if the mirror goes offline for any reason, the transaction log will continue to grow, no matter how many times you try to back it up.  Once the mirror comes back online, all those transactions will be applied to the mirror, and the transaction will be able to be backed up.  This is a possible risk, if your transaction log can get full quickly.

The mirror was successful, and no problems have risen.  I’ll describe mirror monitoring strategies in a future post.