SQL Server 2008 R2 RTM Cumulative Update 11

Posted on December 26, 2011 by Glenn Berry

On December 19, 2011, Microsoft released SQL Server 2008 R2 RTM Cumulative Update 11 (CU11), which is Build 10.50.1807.0. I count 22 fixes in this Cumulative Update. This CU is only for the SQL Server 2008 R2 RTM branch, so it will not work if you have already installed SQL Server 2008 R2 Service Pack 1 (Build 2500) or later. It also will not work if you are running any build of SQL Server 2008, which is an earlier major version of SQL Server. If you are still on the RTM  branch of SQL Server 2008 R2, you should be pushing your organization to get on the SP1 branch. This is for two main reasons. First, Microsoft added a number of useful DMVs in SQL Server 2008 R2 Service Pack 1 that make it a little easier to manage your database servers. Second, we are getting ever closer to the point where Microsoft will retire the RTM branch of SQL Server 2008 R2. If that happens, and you are still on the RTM branch, you will only get limited support if you ever need to open a support case with Microsoft Support. Don’t let that happen to you!

Posted in Microsoft, SQL Server 2008 R2 | Tagged | Leave a comment

SQL Server 2008 R2 SP1 Cumulative Update 4

Posted on December 26, 2011 by Glenn Berry

On December 19, 2011, Microsoft released SQL Server 2008 R2 SP1 Cumulative Update 4 (CU4), which is Build 10.50.2796. I count 52 fixes in the fix list for this Cumulative Update, which is an unusually high number for a CU. Keep in mind that this CU is only for SQL Server 2008 R2 Service Pack 1. If you are still on the RTM branch, you will have to install SQL Server 2008 R2 Service Pack 1 before you install this CU. It also will not work on any build of SQL Server 2008 (which is an earlier “major” version of SQL Server).

Here are lists of the SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released, and the SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released.   My little SQL Server 2008 R2 Build List below might also be helpful.

 

— SQL Server 2008 R2 Builds                                SQL Server 2008 R2 SP1 Builds
— Build                   Description                               Build                Description
— 10.50.1092        August 2009 CTP2       
— 10.50.1352        November 2009 CTP3
— 10.50.1450        Release Candidate
— 10.50.1600        RTM
— 10.50.1702        RTM CU1
— 10.50.1720        RTM CU2
— 10.50.1734        RTM CU3
— 10.50.1746        RTM CU4
— 10.50.1753        RTM CU5
— 10.50.1765        RTM CU6               —>            10.50.2500      SP1 RTM
— 10.50.1777        RTM CU7
— 10.50.1797        RTM CU8               —>            10.50.2769      SP1 CU1
— 10.50.1804        RTM CU9               —>            10.50.2772      SP1 CU2
— 10.50.1807        RTM CU10            —>            10.50.2789      SP1 CU3
— 10.50.1809        RTM CU11            —>            10.50.2796      SP1 CU4       

Posted in Microsoft, SQL Server 2008 R2 | Tagged | 2 Comments

SQL Server 2012 Diagnostic Information Queries (December 2011)

Posted on December 19, 2011 by Glenn Berry

Since the final RTM release of SQL Server is getting ever closer, I thought it was a good time to release an updated version of my SQL Server 2012 Diagnostic Information Queries. These queries are meant to be used with SQL Server 2012, but many of them will also work with SQL Server 2008 R2 SP1 or later. There are a few breaking changes in some of the DMVs between SQL Server 2008/2008 R2 and SQL Server 2012.  Some of the queries in this set will also work with older versions of SQL Server.

The main changes for this version are some additional comments on how to interpret the results of the queries, plus some changes in the order of the queries. There was also some minor cleanup of several of the queries. As always, you will need VIEW SERVER STATE permission to run most of these queries. I recommend that you run them one at a time, after reading the directions and interpretation comments.

Posted in SQL Server 2012, SQL Server Denali | Tagged , | 3 Comments

CPU-Z 1.59 Released

Posted on December 12, 2011 by Glenn Berry

One of my favorite free tools for getting some processor and memory specific information from a Windows computer (whether is is a laptop or a database server), is CPU-Z.  It gives you a lot of very useful, accurate information about your processor(s) and memory, including what the current core speed of your processor is (compared to its rated speed). This can help you accurately determine whether your processor cores are being throttled back (to save electrical power usage) with some form of power management, either from the hardware itself or from the operating system.

In Figure 1, you can see my Intel Core i5 650, which is rated at 3.2GHz, is actually running at 3325.5MHz, which indicates that it is running at its full rated speed, plus 125.5MHz more, due to the effect of Intel Turbo Boost.

