Database Mirroring SQL Server 2008 R2 Memory Usage

Question

Hello:

SQL Server 2008 Ent. edition 64 bit.

My SQL Server was consuming less memory. but after configuring Database Mirroring it using MAX memory even though my SQL Server is limit to MAX. As per this link http://support.microsoft.com/kb/2001270 

i don't see enough info about memory requirement for Mirroring even though memory required by mirroring thread is outside buffer pool.

Does mirroring impact buffer pool & if yes then how i can find that out.

Any pointer or link is greatly appreciated.

Thanking in advance.

Thanks

Answer 1

Hi aaditya2000,

Yes,mirroring uses the memory in Global memory other than buffer pool.

Please run DBCC MEMORYSTATUS  and check the buffer value of SubpDesc AllocatorsGlobal in memory objects.

Refer to this online article :

SubpDesc Allocators: This value shows the memory that is used for managing subprocesses for parallel queries, backup operations, restore operations, database operations, file operations, mirroring, and asynchronous cursors. These subprocesses are also known as parallel processes.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

How much RAM does it have? Have you enabled Locked Page in Memory? Are you implementing large number of databases to be mirrored?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

Hi Peja,

Could you please expand upon your answer a little further?

Are you suggesting that one should limit the MAX memory setting further (i.e. reduce it) in order to permit more memory to be allocated to SubpDesc AllocatorsGlobal, when Databse Mirroring is used?

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

Answer 4

Hi John,

As we know,MAX memory setting need to be reconfigured according to server's physical RAM and other memory consumed outside buffer pool. I just want to know how much memory were used by mirroring from OP.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 5

I see.

More specifically then, my question is were Database Mirroring requesting for/needing more memory, would that create memory pressure on the Buffer Pool? 

If ignored, one would assume the need to reduce the MAX memory setting in order to accommodate.

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

Answer 6

Hello Peja\Uri:

Thanks for your reply & looking into this, greatly appreciated.

Total Server memory is 48GB, where SQL Server MIN-MAX is 40GB. Locked Page in Memory is enabled & only 3 small database is mirrored.

Rest 8GB is allocated to OS. after monitoring for at least 2 days, i have seen 6.5GB free all the time for OS (Available MBytes).

As you mention about "SubpDesc Allocators ", so below is the info from DBCC memorystatus and i see it is using 24 pages which is 192KB.

Thanks

Global Memory Objects                    Pages
---------------------------------------- -----------
Resource                                 299
Locks                                    205841
XDES                                     271
SETLS                                    24
SE Dataset Allocators                    48
SubpDesc Allocators                      24
SE SchemaManager                         1130
SE Column Metadata Cache                 249
SQLCache                                 306
Replication                              2
ServerGlobal                             50
XP Global                                2
SortTables                               2

Answer 7

I see.

More specifically then, my question is were Database Mirroring requesting for/needing more memory, would that create memory pressure on the Buffer Pool? 

@John & aaditya2000,

In test , I find the memory usage in Buffer Pool will raise if Database Mirroring is running. But I could not find more details in KB about Buffer Pool and Database Mirroring.SubpDesc AllocatorsGlobal is a just for managing sub process like mirroring, it is not the memory used by mirroring. Sorry for the confusion in the previous post.

So the memory used by mirroring is in buffer pool.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 8

Interesting stuff!

No need to apologise my good sir. We're all here to learn and that's exactly what's happening thanks to your efforts.

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

Answer 9

Hi Aditya,

Just curious to understand how did you say that SQL server was consuming less earlier and it increased after mirroring? Would like to know what all counters you checked to understand this.

Thanks

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 10

before configuring mirroring my buffer pool was around 6GB & after mirroring below is the counter info..............

Answer 11

Based on counters, memory clerks & DBCC MEMORYSTATUS provided. I am very thankfull to Steve Lindell & Luis Vargas from Microsoft to provide following info about DBM memory usage in detail. following info is as it is & i hope this helps.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

The server memory is below the target and there is lots of free memory on the box so the server will not do anything to return the unused memory until there is some system pressure to force sql.  
 
The DBM memory usage is low
 
MEMORYCLERK_SQLUTILITIES (Total)         KB
---------------------------------------- -----------
VM Reserved                              360
VM Committed                             360
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     280
MultiPage Allocator                      26960
 

And the SSB clerk is also low (msg allocation).

MEMORYCLERK_SQLSERVICEBROKER (node 0)    KB
---------------------------------------- -----------
VM Reserved                              0
VM Committed                             0
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     136
MultiPage Allocator                      544

----------------------------------------------------------------------------------------

OBJECTSTORE_LOCK_MANAGER (Total)         KB
---------------------------------------- -----------
VM Reserved                              131076
VM Committed                             131076
Locked Pages Allocated                   0
SM Reserved                              0
SM Committed                             0
SinglePage Allocator                     1646728
MultiPage Allocator                      0

The biggest memory consumers other than pages are the lock manager and query optimizer so it looks like someone ran some large complicated queries that used a bunch of memory and locks which loaded up the memory and since there is no reason for sql to return the memory it still has it to be prepared for this to recur.   If another program starts up and allocating memory sql should notice the low memory on the box and start its memory cleanup and release memory back to the OS. Or at least that is what I am seeing with the memory stats.

DBM does its memory allocation from the utilities clerk so that includes the runtime objects queued events and caches.  Dbm uses a common messaging provided by service broker so incoming msgs get allocated from the ssb clerk along with the memory needed to do the sends.

Since sync mirroring can add some time to the commit processing there can be more lock conflicts depending on the application but it is usually a low percentage of the normal lock memory.

Seeing the largest items being the query and lock objects usually means large complicated queries that too lots of concurrent locks and data including sort and intermediate results.

The SQL internal memory manager will grow up to the target as needed until there is external pressure which will cause the target to decrease.  Then it caps the memory objects to get them yo adjust to the new target.

If you need to reserve space for other apps on the box you can configure the max SQL memory to keep SQL from consuming all the os memory during large queries.

The locks are held while waiting for the secondary to harden the log so there will be some more concurrent locks in the system, but the main driver of lock space used is the total locks held so something has to be getting a large number of locks at once.  To take an extreme case if the mirroring delay on commit is 3x the local total transaction time there could be 4x the number of transactions in flight -> 4x the lock space if there are no conflicts.   But a more normal case is more like a 10-20% delay from sync so the extra locks held are more like 1.2-1.5x the standalone.  In your case there was memory used by locks but it is not enough to cause OOMs in other processes and the server is showing lots of free memory  so there is room to run other things in SQL or to easily return to the OS if asked.
 
The quickest way get the server to max the memory is to forget the where and join keys on query with sorts so you get a n squared data size loaded in memory from a query then start multiples of it with sort runs.  If you have the memory it can run reasonably fast so the user does not notice too much but running it once will expand the memory and it stays there until the outside pressure is applied.
 
With the 7GB of free memory that should hopefully be enough to run your other application on the box.   There can be a bit of time lag to get sql to release as it searches for free memory, but in the interim things may page out a bit.   There used to be some issues with the windows page files being set too small on some sites years back but that seems to be handled better now,  but the lack of either page file space or contiguous virtual memory address space used to cause out of memory when there was still free memory shows (64 bit took care of the virtual address space).

Thanks