If you look at the output of DBCC MEMORYSTATUS that is automatically logged to the error log on 701 error messages, it will have entries similar to the following:
Additionally, if you query the sys.dm_os_memory_clerks dynamic management view (DMV), during the time the batch is getting executed, the single_pages_kb column for the USERSTORE_SXC cache show a continuous growth over a period of time that leads to the 701 error Cause
The amount of memory allocated to store a request in SQL Server depends on:
The batch size (number of RPCs per request)
Type of parameters.
For certain types of parameters (for example sql_variant), SQL Server can save the requests in memory in a potentially inefficient manner. When a client sends a large batch of requests that use these types of parameters, multiple RPCs can be sent in one request. In this scenario, the server accumulates the whole request in memory before it is executed. This could potentially lead to 701 error discussed in symptoms section.
Resolution:
You can use one of the following workarounds:
Reduce batch sizes.
Change parameter types, for example, replace sql_variants with other types.
Additionally, if you query the sys.dm_os_memory_clerks dynamic management view (DMV), during the time the batch is getting executed, the single_pages_kb column for the USERSTORE_SXC cache show a continuous growth over a period of time that leads to the 701 error Cause
The amount of memory allocated to store a request in SQL Server depends on:
The batch size (number of RPCs per request)
Type of parameters.
For certain types of parameters (for example sql_variant), SQL Server can save the requests in memory in a potentially inefficient manner. When a client sends a large batch of requests that use these types of parameters, multiple RPCs can be sent in one request. In this scenario, the server accumulates the whole request in memory before it is executed. This could potentially lead to 701 error discussed in symptoms section.
Resolution:
You can use one of the following workarounds:
Reduce batch sizes.
Change parameter types, for example, replace sql_variants with other types.