The latest release, which is version 1.59 of CPU-Z, was released on November 23, 2011.

 

image

Figure 1: CPU-Z 1.59

 

I have been using previous versions of this tool on production systems for many years, and it has never caused a problem for me. I think it is quite safe. If you are worried about using it, then you should not use it.

Posted in Computer Hardware, Processors | Tagged | 2 Comments

Scaling SQL Server – SQL Saturday #104 Precon

Posted on December 3, 2011 by Glenn Berry

I will be presenting a day long preconference session on Scaling SQL Server for SQLSaturday #104 on Friday, January 6, 2012 from 8:00 AM to 5:00 PM (MT) in Colorado Springs, CO. This will be a revised and expanded version of the half-day session I gave at the PASS 2011 Summit in Seattle. Here is the abstract and biography for the session.

Scaling SQL Server

How can you scale SQL Server? 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.

Biography

Glenn works as a Database Architect at Avalara in Parker, CO. He has been a SQL Server MVP since 2007, and he has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, hardware selection, full text search, and SQL Azure. He is also an Adjunct Faculty member at University College – University of Denver, where has been teaching since 2000. He has completed the Master Teacher Program at Denver University – University College. He is heavily involved in the SQL Server community, and is a frequent speaker at user groups, SQL Saturdays, and the PASS Community Summit. He is the author of the book SQL Server Hardware, and he wrote chapters for SQL Server MVP Deep Dive and SQL Server MVP Deep Dives Volume 2 books. Glenn blogs regularly at https://sqlserverperformance.wordpress.com. He is active on Twitter as @GlennAlanBerry.

 

This precon session should be a lot of fun, and it will have the advantage of me not being exhausted like I was when I presented the half day version of it on the last day of the PASS 2011 Summit. I hope to see you there!

Posted in Computer Hardware, PASS, Processors, SQL Server 2008 R2, SQL Server 2012, Teaching | Tagged | 1 Comment

Tuesday Hardware Tidbits, Nov 29, 2011

Posted on November 29, 2011 by Glenn Berry

Since Microsoft is moving to core-based licensing for SQL Server 2012 Enterprise Edition, with a minimum of four physical core licenses per physical socket, I have had a few questions about what is the “best” quad-core processor to choose for SQL Server 2012 (in order to minimize your SQL Server 2012 licensing costs).  Right now, this would be the 3.6GHz, Intel Xeon X5687 that has 12MB of L3 cache and can Turbo Boost to 3.86GHz. This processor will give you the best single-threaded OLTP performance in a two-socket server, while staying with only four physical cores. It will not have as much scalability as the 3.46GHz Intel Xeon X5690 (which has six physical cores), but it would save you $27,496 in SQL Server 2012 licensing costs (at full retail) compared to using the six-core Xeon X5690 in the exact same server.

In Q1 of 2012, you will want to keep an eye on the quad-core, 3.3GHz Xeon E5-2643 with 10MB of L3 cache, which is part of the 32nm Sandy Bridge-EP family. Even though they have a slightly lower clock speed and a smaller L3 cache than the Xeon X5687, they should do much better for database workloads because of their improved quad-channel memory controller and support for PCI-E 3.0, among other things.

On the Intel desktop processor front, there has been a lot of leaked information about the upcoming (Q2 2012) 22nm Ivy Bridge processors over the past few days. Ivy Bridge is a Tick release that takes the existing Sandy Bridge architecture and shrinks it down with a move to the 22nm manufacturing process. Some initial benchmarks show anywhere from a 7%-25% application performance increase at the same clock speed, while power usage is also reduced. There are much larger increases in integrated graphics performance, which is plenty good enough for non-3D gaming usage. If you are thinking about buying a new desktop system, you might want to hold off until April 2012 (unless you really need something now).

Posted in Computer Hardware, Processors, SQL Server 2012, SQL Server Denali | Tagged , | 1 Comment

Promoted to Master Group By Thomas LaRock

Posted on November 28, 2011 by Glenn Berry

SQLRockstar Thomas LaRock has updated his blogger rankings this month, and I was pleasantly surprised to see that he had moved me up from the Model group to the Master group. This is quite an honor, since Thomas spends quite a bit of time monitoring and evaluating the content from some of the top SQL Server bloggers. In case you have never heard of Thomas’s rankings, this might explain it a little better.

The Ranking list is a great start for finding SQL Server bloggers that you probably want to read (and people to follow on Twitter). One of the best things about the SQL Server Community is the amount of good, free information that people take the time to write and push out to the world. Reading good quality blogs and being active in the SQL Server Community on Twitter is a very good idea for your professional development.  You are not alone in your cubicle, with only Google and Bing to help you!

 

