Performance and Stability Related Fixes in Post-SQL Server 2012 SP1 Builds

Updated to include SP1 CU11: There have already been eleven Cumulative Updates (CU) for the Service Pack 1 branch of SQL Server 2012. There have been a fairly high number of hotfixes in every one of these Cumulative Updates, as more people are using SQL Server 2012 over the past two years. SQL Server 2012 SP1 has all of the fixes through SQL Server 2012 RTM CU2. If you are running SQL Server 2012, I really think you should be running the latest SQL Server 2012 Service Pack and Cumulative Update. Right now, that means Service Pack 1, CU11 (Build 11.0.3449). I am advising people to hold off on SQL Server 2012 Service Pack 2 until SQL Server 2012 SP2 CU1 is released (which should be very soon).

This table shows the SP1 CU builds that have been released so far.

Build Description Release Date
11.0.3000 SP1 RTM November 7, 2012
11.0.3321 SP1 CU1 November 20, 2012
11.0.3339 SP1 CU2 January 21, 2013
11.0.3349 SP1 CU3 March 18, 2013
11.0.3368 SP1 CU4 May 30, 2013
11.0.3373 SP1 CU5 July 15, 2013
11.0.3381 SP1 CU6 September 16, 2013
11.0.3393 SP1 CU7 November 18, 2013
11.0.3401 SP1 CU8 January 20, 2014
11.0.3412 SP1 CU9 March 17, 2014
11.0.3431 SP1 CU10 May 19, 2014
11.0.3449 SP1 CU11 July 21, 2014
     

Table 1: SQL Server 2012 SP1 CU Builds

 

You can follow the KB article link below to see all of the CU builds for the SQL Server 2012 Service Pack 1 branch.

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

Like I did in my previous post, I decided to scan the hotfix list for all of the Cumulative Updates in the SP1 branch, looking for performance and general reliability-related fixes for the SQL Server Database Engine. I came up with the list below, but this listing 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.

Here are the fixes in the RTM branch, before Service Pack 1 was released (you will get these when you install SP1):

SQL Server 2012 RTM Cumulative Update 1  (Build 11.0.2316) , 65 total public hot fixes

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) , 37 total public hot fixes

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

Here are the fixes in the Service Pack 1 branch:

SQL Server 2012 SP1 Cumulative Update 1 (Build 11.0.3321), 44 total public hot fixes

FIX: “out of memory” error when you use ODBC with SQL Server 2012, SQL Server 2008 R2 or SQL Server 2008

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: Slow performance or deadlock when you restore a database and execute statements at the same time in SQL Server 2012

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

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

SQL Server 2012 SP1 Cumulative Update 2 (Build 11.0.3339), 50 total public hot fixes

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: The size of a database file is not reduced when you use the DBCC SHRINKFILE command in SQL Server 2008 R2 or in SQL Server 2012

FIX: High CPU usage when you query a binary large object column by using the NOLOCK hint in SQL Server 2008 R2 or in SQL Server 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: 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 or in SQL Server 2012

SQL Server 2012 experiences out-of-memory errors

FIX: Deadlocks occur when you execute a stored procedure to alter a temporary table if lock partitioning is enabled in SQL Server 2008 R2 or in SQL Server 2012

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

FIX: A “non-yielding” error occurs on a server for Service Broker Message Forwarding in SQL Server 2008 R2 or in SQL Server 2012

FIX: Error 17883 when you run a query on a server that has many CPUs and a large amount of memory in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 3 (Build 11.0.3349), 38 total public hot fixes

FIX: Access violation when you run a query that contains many constant values in an IN clause in SQL Server 2008 or in SQL Server 2012

FIX: Memory leak when you run queries against a temporary table in a nested stored procedure in SQL Server 2012

FIX: Poor performance in SQL Server 2012 when you run a SQL Server trace

FIX: You experience poor performance when you run a query against an RCSI-enabled table in SQL Server 2012

FIX: CPU spike when there is no load on a server after you install SQL Server 2012 on the server

SQL Server 2012 experiences performance issues in NUMA environments

SQL Server 2012 SP1 Cumulative Update 4 (Build 11.0.3368), 38 total public hot fixes

FIX: Out-of-memory errors related to a memory clerk in SQL Server 2012

FIX: Out of memory error when you build a columnstore index on partitioned tables in SQL Server 2012

