Questions regarding utilization of the MTL area by the SQL CLR

Question

Questions regarding utilization of the MTL area by the SQL CLR

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
December 22, 2008 at 12:08 pm

#195366

We are getting the following error on a CLR assembly running on our SQL instance:
Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileNotFoundException: Could not load file or assembly 'regex, Version=1.0.3121.22187, Culture=neutral, PublicKeyToken=b92964ae5ac1af4f' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
Our assembly is running on PERMISSION_SET safe and consists of 3 REGEX UDFs.
I had opened a PSS case with Microsoft some time ago about this, and they recommended that I increase the size of the MTL memory area through the -g startup parameter: http://msdn.microsoft.com/en-us/library/ms190737(SQL.90).aspx
I tried it on our server last week (added -g384) and, after rebooting the server, I ran the attached query in my instance to determine the total and maximum size the free VAS memory.
Here are the values:
Total avail mem, KB: 465256
Max free size, KB: 157412
I have the following questions:
(1) What determines the Max free size value above? Why is it at 157 MB and not higher?
(2) When making use of the -g flag to increase the MTL area, do I also need to reduce the max-memory setting in the SQL instance accordingly, so that the Buffer Pool size does not end up encroaching on the MTL area?
Thanks for any comments!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Marios Philippopoulos (12/22/2008)
I tried it on our server last week (added -g384) and, after rebooting the server, I ran the attached query in my instance to determine the total and maximum size the free VAS memory.
Here are the values:
Total avail mem, KB: 465256
Max free size, KB: 157412
I have the following questions:
(1) What determines the Max free size value above? Why is it at 157 MB and not higher?
(2) When making use of the -g flag to increase the MTL area, do I also need to reduce the max-memory setting in the SQL instance accordingly, so that the Buffer Pool size does not end up encroaching on the MTL area?
Thanks for any comments!

For Question #1:

The first number is the total available/free memory in the MTL/VAS region and the second number is the largest contiguous allocation available, in this case 157MB. You have 465MG total avaiable, but due to fragmentation, a good chunk of it is fragmented, and there isn't anything you can really do about MTL/VAS fragmentation.

For Question #2:

No, you don't need to change your Max Server Memory Setting and the BPool can not encroach on MTL/VAS space. The MTL is calculated at first and completely reserved when SQL Server starts up. Then the BPoole is sized based on the total user VAS minus the MTL reservation and then compared to the actual phyiscal memory, and the smaller of the two numbers is used to set the maximum buffer pool size. If the Max Server Memory is smaller than this size then it is further reduced to the Max Server Memory size.

MTL/VAS allocations have to be contiguous memory allocations. What happens when your VAS is heavily fragmented is that the needed memory can't be allocated contiguously which is why you were getting the problems you have above. Expanding the VAS reservation gives you 128MB more to obtain that contiguous allocation. Watch the numbers over time and see how they shift. You will probably see the available stay high, while the contiguous gets smaller. If it gets to small, or you experience app domain unloading, you need to look at what else is pulling from the MTL/VAS.

