Most Recent Cumulative Updates for SQL Server

Here are the most recent cumulative updates for SQL Server 2012, 2008 R2 and 2008.

SQL Server 2012 RTM CU10 (11.0.2420)          released on October 21, 2013

SQL Server 2012 SP1 CU6 (11.0.3381)            released on September 16, 2013

If you are on SQL Server 2012, both RTM and Service Pack 1 are still a “supported service pack”, but this will change pretty soon. I am also wondering roughly when SQL Server 2012 SP2 is going to be released.  In the past, we have often seen public CTPs for SPs, but so far, I have not heard of one for SQL Server 2012 SP2. Despite this, I am thinking it should be pretty soon, probably before the end of 2013.

I do think that the SQL Server 2012 code base has settled down quite a bit, since we are not typically seeing 40-50 hotfixes in every single CU. There were only four hot fixes in SQL Server 2012 RTM CU10, so that reinforces my opinion that SQL Server 2012 SP1 CU6 is where you should be now, especially since the RTM branch will be retired pretty soon. I really hope that Microsoft will choose to get the SQL Server 2012 SP1 and SP2 branches synchronized as far as CU release dates go.

In my fantasy world, here is what would happen for SQL Server 2012:

“SQL Server 2012 SP2 RTM is released on November 5, 2013, and then SQL Server 2012 SP1 CU7 and SQL Server 2012 SP2 CU1 are both released on November 19, 2013. This allows people to move to the SP2 branch and not be behind or out of synch with the SP1 branch.”

 

SQL Server 2008 R2 SP2 CU9 (10.50.4295)      released on October 28, 2013

If you are on SQL Server 2008 R2, only Service Pack 2 is still a “supported service pack”. All other branches are retired.

 

SQL Server 2008 SP3 CU13 (10.0.5846)          released on September 16, 2013

If you are on SQL Server 2008, only Service Pack 3 is still a “supported service pack”. All other branches are retired.

I am still curious about whether there will be a SQL Server 2008 SP4 and/or a SQL Server 2008 R2 SP3 before both SQL Server 2008 and 2008 R2 fall out of mainstream support on July 8, 2014?  Especially for people who don’t believe in deploying cumulative updates, the SQL Server 2008 SP3 RTM build and the SQL Server 2008 R2 SP2 RTM build are going to be quite ancient (and missing a lot of important fixes) by next July.

So, to recap, if you are on SQL Server 2012, you should be on the SP1 branch by now (even though RTM is still supported). If you are on SQL Server 2008 R2, you should be on the SP2 branch, since SP1 and RTM are retired. If you are on SQL Server 2008, you should be on the SP3 branch, since all previous branches are retired. All of SQL Server 2005 is out of mainstream support, but if you are still on SQL Server 2005, you should be on SP4.

SQL Server 2008 R2 SP2 CU9

Microsoft has released SQL Server 2008 R2 SP2 CU9, which is Build 10.50.4295. This CU has eighteen hotfixes in the public fix list. Some of the more important ones are listed below:

FIX: Poor performance when SQL Server 2008 or SQL Server 2008 R2 requires memory from a local node in a NUMA environment

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: 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

If you are running SQL Server 2008 R2, you need to be on the SP2 branch, since the SP1 branch was retired earlier this year. If you are on SQL Server 2008 R2 SP2, I think you should be planning on getting on the latest CU9 as soon as it makes sense. That does not mean you install it in Production the day it is released, but rather, you have a set procedure and test plan that you follow (just as you would for any other type of change), that you actually execute on a regular basis.

Being proactive with important updates as part of a regular, well planned and tested maintenance procedure will reduce the amount of problems you have with your system over the long term.

CPU-Z Version 1.67 is Available

On October 12, 2013, Frank Delattre released CPU-Z 1.67, which has some support for some new processors and a new validation tool. Even though CPU-Z was originally developed for the hardware enthusiast over-clocking community, it actually is very valuable for server administrators and for database professionals. First, it lets you identify your exact processor model, with all of its specifications and instruction set support. Second, it helps confirm whether you have any form of power management in effect that is reducing the current speed of the processor cores on your database server.

This can be caused by your Windows Power Plan in Windows Server 2008 or newer. By default, the Power Plan is set to Balanced instead of High Performance like we want for a database server. This is easy to change, and goes into effect immediately without rebooting the server. After you make this change in Windows, you should use CPU-Z to see what your processor core speed is. If it is still throttled back, you are going to have to change your BIOS power management setting.

This is necessary because the Windows Power Plan can be overridden by hardware-level power management settings that are set in your main BIOS setup. You typically want to set your hardware-level power management to OS Control or to Max Performance in the BIOS. This will let your processors take advantage of Intel Turbo Boost or AMD Turbo Core, and it will let them run at their full base clock speed or above at all times. This is quite important for OLTP query performance, but have also seen it affect ETL performance. Many storage vendors, such as Fusion-io and LSI also recommend that you make these changes to avoid having the PCI-E expansion slots affected by power management.