Update that improves the Service Broker when you send messages to remote servers in SQL Server 2012 is available

An update is available for SQL Server 2012 Memory Management

FIX: “Non-yielding Scheduler” error occurs when you insert a row in SQL Server 2012

FIX: You may experience performance issues in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 5 (Build 11.0.3373), 27 total public hot fixes

FIX: Poor performance when you run a query that uses the LIKE operator in the WHERE clause in SQL Server 2008 R2

FIX: Non-yielding scheduler error when you run a stored procedure that uses a TVP in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 6 (Build 11.0.3381), 24 total public hot fixes

FIX: Slow performance on SQL Server 2012 linked server when you update the data together with different collation on the remote server

FIX: A memory leak occurs when you enable AlwaysOn Availability Groups or SQL Server failover cluster in Microsoft SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 7 (Build 11.0.3393), 47 total public hotfixes

FIX: Access violation when you trace an RPC event class by using SQL profiler or XEvents in SQL Server 2012

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, SQL Server 2008 R2 or SQL Server 2012

FIX: The threads are not scheduled evenly in SQL Server 2012 Standard Edition

A memory leak occurs when a SQL Server Native Client OLE DB provider application calls the Prepare method in SQL Server 2012

FIX: “Non-yielding Resource Monitor” when you run a workload that executes concurrent queries in SQL Server 2012

FIX: Function sys.fn_hadr_backup_is_preferred_replica costs almost all the CPU usage in Log Shipping in SQL Server 2012

FIX: Slow performance in SQL Server when you build an index on a spatial data type of a large table in a SQL Server 2012 instance

FIX: Slow performance in SQL Server 2012 when you build an index on a spatial data type of a large table

An access violation occurs when “sys.dm_db_index_physical_stats” is used in an IF EXISTS statement in an SQL query in SQL Server 2012

FIX: Suboptimal execution plan is generated when you run a query in SQL Server 2012

Slow SQL Server performance and a memory leak occurs after you apply Cumulative Update 3 for SQL Server 2012 Service Pack 1

FIX: Slow performance in SQL Server 2012 when you build an index on a spatial data type of a large table

Slow performance or error messages are logged when you have a transactional replication publisher server in SQL Server 2012

FIX: Insufficient system memory error occurs when you try to create an index in a char, varchar, or nvarchar type column in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 8 (Build 11.0.3401), 32 total public hotfixes

FIX: Data purity corruption in sys.sysbinobjs table in master database when you log on to SQL Server 2008 R2 or SQL Server 2012 by using the SA account and then run DBCC CHECKDB

FIX: Interrupted distributed query may return partial result set without any error in SQL Server 2008 R2 or in SQL Server 2012

FIX: Replication Log Reader Agent fails when you enable CDC and transactional replication for some columns in SQL Server 2008 R2 or SQL Server 2012

FIX: Query that you run against a partitioned table returns incorrect results in SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012

FIX: A small number of rows are sampled in SQL Server 2008 R2 or in SQL Server 2012 when table statistics are updated automatically or together with specified sampling rate

FIX: Access violation may occur when you query sys.dm_server_memory_dumps in SQL Server 2008 R2 or SQL Server 2012

FIX: Replication Queue Reader Agent crashes when you run transactional replication in SQL Server 2008 R2 or SQL Server 2012

FIX: Validation of service accounts takes a long time when you set up SQL Server 2012

FIX: The system function sys.fn_hadr_backup_is_preferred_replica does not work correctly after you have CU7 for SQL Server 2012 SP1 installed

FIX: The query deadlocks when the ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled in SQL Server 2012

FIX: The clustered index table may take longer than you expect to be rebuilt when you use the ALTER INDEX REBUILD statement in SQL Server 2008 or SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 9 (Build 11.0.3412), 30 total public hotfixes

FIX: Access violation in SQL Server internal deadlock monitor when you run multiple applications together with Multiple Active Result Sets enabled in SQL Server 2008 R2 or in SQL Server 2012

FIX: Access violation in replication Distribution Agent in SQL Server 2008 R2 or SQL Server 2012 Transactional Replication

FIX: Poor performance when many table-valued parameters are used in SQL Server 2008 R2 or SQL Server 2012

FIX: Merge Agent fails or you experience non-convergence when you use a custom stored procedure conflict resolver in SQL Server 2008 or SQL Server 2008 R2 or SQL Server 2012