Things to look for include heavy use of Extended Procedures (xp's), COM/OLE Automation calls (sp_OACreate), SQLXML use (sp_preparedocument), Linked Servers, third party backup tools with a network packet size > 8000 bytes, and SQLCLR. If you are making COM calls, are you cleaning up with sp_OADestroy? If you load an XML document with sp_preparedocument are you cleaning up with sp_removedocument? If you are using third party backup tools, change the buffer/packet sizes and see if that helps. SQL Litespeed is known for MTL issues when configured with to large a packet size. If configured properly it works just fine.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Jonathan, thanks for the response, I was hoping you would! 🙂

In my environment we have 8 GB of RAM (32-bit OS).

3-gb and PAE switches enabled.

2 SQL db-engine instances and an SSIS instance installed:

instance 1: max memory not set (buffer counts committed is at 2.5 GB)

instance 2: AWE enabled to a max memory of 4 GB

SSIS instance presumably sees the max default of 2 GB of VAS (?).

I am also thinking of increasing the MTL through adding the -g512 parameter.

We are using linked servers (heavily) and SQLCLR.

Do you think I need more RAM on that box?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

You really need to goto x64. I can certainly understand that might not be an option, but for what you are trying to do x64 is so much better. I would actually recommend that you not change the -g parameter to 512. I would have to do some math and read a few references to make sure I am making appropriate calculations for multiple instances. With /3GB and /PAE you have 3 GB of user VAS not 2GB, so with -g384, your bpool should size out to just at 2.5GB give or take some.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Jonathan Kehayias (12/22/2008)
You really need to goto x64. I can certainly understand that might not be an option, but for what you are trying to do x64 is so much better. I would actually recommend that you not change the -g parameter to 512. I would have to do some math and read a few references to make sure I am making appropriate calculations for multiple instances. With /3GB and /PAE you have 3 GB of user VAS not 2GB, so with -g384, your bpool should size out to just at 2.5GB give or take some.

I know, x64 is definitely the way to go, and we are heading in that direction, albeit not quickly enough...

To make matters worse, there is a largish data warehouse on the AWE-enabled instance, and users issue ad-hoc

queries on it causing the buffer pool to be flushed every half hour or so!

Looks like I will be using the -g384 parameter then, and hoping for the best.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

Here is also a related question that I missed asking in my OP.

The -g384 parameter had an effect only on the Total avail mem, KB value and not the Max free size, KB value.

The Max free size, KB value stayed the same with or without the parameter.

Shouldn't it be changed also?

Here are the results after restarting SQL each time (I tried this in our test environment):

With -g384

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

467308 157412

Without -g384

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

338264 157412

With -g512

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

599628 157412

Without -g512

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

338140 157412

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Let me double check that query. I know it is one I provided elsewhere, but something could be wrong with it... I'll get back to you in a bit.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

My numbers in testing seem to line up with what you are seeing. Change the last query out to be:

SELECT

CAST(Size AS BIGINT)/1024 AS [Free size, KB]

FROM

VAS_Summary

WHERE

Free <> 0;

and you can see that there are other large blocks available, and they differ between parameter values. You should be ok for Regex with -g384. The only people I have seen that required -g512 were doing heavy data operations in datasets which is not recommended, and or doing heavy webservice output consumption, which is generally bloated serialized XML which has a large memory demand.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 8

Jonathan Kehayias (12/22/2008)
My numbers in testing seem to line up with what you are seeing. Change the last query out to be:
SELECT
CAST(Size AS BIGINT)/1024 AS [Free size, KB]
FROM
VAS_Summary
WHERE
Free <> 0;
and you can see that there are other large blocks available, and they differ between parameter values. You should be ok for Regex with -g384. The only people I have seen that required -g512 were doing heavy data operations in datasets which is not recommended, and or doing heavy webservice output consumption, which is generally bloated serialized XML which has a large memory demand.

Thank you so much for your input!

I will implement -g384.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 9

Is a server reboot necessary after the -g384 parameter is added, or is an instance restart sufficient for the change to take effect?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Just a restart of the SQL Server Service is all that is needed.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

Watch your Error Log for the next few weeks and see if you experience App Domain Unloads due to memory pressure. If you get one or two a day, that isn't really an issue, but if you get frequent unloading/reloading, then monitor your VAS numbers and see how they change over time. You should be ok, but something to watch for.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 12

Jonathan Kehayias (12/23/2008)
Watch your Error Log for the next few weeks and see if you experience App Domain Unloads due to memory pressure. If you get one or two a day, that isn't really an issue, but if you get frequent unloading/reloading, then monitor your VAS numbers and see how they change over time. You should be ok, but something to watch for.

Will do, thanks!

We are using IDERA's SQLsafe for backups. We will check with them on possible memory leaks with the tool, as this is a likely hog of MTL resources.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 13

It won't be a leak. When a backup is running, query the sys.dm_exec_requests DMV and find the session_id that it is running on. Then query the sys.dm_exec_connections DMV and look at the network_packet_size for the connection. If it is over 8000 then it is pulling resources from the MTL since this is where large page/multi page allocations are done from. It isn't a bug, it is actually a by design thing since they can run 64K packet sizes and get the database about 8 times faster, but there is a trade off. You wouldn't ever have known if you weren't using CLR most likely.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 14

Jonathan Kehayias (12/23/2008)
It won't be a leak. When a backup is running, query the sys.dm_exec_requests DMV and find the session_id that it is running on. Then query the sys.dm_exec_connections DMV and look at the network_packet_size for the connection. If it is over 8000 then it is pulling resources from the MTL since this is where large page/multi page allocations are done from. It isn't a bug, it is actually a by design thing since they can run 64K packet sizes and get the database about 8 times faster, but there is a trade off. You wouldn't ever have known if you weren't using CLR most likely.

Great, I was wondering how to get the packet size info...

Thanks for your help!

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]