Microsoft recently released a hotfix for a memory leak leak issue that affects SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. This memory leak only occurs if the have the AUTO_UPDATE_STATISTICS_ASYNC database level setting enabled for any of your databases. Here is how Microsoft describes the issue in the hotfix KB article:

When you enable the Auto Update Statistics Asynchronously statistics option in a database of Microsoft SQL Server 2012, Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2, and then you run queries on the database, a memory leak occurs. The memory structure that is leaked is about 80bytes and it occurs every time you submit a task to update stats async which is per object. The symptom of this leak can be seen in DBCC MEMORYSTATUS from the Resource Global Memory Object.

I have cleaned up the T-SQL for the two queries that Microsoft supplies in the KB article to detect this issue, as shown below:

According to my friends at Microsoft, this issue has been present since the RTM builds of all three of these major versions of SQL Server. Depending on how many databases you have with this database-level setting, how many objects (indexes and statistics objects) they have, and how often they are automatically updated asynchronously, this memory leak could add up at quite different rates.

The latest Cumulative Updates for all of the active branches (except for SQL Server 2012 SP1) of these three major versions of SQL Server all have this hotfix, as shown below:

SQL Server 2012 SP1 CU2 is due to be released sometime in January, and it should also have this hotfix. SQL Server 2008 RTM, SQL Server 2008 SP1, SQL Server 2008 SP2 , and SQL Server 2008 RTM are all considered “retired service packs”, so they will not get this hotfix (or any other non-security hotfixes).

If you are on an older build of any of these three major versions of SQL Server, you can either disable this database level setting or you can get one of these Cumulative Updates installed. Personally, I would much rather be up-to-date on my SQL Server Build rather than disable this setting (which I believe is quite beneficial for most workloads).

This setting reduces unpredictable query performance by allowing statistics to be automatically updated asynchronously while your currently running queries continue to use the old statistics until the updated statistics are ready to use. The alternative to this (which is the default) is for query execution to halt (only for queries that use statistics for that object) while the statistics are automatically updated synchronously for that object. Depending on the size of the object, and on your hardware and I/O subsystem, this could take anywhere from a few seconds to many minutes.

Of course, you are also better off if you try to manage your statistics updates on your more important objects yourself, using scheduled SQL Server Agent jobs, while leaving Auto Update Statistics enabled “just in case”. That way, you are less likely to ever have any type of automatic statistics updating kick in at an inopportune time and you can tailor your statistics updating more appropriately for your workload.

You can check which of your databases have this setting enabled with this query:

1:-- Check the status of auto_update_stats_async for all databases

2:SELECT [name] AS [Database Name], is_auto_update_stats_async_on

3:FROM sys.databases;

Code Sample 2: Checking Database Properties

You can also look in the SQL Server Management Studio (SSMS) Database Properties – Options page to see this setting.

Figure 1: Database Properties – Options

You can use a query like this to change this database-level setting

1:-- Disable auto_update_stats_async for a database

2:USE [master];

3:GO

4:-- Change to use the name of your database

5:ALTERDATABASE [AdventureWorks2012]

6:SET AUTO_UPDATE_STATISTICS_ASYNC OFFWITH NO_WAIT;

7: GO

Code Sample 3: Changing a Database Property

I have used this database-level setting for years, on many different databases, in many different environments, with very good results, and I plan on continuing to use it in the future, because I think it is very helpful, especially for OLTP workloads. If you are concerned about this memory leak, I think the best solution is to get a Cumulative Update that includes the hotfix installed on your instance as soon as you can do your planning and testing.

13 Responses to Important Hotfix for SQL Server 2008 and Newer

Thanks for the information. I’ve looked for an RSS feed with the latest FIX information but I can never seem to find it. I usually try to keep an eye on “http://technet.microsoft.com/en-us/sqlserver/ff803383.aspx” for new CUs. Do you have an RSS feed you use to stay abreast of these releases?

The people I talked to at Microsoft were not 100% sure whether this issue existed in SQL Server 2005 or not. They did say that it would be quite a bit of trouble to find out for sure. There won’t be a fix for it if it does exist in SQL Server 2005, since it is out of mainstream support.

Hi Glen
We have multi instances of 2012 running cu7, and memory is being drained.
I suspect its not this particular memory leak, but one of the many I have seen updates for.
Do you have further info regarding identifying the memory leak source in order to take appropriate action?
For now, I shall fail the instance over to the other node, however we run in an AA configuration, and im worried that I might need to restart the server to resolve?
Shall update so sp2 soon, but I note that there are patches for memory leaks for that also…
I know I will have to try to identify all the possibilities, and rule them out one by one, and there is no silver bullet – a restart will resolve, (its taken 6 months to eat through 70GB) for a while.
Many thanks for your post on the subject:)
Regards

Apologies for the late post to this thread but thought id see if still active. Glen, really good information above, I just wondered if anyone else still suffered the memory leak even with the required CU in place?

Servers deployed 6 weeks ago with 2008 R2 CU13 build version, no issues at all until this weekend when the production instance suddenly let go giving paging file too small errors, shared memory provider time-outs and memory leak warnings before the service terminated.

Before I go off on the proverbial goose chase thought id see if anyone else had continued to have memory leak issue following the patch being deployed? Not possible to turn async update off at this point due to the changing distribution of data however Im keeping it back as a last resort.

If my SQL server 2008 r2 “enable the Auto Update Statistics Asynchronously statistics “is disable but I realized that my sql server is take up quite a lot of memory about 4 GB.
I try to restart my database server and it work normal.
My current sql server 2008 R2 is version 10.50.4000.
Should I apply this hotfix to fix this memory leak issue but my database “enable the Auto Update Statistics Asynchronously statistics “is disable?
Kindly advise. Is there any others suggestion for me regarding my case?

Enabling auto update statistics asynchronously will help reduce how much memory is used to cache ad hoc and prepared query plans. The main reason that SQL Server uses a lot of memory is from caching data in the buffer pool, and this setting has no effect on that.

I have a cluster 2 nodes active/active, each one with Microsoft SQL Server 2008 R2 (SP3) – 10.50.6220.0 (X64).
If I run the above query it returns back 2 rows (with different values, of course) on both nodes: is it because of clustering?