Figure 1 shows my desktop processor running at full speed with Turbo Boost (see the Core Speed indicator at the bottom left quarter of the form), while Figure 2 shows the processor being throttled back due to power management.

image thumb CPU Z Version 1.67 is Available

Figure 1: Processor Core Speed without any Power Management

image thumb1 CPU Z Version 1.67 is Available

Figure 2: Processor Core Speed with Power Management in Effect

I have seen OLTP query performance improve by as much as 25% by making these simple changes. The level of improvement you will see depends on the exact processors you are using (newer processors like Haswell, Ivy Bridge, and Sandy Bridge handle power management better than older processors, such as Nehalem and Westmere), and whether or not you have other big performance bottlenecks.

SQL Server 2012 RTM Cumulative Update 10

Microsoft has released SQL Server 2012 RTM Cumulative Update 10 (Build 11.0.2420). This CU only has four fixes in the Public hotfix list. Two for SSAS, one for SSRS, and one for the Database Tuning Advisor (DTA). From a Database Engine perspective, there is not too much to get excited about for this particular cumulative update. This is likely to be one of the last (if not the last) cumulative updates for the RTM branch of SQL Server 2012. I am guessing that SQL Server 2012 Service Pack 2 should be showing up relatively soon.

Hopefully, most people have made the jump to SQL Server 2012 Service Pack 1 by now. If you are on the SP1 branch with a recent CU, I would wait until after SQL Server 2012 SP2 CU1 is released before you move to the SP2 branch. This is because the SP2 branch will have been frozen for a while (probably two-four months), so it will not include the hotfixes from the most recent cumulative updates for SQL Server 2012 SP1.

If you are one of those organizations that does not deploy cumulative updates (so you are still on SQL Server 2012 RTM or SQL Server 2012 SP1 RTM), I would plan on getting SQL Server 2012 SP2 RTM deployed as soon as possible after it becomes available, after you have done your necessary testing.

PASS Summit 2013 Summary

The PASS Summit 2013 was held in Charlotte, North Carolina, which is a big change from the usual location in Seattle. This new location may have been more successful in attracting what seemed to be an unusually high number of first-time attendees, which I think is a good thing. I heard fewer complaints during the week about travel time and time away from work from people who were from the east coast and from the southeast part of the United States. On the other hand, I heard a decent number of complaints about travel time and time away from work from people from other parts of the United States. Honestly, unless the PASS Summit is held in your home town, you are going to have to travel, so there is really no way to satisfy everyone regarding a location. Personally, I like Seattle, and I know my way around the downtown Seattle area pretty well, and it is easier for me to fly to Seattle, so I am perfectly happy if the Summit is in Seattle most years. Since I live near Denver, it is not really that hard for me to travel nearly anywhere in the United States.

I had a pre-con session called Scaling SQL Server 2012 on Monday, which went quite well (even though Paul Randal fired me on Twitter). Teaching/speaking all day by yourself is a lot more tiring than you might think, especially when you have a room full of smart SQL Server people in your session.

On Wednesday, I got to present Professor DMV in one of the two large ballrooms (which was also streamed on PASStv and recorded). That was a lot of fun to present to a large session, and I got a lot of questions during and after the presentation. There was also a lot of live commentary on Twitter while I was speaking. I think that most speakers appreciate when you give live Twitter commentary and feedback while they are speaking (even if they read it later). I certainly appreciate it!

On Friday, I got to present Storage Fundamentals for the DBA in one of the smaller (300 seat) rooms. I was pretty surprised to see people lined up, waiting to get into this session. That has never happened to me at the PASS Summit. I am more used to seeing people lined up waiting to get into to see people like Paul Randal or Kimberly Tripp. Later on Friday, I watched a very good session on virtualization performance from David Klee.

I think the people behind the PASS Summit 2013 did a great job of planning, organizing and running the event. This includes the PASS board members, the PASS employees, all of the PASS volunteers, and all of the other people who worked so hard to make the event a smoothly running success. I have a lot of respect for the people that do all of the work to make the PASS Summit possible each year.

It was great to see so many people that I mostly talk to online, and to meet a lot of new people in person. I had a lot of people come up to me throughout the week and thank me for the work I do on my DMV Diagnostic Information Queries, which was very gratifying. All of the demo scripts from all of the SQLskills speakers (Paul, Kimberly, Erin, Jon, and myself) are posted here.

Performance Related Fixes in Post-SQL Server 2012 SP1 Builds

There have already been six 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 early Cumulative Updates, as more people are using SQL Server 2012 over time. SQL Server 2012 SP1 has all of the fixes through SQL Server 2012 RTM CU2. 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 SQL Server 2012 Service Pack and Cumulative Update. Right now, that means Service Pack 1, CU6 (Build 11.0.3381).

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

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 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:

 

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

SQL Server 2012 RTM Cumulative Update 3  (Build 11.0.2332) , 35 total public hotfixes

FIX: Incorrect results when you run a parallel query that uses 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

 

Again, the idea here is to give you some 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.

Scaling SQL Server 2012 Pre-Conference Session

