SQL Memory Related Counters

Key Performance Counters Related to memory : 

General Memory Counters :
Available Mbytes,
Pages Inputs / Sec,
Paging File

%Usage

Related to Memory Grants :

Granted Memory,
Maximum Memory,
Grants Outstanding,
Grants Pending

Related to SQL Server Buffer :

Lazy Writes / Sec,
PLE, Pages Reads & Writes,
ReadAhead,
Buffer Cache hit Ratio

If you decide to go little further and capture more in details on Memory Distribution

Procedure Cache Pages,
Stolen pages,
Total Server Mem,
Target Server Mem,
Data Pages,
Free Pages
Private Bytes,
Working Set are other key counters

You will be able to find all these in, Memory, Process, SQL Server memory manager & SQL Server Buffer manager

Memory Pressure

So far I touched on subjects that have been widely discussed in the industry. Today I would like to talk about a subject that you would hardly find information about:  memory pressure..  On a surface the subject appears simple but in reality this is not the case.

There are two types of memory pressure a process can be exposed to external and internal. To maximize its performance and reliability a process might want to react to both of them. External memory pressure might cause a process and whole system go into paging . Internal memory pressure might cause OOM conditions and eventual process's crash.

External memory pressure is controlled by Windows, operating system. There are two types of external memory pressure such as physical dynamic memory pressure and physical “static” memory pressure. The latter type happens when a system runs out of page file. This type of memory pressure might drive the whole system into OOM condition. You might have seen those pop ups in the right corner indicating that system runs low on virtual memory. In order to detect this type of pressure one needs to monitor the size of page file. Usually applications don’t do it.

The external dynamic memory pressure rises when Windows runs low on free RAM and about to start trimming existing working sets on the box, i.e paging. A process can monitor this type of pressure by leveraging memory resource notification API described here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/memory/base/querymemoryresourcenotification.asp.  An application can have dedicated thread that listens on memory resource notifications. Keep in mind that these notificaitons are global, i.e. they are shared by all processes. There are two types of memory resource notifications that a thread can wait on: memory high and memory low. Before Windows starts paging it will turn on low memory resource notificaion. Applications’ threads that waiting on such notification will be waken up and given opportunity to shrink process’s memory usage before OS comes into the picture. This is very useful for highend services that have better idea than the operating system about their memory usage and what needs to be shrunk. Once memory goes back to normal Windows unsets the low memory resource notification. As you might expect, when Windows thinks there is a plenty of memory on the box, it turns on memory high resource notification. If both of the memory resource notifications are not set it means that system is in stable state and processes shouldn’t either grow or shrink.

There are two types of internal memory pressure such as physical memory pressure and Virtual Address Space, VAS, pressure. Depending on its memory manager there are several ways for a process to get into physical internal memory pressure. For example external pressure might cause a process to shrink. This might trigger process’s releasing memory which in its turn will trigger internal memory pressure. The other possibility to get into this type of pressure is if an administrator sets memory limits for a process. Once max is reached process will get into internal memory pressure. Usually application recovers from this type of pressure by shrinking internal caches and pools back to its memory manger. In cases when there is no external memory pressure there is no reason to free this memory back to operating system.  

VAS pressure is the most difficult one to detect and react to. VAS pressure could happen due to two reasons. The first reason is VAS fragmentation. It happens when  a process might have plenty of VAS regions but there is no a VAS region of a given size available. Currently there is no easy way to detect largest free VAS region. One could try to allocate a VAS region of given size to identify VAS pressure state. Be careful though, periodic attempt to allocate a VAS region of large sizes, say 4MB, might cause VAS fragmentation. This will happen if some component in the process keeps on allocating and caching VAS regions of smaller sizes, for example threads. The second reason for VAS pressure is the whole VAS could be consumed. In this case any VAS allocation fails. High-end servers have to be able to deal with VAS pressure especially on 32 bit platforms. Not recovering from VAS pressure might cause first process's  slowing down and then terminating. When an application detects VAS pressure it could react to it the same way as to internal physical pressure by shrinking caches and pools. In addition a process might decide to shrink thread pools, remove shared memory regions, unload dlls and etc…

