MTL Consumers

A previous post of mine talked about SQL Server memory architecture in a nutshell. Let’s find out what are the common consumers of the much talked about MemToLeave region and what you should be looking for in-case you run into MemToLeave issues.

Major Consumers of MTL

Use of Linked Servers – You can find out the linked servers that being used in your environment using the sysservers system catalog.
Use of XML documents – You would have to find out if any queries or procedures perform any kind of XML data manipulation or use sp_xml_preparedocument.
Extended Stored Procedures or sp_OAcreate calls
1. Extended stored procedure usage can be identified by inspecting the SQL Server Errorlog and searching for the word using. The first XSP calls is logged in the Errorlog in the following manner: Using ‘<dll name>’ version ‘<version>’ to execute extended stored procedure ‘<XSP name>’.
2. If you are using sp_OAcreate, then this information would be logged in the SQL Errorlog for the first invocation of sp_OAcreate using the same pattern mentioned above. The only difference would be that the DLL name would be odsole70.dll.
Query Plans which are larger than 8 KB in size
1. If you are using SQL Server 2000, query syscacheobjects and search for entries which have values greater than 1 for the pagesused column.
2. If you are using SQL Server 2005, search for plans greater than 8KB using the DMV sys.dm_exec_cached_plans. Inspect the size_in_bytes column value and search for entries which have values greater than 8192 bytes.
Use of SQLCLR (Applicable to SQL Server 2005 and above) – Check the memory usage for SQLCLR clerk using DBCC MEMORYSTATUS.
Backups using larger MAXTRANSFERSIZE parameter – In such a case find out BACKUP DATABASE/LOG commands and inspect the MAXTRANSFERSIZE parameter value. The lowest size that can be provided is 65536 bytes.
Using Network Packet Size higher than 8192 bytes. You can find all connections on SQL Server 2005 that use more than 8KB of connection using the following DMV: select * from sys.dm_exec_connections:

select * from sys.dm_exec_connections
where net_packet_size > 8192

9 thoughts on “MTL Consumers”

Pingback: Non Bpool region on 64bit SQL instances may be larger than you think « Troubleshootingsql's Blog

Using your suggestion above, querying against sys.dm_exec_cached_plans, I’m finding for our application, the majority (+90%) of our queries are > 8192 bytes. Is that normal?

LikeLike

It depends on whether you are facing a non-BPool memory crunch. You would need to identify the SQL plans and look into the largest plans and find out if there are any opportunities to optimize them. Queries which do not contain multiple T-SQL batches and have large plans typically have chances of optimization. However, it depends on the type of workload you are running on the server. Also identify the tables which are being used by the large plans and find out if the table statistics are updated with a fullscan. Sub-optimal plans most commonly are a victim of either lack of proper statistics or outdated statistics.

LikeLike

Hello Amit,

Thanks for your post, it is very useful to me, but i have one question for you.

In this item you say
“If you are using SQL Server 2005, search for plans greater than 8KB using the DMV sys.dm_exec_cached_plans. Inspect the size_in_bytes column value and search for entries which have values greater than 8192 bytes. ”

So, Do i have to run any especific query againts sys.dm_exec_cached_plans to get the real syze_in_bytes for the query?

If i just run:
select c.usecounts,c.cacheobjtype,c.objtype,c.size_in_bytes,s.text, p.query_plan
from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_sql_text(plan_handle) as s
cross apply sys.dm_exec_query_plan(plan_handle) as p
where c.size_in_bytes > 8912
order by usecounts desc

I will get queries that are using MemToLeave?

Thanks again and sorry for my poor english 🙂

Regards,
Marcos Freccia (@SQLFreccia)

LikeLike

Yes, you are right. The above query should give you the cached plans which are larger than 8KB in size.

LikeLike

Hi Amit,

Is there any DMV or perfcounter that can tell me amount of memory used by SQL Server process (sqlservr.exe) including buffer pool and MemToLeave usage?

I believe SQL Server: Memory Manager: TotalServerMemory perf counter only gives me buffer pool usage as well as physical_memory_in_use_kb in sys.dm_os_process_memory also gives buffer pool usage.

I am looking for total memory used by SQL Server (buffer pool + memtoleave).

LikeLike