Performance and Stability-Related Fixes in All SQL Server 2008 SP2 Cumulative Updates

SQL Server 2008 Service Pack 2 was retired and became an “unsupported service pack” back on September 17, 2012. Despite this, I have been running into a lot of systems lately that are still running SQL Server 2008 Service Pack 2 or older lately, so I thought it made sense to follow up my previous post, Performance and Stability-Related Fixes in All SQL Server 2008 SP3 Cumulative Updates with another one listing some of the most relevant hotfixes in all eleven of the SQL Server 2008 Service Pack 2 Cumulative Updates.

Since SQL Server 2008 Service Pack 2 is retired, I think you should be on SQL Server 2008 Service Pack 3. The purpose of this list is to help convince you (and your organization) that you should keep your SQL Server 2008 up-to-date and in a supported status by highlighting some of the fixes that you are missing by staying on an older build of SQL Server 2008.

If you don’t believe in applying SQL Server Cumulative Updates, you should be aware that SQL Server 20008 SP3 RTM (Build 10.0.5500) only includes the fixes up through SQL Server 2008 SP2 CU4 (Build 10.0.4285), so you won’t have many of the fixes listed in this post.

You can always read the fix-lists for each cumulative update since SP2 RTM, available from this master CU list article from Microsoft:

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

Or, you can start out with this filtered list (of Engine-related fixes) that I put together:

SQL Server 2008 SP2 CU1 (10.0.4266)  November 16, 2010

FIX: Poor performance and some occasional non-yielding scheduler errors occur when you create a complex view that references a large amount of nested views or tables in SQL Server 2008

FIX: A query that uses a parallel query plan returns different results every time that you run the query in SQL Server 2005, SQL Server 2008 R2 or SQL Server 2008

FIX: A query returns an incorrect result if it uses an index that is being rebuilt online in SQL Server 2005, SQL Server 2008 R2 or SQL Server 2008

SQL Server 2008 SP2 CU2 (10.0.4272)  January 17, 2011

FIX: An unresolved deadlock occurs when a database is recovered in SQL Server 2005, SQL Server 2008, or in SQL Server 2008 R2

FIX: LOB pages that are allocated in a failed INSERT statement may not be reclaimed after you apply SQL Server 2005 SP3, SQL Server 2005 SP4, SQL Server 2008 SP1 or SQL Server 2008 R2

FIX: Error message when you run the “sp_createstats” stored procedure in SQL Server 2008 or in SQL Server 2008 R2 if the table contains a nondeterministic computed column

FIX: High CPU usage when you run a SQL Server Agent job in SQL Server 2008 or SQL Server 2008 R2 if a column of a dynamic SELECT statement has a value that is many characters long

FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005 or in SQL Server 2008

“Non-yielding Scheduler” error and SQL Server 2008 stops responding intermittently in Windows Server 2008

SQL Server 2008 SP2 CU3 (10.0.4279)  March 21, 2011

FIX: Database mirroring session is suspended in SQL Server 2005 and in SQL Server 2008 and in SQL Server 2008 R2 if the High-performance (asynchronous) mode is used

FIX: Poor performance may occur when you execute a query that contains multiple self-join operations on the same column in SQL Server 2005, in SQL Server 2008 and in SQL Server 2008 R2

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

FIX: SQL Server Agent job runs incorrectly if it is scheduled to run every hour on the last day of every week or month in SQL Server 2005 or in SQL Server 2008

FIX: Error 7359 when you run a query against a user-defined function or a view that uses a synonym on a linked server in SQL Server 2005 or SQL Server 2008

FIX: SQL Server 2008 stops responding when you alter or stop an Extended Events session

SQL Server 2008 SP2 CU4 (10.0.4285)  May 16, 2011

FIX: “A severe error occurred on the current command” error message when you use the Freetexttable or the Containstable function on an indexed view in a CTE query in SQL Server 2008 or in SQL Server 2008 R2

FIX: Computer stops responding when you create a database that contains a FILESTREAM filegroup in SQL Server 2008 or in SQL Server 2008 R2 if a third-party mini-filter driver is installed

FIX: A common table expression-based query that uses the CHANGETABLE function runs very slowly in SQL Server 2008 Service Pack 1 or SQL Server 2008 R2

FIX:A query that has a CONTAINS predicate in a WHERE clause takes a long time to compile in SQL Server 2008 or SQL Server 2008 R2

FIX: SQL Server Browser service periodically does not respond to incoming requests

FIX: An access violation occurs when you restore a database and run the sp_replcounters stored procedure at the same time on a server that is running SQL Server 2008

FIX: Database corruption if data compression enabled on a partitioned table in SQL Server 2008

SQL Server 2008 SP2 CU5 (10.0.4316)  July 18, 2011

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

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

SQL Server 2008 SP2 CU6 (10.0.4321)  September 19, 2011

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

FIX: Assertion failure or other issues occur when you run a DML query against a table or a view that has two indexes in SQL Server 2008

FIX: Certain change tracking functions and DMVs do not work correctly after an automatic database mirroring failover occurs in SQL Server 2008 R2

FIX: You receive an incorrect result when you run a query that uses the row_number function in SQL Server 2008

SQL Server 2008 SP2 CU7 (10.0.4323)  November 21, 2011

FIX: SQL Server Agent job randomly stops when you schedule the job to run past midnight on specific days in SQL Server 2005 or in SQL Server 2008

FIX: Backup fails in SQL Server 2008 or in SQL Server 2008 R2 if you enable change tracking on the database

FIX: Transaction log backup is created even though no valid full backup exists in SQL Server 2008

FIX: Access violation when a request reads a missing index in SQL Server 2008

FIX: Access violation when you run a DBCC CHECKDB command against a database that contains a table that has a spatial index in SQL Server 2008

FIX: Access violation when a query is compiled in SQL Server 2008

SQL Server 2008 SP2 CU8 (10.0.4326)  January 16, 2012

FIX: Access violation when you insert a record into a new empty partition in SQL Server 2008 or in SQL Server 2008 R2

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

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

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

FIX: Unnecessary updates to a subscriber after you reinitialize it if a new article is added to a publication in SQL Server 2008

SQL Server 2008 SP2 CU9 (10.0.4330)  March 19, 2012

FIX: A database page is copied into a database snapshot even though the page is not updated when you perform a read operation in SQL Server 2008

Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option

FIX: Backup operation fails on a SQL Server 2008 database after you enable change tracking

SQL Server 2008 SP2 CU10 (10.0.4332)  May 21, 2012

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

SQL Server 2008 SP2 CU11 (10.0.4333)  July 16, 2012

FIX: Event ID 322 error even though operations complete successfully in SQL Server 2008

Most of those hotfixes seem pretty significant to me (and this is just the Engine-related subset that I picked since Service Pack 2 RTM). 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 that may convince you and your organization to stay more current with your SQL Server Cumulative Updates. Keep in mind that SQL Server Cumulative Updates are actually cumulative, so if you install one, you will get all of the fixes for all of the previous Cumulative Updates for the Service Pack that you are on.