To correctly handle all types of memory pressure you will need to build special infrastructure. As it turns out this type of infrastructure is not simple. Just consider different states your process can be in at the same time. For example Windows might indicate that there is plenty of external RAM, enabling your process can grow, but at the same time your process can hit internal physical or VAS pressure. 

There are several implementation caveats that you need be aware of when implementing such infrastructure.  If your process slow enough to react to external pressure Windows will page your process out. Then it will turn off low memory resource. Once it seesm that there is plenty of memory it will turn on high memory resource. In this case you might see that high is on and start allocating more memory even though your process is paged out. This might cause your process to page against itself. It seems that when deciding to grow you need to take your working set into account, please remember neither AWE pages nor large pages are part of working set so you have to be really careful. The other caveat is when running low on paging file, Windows won’t turn on low memory resource even though it is about to return OOM for next memory requests. In addition keep in mind that your well behaved application can be affected by a bad one that doesn’t care about memory state on the box at all.

Understanding memory pressure should really help you when we will be discussing SQL Server memory manger. Moreover having this knowledge should help you to administrate SQL Server and other applications sharing a box

Checkpoint and Lazy Writer are somewhat similar. They both write pages from buffer pool memory to the disk. So, what is the difference?

CHECKPOINT‘s goal is to shorten database recovery time (e.g. after a server crash), therefore increasing availability. It makes data files not lagging too much behind the transaction log. Recovery of every single database starts at the last checkpoint and rolls-forward all the transaction log records from that point on. If checkpoint was long time ago, it could be that amount of log to roll forward is huge and it can take considerable time. During that time your database is unavailable, affecting availability (you have longer downtime). More frequent checkpoints means less amount of log needed to roll-forward, resulting in faster recovery. Too frequent checkpoints make buffer pool caching less efficient and can negatively affect performance. E.g. you don’t want to checkpoint every second. Frequency of automatic checkpoint is determined by recovery interval option and the log generation rate, and usually is around every 1 minute. Some TSQL commands also do a checkpoint inside, like BACKUP DATABASE command for example. Such checkpoints are called “Internal”. And we can do a manual checkpoint by invoking a CHECKPOINT tsql command.

LAZY WRITER purpose is to release the buffer pool memory (for pages cached in buffer pool) when memory pressure occurs. When more memory is needed (e.g. for bringing in new pages to the cache), lazy writer responds to a memory pressure releasing the “coldest” pages from the buffer pool, and makes more memory available for new pages to come in. And that is normal to see that lazy writer occasionally “makes a room”. But if lazy writer constantly has a lots of work to do, starting to purge pages that are not old at all (you see ‘Buffer Node:Page Life Expectancy’ perfmon counter stays below DataCacheSizeInGB/4GB *300) – you have a problem with buffer cache memory. You do not want pages flow though the buffer cache like a rapid river. You want them to stay there and be reused, read/written and rewritten again in-memory and not the disk which is slow compared to memory, as long as possible. Low page life expectancy nullifies buffer caching purpose, affecting performance.

They both write in-memory pages to the data files on the disk. But Which pages, when, and do they release memory or not – there is the difference!

CHECKPOINT writes only dirty pages pages to the disk (dirty = changed in memory since the last checkpoint, not yet written/checkpointed to disk), making them “clean”. Checkpoint does not release any memory, the pages STAY in memory, they are not removed from the buffer pool!

LAZY WRITER looks for least recently used (“cold” = least recently read or written, not accessed in recent time) pages in the buffer pool, and releases the memory taken by them. Lazy writer releases both dirty and clean pages. Clean pages can be released without writing to disk, but dirty pages must first be written to the disk (“flushed” to the disk and become “clean”) and then buffer pool memory can be released. So, total number of pages that lazy writer releases can be higher than the number of pages lazy writer writes to the data files, because “clean” pages can be released without writing them to disk. The final result of the lazy writer is less buffer pool memory used, therefore more memory available for the fresh pages in the buffer pool.

