none
How to measure the SQL Server MTL Memory

    Question

  • Having issues with the following scenario, pls help if anyone can Measure the MTL Usage.

    DB Environment Details

    Windows/SQL Server
    2008 R2 with 64 Bit
    SQL Max Memory 12 GB ( BPool Area )
    3 GB for MTL (
    Non-Bpool )
    Max Worker Thread 512

    Application Environment Details
    Solaris Server with 64 Bit 32 GB RAM
    4 Instance
    Application with 20 SQL Connection each
    Application use the ODBC Drivers for
    connecting SQL Database

    • Application getting restarting when allowing with default memory setting due to insufficient memory
    • Application working fine when default MTL setting changed to 3GB using –g switch during SQL Server Startup.

    In the test lab server, we did not have any issues after allocating  the Custom Memory while startup using –g ( 3GB Size )

    By using below Query, MTL not crossing more than 100 MB

    select type, sum(single_pages_kb) BPool, sum(multi_pages_kb) MTL from sys.dm_os_memory_clerks
    where multi_pages_kb > 0 group by type order by 3 desc

    Questions:
    • Why the application restarting at the time of default Memory setting, I hope by default It suppose to have atleast 1 GB and my application not even consuming ¼ of the 1 GB but still application restarting.

    • Similarly, why the application not restarting when memory increased for the MTL Portion to the higher value.

    • Which query will help me for measuring the MTL Usage for the allocated 3 GB. Output like,

    Allocated MTL Area : 3 GB
    Consumed
    MTL : 250 MB
    Free MTL Space : 2822 MB

    Because, based on the SQL Script I will scale up the Memory usage by the C/C++ Applications.
     
    For easy understanding I have attached the App and DB Layer.

    Please share your thoughts for measuring the memory usage.


    Wednesday, July 17, 2013 1:12 AM

