Symptoms
Assume that you create an Extended Events session or a SQL Profiler Trace that captures the rpc_starting or rpc_completed event in Microsoft SQL Server 2016, and the collecting statement or data stream is not enabled (you did not set the collect_statement or collect_data_stream parameter for the Xevents session, or the Text Data or Binary Data parameter for the SQL Profiler). If the SQL Server activity involved table-valued parameters (TVPs) is captured during the session, the memory usage of SQL Server may grow much more than is necessary. That may cause severe slowdown and in extreme cases exhaust SQL Server memory, and you receive the following hard errors 701 message for ongoing operations:
There is insufficient system memory in resource pool 'default' to run this query
Note: If you use DBCC MEMORYSTATUS or query sys.dm_os_memory_clerks, you may observe that the OBJECTSTORE_LBSS memory clerk shows large memory consumption.
Resolution
The issue in which an extended events session or a profiler trace that's not configured to collect statement or data stream still requires large memory buffers because the TVP material is addressed by this fix.
Note If your extended events session or SQL Profiler trace is configured to collect statement or data/binary stream, and the SQL Server activity involved TVPs is captured during this session, the memory usage of SQL Server may still grow even with the current fix applied, as in this case the memory consumption is required to handle the TVP stream within the trace and is fully expected.
Therefore, we recommend that you not run permanent sessions or traces with the previous mentioned combinations of events and data fields on SQL Server instances that are likely to receive intensive (in numbers or in size) TVP-based activities.
Service Pack for SQL Server:
This fix is included in Service Pack 2 for SQL Server 2016.
Note: For another fix which limits the number of traced rows in an TVP, please refer to the KB 4468102.
About SQL Server 2016 builds
Each new build for SQL Server 2016 contains all the hotfixes and security fixes that were in the previous build. We recommend that you install the latest build for SQL Server 2016.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Learn about the terminology that Microsoft uses to describe software updates.