FIX: Performance problems occur when database lock activity increases in SQL Server 2012

FIX: Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012

FIX: Operating system version information that is returned from SQL Server 2008 or SQL Server 2008 R2 or SQL Server 2012 is incorrect in Windows 8.1

FIX: Performance decreases after an ALTER INDEX…ONLINE operation is aborted in SQL Server 2012

FIX: Access violation may occur when trigger query joins large dataset in deleted/inserted table and runs in parallel in SQL server 2012

FIX: Database shows “recovery pending” state when you use TDE with EKM provider in SQL Server 2012

FIX: Incorrect usage of built-in FORMAT function brings down SQL Server 2012 Instance

FIX: SQL Server 2012 instance shuts down when you join database as secondary replica during AlwaysOn Availability Groups configuration

FIX: Merge agent fails when you apply snapshot in SQL Server 2012 instance if cross database dependencies are present

FIX: Distribution Agent skips applying sp_MSins_, sp_MSupd_, sp_MSdel_ stored procedures on a Subscriber that is initialized with Backup in SQL Server 2012

FIX: Access violation occurs when you update a table that has an XML calculated column in SQL Server 2012

FIX: SQL Server takes long time to open the databases after the recovery phase when the number of databases or database files or both is large in SQL Server 2012

FIX: Message is not removed from transmission queue even though the ACK is received successfully in an AlwaysOn availability group in SQL Server 2012

FIX: Non-yielding scheduler tries to close the listener for availability group in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 10 (Build 11.0.3431), 36 total public hotfixes

FIX: Intense query compilation workload does not scale with growing number of cores on NUMA hardware and results in CPU saturation in SQL Server 2012

FIX: Assertion failure when you execute a query specifying TOP N and ORDER BY in SQL Server 2008 R2 or SQL Server 2012

FIX: Access violation occurs when you run CHECKTABLE or CHECKDB against a table that has persisted computed columns in SQL Server 2008 R2 or SQL Server 2012

FIX: Incorrect results when you run queries that contain UNION operator by using parallel query plan in SQL Server 2012

FIX: An access violation occurs when you execute update query on a table that has a DML trigger in SQL Server 2008 R2 or SQL Server 2012

FIX: Access violation when the start offset is larger than the string length in function Substring in SQL Server 2012

FIX: Poor cardinality estimation when the ascending key column is branded as stationary in SQL Server 2012

FIX: Poor performance on I/O when you execute select into temporary table operation in SQL Server 2012

FIX: Parallel deadlock or self-deadlock occurs when you run a query that results in parallelism in SQL Server 2012

SQL Server 2012 SP1 Cumulative Update 11 (Build 11.0.3449), 32 total public hotfixes

FIX: “Cannot insert duplicate key” error occurs in Spool operation when you update a table in SQL Server 2012

FIX: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012

FIX: Memory leak occurs when you start and stop an XEvent session repeatedly in SQL Server 2012

FIX: Cannot reclaim unused space by using shrink operation in the table that contains a LOB column in SQL Server

FIX: Fatal exception when you run a query that contains CLR functions against an indexed view in SQL Server 2012

FIX: Undetected deadlock occurs when you use a sequence object in SQL Server 2012

FIX: Data corruption occurs in clustered index when you run online index rebuild in SQL Server 2012 or SQL Server 2014

FIX: The database sticks in role configuration phase when you perform a manual failover of a mirrored database

FIX: Error when you execute a Transact-SQL query that contains a long case statement in SQL Server 2012

FIX: Error when you insert a record into a FileTable or FILESTREAM in SQL Server 2012

FIX: Log Reader Agent crashes during initialization when you use transactional replication in SQL Server 2012

FIX: Poor performance when you create spatial index on Point data in SQL Server 2012

FIX: Log Reader Agent fails when you upgrade from SQL Server 2008 to SQL Server 2012

FIX: A severe error occurs when you run a query that uses a hash join parallel execution plan in SQL Server 2012

  

Once again, the idea here is to give you a lot of concrete reasons to want to stay current with the latest SQL Server 2012 SP and CU, by pointing out some of the more valuable fixes in each CU in the Service Pack 1 branch.

SQL Server Diagnostic Information Queries for July 2014

I revised several of the queries this month in all five versions of the script. There are also two new queries for SQL Server 2014, and one new query in the SQL Server 2008 through SQL Server 2012 versions of the script.