There is another difference: checkpoint process is more efficient in writing to the disk because it can group subsequent pages into larger disk IOs, e.g. 128KB IO size. It internally uses WriteFileGather Windows API function. For details, see here and here. Lazy writer can only write 8K pages. Therefore checkpoint disk throughput is much better than lazy writer’s.
How to track their activity?
Easiest is probably through the performance counters:

Transact-SQL

SELECT * FROM sys.dm_os_performance_counters WHERE counter_name in ('Checkpoint pages/sec', 'Lazy writes/sec', 'Page life expectancy')

1 2 3 4 5

SELECT * FROM sys.dm_os_performance_counters WHERE counter_name in ('Checkpoint pages/sec', 'Lazy writes/sec', 'Page life expectancy')

Using perfmon graph, we can see checkpoint is happening here about every 1 minute, and writes up to 7 000 pages per second (almost 60 MB/sec):

SQL Server Memory

SQL Server Database Mirroring

Top 15 SQL Server Performance Counters to Monitor

Processor(_Total)\% Processor Time

Percent Processor Time tells us how busy the server's CPUs are. It's a basic indicator to help us know that a server is running well within acceptable operating parameters. Normally I'd expect to see this counter in the 20 to 40 percent range. When it jumps above 80% I get very nervous, as that means that activity that requires the processor is probably waiting for resources, and thus is slowing down someone's vital work.

Memory\Available MBytes

The Available MBytes Memory counter helps me know if server memory is an issue. I can set Max Server Memory settings in SQL Server, which will help SQL Server share the memory nicely with the Windows OS, but there may be other processes on the server besides SQL Server. Capturing this counter allows me to know if other processes are taking memory SQL Server needs to perform well.

Paging File(_Total)\% Usage

When Windows runs out of memory it takes large chunks of memory and swaps it out to disk, to the Paging File. Unfortunately, the slowest operation in all computing is writing to disk, regardless of the physical media involved, so swapping memory to disk is naturally going to slow down the performance of your system. Keeping an eye on this counter will help you know when you are encountering memory issues, and you can then take action to resolve the conflicts.

 

PhysicalDisk(_Total)\Avg. Disk sec/Read
PhysicalDisk(_Total)\Avg. Disk sec/Write

These two counters tell you how quickly your I/O subsystem is responding to requests for data from the operating system; in other words, latency. The latency values returned are valid regardless of the type of I/O subsystem you're using, whether it's local physical magnetic disk, SAN drives, NAS drives, or solid state drives. Your latency values should normally not be more than 20ms; if you're using SSD, probably not more than 5ms. If you see latency values of a second or more, your I/O subsystem has issues that need to be addressed to keep performance at an acceptable level.

 

System\Processor Queue Length

The Processor Queue Length counter tells you the number of threads that are waiting for time on the system processor. If this number is greater than 0, that means that there are more requests per core than the system can handle, and this can be a cause for significant performance issues. I once had a client that had a month-end process that had to be run during the business day, which would take 2.5 to 3 hours to run; when it ran, performance for everyone else on that system would be horribly slow. I looked at the Processor Queue Length counter – normally it would get to no higher than 3 or 4 during the day, but During month-end it jumped to somewhere between 30 and 50. The client was running on a virtual machine with 4 processors, and I asked if they could double that. They did, and the next month-end completed in 45 minutes.

 

Network interface\Bytes total/sec

This is another baseline indicator that tells you the rate at which the network adapter is processing data. This number should remain lower than 60% of the maximum sustainable bandwidth of the network.
In addition to the operating system counters, you'll also want to monitor the SQL Server performance counters. If you're observing a default instance the first part of the counter name (to the left of the colon) is SQLServer. If it's a named instance, like INST01, it's MSSQL$ followed by the instance name, so in this case, MSSQL$INST01.

 

