SQL Server : large RAM and DB Checkpointing

SQL Server : large RAM and DB Checkpointing   Hi everyone, This post’s purpose is to establish a summary of the specific behaviors with relation to DB Checkpoint that may happen within SQL Server when running with a large quantity of allocated memory and when applicable, how to best address them. SQL Server 2016 improves…


Troubleshooting Memory Issues with Reporting Services

We had a case where Reporting Services was crashing with the following error. Failed allocate pages: FAIL_PAGE_ALLOCATION 2 The number at the end can be different.  In the customer’s case it was a 1.  In my local repro it was a 2.  This is basically an indication that we are out of memory for the…

2

As The World Turns: SQL Server NUMA Memory Node and the Operating System Proximity

It felt a bit like ‘As The World Turns’ as I unraveled how the following worked so the title is a bit of a tribute to my grandmother.  She could not miss here ‘stories’ in the afternoon. Proximity Before I dive into the details I would like to talk about NUMA node proximity.    The idea…

0

How It Works: CMemThread and Debugging Them

The wait type of CMemThread shows up in outputs such as sys.dm_exec_requests.  This post is intended to explain what a CMemThread is and what you might be able to do to avoid the waits.  The easiest way to describe a CMemThread is to talk about a standard Heap, HeapCreate and the options (with or without…

4

SQLCLR and sp_OA* procedures are not compatible

We ran into an issue today that is a bug you may need to be aware of because of its behavior.   When a SQLCLR procedure calls back into the SQL Server (in proc provider) and executes sp_OA*, during the callback activity, it triggers a bug (currently filed and being evaluated) that results in heap corruption…

1

AWE Allocated Values Reported Incorrectly (Large or Negative Value)

I ran into an issue today that is documented but you have to know where to find it so I wanted to point it out. In the middle of a lengthy KB article # 907877 (http://support.microsoft.com/kb/907877) the the following comment. “In a NUMA-enabled system, this value can be incorrect or negative. However, the overall AWE…

0

How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes

There seems to be some semantic(s) confusion on the books online description of SOFT NUMA.    The area of confusion is from the SQL Server 2008 Books Online section, shown below. Soft-NUMA SQL Server allows you to group CPUs into nodes referred to as soft-NUMA. You usually configure soft-NUMA when you have many CPUs and do…

3

How It Works: SQL Server 2008 NUMA and Foreign Pages

I have received numerous questions about ‘foreign pages’ so I have put this post together to answer the questions. The SQL Server buffer pool goes through three (3) distinct states. Initial / Ramp-up This state is established during buffer pool initialization and only transitions once committed buffers reaches the target.   Once Committed >= Target the…

0