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:

   1: -- Check for memory leak described in KB2778088 (SQL Server 2012)

   2: -- http://support.microsoft.com/kb/2778088

   3: SELECT pages_in_bytes /1024 AS [Memory Used KB]

   4: FROM sys.dm_os_memory_objects

   5: WHERE [type] = N'MEMOBJ_RESOURCE';

   6:

   7: -- Check for memory leak described in KB2778088 (SQL Server 2008 and 2008 R2)

   8: SELECT (pages_allocated_count * page_size_in_bytes)/1024 AS [Memory Used KB]

   9: FROM sys.dm_os_memory_objects

  10: WHERE [type] = N'MEMOBJ_RESOURCE';

Code Sample 1: Checking for this memory leak

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:

Cumulative Update 5 for SQL Server 2012 RTM

Cumulative Update 4 for SQL Server 2008 R2 SP2

Cumulative Update 10 for SQL Server 2008 R2 SP1

Cumulative Update 8 for SQL Server 2008 SP3

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.

image thumb Important Hotfix for SQL Server 2008 and Newer

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: ALTER DATABASE [AdventureWorks2012]

   6: SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH 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.