Denali memory allocator changes and SQLCLR

Once upon a time (well, now, currently), there were two memory allocators in SQL Server, the single-page allocator and the multi-page allocator. The single-page allocator was used for almost everything (data buffers, caches, etc); the multi-page allocator was used for somewhat more esoteric things. Like most of SQLCLR (that's CLR code that runs inside SQL Server). Well, as the SQLOS team blog says (and I've confirmed with experiments), in the Denali release of SQL Server, there is only one allocator. And that allocator covers SQLCLR memory. The fields in some of the DMVs pertaining to memory need to change, naturally, so watch out for this if you write you own monitoring queries (and who doesn't?). 

Why is this interesting? Before Denali, on 32-bit systems, you were always futzing with the -g startup switch if you wanted to give SQLCLR more room. And on 64-bit systems (where -g is meaningless), max_server_memory might have to be reduced, because SQLCLR memory wasn't covered by max_server_memory and you needed to leave some. Well, now it is covered. The "any size memory allocator" handles it all. Nice.

Now BTW, in case this needs to be said, just because SQLCLR is using the same allocator and being coordinated with the other buffer pools, doesn't mean you should go wild and, for example, use DataSets (or other memory hogs) in SQLCLR stored procedures. Every byte that you allocate, every garbage-collected heap, is taking memory from *the rest* of the buffer pools. Remember that. If you don't, you can still be rewarded with: "Msg 6532, Level 16, State 49, Procedure your_proc, Line 0 .NET Framework execution was aborted by escalation policy because of out of memory." The SQL Server memory manager looks out for its own.

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.