Rather than having a separate blog post for each version, I have just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual query script, and the one below on the right is the matching blank results spreadsheet.  

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid in SSMS to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet. There are also some comments on how to interpret the results after each query.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

It is also very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server.

If you want to understand how to better run and interpret these queries, you should consider listening to my latest Pluralsight course, which is SQL Server 2014 DMV Diagnostic Queries – Part 1. This course is short and to the point (only 67 minutes), and I think you will enjoy it!

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

SQL Server 2014 RTM Cumulative Update 2

Microsoft has released SQL Server 2014 RTM Cumulative Update 2, which is Build 12.0.2370.0. It has 48 hotfixes in the public fix list.

This is one of the more interesting fixes:

FIX: Intense query compilation workload does not scale with growing number of cores on NUMA hardware and results in CPU saturation in SQL Server

As usual, I recommend that you install this CU if you are using SQL Server 2014, because of the relatively high number of significant fixes in this build.

Upcoming SQLskills Training Classes

As Paul announced last week, SQLskills has revamped the previous IE1 and IE2 classes to focus even more on performance tuning and optimization. If you are interested in how to select and configure database hardware for various workloads as well as how to configure your operating system and SQL Server for the best performance and scalability, you should consider attending my IEHW class in Chicago on October 9-10, 2014.

This is the schedule for the next round of SQLskills training classes:

2014 Immersion Events
Chicago, IL

  • October 6-8, 2014: IE0: Immersion Event for the Accidental/Junior DBA
  • October 9-10, 2014: IEHW: Immersion Event on SQL Server Hardware
  • October 6-10, 2014: IEPTO1: Immersion Event on Performance Tuning and Optimization – Part 1 (formerly IE1)
  • October 13-17, 2014: IEPTO2: Immersion Event on Performance Tuning and Optimization – Part 2 (formerly IE2)

See here for the main Immersion Event Calendar page that allows you to drill through to each class for more details and registration links.

Special Pricing Options and Referrals
  • Past attendee price: If you’ve attended an Immersion Event in the past, you can register any time for 75% of the full price ($1,099). Please contact us for instructions.
  • Refer someone: If you know someone who would benefit from this class, refer them to us and when they register, we’ll give you a $50 Amazon gift card. They or you just need to let us know you’re referring them, and when they register, we’ll match them to your referral and send you the gift card.

Fall SQLintersection
This year our Fall SQLintersection conference will be the week of November 10th in Las Vegas. See here for details. Don’t forget to use the discount code “SQLskills” (without the quotes and it isn’t case-sensitive) and you can save $50 off registration! I hope to see you there!

Learn About SQL Server Hardware This October in Chicago

On October 9-10, 2014, I will be teaching IEHW: Immersion Event on SQL Server Hardware in Chicago, IL. This is a great opportunity for you to learn how to properly select and configure your server hardware and storage subsystem to get the best performance and scalability for the lowest SQL Server 2012/2014 licensing costs. You will also learn how to properly configure your hardware, storage subsystem, operating system, and SQL Server itself for the best performance and scalability for your workload. Attendees of this class will learn how to analyze, select, and size their database server hardware and storage subsystems for different types of SQL Server workloads in order to get the best performance and scalability while minimizing their SQL Server 2012/2014 license costs.

Since SQL Server 2014 is available, and Windows Server 2012 R2 has been available for over nine months, and we have new server models with the 2nd generation Intel Xeon E5 and E7 processor families, I think that it is going to make a lot of sense for many organizations to do a complete data platform refresh sometime during 2014/2015. By making wise hardware selection choices, you can easily save so much money on your SQL Server 2012/2014 licensing costs, that your actual server hardware is free. This is not an exaggeration! 

This two-day SQL Server hardware training class explains the core fundamentals and deeper details of database server hardware and storage subsystems for SQL Server database professionals. Many database professionals are completely unfamiliar with the details and nuances of modern server hardware and storage subsystems, while many server and storage administrators are completely unfamiliar with the specific workload demands of a SQL Server database server. Taking this course will allow you to bridge that gap.

This class also covers how to properly configure and benchmark your database server hardware and storage subsystems, along with how to properly install and configure the operating system and SQL Server for the best performance and reliability. The class will show you how to diagnose and troubleshoot hardware and storage related performance issues, and will include coverage of how virtualization interacts with your database server hardware and storage subsystem. Note: the primary audience for this class is SQL Server database professionals, not general system/server admins who are already familiar with server/storage hardware.

