sqlskills-logo-2015-white.png

Important Hotfix for SQL Server 2008 and Newer

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

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.

16 thoughts on “Important Hotfix for SQL Server 2008 and Newer

  1. Glenn,

    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?

    Thanks,

    Scott Caldwell

  2. Glenn,

    You mentioned that this was there from RTM of SQL2008. We have used it from SQL2005 onwards. Wonder if it is in SQL2005 but being out of support would not be reviewed or mentioned.

    Chris

  3. 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.

  4. Glenn

    Thanks your infor I’m using SQL 2008 R2 with windows 2008 (32 bit)

    I’m always getting “There is insufficient system memory in resource pool ‘default’ to run this query.” Error What can I do for Rectify this Issue

    Thanks

    Dushan

    1. Are you using full-text search with SQL Server 2008 R2? What exact build of SQL Server 2008 R2 are you using?

      There is a bug in early builds of SQL Server 2008 R2 that you might be running into:
      http://support.microsoft.com/kb/982854

      Otherwise, the fact that you are using a 32-bit version of SQL Server 2008 R2 is really limiting how much memory you have to work with, and is likely a big part of your problem.

  5. Glenn,

    What does the value of ‘Memory Used KB’ means? How do we know the memory leak is happening by looking at this number?
    Could you elaborate a little more?
    Thank you,

    1. It just means how much memory is being used (in KB) by that particular memory object. If the value is growing over time, then you are seeing that memory leak.

      The solution is to get on a new enough build of SQL Server so that you do not see the leak, or to not use the update statistics asynchronously database property setting.

  6. 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

  7. Hi,

    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.

    Thx

  8. Hi all,

    I need some suggestion from you all as below.

    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?

    Thank you all advanced.

      1. Glenn,

        Are you certain that the auto Update Statistics asynchronously setting helps reduce how much memory is used to cache ad hoc and prepared query plans?

        1. No, I’m not certain of that, because that is not what I said in the blog post. The hotfix I was referring to fixed a memory leak that was present when you had auto update statistics asynchronously enabled for a database. That has nothing to do with ad hoc or prepared query plans.

  9. Hello Glenn,

    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?

    Thank you

  10. Glenn,
    I came across your article about a possible memory leak. I ran the TSQL to see how much memory is used, 6368KB is returned. What does that signify? Server is SQL Server 2008 SP4 on Windows Server 2008 R2 SP1 and a VM on VMWare.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.