Answers

  • Dear Jonathan,

    Thanks for Participating in this Post :)

    One of my Friend [ Ram ] from "Chennai SQL User Group" clearly demonstrated the purpose of "Lock Page in Memory" using SysInternal tools and agree its strongly required for having better performance/avoiding page shrinking from SQLServer Buffer.

    However, my basis request is how to measure the memory Usage from MTL Region using DMV and based on the output I will design the Capacity Planning for Memory but with the current allocation ( 3GB ) for MTL region the application is working Fine without any hassle. So please share some DMV script or ideas for assessing the Memory Consumption inside MTL Region alone.

    Thanks,

    Mohanraj Jayaraman

    The first and most important thing to understand, which Karthick has already pointed out is that MemToLeave is not a factor in 64-bit SQL Servers.  Perhaps Bob Wards blog post, probably one of the most prominent Escalation Engineers for SQL Server which Karthick also happens to be as well, will help make this clear:

    http://blogs.msdn.com/b/psssql/archive/2009/08/26/come-on-64bit-so-we-can-leave-the-mem.aspx

    As Bob, and Karthick, have both pointed out, the -g parameter is bypassed in code at startup on 64-bit systems and is not affecting SQL Server in any way at all.  You have absolutely zero capacity planning for MemToLeave, you need to plan for memory allocations outside of the Buffer Pool so that you can properly size MaxServerMemory for the instance to leave available memory for non-buffer pool usage.


    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server

    Thursday, July 18, 2013 9:47 PM
  • Hello Mohan,

    1. -g switch is nop (no operation) in 64-Bit sql servers so all your remaining analysis is :( . Refer : http://mssqlwiki.com/2013/03/05/sql-server-g/ ) and to add  sys.dm_os_memory_clerks will report the memory allocated in sql server process using SQL memory manger. There can be allocations outside SQL Server memory manger. (Ex: your application loads a DLL in SQL Server and  DLL allocates memory)


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Wednesday, July 17, 2013 8:15 AM
  • As Karthick said, the -g option has no meaning in a 64bit OS.  Please see "2. 64-Bit SQL Server memory architecture" in his blog: http://blogs.msdn.com/b/karthick_pk/archive/2013/03/16/sql-server-memory.aspx 

    Are you actually having a problem or just trying to setup your new server the same as your old server?

    Wednesday, July 17, 2013 1:34 PM

All replies

  • Why do set MTL 3GB at all? Why do not use a default? How much memory the server have? Do you enable Locked Page in Memory settings?

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, July 17, 2013 6:52 AM
  • Hi Uri Dimant,

    Thanks for responding and below answers for your questions.

    • Similar Issue occurred in SQL 2k Version and there I have assigned 3GB RAM for MTL without any calculation so similar approach followed in SQL 2K8 R2 Version to resolve the problem.
    • With the default MTL Value, application getting restart continuously so increased RAM for MTL  Portion.
    • Yes, Locked Page in Memory enabled.
    • Server has 32 GB Memory

    My objective to focus on MTL Buffer area because as stated in the previous post diagram, the applications are developed in C/C++ Program and its exists in Solaris OS. Application connecting the database through ODBC Connections using DataDirect Providers. From the below query I found that application connecting database using ODBC Connection.

    select * from sys.dm_exec_sessions where session_id > 50

    From this Post my Question is, what will be the yard stick to Measure the MTL Usage for scale up the Applications usage because in future if the application instance increased from 4 to N then it will become memory bottleneck and again I don't want to give unknow Memory value to MTL and I need to give the meaning full Memory Size to MTL Portion.

    I have taken few Script from the below post but I am not convinced with MTL Usage Results where I do the App and DB Testing. Because its not even consuming 100 MB with 100 Connections. Furthermore, from this DMV sys.dm_exec_sessions at Memory usage shown as 2 for each connections i.e, ( 2 * 8 KB = 16 KB ) so finally, 80 * 16 = 1280 KB (1.2 MB) .

    Its understood that, I am doing my analysis in wrong direction so please direct me to the correct path where I can get the results what I need.

    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/02/16/how-to-find-who-is-using-eating-up-the-virtual-address-space-on-your-sql-server.aspx

    - Mohanraj

    Wednesday, July 17, 2013 7:18 AM
  • You may read the below article:

    http://blogs.msdn.com/b/joesack/archive/2009/01/08/find-non-buffer-pool-memory-memtoleave-in-private-bytes.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 17, 2013 7:29 AM
  • Hello Mohan,

    1. -g switch is nop (no operation) in 64-Bit sql servers so all your remaining analysis is :( . Refer : http://mssqlwiki.com/2013/03/05/sql-server-g/ ) and to add  sys.dm_os_memory_clerks will report the memory allocated in sql server process using SQL memory manger. There can be allocations outside SQL Server memory manger. (Ex: your application loads a DLL in SQL Server and  DLL allocates memory)


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Wednesday, July 17, 2013 8:15 AM
  • As Karthick said, the -g option has no meaning in a 64bit OS.  Please see "2. 64-Bit SQL Server memory architecture" in his blog: http://blogs.msdn.com/b/karthick_pk/archive/2013/03/16/sql-server-memory.aspx 

    Are you actually having a problem or just trying to setup your new server the same as your old server?

    Wednesday, July 17, 2013 1:34 PM
  • Hi Karthick,

    We need to migrate the SQL DB from SQL 2K to SQL 2K8 R2 64 Bit platform but the application works fine at SQL 2K 32 Bit environment without any configuration changes in the MTL Region (-g Switch). The same application with SQL/Windows 2K8 R2 environment not working properly with the default MTL Value but when the -g3072; value assigned in the StartUp location then the application working nicely without any restart for 14 hours(tested for 14 hours). Based on this experiment, I understood custom value has to assign for MTL region to avoid the application restart due to insufficient memory. However, as per your post I agree that -g switch has been decommissioned in 64 platform but its not highlighted in BOL. Refer the below URL where MS nowhere its mentioned it has been deprecated or nop (No Operation)

    http://msdn.microsoft.com/en-us/library/ms190737(v=sql.105).aspx

    As per this URL, the default MTL Region Value has to change when we get the below error otherwise not required

    -- Taken from BOL --

    Use the default for the -g parameter unless you see any of the following warnings in the SQL Server error log:

    • "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>"

    • "Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>"

    -- End of the BOL Info ----

    @Tom - As stated above, we are upgrading the system from Win2K8 32 Bit to Win2K8 64 as like SQL DB from 2K 32 Bit to 2K8 R2 64 Bit.

    Ok, In any Case, What will be the query for measuring the ODBC Connection Memory usage.

    To Both, thanks for responding to my queries.

    - Mohanraj

    Wednesday, July 17, 2013 3:35 PM
  • The command DBCC MEMORYSTATUS displays all memory usage in SQL Server

    http://support.microsoft.com/kb/271624


    Wednesday, July 17, 2013 4:54 PM
  • Also please post the results of SELECT @@VERSION from you server.
    Wednesday, July 17, 2013 4:55 PM
  • Application gets restarted or sql? Turn off "Lock pages in memory".  It is a dangerous setting if you don't know exact consequences. That could cause instability of sql. Also, a page file should exist and it's usage tracked. Is sql alone on the server? Because 12GB max mem for 32GB server is way too low.
    Wednesday, July 17, 2013 5:34 PM
  • Application gets restarted or sql? Turn off "Lock pages in memory".  It is a dangerous setting if you don't know exact consequences. That could cause instability of sql. Also, a page file should exist and it's usage tracked. Is sql alone on the server? Because 12GB max mem for 32GB server is way too low.

    I'd suggest you read my article:

    https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

    Lock Pages in Memory is not dangerous and I could easily say not using it is dangerous because it puts you at risk of having your buffer pool paged to disk by Windows.  


    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server

    Wednesday, July 17, 2013 10:14 PM
  • Glad to hear from you, Jonathan. I already read your blog post before, and by "dangerous" I mean one can finish with out-of-memory errors if not fully understood and set up properly. There are smarties who even disable page file.


    Wednesday, July 17, 2013 10:34 PM
  • Glad to hear from you, Jonathan. I already read your blog post before, and by "dangerous" I mean one can finish with out-of-memory errors if not fully understood and set up properly. There are smarties who even disable page file.


    The SQLOS monitors memory notifications from Windows and responds to memory pressure by forcing an External Clock Hand sweep of the caches to reduce it's memory usage.  If you don't set 'max server memory' your instance will grow/shrink/grow/shrink memory based on other memory demands outside of SQL Server but it won't end up in an OOM condition.  This same behavior occurs if you don't set 'max server memory' for the instance when it doesn't have LPIM enabled, the SQLOS responds the same either way.  Windows can't page out the buffer pool under LPIM, but it can still page out the thread call stacks, and VAS allocations from sqlservr.exe just like any other process so it still has room to reclaim memory through paging under demand while SQLOS responds to the low memory condition and returns memory back to the OS.  

    The only place I've ever seen this be a problem is a VM on VMware that wasn't configured with memory reservations, following VMware's own recommended best practices, and memory ballooning occurred faster than either mechanism could respond to the memory pressure, and those are still exceptions not generally experienced.


    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server

    Wednesday, July 17, 2013 10:55 PM
  • Hi,

    Result of @@Version from the SSMS

    Microsoft

    SQL Server 2008 R2(RTM) - 10.50.1600.1(X64)   Apr  2 2010 15:48:46   Copyright(c) Microsoft Corporation  Standard Edition(64-bit) on Windows NT 6.1 <X64>(Build 7601: Service Pack 1)

    Thursday, July 18, 2013 5:12 AM
  • Dear Jonathan,

    Thanks for Participating in this Post :)

    One of my Friend [ Ram ] from "Chennai SQL User Group" clearly demonstrated the purpose of "Lock Page in Memory" using SysInternal tools and agree its strongly required for having better performance/avoiding page shrinking from SQLServer Buffer.

    However, my basis request is how to measure the memory Usage from MTL Region using DMV and based on the output I will design the Capacity Planning for Memory but with the current allocation ( 3GB ) for MTL region the application is working Fine without any hassle. So please share some DMV script or ideas for assessing the Memory Consumption inside MTL Region alone.

    Thanks,

    Mohanraj Jayaraman

    Thursday, July 18, 2013 5:23 AM
  • Mohan,

    When you have LPIM enabled below perfmon counter will give you the approximate MTL usage (Not reliable in Denali)

    Performance object: Process
    Counter: Private Bytes
    Instance: sqlservr

    Performance object: Process
    Counter: Working Set
    Instance: sqlservr

     


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thursday, July 18, 2013 5:32 AM
  • Hi,

    Attempted many scenarios in this testing like setting Max Server Memory as 25 GB ( In the Initial Setup for BPool Area) later I realized Memory required for MTL region so after many experiment the setting changed to 12 GB.

    I Agree, its too low and Page File exists in this System and sysfile tracking happening through Perfmon Counters.

    Following, Memory Configuration best practices from the book material called.

    " SQL Server Hardware by Glenn Berry"

    Thanks for sharing your thoughts.

    - Mohanraj


    Thursday, July 18, 2013 5:42 AM
  • Mohanraj,

    As discussed, Observe SQL Server process memory allocation in VMMAP (and also in RAMMAP and Process Explorer).

    Let me know your Finding.

    Regards

    Ram

    Thursday, July 18, 2013 6:24 AM
  • Mohan,

    Unless you have restricted page file size you will never face MTL error in 64-Bit (With some remote exceptions). If your application is crashing when you have large memory allocated for SQL Server then I would troubleshoot the application not SQL Server. Most probably one of query which is executed by application must be timing out when you have large value for max server memory. When you reduce he max server memory same query might be choosing a different plan and must be executing faster so application doesn't crash. Start SQL Server with  Trace flag 2335 and check if the problem reoccurs. 

     

    • Similar Issue occurred in SQL 2k Version and there I have assigned 3GB RAM for MTL without any calculation so similar approach followed in SQL 2K8 R2 Version to resolve the problem.
    • With the default MTL Value, application getting restart continuously so increased RAM for MTL  Portion.
    • Yes, Locked Page in Memory enabled.
    • Server has 32 GB Memory

    You can not have 3GB MTL in SQL2k so again what you are suspecting is absolutely wrong.  

    Please do not get confused with MTL. You need not worry about it in 64-Bit just ensure you have page file configured properly. Review your sqlerrorlog and check if there are any errors when the application crash. Read http://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/ when you get time


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Thursday, July 18, 2013 6:53 AM
  • Application gets restarted or sql?
    You did not answered this question. If sql is crashing, bear in mind that your version (10.50.1600) do not have the latest SP (currently it is SP2, that gives you version 10.50.4000).
    Thursday, July 18, 2013 8:18 AM
  • You are running the RTM version of 2008 R2.  The first thing you need to do is install the current service pack and retest.

    Please see:

    http://support.microsoft.com/kb/2527041

    Thursday, July 18, 2013 1:53 PM
  • Dear Jonathan,

    Thanks for Participating in this Post :)

    One of my Friend [ Ram ] from "Chennai SQL User Group" clearly demonstrated the purpose of "Lock Page in Memory" using SysInternal tools and agree its strongly required for having better performance/avoiding page shrinking from SQLServer Buffer.

    However, my basis request is how to measure the memory Usage from MTL Region using DMV and based on the output I will design the Capacity Planning for Memory but with the current allocation ( 3GB ) for MTL region the application is working Fine without any hassle. So please share some DMV script or ideas for assessing the Memory Consumption inside MTL Region alone.

    Thanks,

    Mohanraj Jayaraman

    The first and most important thing to understand, which Karthick has already pointed out is that MemToLeave is not a factor in 64-bit SQL Servers.  Perhaps Bob Wards blog post, probably one of the most prominent Escalation Engineers for SQL Server which Karthick also happens to be as well, will help make this clear:

    http://blogs.msdn.com/b/psssql/archive/2009/08/26/come-on-64bit-so-we-can-leave-the-mem.aspx

    As Bob, and Karthick, have both pointed out, the -g parameter is bypassed in code at startup on 64-bit systems and is not affecting SQL Server in any way at all.  You have absolutely zero capacity planning for MemToLeave, you need to plan for memory allocations outside of the Buffer Pool so that you can properly size MaxServerMemory for the instance to leave available memory for non-buffer pool usage.


    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server

    Thursday, July 18, 2013 9:47 PM