Performance and Stability-Related Fixes in Recent SQL Server 2008 SP3 Cumulative Updates

Even though I like to talk about the most recent major releases of SQL Server, there are still plenty of organizations running SQL Server 2008, which was a good, solid release of the product, with many improvements over SQL Server 2005. Lets assume that you have been pretty diligent about keeping your SQL Server 2008 instances up-to-date with Service Packs and Cumulative Updates, so you find yourself on SQL Server 2008 SP3 CU5 (Build 10.0.5785), which was released back on May 21, 2012. Actually, you should be pretty proud to only be on a build that is about seven months old, since I regularly see people running builds so old that they are on “unsupported service pack” levels, often being 18-24 months old.

You want some specific justification and ammunition for applying the latest SQL Server 2008 SP3 Cumulative Update, which is CU8 (Build 10.0.5828), that was released on November 19, 2012. You could read the fix-lists for each cumulative update since SP3 CU5, available from this master CU list article from Microsoft:The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 3 was released

Or, you can start out with this filtered list that I put together:

SQL Server 2008 SP3 CU6 (10.0.5788) July 16, 2012

FIX: Errors when a client application sends an attention signal to SQL Server 2008 or SQL Server 2008 R2

FIX: SQL Server 2008 R2 or SQL Server 2008 stops responding and a “Non-yielding Scheduler” error is logged

FIX: Incorrect results are returned when you run a query that uses parallelism in the query execution plan in SQL Server 2008

SQL Server 2008 SP3 CU7 (10.0.5794) September 17, 2012

FIX: Error messages when you use dtexec to execute packages in SQL Server 2008 Integration Services

FIX: SQL Server Agent job fails if you set the job schedule type as “Start automatically when SQL Server Agent starts” in SQL Server 2008 SP2, SQL Server 2008 SP3 or SQL Server 2008 R2 SP2

FIX: Poor performance when you run a query that contains correlated AND predicates in SQL Server 2008 or in SQL Server 2008 R2 or in SQL Server 2012

SQL Server 2008 SP3 CU8 (10.0.5828) November 19, 2012

FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC statistics option in SQL Server 2008

FIX: Large queries that modify data run slower than expected when many locks accumulate in a SQL Server 2008 R2

Most of those hotfixes seem pretty significant to me (and this is just the subset that I picked). I strongly encourage you to start reading the hotfix list when new cumulative updates are released, because there is a lot of good information there.   SQL Server 2008 SP3 CU9 should be released around January 21, 2013, which is good timing after the holiday freezes and quiet periods for many organizations are over.

Performance Related Fixes in Post-SQL Server 2012 RTM Builds

There have already been five Cumulative Updates (CU) for the RTM branch of SQL Server 2012. Microsoft has also released SQL Server 2012 Service Pack 1, along with SQL Server 2012 SP1 Cumulative Update 1, with Cumulative Update 2 being due in the near future. There have been a high number of hotfixes in every one of these early Cumulative Updates, as more people are using SQL Server 2012 over time. Update: I have added information for SQL Server 2012 RTM CU5, which was released on December 17, 2012.

SQL Server 2012 SP1 has all of the fixes through SQL Server 2012 RTM CU2. If you have moved to the SP1 branch, you will want to get SQL Server 2012 SP1 CU1 so that you will have all of the same fixes as SQL Server 2012 RTM CU4.

This diagram of the post RTM builds might make this relationship more clear.

   SQL Server 2012 RTM Branch Builds                       SQL Server 2012 SP1 Branch Builds

— 11.0.2300        RTM
— 11.0.2316        RTM CU1                4/12/2012
— 11.0.2325        RTM CU2                6/18/2012  –>        11.0.3000        SP1 RTM        11/7/2012
— 11.0.2332        RTM CU3                8/31/2012
— 11.0.2376        RTM CU3 + QFE     10/9/2012
— 11.0.2383        RTM CU4                10/15/2012 –>        11.0.3321        SP1 CU1     11/20/2012
— 11.0.2395        RTM CU5                12/17/2012 –>

 

You can follow the KB article links below to see all of the CU builds for the RTM branch and the Service Pack 1 branch.

The SQL Server 2012 builds that were released after SQL Server 2012 was released

The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released

Especially if you are getting ready to migrate to SQL Server 2012 from an earlier version, I think you should start out with the latest Service Pack and Cumulative Update. Right now, that means Build 11.0.3321, that will probably change next week.