badge_mstr

Posted in SQL Server 2008 R2, SQL Server 2012, Teaching | Tagged | 3 Comments

Hotfix for SQL Server 2008/2008 R2 Periodically Does Not Accept Connections Bug

Posted on November 26, 2011 by Glenn Berry

Microsoft has released a hotfix for a very frustrating issue that plagued me for a couple of years at NewsGator.  I recently became reacquainted with this old problem with Error: 18056, Severity: 20, State: 29. Basically what happens is that a SQL Server 2008 or 2008 R2 database server that is under  absolutely no CPU or memory stress suddenly stops accepting connections from your application and web servers. You will get a number of errors in the SQL Server error log, like you see below:

Date  11/18/2011 8:42:40 PM
Log  SQL Server (Current – 11/18/2011 9:00:00 PM)

Source  spid81

Message
Error: 18056, Severity: 20, State: 29.

Date  11/18/2011 8:42:40 PM
Log  SQL Server (Current – 11/18/2011 9:00:00 PM)

Source  spid81

Message
The client was unable to reuse a session with SPID 81, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

While this is going on, no middle-tier servers can connect to the SQL Server instance in question. Quite often, you as the DBA will not be able to make a new connection to the server in question either, using SSMS.  Your CPU utilization will go down to zero, with seemingly no activity happening, and the SQL Server Service will continue to run just fine, with no cluster failover or database mirroring failover being triggered. Essentially, your database instance seems to be pouting and not talking to anyone, like an unruly two-year old…  Usually, this problem clears itself up with no intervention within anywhere from one to ten minutes, but occasionally it requires restarting the SQL Server Service to resolve.

We used to see this issue periodically at NewsGator, starting back in 2009 on SQL Server 2008. There was no change or improvement as we moved to SQL Server 2008 R2. I had previously filed a couple of Connect items about it, opened a CSS case, etc., with no final resolution from Microsoft.  Here are some Connect items that describe the issue in more detail:

SQL Server 2008 Periodically Does Not Accept Connections

SQL Server 2008 SP1 CU6 Periodically Does Not Accept Connections  

SQL Server 2008 R2 Does Not Accept Connections

Increasing the default MaxWorkerThreads instance configuration setting seemed to mitigate the issue somewhat, when I first started seeing the issue.  Another change that seemed to help reduce the frequency of the issue was lowering the MaxServerMemory instance configuration setting by a few GB lower than you would otherwise have it set at. Lots of people I know and respect in the SQL Server community have also run into this over the last couple of years. Bob Dorr talked about this back in August 2010 and a Microsoft Escalation Engineer named Tejas Shah talked about it in May 2010.

After this background and history, it seems that the hotfix that I linked to in the first sentence of this blog post corrects the issue. I have not been able to deploy the fix yet on my particular production server where I recently saw the problem, but a good friend of mine from Microsoft has told me that one of his largest customers recently deployed the fix, and they have not seen the issue reoccur since then. According to the KB article for the fix, it is included in these Cumulative Updates:

SQL Server 2008 R2 RTM CU9 (10.50.1804)

SQL Server 2008 R2 SP1 CU2  (10.50.2772)

SQL Server 2008 SP2 CU5  (10.00.4316)

SQL Server 2008 SP3 CU1  (10.00.5766)

The relevant fix does show up in the fix-list for the SQL Server 2008 R2 RTM CU9 Cumulative Update, but not in the fix-list for SQL Server 2008 R2 SP1 CU2 or for SQL Server 2008 R2 SP1 CU3. I have been assured by someone else at Microsoft (who is in a position to know), that the fix is in the SQL Server 2008 R2 SP1 branch, even though it is not explicitly listed in the fix-list.  If you have been running into this issue, I would suggest that you make plans to get a Cumulative Update that is new enough to include the fix as soon as possible. If you are at an organization that does not believe in deploying Cumulative Updates, you might have to make an exception in this case. I would love to hear from anyone else who has been seeing this problem themselves. Thanks for reading!

Posted in SQL Server 2008, SQL Server 2008 R2 | Tagged | 18 Comments

Speaking at Spring 2012 SQL Server Connections in Las Vegas

Posted on November 23, 2011 by Glenn Berry

I am going to be one of the speakers at the Spring 2012 SQL Server Connections Conference in Las Vegas, Nevada. This conference will be held from March 26-29, 2012, and you can register here. The banner on the conference web page says “We are excited to host one of the SQL Server 2012 Launch Events”, and “Take the journey into SQL Server 2012 at one of the premier launch events happening during the SQL Server 2012 Launch Season”, so that should give you an idea that there will be a very good amount of SQL Server 2012 specific content at this conference.

