SQLskills SQL101: SQL Server Maintenance

By: Glenn Berry
Posted on: February 23, 2017 8:17 am

Microsoft has a methodology for developing and distributing updates to SQL Server, which they call the Incremental Servicing Model (ISM).  This model has a hierarchy of on-demand hotfixes (HFs), Cumulative Updates (CUs), and Service Packs (SPs) that are used to distribute updates to SQL Server. 

Microsoft’s official policy and guidance about when and whether to apply SQL Server updates changed on March 24, 2016, as described here. It is important that DBAs understand how this update system works whether they are working with traditional on-premises SQL Server or SQL Server running in an Azure VM (or any other IaaS cloud solution such as Amazon EC2).

Why do you need to maintain SQL Server?

Actively maintaining your SQL Server instances by proactively installing CUs and SPs as they become available will make your database server more reliable and possibly perform better. Microsoft has historical CSS data that indicates that a significant percentage of customer issues have already been fixed in a previously released CU, that had not been applied by the customer. My own personal experience as a DBA and consultant reinforces this view.

What happens if I don’t maintain my SQL Server instances?

You are more likely to run into problems that Microsoft has already fixed (because other customers have run into them). If your build of SQL Server is old enough, it may actually become what is called an “unsupported service pack”, which means that Microsoft CSS may be unwilling to fully support you (beyond basic troubleshooting) until you update to a supported service pack level. You don’t want to find yourself in this situation!

Are there any other benefits from updating SQL Server?

Developing a detailed plan for how you test and deploy a SQL Server update, and then actually implementing and updating the plan on a regular basis forces you and your organization to have a plan you also can follow whenever you make any kind of change or update to your database servers or the applications that use them. If you have any sort of HA/DR technology in place, updating SQL Server gives you an opportunity to use it in a planned fashion to minimize your downtime. Doing this on a regular basis validates your HA/DR solution and increases your confidence that it actually works as designed.

Are there any risks from updating SQL Server?

Certainly. Anytime you make any change to a computer system, there is a chance that something can go wrong. That is why you should have a written plan for how you test and deploy a SQL Server update that also includes how to rollback and recover in case something does go wrong. In reality, it is actually quite rare for a SQL Server update to cause a problem, but that doesn’t mean you should not be ready to deal with it if it does happen. Having a detailed plan that you actually follow dramatically decreases the chances of having any issues when you deploy your SQL Server update to Production.

How often does Microsoft release Cumulative Updates?

Microsoft releases Cumulative Updates every eight weeks for the versions of SQL Server that are still in mainstream support. This includes SQL Server 2012, SQL Server 2014, and SQL Server 2016. Currently, the CU release cycles for SQL Server 2012 and SQL Server 2016 are in sync, while SQL Server 2014 releases CUs slightly later. Hopefully, they will get the CU release cycle for all three versions back in sync.

How do I find out about new SQL Server Cumulative Updates?

The first place to look is the SQL Server Release Services blog. You can also check these Microsoft KB articles:

How do I find more information about this subject?

You can watch my Pluralsight courses SQL Server 2012: Installation and Configuration and SQL Server: Installing and Configuring SQL Server 2016, and read my article on SQLPerformance.com, Making the Case for Regular SQL Server Servicing.

You can attend one of our in-person training classes, such as IE0: Immersion Event for the Accidental/Junior DBA or IEHADR: Immersion Event on High Availability and Disaster Recovery. You can also contact me if you have specific questions. And, if you want to find all of our SQLskills SQL101 blog posts – check out: SQLskills.com/help/SQL101

Thanks for reading!

Posted in: Cumulative Updates, SQL101
2 Comments

SQL Server 2014 Service Pack 2 Cumulative Update 4

By: Glenn Berry
Posted on: February 21, 2017 4:05 pm

Microsoft has released SQL Server 2014 Service Pack 2 Cumulative Update 4, which is Build 12.0.5540.0. There are 30 hotfixes in the public fix list. In my opinion, you should be on the SP2 branch by now. If you have not made that move, you should be making plans to get on SP2 as soon as possible.