Like I did in my previous post, I decided to scan the hotfix list for all of the Cumulative Updates in the RTM branch, looking for performance and general reliability-related fixes for the SQL Server Database Engine. I came up with the list below, but this is completely arbitrary on my part. You may come up with a completely different list, based on what SQL Server 2012 features you are using.

Again, the idea here is to give you some concrete reasons to want to stay current with the latest SP and CU, by pointing out some of the more valuable fixes in each CU.

SQL Server 2012 RTM Cumulative Update 1  (Build 11.0.2316)

FIX: Low CPU spikes at a set interval even without user activity in SQL Server 2008 R2 or in SQL Server 2012

FIX: Slow performance when an AFTER trigger runs on a partitioned table in SQL Server 2008 R2 or in SQL Server 2012

FIX: It takes a long time to restore a database in SQL Server 2008 R2 or in SQL Server 2008 or in SQL 2012

FIX: Poor performance when you run a query that contains correlated AND predicates in SQL Server 2008 or in SQL Server 2008 R2 or in SQL Server 2012

FIX: Slow performance occurs in SQL Server 2008 R2 or in SQL Server 2012 if high CPU usage is observed with contention over the QUERY_EXEC_STATS spinlock

FIX: Out-of-memory error when you run SQL Server 2012 on a computer that uses NUMA

SQL Server 2012 RTM Cumulative Update 2  (Build 11.0.2325)

FIX: An access violation occurs intermittently when you run a query against a table that has a columnstore index in SQL Server 2012

SQL Server 2012 RTM Cumulative Update 3  (Build 11.0.2332)

FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2 or in SQL Server 2012

FIX: SQL Server 2012, SQL Server 2008 R2 or SQL Server 2008 stops responding and a “Non-yielding Scheduler” error is logged

FIX: Incorrect results when you run a parallel query that uses a columnstore index in SQL Server 2012

SQL Server 2012 RTM Cumulative Update 4  (Build 11.0.2383)

FIX: Worker threads do not wake up immediately when multiple I/O-intensive tasks are running at the same in SQL Server 2012

FIX: Database is offline and in “In Recovery” state when you rebuild an index in SQL Server 2012 if the transaction log is full

FIX: Access violation occurs when you run a full-text query in SQL Server 2012

FIX: “Process <block list> appears to be non-yielding on Scheduler <ID>” error message when you run a query in SQL Server 2012

SQL Server 2012 RTM Cumulative Update 5 (Build 11.0.2395)

FIX: High CPU usage when you query a binary large object column by using the NOLOCK hint in SQL Server 2008 R2

FIX: Large queries that modify data run slower than expected when many locks accumulate in a SQL Server 2008 R2

SQL Server 2012 experiences out-of-memory errors

FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC statistics option in SQL Server 2008

Performance Related Fixes in Post-SQL Server 2008 R2 Service Pack 1 Builds

As you may be aware if you have been reading my blogs or books over the years, I am a pretty strong advocate of staying current with both SQL Server Service Packs and SQL Server Cumulative Updates. This is also something that SQLskills recommends on our customer engagements. Despite this, it is still quite common to see people running very old builds of the major version of SQL Server that they are using. They might be running a SQL Server build that is 12, 18, or 24 months old. It might be so old that it is unsupported by Microsoft, because it is considered to be an “unsupported service pack”, meaning that Microsoft CSS will only provide limited troubleshooting support until you upgrade to a supported service pack.

As Microsoft releases new Service Packs for SQL Server, they eventually “retire” previous branches of code for that particular major release of SQL Server. When a new major version of SQL Server (such as SQL Server 2008 R2) is released, that initial build (which is what comes on the installation media unless you have built a slipstream installation) is called the release-to-manufacturing (RTM) build. Eight weeks after the RTM build, Microsoft will release a rollup of hot fixes called a Cumulative Update (CU). A SQL Server CU typically has anywhere from about ten to fifty hotfixes. Cumulative Updates are not fully regression tested like a full Service Pack is, but I have had very good luck with Cumulate Updates over the years.  The first CU for the SQL Server 2008 RTM branch was called SQL Server 2008 RTM CU1. Every eight weeks after that, Microsoft will release another CU for that RTM branch.

Eventually, Microsoft will release Service Pack 1 for that major version of SQL Server, which starts another separate release branch of the product. Eight weeks after that, the first CU for that new Service Pack will be released. So for example, you would have SQL Server 2008 R2 SP1 RTM, and then eight weeks later, you would have SQL Server 2008 R2 SP1 CU1.  Eventually, Microsoft will release Service Pack 2 for that major Version of SQL Server, which starts another separate release branch of the product. When SP2 is released, the original RTM release branch is retired, and the entire RTM release branch becomes an “unsupported service pack”, regardless of what RTM CU you have installed. That means you will have to upgrade to SP1 or SP2 to be on a supported service pack.

