A session I have wanted to see. Marciej Pilecki has done this before with high ratings and I have missed until today

He starts with a description of how memory is allocated in the 32 and 64 bit systems. One interesting thin he noted is that the lock Pages in Memory setting on 64 bit still uses the AWE API though the awe setting doesn’t make sense and isn’t used.

SQL server 2005+ is NUMA aware. Worth noting if you upgrade hardware for those old SQL 2000 instances

One lazy writer thread per NUMA node.

TCP NUMA affinity. Hadn’t heard of that but it is something to look up and check on. Marciej calls this “poor man’s Resource Governor”

The buffer pool is not just for data? Also does single page allocations for other caches. Interesting

An interesting demo to show the allocation of memory as the server starts and you run queries. dbcc freeproccache

Might clear buffers but the memory remains allocated.

Changing the Max memory live shown in Perfmon. Both target and total server memory adjust in real time.

Memory pressure can be virtual or physical and either internal or external. Inside SQL server, your physical memory pressure could be global across the instance or local to some smaller cache.

External pressure is a notification from the OS. Clock hands in SQL will sweep cache stores (need to read about this) and asks lazy writer to sweep buffer pool. This external pressure can lead to internal pressure.

Every memory cache has two clock hands: internal and external. Externals move together. Internal caches move separately from each other

Demo showing this by querying sys.dm_os_memory_cache_clock_hands

LRU algorithm used to clear caches

Procedure cache aging is based on cost of compilation, not cost of execution. This cache can also steal memory from the buffer pool.

Check on your caches with dbcc Memorystatus. Never have used that before but it shows nicely the “stealing” of buffer pool by the procedure cache

Good session and very interesting to get a little more insight into the memory of a server.

Filed under: Blog Tagged: SQL Connections, sql server, syndicated