Monitoring SQL Performance for Notification Server

Often, you sit down to do some client management task and start to load the Notification Server Console. It seems to take a really long time to load, or even after it is loaded, some items just take a long time to get cooking. How can you tell what is making it slow down, or at least how can you figure out what to look at? In a lot of cases, Performance Counters are a great place to start.

As Notification Server relies heavily on Microsoft SQL Server, frequently, when console performance goes out the window, SQL behavior is frequently the culprit. This article will go over a few of the most common SQL performance counters and what data they are telling you. After reviewing this article, you should be able to make some simple determinations, such as:

1. What kind of load is the memory in my SQL server under? Does my SQL Server need more memory?
2. Does my SQL Server need to have some changes made to disk configurations? Do I have too many things all running on the same disk spindle sets?

While the contents of this article are not meant to be a complete resolution for all performance problems on the Notification Server, collecting and interpreting the ensuing data will help you properly analyze the amount of support a SQL server may giving or taking away from your NS.

For an in-depth SQL tuning review for Notification Server, see KB 17079. For instructions on how to set the Perfmon counters, see KB 32258.

SQL Memory Terminology:

There are a few items of terminology to note regarding SQL memory.

Buffer Pool (BPool)

This consists of several fragmented regions (up to 32) of address space used by SQL Server. This area is dynamic (meaning the size can change) and is used by SQL Server for many SQL -specific processes, such as storing compiled execution plans, creating indexes, and cursor allocation.

MemToLeave

This consists of a single contiguous region of addresses within the SQL Server process space that is set aside at startup. This area is static (meaning the size cannot change) and is left unallocated by SQL Server for the use by components in the SQL Server process space, such as extended procedures, COM/OLE Automation objects, and linked servers. Memory allocations by SQL Server larger than 8 KB are also made from this memory area.

Reserved

A region of address space that is set aside for future use of a process, and the OS has not used any physical RAM for this allocation.

Committed

A region of address space that is currently being used by a process, and the OS has provided either physical RAM and/or paging file space for this allocation.

Allocation

The act of giving the memory resource to the consumer.

Now that you have some terminology in place, here are the counters and how they apply.

SQL Memory Counters

Click to view.

Object: Process (sqlservr)

• %Processor Time - Tells you the amount of time the SQL Server Process is using of the available processor cycles, in time percentages. If this process is taking a long time, adding memory or adjusting disk subsystem can help, but this is the first indicator of pressure, not the most specific. On Notification Servers where SQL and NS are installed on the same server, this becomes important, as you will want to allow the NS and the operating system processor cycles as well. Not so critical on a dedicated SQL server.
• Private Bytes - This is the current size, in bytes, of memory that this process has allocated that cannot be shared with other processes. This is what SQL uses. If it is taking too much, this can slow down the operating system. The OS and other non-SQL processes usually require 2-3 GB, depending on load.
• Virtual Bytes - This counter is the current size, in bytes, of the virtual address space the process is using. Use of virtual address space does not necessarily imply corresponding use of either disk or main memory pages, but can. Virtual space is finite, and the process can limit its ability to load libraries.
• Working Set - Working Set is the current size, in bytes, of the Working Set of this process. The Working Set is the set of memory pages touched recently by the threads in the process. If free memory in the computer is above a threshold, pages are left in the Working Set of a process even if they are not in use. When free memory falls below a threshold, pages are trimmed from Working Sets. If they are needed they will then be soft-faulted back into the Working Set before leaving main memory.

Monitoring Object: SQLServer:Buffer Manager

Click to view.

- Sub-Items:

• Buffer Cache hit ratio - The "Buffer Cache hit ratio" count is the Ratio of execution cache hits versus total number of lookups. This essentially outlines whether the process found what it was looking for in the memory buffer cache, or needed to go to disk to retrieve that data. As a target, looking for as close to 100% for a Buffer Cache Hit Ratio as possible is the goal. The threshold is therefore understandably high. If values start dipping below 90 %, it's time to consider extra RAM.
• Page Life Expectancy - This counter is the "number of seconds a page will stay in the buffer pool without references". Essentially, a buffer that has a 300-second page life expectancy will retain any given page in memory, in the buffer pool, for 5 minutes. Then the buffer pool flushes the information to disk - unless another process references that page. That make the use of this counter helpful in making a determination of whether the SQL server has a potential memory problem or not. This provides a reasonably accurate view of the memory pressure the SQL server is receiving. With 300 seconds being the minimum target for page life expectancy, if flushing is occurring in a shorter time window than 300 seconds, then you probably have a concern with your SQL server memory utilization. If the page life expectancy is below that value, the SQL server will respond more slowly, as it has to go to disk to obtain the information required. If the values stay consistently higher than 300 seconds, then memory would not be the primary performance concern for any observed slowdowns.

Disk Counters

Click to view.

Monitoring Object: Physical Disk (All Instances)

• Avg. Disk Queue Length - This counter tracks the number of requests that are queued and waiting for a disk during the sample interval, as well as requests in service. This is a summary of the next two below. As a summary result, this might overstate activity. If more than two requests are continuously waiting on a single-disk system, the disk might be a bottleneck.
• Avg. Disk Read Queue Length - This counter tracks the number of requests that are queued and waiting for a disk to be read during the sample interval, as well as requests in service. As a result, this might overstate activity. If more than two requests are continuously waiting on a single-disk system, the disk might be a bottleneck.
• Avg. Disk Write Queue Length - This counter tracks the number of requests that are queued and waiting for a disk to be read during the sample interval, as well as requests in service. If more than two requests are continuously waiting on a single-disk system, the disk might be a bottleneck.

Generally when you see counter values for either of these that sustain more the 2, the disk subsystem can be considered a bottleneck.

Some different ways to alleviate this bottleneck might be:

1. Move the transaction logs to a separate disk spindle set (Separate Controller and Separate Drive(s))
2. Move TEMPDB to a separate disk spindle set (Separate Controller and Separate Drive(s)). The Altiris DB makes very extensive use of TEMPDB, and this should be on its own drive subsystem anyway.
3. Separate the Notification Server program files and drive locations where the queue files are stored (EvtQFast, EvtQueue, etc), and put all the Database files on a separate drive subsystem. This will allow hardware to work independently to service the two requirements that NS needs.