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”. 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. 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. 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. 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). 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. 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).
SQL Server 2012 SP1 CU2 is due to be released sometime in January, and it should also have this hotfix.
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:Ĭumulative Update 5 for SQL Server 2012 RTMĬumulative Update 4 for SQL Server 2008 R2 SP2Ĭumulative Update 10 for SQL Server 2008 R2 SP1Ĭumulative Update 8 for SQL Server 2008 SP3 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.
1: - Check for memory leak described in KB2778088 (SQL Server 2012)ģ: SELECT pages_in_bytes /1024 AS ħ: - Check for memory leak described in KB2778088 (SQL Server 20 R2)Ĩ: SELECT (pages_allocated_count * page_size_in_bytes)/1024 AS Ĭode Sample 1: Checking for this memory leakĪccording to my friends at Microsoft, this issue has been present since the RTM builds of all three of these major versions of SQL Server.
0 kommentar(er)
