If you use linked server queries, you need to read this….

  • Article

If you use the linked server feature with SQL Server 2005 and 2008, please read through this carefully. We have discovered several problems that can result in memory leak(s). The conditions are a bit complicated so let me try to describe the problems, how you could be affected, and what action(s) you can take.

SQL Server 2008

  • ANY remote stored procedure execution will leak memory for each execution on the local server (the server where you initiated the remote procedure execution). The leak is not large for each execution (around 40 bytes) but over time this can add up. This specific problem involves the RETURN status of a stored procedure so there is no way to avoid it (even if you don’t use RETURN in your proc a return status is sent back to the client) if you execute remote stored procedures.
  • If you use sql_variant SQL Server data types you could also face a different memory leak (again on the local server only). These leaks can occur under the following conditions: 
    • If you use a sql_variant type for an OUTPUT parameter of a remote stored procedure you will leak around 40 bytes just for using a sql_variant OUTPUT parameter (the leak is per parameter). If the value of the parameter is of type char, varchar, nchar, nvarchar,binary, or varbinary, you will also leak the size of the value itself for each parameter. So a large character string over time could result in a considerable memory leak.
    • if you run a remote query that returns a result with a sql_variant column AND the value of the column is a char, varchar, nchar, nvarchar, binary, or varbinary value, you will leak the value of that variant for each row returned to the local server. This one has a potential to cause a fairly significant leak depending on how big the values are and how many rows are returned.

SQL Server 2005

  • You are only affected by the sql_variant problems listed above.

This problem occurs if the linked server query uses the the SQL Server Native Client (SNAC) provider (this will happen with both the SNAC9 and SNAC10 providers), or the Microsoft OLE-DB Provider for SQL Server. The problem is specific to how the SQL Server engine handles a SSVARIANT structure so only applies to SQL Server providers. If you add a linked server query through Management Studio and pick “SQL Server” you are using the SQL Server Native Client so you would be subject to this problem.

I’ve tried to plan head of time a FAQ on this topic to help fill in more details:

1) Are these memory leak problems fixed?

The first problem is..…but not completely (yet). The first problem for a leak of return status values is fixed in SQL Server 2008 CU3 for SP1 (See fix article https://support.microsoft.com/kb/971622). That problem doesn’t happen in SQL Server 2005 so no need for a fix with that version. We are also looking to create a fix for this in the next cumulative update for SQL Server 2008 RTM.

For the sql_variant problem, we are also looking to create fixes for SQL Server 2005 CU6 for SP3 and for SQL Server 2008 RTM and SP1 in a future cumulative update.

Please note that the fix for this problem is within the SQL Server Engine code, not in the SNAC code. Therefore, when you are applying a fix from the CU updates, you need to pick the complete update package. For SQL Server 2008 CU3 for SP1 this package is called SQL_Server_2008_SP1_Cumulative_Update_3. This package also includes any update to SNAC. The package called SQL_Server_2008_SP1_Cumulative_Update_3_SNAC just contains updates to the SNAC providers so don’t try to just pick this package to get a fix for this problem.

2) What can I do to avoid the problem if a fix is not available yet?

Aside from not running remote procedures or using sql_variant columns, there is no way to avoid the problem. For 32bit customers, you may be able to mitigate VAS issues this problem causes by running all linked server queries out of process.

3) What symptoms can I see due to this problem?

It is possible you will never see a problem depending on which condition of the leak(s) you are hitting. For example, for the “return status” leak, the leak is only around 40 bytes for each procedure execution. You may run for weeks or even months without this causing a problem. For 32bit users, this may cause you problems quicker because of the limited amount of VAS space. Running the linked server out of process can definitely help your situation for 32bit. Note the memory that is leaked here is in the default Windows heap for the SQLSERVR.EXE process, not in the Buffer Pool. This is why for a 32bit SQL Server installation the problem might result in memory errors because the VAS is limited (e.g.MemToLeave)

The problem was first reported to us because of a 32bit system that saw errors in the ERRORLOG like:

Date Time SPID AppDomain 1411 (mch.dbo[runtime].1516) is marked for unload due to memory pressure.
Date Time SPID AppDomain 1411 (mch.dbo[runtime].1516) unloaded.

These messages can occur due to memory pressure (typically VAS pressure for 32bit and physical memory pressure for 64bit). But there are other types of memory related errors that might occur due to a leak of heap memory.

As future fixes for this problem are released, I’ll update this blog post

 

Bob Ward
Microsoft