There are a LOT of good sessions scheduled for this conference! That should be no surprise, given the list of speakers that were selected. They include:

Bradley Ball

Glenn Berry

Aaron Bertrand

Grant Fritchey

Thomas Grohser

Steve Jones

Jonathan Kehayias

Brad McGehee

Peter Myers

Benjamin Nevarez

Brent Ozar

Paul Randal

Edwin Sarmiento

Kimberly L. Tripp

Paul Turley

Mike Walsh

Allen White

I am not sure how I got on this list, but I am honored. I will be giving three sessions, which are:

SQL301: Scaling SQL Server 2012
How can you scale SQL Server 2012? 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.

SQL302: DMV Emergency Room!
If you have ever been responsible for a mission critical database, you have probably been faced with a high stress, emergency situation where a database issue is causing unacceptable application performance, resulting in angry users and hovering managers and executives. If this hasn’t happened to you yet, thank your lucky stars, but start getting prepared for your time in the hot seat. This session will show you how to use DMV queries to quickly detect and diagnose the problem, starting at the server and instance level, and then progressing down to the database and object level. Based on the initial assessment of the problem, different types of DMV queries will help you narrow down and identify the problem. This session will show you how to assemble and use an emergency DMV toolkit that you can use to save the day the next time a sick database shows up on your watch in the database ER!

SQL303: Hardware 301: Diving Deeper into Database Hardware
Making the right hardware selection decisions is extremely important for database scalability. Having properly sized and configured hardware can both increase application performance and reduce capital expenses dramatically. Unfortunately, there are so many different choices and options available when it comes to selecting hardware and storage subsystems, it is very easy to make bad choices based on outmoded conventional wisdom. This session will give you a framework for how to pick the right hardware and storage subsystem for your workload type. You will learn how to evaluate and compare key hardware components, such as processors, chipsets, and memory. You will also learn how to evaluate and compare different types of storage subsystems for different database workload types. This session will give you the knowledge you need to make sure you get the best performance and scalability possible from your hardware budget!

Posted in SQL Server 2012, Teaching | Tagged | 1 Comment

SQL Server 2008 Service Pack 3 Cumulative Update 2 Released

Posted on November 22, 2011 by Glenn Berry

Microsoft has released SQL Server 2008 Service Pack 3 Cumulative Update 2, which is Build 10.00.5768.00. I count only 3 fixes in this CU. As I always do, I want to remind everyone that this CU is only for SQL Server 2008 SP2, not for SQL Server 2008 R2.

Here is a link to a list of the SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released.  I am sure there is a similar page for SQL Server 2008 SP3 builds, but I can’t find it right now.

This little build chart might make it a little more clear how the branches and builds relate to each other.

 

-- SQL Server 2008 RTM is considered an "unsupported service pack" as of April 13, 2010
-- SQL Server 2008 RTM Builds   SQL Server 2008 SP1 Builds     SQL Server 2008 SP2 Builds        SQL Server 2008 SP3 Builds
-- Build       Description      Build       Description           Build     Description            Build        Description
-- 1600        Gold RTM
-- 1763        RTM CU1
-- 1779        RTM CU2
-- 1787        RTM CU3    -->    2531        SP1 RTM
-- 1798        RTM CU4    -->    2710        SP1 CU1
-- 1806        RTM CU5    -->    2714        SP1 CU2 
-- 1812        RTM CU6    -->    2723        SP1 CU3
-- 1818        RTM CU7    -->    2734        SP1 CU4
-- 1823        RTM CU8    -->    2746        SP1 CU5
-- 1828        RTM CU9    -->    2757        SP1 CU6
-- 1835        RTM CU10   -->    2766        SP1 CU7
-- RTM Branch Retired     -->    2775        SP1 CU8        -->  4000      SP2 RTM
--                               2789        SP1 CU9
--                               2799        SP1 CU10    
--                               2804        SP1 CU11       -->  4266      SP2 CU1        
--                               2808        SP1 CU12       -->  4272      SP2 CU2    
--                               2816        SP1 CU13       -->  4279      SP2 CU3    
--                               2821        SP1 CU14       -->  4285      SP2 CU4            -->   5500        SP3 RTM
--                               2847        SP1 CU15       -->  4316      SP2 CU5  
--                                                          -->  4321      SP2 CU6            -->   5766        SP3 CU1    
--                                                          -->  4323      SP2 CU7            -->   5768        SP3 CU2
Posted in Microsoft, SQL Server 2008 | Tagged | Leave a comment