They have also released SQL Server 2014 Service Pack 1 Cumulative Update 11, which is Build 12.0.4502.0. There are 15 hotfixes in the public fix list for this CU.

There is no corresponding CU for the RTM branch, since SQL Server 2014 RTM is no longer a supported Service Pack level.

Posted in: Cumulative Updates, Service Packs, SQL Server 2014
Be the first to Reply!

New Flagship Xeon E5 and E7 Processors

By: Glenn Berry
Posted on: February 16, 2017 12:45 pm

Intel has recently released two, new “flagship” Xeon processors, one for the E7 v4 product family, and one for the E5 v4 product family. The new Intel Xeon E7-8894 v4 processor has 24 physical cores, and runs at a slightly higher base clock speed of 2.4GHz, compared to the 2.2GHz base clock speed of the previous flagship Intel Xeon E7-8890 v4 processor.

All of the other specifications of the E7-8894 v4 are identical to the earlier E7-8890 v4. One big difference between these two processors is the price. The new Xeon E7-8894 v4 is $8898.00 while the older Xeon E7-8890 v4 is $7174.00, which is a 24% price increase. While this seems like a pretty significant price increase by Intel, I think that most organizations that have a need for this type of hardware are not going to be very sensitive to that difference in hardware cost.

From a SQL Server 2016 Enterprise Edition license cost perspective, each physical core license is $7128.00. A four-socket Dell PowerEdge R930 server would require 96 core licenses, which would cost $684,288.00. The added $6,896.00 hardware cost of four E7-8894 v4 processors vs. four E7-8890 v4 processors is pretty trivial. The base clock speed increase is 9%, which means better single-threaded performance, which actually makes that large investment in SQL Server 2016 licenses more acceptable. Getting 9% more CPU capacity and 9% better single-threaded performance for less than 1% of the total hardware and license cost is actually a pretty good ROI. Table 1 shows some comparative metrics for a four-socket system using either of these two processors.

 

Picture1

Table 1: Comparative Metrics for Xeon E7-8894 v4 vs. Xeon E7-8890 v4 Processors

 

Back in Q4 of 2016, Intel made a similar new flagship model introduction in the Xeon E5 v4 product family with the rollout of the Intel Xeon E5-2699A v4 processor. This new flagship SKU has 22 physical cores and a base clock speed of 2.4GHz, compared to the 2.2GHz base clock speed of the previous flagship Intel Xeon E5-2699 v4 processor. Again, all of the other specifications for the E5-2699A v4 are identical to the earlier E5-2699 v4. There was also a significant price increase for this new flagship processor, with the new SKU costing $4938.00 vs. $4115.00 for the older flagship SKU, which represents a 20% price increase. This also seems like a case of price gouging from Intel, but is is actually acceptable from a SQL Server 2016 license cost perspective.

A two-socket Dell PowerEdge R730 server would require 44 core licenses, which would cost $313,632.00. The added $1,646.00 hardware cost of two E5-2699A v4 processors vs. two E5-2699 v4 processors is even more trivial. Table 2 shows some comparative metrics for a two-socket system using either of these two processors.

 

Picture2

Table 2: Comparative Metrics for Xeon E5-2699A v4 vs. Xeon E5-2699 v4 Processors

 

In both cases, my standard guidance about selecting the fastest available processor for a given physical core count for SQL Server usage still stands. The added hardware cost for getting the fastest processor core is really insignificant compared to the total system cost, including licensing costs.

The fact that Intel feels justified in charging 20-24% more for just 9% more performance is just a sad fact that stems from them not currently having any viable competition in the server CPU space from AMD. I really do hope that the next round of AMD Opteron processors based on the Zen microarchitecture are successful, and start to give Intel some decent competition.

Still, as a SQL Server DBA, getting 9% more capacity and 9% better single-threaded CPU performance for less than 1% higher system cost is actually a pretty good deal.

 

 

Posted in: Broadwell-EP, Broadwell-EX, Hardware, Intel, Processors
1 Comment