I think it is very important for database administrators to know as much as possible about the critical details of their server hardware and storage subsystem, rather than trusting their fate to “Shon the server guy”, who may or may not know that much about modern server hardware (maybe he is a networking specialist). Even if Shon is very knowledgeable about hardware, he may not understand the different demands that SQL Server will create with different types of workloads. I want you to be able to successfully make the case for selecting the best hardware and storage subsystem components for your workload and budget.  You can read more about the registration details here.

Special Pricing Options and Referrals
  • Past attendee price: If you’ve attended an Immersion Event in the past, you can register any time for 75% of the full price ($1,099). Please contact us for instructions.
  • Refer someone: If you know someone who would benefit from this class, refer them to us and when they register, we’ll give you a $50 Amazon gift card. They or you just need to let us know you’re referring them, and when they register, we’ll match them to your referral and send you the gift card.

SQL Server Diagnostic Information Queries for June 2014

I added several new queries this month to all five versions of the script. I also spent quite a bit of time getting all five versions back in synch as much as possible, as far as my comments and other minor differences that had cropped up over the last few months.

Rather than having a separate blog post for each version, I have just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual query script, and the one below on the right is the matching blank results spreadsheet.  

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions). You need to click on the top left square of the results grid in SSMS to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet. There are also some comments on how to interpret the results after each query.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

It is also very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server.

If you want to understand how to better run and interpret these queries, you should consider listening to my latest Pluralsight course, which is SQL Server 2014 DMV Diagnostic Queries – Part 1. This course is short and to the point (only 67 minutes), and I think you will enjoy it!

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

SQL Server 2008 Service Pack 3 Cumulative Update 17

On May 19, 2014, Microsoft released SQL Server 2008 Service Pack 3 Cumulative Update 17, which is Build 10.00.5861.0. This Cumulative Update has nine hotfixes in the public fix list, most of them for the Database Engine.

The most interesting one to me is this one:

FIX: Performance problems occur when database lock activity increases in SQL Server

This issue is going to affect systems with 16 or more logical processors, running on SQL Server 2008 or newer. The fix has been released for SQL Server 2008, SQL Server 2012 and SQL Server 2014, but not for SQL Server 2008 R2 (since SQL Server 2008 R2 SP2 CU13 has not been released yet). This hotfix introduces DATABASE lock partitioning when trace flag T1236 is enabled at startup.

One thing that you may not be aware of is that this is the final Cumulative Update for SQL Server 2008. As Microsoft states:

Note This is the last cumulative update for SQL Server 2008. Mainstream support for SQL Server 2008 ends on July 8, 2014. For more information about the lifecycle for SQL Server, check out the Microsoft product support life cycle information

So far, Microsoft has not announced their plans about whether there will be a SQL Server 2008 Service Pack 4. While you might assume that it is now too late to affect Microsoft’s decision process, this is actually not the case. First, you can comment on the SQL Server Release Services Blog post that I linked above.

Second, you can up vote my Microsoft Connect item on this subject. Even more effective is if you write a polite, well-reasoned comment on that Connect Item, explaining why you care whether or not Microsoft releases a final set of Service Packs for SQL Server 2008 and SQL Server 2008 R2. Try to explain how this decision will affect you and your customers.

Taking just a few minutes to do these items is very, very helpful to this effort!  You should also feel free to blog and tweet about this to help spread the word and gather more public support.

Microsoft DreamSpark for Students and Educators

Back in 2008, Microsoft rolled out the DreamSpark program as a means to get Microsoft software design and development tools in the hands of students at no charge. It was initially only for university or college students, but it has been expanded to also cover high school students. Basically, this means that any high school, college student, or faculty member (in most major countries) can download copies of many Microsoft operating systems, servers, and development tools for free.  If you fall into one of those categories, it is pretty easy to get signed up and verified into the program, so you can get your hands on the software for free.

Of course Microsoft is not doing this simply for altruistic reasons. They want more people to be familiar with Microsoft products and technologies, so that they are more likely to use them and recommend them in the future. There is nothing wrong with that, and if you are a student or educator, you might as well take advantage of the program!

Some of the most valuable software-related items are Visual Studio 2013 Professional, Windows Server 2012 R2 Standard, and SQL Server 2014 Developer Edition. You can also get a free 90-day subscription to Pluralsight On-Demand, a number of free eBooks from Microsoft Press, along with discount vouchers for Microsoft Certification exams.

