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 18, 24 or more 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 they have more regression testing than a stand-alone hot fix), 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. Right now, only SQL Server 2008 R2 SP2 is 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 SP2 RTM (10.50.4000) to SQL Server 2008 R2 SP2 CU10 (10.50.4297). I decided to look through the Knowledge Base (KB) articles for every single Cumulative Update released after SQL Server 2008 R2 SP2 RTM was released, looking for specific hotfixes that seemed to affect database engine performance or basic reliability. I went from SQL Server 2008 R2 SP2 CU1 through CU10.
Update: I have updated this post to include SP2 CU13, which was released on June 30, 2014.
You can look at the KB article list for SQL Server 2008 R2 Service Pack 2 to put together your own list of “important” fixes based on what features of SQL Server you are using, using the link below:
Microsoft SQL Server 2008 R2 Cumulative Update KB Article List
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.
SQL Server 2008 R2 SP2 CU1 (10.50.4260.0) July 24, 2012
FIX: Errors when a client application sends an attention signal to SQL Server 2008 or SQL Server 2008 R2
FIX: Access violation when you run DML statements against a table that has partitioned indexes in SQL Server 2008 R2 or in SQL Server 2012
FIX: The log shipping restore job restores a corrupted transaction log backup to a secondary database when you run a log shipping backup job on an instance of SQL Server 2008 R2 or an instance of SQL Server 2012
FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2
FIX: SQL Server 2008 R2 or SQL Server 2008 stops responding and a “Non-yielding Scheduler” error is logged
FIX: Errors when you run a query that uses the FOR XML clause in the PATH mode in SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU2 (10.50.4263.0) August 31, 2012
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
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 or in SQL Server 2008 R2 SP2
FIX: Event ID 322 error even though operations complete successfully in SQL Server 2008 or in SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU3 (10.50.4266.0) October 15, 2012
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: “out of memory” error when you use ODBC with SQL Server 2012, SQL Server 2008 R2 or SQL Server 2008
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
FIX: Performance decreases when you run a parallel query after you upgrade from SQL Server 2008 to SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU4 (10.50.4270.0) December 17, 2012
FIX: Large queries that modify data run slower than expected when many locks accumulate in a SQL Server 2008 R2 or in SQL Server 2012
FIX: An access violation occurs when you query the missing indexes DMVs in SQL Server 2008 R2
FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC statistics option in SQL Server 2008, in SQL Server 2012 or in SQL Server 2008 R2
A database transaction log continues to grow after you upgrade to SQL Server 2012, SQL Server 2008 R2 SP1, SQL Server 2008 SP2 or SQL Server 2008 SP3
FIX: Incorrect result is returned when you query an indexed view by using the NOEXPAND hint in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012
FIX: You receive an incorrect result when you run a query against a partitioned table in SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU5 (10.50.4276.0) February 18, 2013
FIX: Access violation occurs when you run a spatial query over a linked server in SQL Server 2008 R2 or in SQL Server 2012
FIX: Access violation when you run a query that contains many constant values in an IN clause in SQL Server 2008
FIX: A query that runs against a DMV enters an infinite loop in SQL Server 2008 R2
FIX: Data corruption occurs when LOB data is loaded into a Transact-SQL variable in SQL Server 2008 R2
FIX: Access violation when you restore transaction log backups in a SQL Server 2008 R2-based log shipping environment
FIX: “SqlDumpExceptionHandler: Process <spid> generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.” error when you run a query in SQL Server 2012, in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2005
SQL Server 2008 R2 SP2 CU6 (10.50.4279.0) April 15, 2013
FIX: Access violation or incorrect result when you insert data into or update a new partition of a partitioned table in SQL Server 2008 R2, SQL Server 2012 or SQL Server 2008
FIX: “Failed Assertion = ‘CheckValidInput (wsName, cbName)'” error when you use database mirroring in Microsoft SQL Server 2008 R2
FIX: Index size increases significantly after you rebuild the index online and the RCSI is enabled in SQL Server 2008 or in SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU7 (10.50.4286.0) June 17, 2013
FIX: An access violation occurs when you run a query that both uses the PIVOT operator and uses parallelism in the query execution plan in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012
FIX: SQL Server may freeze when an instance of SQL Server 2012, SQL Server 2008 or SQL Server 2008 R2 is shut down
FIX: Not all results are returned when you search for phrases by using Full-Text search in SQL Server 2008 or SQL Server 2008 R2
FIX: Poor performance when you run a query that uses the LIKE operator in the WHERE clause in SQL Server 2008 R2
FIX: Access violation when you run queries that use the sys.dm_server_services DMV multiple times on several UNION clauses in SQL Server 2012 or SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU8 (10.50.4290.0) August 22, 2013
FIX: Query stops responding in SQL Server 2008 or in SQL Server 2008 R2 if the query alters or stops an Extended Events session
FIX: Slow performance when many connections to a database that run the same query together with different query options in SQL Server 2008 R2
FIX: Error 8985 when you run the “dbcc shrinkfile” statement by using the logical name of a file in SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU9 (10.50.4295.0) October 28, 2013
FIX: Index size increases significantly after you rebuild the index online and the RCSI is enabled in SQL Server 2008 or in SQL Server 2008 R2
FIX: Access violation in replication Distribution Agent in SQL Server 2008 R2 Transactional Replication
FIX: Poor performance when SQL Server 2008 or SQL Server 2008 R2 requires memory from a local node in a NUMA environment
FIX: It takes longer than expected to create a filtered statistic after you create a clustered index on a partitioned table in SQL Server 2008 R2
FIX: Nonclustered index corruption may occur when you run a complex UPDATE statement together with a NOLOCK hint against a table in SQL Server 2008 or in SQL Server 2008 R2
FIX: “Could not redo log record” error and log shipping stops working after an automatic failover in SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU10 (10.50.4297.0) December 16, 2013
FIX: A small number of rows are sampled in SQL Server 2008 R2 when table statistics are updated automatically or together with specified sampling rate
FIX: Access violation when you query sys.dm_server_memory_dumps in SQL Server 2008 R2
FIX: Replication Queue Reader Agent crashes when you run transactional replication in SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU11 (10.50.4302.0) February 17, 2014
FIX: Memory leak occurs when an application that uses SQL Server Native Client tries to connect and close the connection to a SQL Server 2008 R2 instance
FIX: Poor performance when many table-valued parameters are used in SQL Server 2008 R2
FIX: SELECT queries deadlock when ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled in SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU12 (10.50.4305.0) April 21, 2014
FIX: SQL Server does not clean up the missing index entries until the corresponding queries are executed in SQL Server 2008 R2
FIX: Assertion failure when you execute a query specifying TOP N and ORDER BY in SQL Server 2008 R2
FIX: An access violation occurs when you delete rows from the tables that have delete cascade and query notifications in SQL Server 2008 R2
FIX: An access violation occurs when you execute update query on a table that has a DML trigger in SQL Server 2008 R2
SQL Server 2008 R2 SP2 CU13 (10.50.4319.0) June 30, 2014
FIX: Performance problems occur when database lock activity increases in SQL Server
FIX: Incorrect results when you run queries that contain UNION operator by using parallel query plan in SQL Server
FIX: Cannot reclaim unused space by using shrink operation in the table that contains a LOB column in SQL Server
Hopefully, seeing a partial list of the Post-SP2 fixes for SQL Server 2008 R2 may help convince more people to consider deploying Cumulative Updates for SQL Server 2008 R2. Ultimately, I think you should strongly consider installing SQL Server 2008 R2 SP3, which was released on September 26, 2014, and includes all of the fixes from SQL Server 2008 R2 SP2 CU13, that also went through the more exhaustive Service Pack testing cycle.