In just a couple of weeks, I will be giving a full day pre-conference session at the SQLPASS 2013 Conference in Charlotte, North Carolina. My pre-conference session will be on Monday, October 14, 2013. This pre-con’s going to be a lot of fun, and it will be a great way to kick off the SQLPASS 2013 Conference.

What you’ll learn

You’re a DBA, database developer, or system admin who must maintain a database server that is not performing and scaling well. You are not sure where the main scalability problems are or what you can do to solve them. The thought of picking out the best server and storage subsystem without making an expensive mistake makes you more than a little bit nervous.

This pre-conference session will cover the following topics and more:

  • Top scalability issues with SQL Server 2012
  • How you can postpone the scaling decision by finding and removing bottlenecks
  • How to use my SQL Server Diagnostic Information Queries to pinpoint performance issues
  • How to select and size your hardware and storage subsystem for maximum scalability
  • How to select hardware to get the best performance while minimizing your SQL Server 2012 licensing costs
  • How to use the scaling features built into SQL Server 2012 and 2014
  • How to scale up SQL Server 2012
  • How to use engineering techniques to scale out SQL Server 2012

Here is the full abstract:

Scaling SQL Server 2012

SQL Server implementations can quickly evolve and become more complex, forcing DBAs and developers to think about how they can scale their solution quickly and effectively. Scaling up is relatively easy (but can be expensive), while scaling out requires significant engineering time and effort. If you suggest hardware upgrades you may be accused of simply “throwing hardware at the problem”, and if you try to scale out, you may be thwarted by a lack of development resources or 3rd party software restrictions. As your database server nears its load capacity, what can you do? This session gives you concrete, practical advice on how to deal with this situation. Starting with your present workload, configuration and hardware, we will explore how to find and alleviate bottlenecks, whether they are workload related, configuration related, or hardware related. Next, we will cover how you can decide whether you should scale up or scale out your data tier. Once that decision is made, you will learn how to scale up properly, with nearly zero down-time. If you decide to scale out, you will learn about practical, production-ready techniques such as vertical partitioning, horizontal partitioning, and data dependent routing. We will also cover how to use middle-tier caching and other application techniques to increase your overall scalability.

How much does it cost?

When you register for the PASS Summit, my “Scaling SQL Server” pre-conference session is just $395.00. If you’ve already registered for the PASS 2013 Summit, email Shannon.Cunningham@sqlpass.org to take advantage of this opportunity.

SQL Server Diagnostic Information Queries for September 2013

I have made some minor updates and bug fixes for all of my SQL Server Diagnostic Information Queries for this month. I have also added a new version for SQL Server 2014.

Rather than having a separate blog post for each version, I’ll just put the links for all five versions here.

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

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.

The idea is that you would 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 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 spreadsheet. There are also some comments on how to interpret the results after each query.

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 databases in 80 compatibility mode, which breaks many DMV queries.

Please let me know what you think of these queries. Thanks!

How To Avoid Orphaned Database Users with SQL Server Authentication

One common issue that database administrators often run into is the old, familiar “orphaned” user problem. This happens when you use SQL Server Authentication to create a SQL Server login on your database server. When you do this, SQL Server generates a unique SID for that SQL Server login. After you create the SQL Server login, you typically create a database user in a user database on that server instance, and associate the database user with that SQL Server login.

This works fine until you try to restore that user database to another SQL Server instance. If you previously created a SQL Server login with the same UserID on the new server, the SID for that SQL Server login will not match the database user in the user database that you have restored (from the other database instance). Hence the term “orphaned” user.  This is an especially big issue if you are using database mirroring, since your database users will be orphans when you failover from one instance to the other instance. It is also an issue with log shipping, and it often comes up when you migrate from an old database server to a new database server.

There are several ways to fix this, but the best thing (outside of just using Windows Authentication and avoiding the problem), is to create the new server login using the same SID as on the original server. Just like you see below:

-- Get Sids for all SQL Server logins on the old server instance
SELECT name, [sid] 
FROM sys.server_principals
WHERE [type] = 's'; 

-- Create new SQL Login on new server instance
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLAppUser')
    DROP LOGIN SQLAppUser;
GO

-- Use the sid from the old server instance 
CREATE LOGIN SQLAppUser WITH PASSWORD = N'YourStrongPassword#', sid = 0x2F5B769F543973419BCEF78DE9FC1A64,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

New Versions of Useful Tools for SQL Server

There is a completely rewritten version of Geekbench, which is a very useful cross-platform processor and memory performance benchmark that you can use for comparing and validating processor and memory performance on anything from your laptop to a very expensive database server in just a few minutes, with no configuration of the benchmark needed. As the Primate Labs web site puts it:

Geekbench 3 is Primate Labs’ next-generation processor benchmark, with a new scoring system that separates single-core and multi-core performance, and new workloads that simulate real-world scenarios. Geekbench 3 makes it easier than ever to find out if your computer is up to speed.

There is also a new 1.66 version of CPU-Z, that has support for some upcoming processors, such as the Intel Xeon E5-2600 v2 series (Ivy Bridge-EP).

image thumb New Versions of Useful Tools for SQL Server