The Microsoft DreamSpark site has more details about the program.

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

As you may be aware, Microsoft released SQL Server 2008 Service Pack 3 back around October of 2011. Even though there has not been an official announcement yet from Microsoft, it seems likely that there will not be a SQL Server 2008 Service Pack 4 release before SQL Server 2008 (and SQL Server 2008 R2) go out of Mainstream support on July 8, 2014.

If you have moved to SQL Server 2008 Service Pack 3 (which is the only currently supported Service Pack for SQL Server 2008) and are patiently waiting for SQL Server 2008 Service Pack 4, you might want to know that Microsoft has announced that there will be a SQL Server 2008 Service Pack 4, that will be available sometime after July 8, 2014. This final Service Pack will include all of the fixes through SQL Server 2008 Service Pack 3 Cumulative Update 17 (and nothing else in addition).

Personally, I think most organizations would be much better off by moving to the latest Service Pack 3 Cumulative Update now, or going to Service Pack 4 when it becomes available. In order to try to convince more people that this is the right thing to do, I have put together this list of what I consider to be the more important hotfixes that have been included in each post-SQL Server 2008 Service Pack 3 Cumulative Update. This list is very Database Engine focused, and completely arbitrary on my part. If you are running SQL Server 2008, you should look at the KB articles for each Cumulative Update (which are linked below), and look for hotfixes that are most relevant to your situation and what SQL Server components that you are using.

Updated to include SQL Server 2008 SP3 CU17. There will not be a SQL Server 2008 SP3 CU18 release, CU17 is the final CU for SQL Server 2008.

 

SQL Server 2008 SP3 CU1 (10.0.5766)  October 17, 2011 (19 public fixes)

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

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

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

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 SP3 CU2 (10.0.5768)  November 21, 2011 (2 public fixes)

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

 

SQL Server 2008 SP3 CU3 (10.0.5770)  January 16, 2012 (14 public fixes)

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 or in SQL Server 2008 R2

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

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

 

SQL Server 2008 SP3 CU4 (10.0.5775)  March 19, 2012 (10 public fixes)

FIX: “A time-out occurred while waiting for buffer latch” error when many transactions concurrently update a database in SQL Server 2008 R2 or in SQL Server 2008 if the database uses the snapshot isolation level

FIX: Transaction log backup is created even though the full backup is canceled in SQL Server 2008 or in SQL Server 2008 R2

FIX: ghost_record_count values keep increasing in SQL Server 2008 R2 or 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 SP3 CU5 (10.0.5785) May 21, 2012 (4 public hotfixes)

FIX: Slow performance when you restore a database in SQL Server 2008 R2 or in SQL Server 2008 if CDC is enabled

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

 

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

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

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 or 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: 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 (11 public hotfixes)

FIX: “out of memory” error when you use ODBC with SQL Server 2012 or SQL Server 2008

FIX: Error is logged when you start SQL Server 2008 or when a client sends a request to the program

FIX: High CPU usage when you execute an UPDATE statement that includes a “WHERE CURRENT OF <cursor>” clause in SQL Server 2008

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 or SQL Server 2008

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 (9 public hotfixes)

FIX: Incorrect result is returned when you query an indexed view by using the NOEXPAND hint in SQL Server 2008

Support for the varchar(max), nvarchar(max), and varbinary(max) data types is added in most OLE DB Providers in SQL Server 2008

FIX: “Non-yielding Scheduler” error message when you insert a record that contains large object (LOB) data into a snapshot database in SQL Server 2008

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

A database transaction log continues to grow after you upgrade to SQL Server 2008 SP2 or SQL Server 2008 SP3

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

 

SQL Server 2008 SP3 CU9 (10.0.5829)  January 21, 2013 (3 public hotfixes)

FIX: Installation or upgrade process fails when you install or upgrade a SQL Server 2008 clustered instance on mount points

FIX: Access violation when you run a query that contains many constant values in an IN clause in SQL Server 2008

 

SQL Server 2008 SP3 CU10 (10.0.5835)  March 19, 2013 (8 public hotfixes)

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 or SQL Server 2008

FIX: Poor performance when table-valued functions use many table variables in SQL Server 2008 R2 or SQL Server 2008

