FIX: Error message when you use a table-valued function (TVF) together with the CROSS APPLY operator in a query in SQL Server 2005: "There is insufficient system memory to run this query" (924954)

MORE INFORMATION

• The CACHESTORE_SQLCP memory clerk from the sys.dm_os_memory_clerks SQL Server operating system management view
• The MEMOBJ_XSTMT memory object from the sys.dm_os_memory_objects SQL Server operating system management view

Steps to reproduce the problem

1. To reproduce the problem faster, configure SQL Server 2005 to use a small amount of memory. To do this, run the following script in SQL Server 2005.

   -- Enable advanced options
   USE master
   EXEC sp_configure 'show advanced options', 1
   go
   RECONFIGURE WITH OVERRIDE
   go
   
   -- Set the maximum amount of memory to 300 MB
   exec sp_configure 'max server memory', 300
   go
   reconfigure with override
   go
   dbcc freeproccache
   go
   

2. To stop and then restart the SQL Server service, run the following commands at a command prompt:

   net stop mssqlserver
   net start mssqlserver

3. In SQL Server 2005, run the following Transact-SQL statements.

   drop table t
   go
   create table t (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int)
   go
   
   drop function fn_test
   go
   create function fn_test (@p1 int, @p2 int, @p3 int, @p4 int, @p5 int, @p6 int, @p7 int, @p8 int, @p9 int, @p10 int)
   returns @t table (c1 nvarchar(4) )
   with schemabinding
   as
   begin
   insert into @t values (N'abcd')
   return
   end
   go
   drop function fn_scalar
   go
   create function fn_scalar (@p1 int, @p2 int, @p3 int, @p4 int, @p5 int, @p6 int, @p7 int, @p8 int, @p9 int, @p10 int)
   returns nvarchar(4) 
   with schemabinding
   as
   begin
   declare @s nvarchar (4)
   set @s = N'abcd' 
   
   return @s
   end
   
   go
   drop function fn_test2
   go
   create function fn_test2 ()
   returns @t table (c1 nvarchar(4) )
   with schemabinding
   as
   begin
   insert into @t values (N'abcd')
   return
   end
   
   
   go
   set nocount on
   go
   declare @i int
   set @i = 0
   while @i < 5000000
   begin
   insert into t values (@i, @i, @i, @i, @i, @i, @i, @i, @i, @i)
   set @i = @i + 1
   end
   
   go
   -- The following select query returns the 701 error if you restrict max server memory to 300 MB
   select count(*) from t cross apply dbo.fn_test(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10)