SQLServer:Access Methods\Forwarded Records/sec

Forwarded Records/sec helps you understand how fragmented your heaps are. A heap is a SQL Server table without a clustered index, and SQL Server uses Row IDs to find the data it's looking for. If it arrives at the page where the data should be, and that data has been moved, SQL Server leaves a pointer there called a forwarding pointer, and the process has to incur an additional I/O operation to get it from the new location. Each time a search for data encounters a forwarding pointer it increments this counter. This may be unavoidable, but if you track this over time and this number starts to increase, you should think about ways to defragment your table (or stop using a heap).

 

SQLServer:Access Methods\Page Splits/sec

Page Splits/sec also helps you understand how fragmented your tables are. In this case, even if your table is in good shape, when SQL Server adds pages to it, it'll increment this counter. If, however, SQL Server needs to insert a row onto a page, and there isn't room, SQL Server will split the page into multiple pages, move rows from one page to another to balance the pages out, and then insert the row. This is a very expensive and time-consuming process, and this counter will help you understand when this is happening a lot. Properly configuring the free space on each page will help minimize this activity, just note that there are "good" page splits and "bad" page splits, and this counter doesn't differentiate (Jonathan Kehayias of SQLskills has an Extended Events session you can use instead.)

 

SQLServer:Buffer Manager\Buffer cache hit ratio

On older SQL Server systems (think SQL Server 2000 and earlier) this counter would help you understand the percentage of times the needed page was already in the buffer pool when it was needed. On today's system it's mostly useless, but many older DBA managers still ask for it, so it doesn't hurt to capture this counter.

 

SQLServer:Buffer Manager\Page life expectancy

Page Life Expectancy is an often mis-interpreted counter. Jonathan Kehayias wrote a blog post called "Finding what queries in the plan cache use a specific index." Jump down to the paragraph entitled "What’s Wrong about Page Life Expectancy >= 300?" and read from there to the end of the post. In the SQL Sentry tool, we use Jonathan's guidelines for indicating what the correct expectation should be. The thing I use this counter for is to see when some process is causing the pages in the buffer pool to be flushed out, and getting to the root of that problem. It may be that person that insists on running the report that has to read all 30 million customer records during the business day, which may require some behavior modification. It could also be that you just ran DBCC CHECKDB, because that brings that counter's value to 0 as well. The important thing is to know your workload and look for those events that are outside normal expectations.

 

SQLServer:General Statistics\Processes blocked

In any multi-user application you're going to have blocked processes, and SQL Server has mechanisms to handle blocked processes well, but when this counter goes outside the normal range (for your system) you'll want to investigate and see what might be causing the issue. There could be excessive blocking due to page escalation, for example, where entire tables are getting locked instead of individual rows or pages.

 

SQLServer:SQL Statistics\Batch Requests/sec

This counter is there to help you understand how busy your SQL Server system is. By capturing this counter, and using it in your baseline, you can identify variances easily – they might be reported by users, or it might just be extra load on the system because people are asking for more than they usually do.

 

SQLServer:SQL Statistics\SQL Compilations/sec
SQLServer:SQL Statistics\SQL Re-Compilations/sec

These counters will increment when SQL Server has to compile or recompile query plans because either the plan in cache is no longer valid, or there's no plan in cache for this query. SQL Server uses a cost-based optimizer that relies on statistics to choose a good query plan, and when those statistics are out-of-date, additional compilations are done unnecessarily. It can be useful to understand the source of this problem, if it is a problem (this might be expected behavior, depending on the workload).

How To Find Out Who Installed MS SQL Server

I checked this on my system.

in  Setup Bootstrap\Logs\Installation Date\MSSQLServer\sql_engine_core_inst_Cpu32_1.log
Installation Date <- check the folder with earliest date..
I found below line in the log:
MSI (s) (AC:2C) [10:46:20:952]: SHE