FIX: Access violation when you run DML statements against a table that has partitioned indexes in SQL Server 2008 R2, in SQL Server 2008 or in SQL Server 2012

FIX: Data corruption occurs when LOB data is loaded into a Transact-SQL variable in SQL Server 2008 R2 or in SQL Server 2008

FIX: Index size increases significantly after you rebuild the index online and RCSI is enabled in SQL Server 2008

 

SQL Server 2008 SP3 CU11 (10.0.5841)  May 20, 2013 (6 public hotfixes)

FIX: “Access is denied. [0x80070005]” error message occurs in SSCM when you try to change the password of an account of SQL Server 2008 or SQL Server 2008 R2 in Windows Vista or in a later version of Windows

FIX: SQL Server may freeze when an instance of SQL Server 2012, SQL Server 2008 or SQL Server 2008 R2 is shut down

 

SQL Server 2008 SP3 CU12 (10.0.5844)  July 15, 2013 (6 public hotfixes)

FIX: A query that runs against a DMV enters an infinite loop in SQL Server 2008 R2, SQL Server 2008 or SQL Server 2012

FIX: SQL Server performance issues in NUMA environments

 

SQL Server 2008 SP3 CU13 (10.0.5846)  September 16, 2013 (2 public hotfixes)

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

 

SQL Server 2008 SP3 CU14 (10.0.5848)  November 18, 2013 (6 public hotfixes)

FIX: You receive an incorrect result when you run a query that uses some ranking functions in SQL Server 2008

FIX: Query that you run against a partitioned table returns incorrect results in SQL Server 2008

 

SQL Server 2008 SP3 CU15 (10.0.5850)  January 20, 2014 (3 public hotfixes)

FIX: “Installer terminated prematurely” error when you install SQL Server 2008 SP3

 

SQL Server 2008 SP3 CU16 (10.0.5852)  March 17, 2014 (2 public hotfixes)

FIX: Operating system version information that is returned from SQL Server 2008 or SQL Server 2008 R2 or SQL Server 2012 is incorrect in Windows 8.1

 

SQL Server 2008 SP3 CU17 (10.0.5861) May 19, 2014 (9 public hotfixes)

FIX: Performance problems occur when database lock activity increases in SQL Server

FIX: Incorrect results when you execute a full-text search with a phrase that contains a hyphen in SQL Server 2008

FIX: Cannot reclaim free up space by using shrink operation in the table that contains a LOB column in SQL Server 2008

New Azure SQL Database Service Tiers

Microsoft’s Eron Kelly has recently blogged about Azure SQL Database introduces new service tiers, where the existing Web and Business service tiers will be replaced in twelve months, as they are gradually replaced by six new service tiers, including Basic, Standard 1, Standard 2, Premium 1, Premium 2, and Premium 3. There will be differences in the “self-recovery” level and geo-replication levels across the SKUs.

Here are a couple of new acronyms for you to learn:

Database Throughput Unit (DTU): The resources powering each performance level are represented in DTUs. It combines CPU, memory, physical reads, and transaction log writes into a single unit. A performance level with 5 DTUs has five times more power than a performance level with 1 DTU. The “Database Throughput Unit” (DTU) represents database power and is meant to replace hardware specifications in the context of Azure SQL Database. 

Azure SQL Database Benchmark (ASDB): ASDB measures the actual throughput of a performance level by using a mix of database operations which occur most frequently in online transaction processing (OLTP) workloads.

Table 1 shows some relevant information about these new Azure SQL Database service tiers.

Service Tier Monthly Cost/DB Database Size Limit DTU/Database ASDB Transactions/Minute
Basic $4.99 2GB 1 DTU 58
Standard 1 $40.00 250GB 5 DTU 283
Standard 2 $200.00 250GB 25 DTU 1,470
Premium 1 $930.00 500GB 100 DTU 5,880
Premium 2 $1,860.00 500GB 200 DTU 11,520
Premium 3 $7,440.00 500GB 800 DTU 43,800

Table 1: Azure SQL Database Service Tiers

While the new service tiers are in a “preview” status, pricing will be 50% lower than what is shown in Table 1. You can read more about pricing details here. I am most interested in exploring the information disclosed in the new Azure SQL Database Service Tiers and Performance Levels, blog post, since it give us some more concrete information about what a DTU is. There is also some more detailed information about the new ASDB in the Azure SQL Database Benchmark Overview.