As a database professional, you need to be aware of how this servicing system works, and what exact build of SQL Server that you are running on your various servers. Then you can make more informed decisions on when you want to plan, test, and implement SQL Server updates (whether you use only Service Packs or both SPs and Cumulative Updates), so that your servers are on a fully supported build of SQL Server. I also firmly believe that you will have fewer problems over time if you make a concerted effort to stay on a current build. Going through the planning and testing effort required to install a Service Pack or Cumulative Update on a production server is a good exercise for you and your organization.  It exercises your testing plan and your HA procedures, and it gives you more experience rolling out updates.

I recently was asked for some specific reasons why someone should upgrade from SQL Server 2008 R2 SP1 RTM (10.50.2500) to SQL Server 2008 R2 SP2 CU3 (10.50.4266). I decided to look through the Knowledge Base (KB) articles for every single Cumulative Update released after SQL Server 2008 R2 SP1 RTM was released, looking for specific hotfixes that seemed to affect database engine performance or basic reliability. I went from SQL Server 2008 R2 SP1 CU1 through CU7, then I jumped to SQL Server 2008 R2 SP2 CU2 and then finally to SQL Server 2008 R2 SP2 CU3. I did it this way to avoid overlap between the two branches.

You can look at the KB article list for each Service Pack to put together your own list of “important” fixes based on what features of SQL Server you are using, using the links below:

Microsoft SQL Server 2008 R2 Cumulative Update KB Article Lists

The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released

The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released

The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 2 was released

 

Here are all of the Cumulative Updates that I looked at, with the relevant fixes listed for each one. Keep in mind that Cumulative Updates are actually cumulative, meaning that a later CU will include all of the hotfixes from all of the previous cumulative updates.  In this scenario, SQL Server 2008 R2 SP1 CU5 is synchronized with SQL Server 2008 R2 SP2  RTM, which is why I jump from SP1 CU7 to SP2 CU2.

SQL Server 2008 R2 SP1 CU1  (10.50.2769.0)

FIX: A query that uses the DATEDIFF function may run slowly in SQL Server 2005 or in SQL Server 2008 or in SQL Server 2008 R2

SQL Server 2008 R2 SP1 CU2 (10.50.2772.0)

FIX: Recovery takes longer than expected for a database in a SQL Server 2008 or in a SQL Server 2008 R2 environment

SQL Server 2008 R2 SP1 CU3  (10.50.2789.0)

FIX: Size of a data file that is committed by online page compression is larger than that is committed by offline page compression in SQL Server 2008 or in SQL Server 2008 R2

SQL Server 2008 R2 SP1 CU4  (10.50.2796.0)

FIX: ghost_record_count values keep increasing in SQL Server 2008 R2

FIX: It takes a long time to restore a database in SQL Server 2008 R2

SQL Server 2008 R2 SP1 CU5  (10.50.2806.0)

FIX: Low CPU spikes at a set interval even without user activity in SQL Server 2008 R2

FIX: Poor performance when you run a query that contains correlated AND predicates in SQL Server 2008 or in SQL Server 2008 R2

FIX: Slow performance occurs in SQL Server 2008 R2 if high CPU usage is observed with contention over the QUERY_EXEC_STATS spinlock

SQL Server 2008 R2 SP1 CU6  (10.50.2811.0)

FIX: Errors when client application sends an attention signal to SQL Server 2008 or SQL Server 2008 R2

FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2

SQL Server 2008 R2 SP1 CU7  (10.50.2817.0)

FIX: SQL Server 2008 R2 stops responding and a “Non-yielding Scheduler” error is logged

SQL Server 2008 R2 SP2 CU2  (10.50.4263.0)

FIX: Incorrect results are returned when you run a query that uses parallelism in the query execution plan in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012

SQL Server 2008 R2 SP2 CU3  (10.50.4266.0)

FIX: You receive error messages when you perform a population for a large full-text index in SQL Server 2008 or in SQL Server 2008 R2

FIX: SQL Server Agent logs verbose logs in the Application log when you start a failover cluster instance in SQL Server 2012 or in SQL Server 2008 R2

FIX: High CPU usage when you query a binary large object column by using the NOLOCK hint in SQL Server 2008 R2

FIX: A transaction log restore operation takes longer than expected and a 3402 error occurs if a database file is removed in SQL Server 2008 R2