SQL Server Diagnostic Information Queries for September 2017

On September 1, 2017, Dropbox turned off the sharing feature that I was previously using to host these scripts, which broke hundreds of existing links on my blog. I will be switching to their new sharing method, at least temporarily, until I decide what to do. This month, there are more minor updates to the […]

Upgrading SQL Server– Intel Xeon-W Family

On August 30, 2017, Intel released their new Intel Xeon Processor W Family of one-socket workstation processors (hence the W) based on the Skylake-SP Xeon server processors. These processors are meant as a replacement for the older one-socket Intel Xeon E5-1600 v4 series that had up to eight physical cores. The Xeon-W processors will have […]

Upgrading SQL Server– Database Experimentation Assistant

Another useful, if somewhat complicated, free tool from Microsoft is the Database Experimentation Assistant (DEA). Microsoft released DEA version 2.1 on July 25, 2017, with many new improvements over the earlier preview versions as detailed here. Here is how Microsoft describes the DEA tool: “Database Experimentation Assistant (DEA) is a new A/B testing solution for […]

Upgrading SQL Server– Microsoft Data Migration Assistant 3.2

One useful tool that you should definitely take advantage of during an upgrade and migration effort is the Microsoft Data Migration Assistant (DMA), which replaces the old SQL Server Upgrade Advisor. Microsoft released the new DMA 3.2 on August 25, 2017. This tool can be used for three main purposes. First, you can use it […]

Upgrading SQL Server– SQL Server 2016 Upgrade Considerations

One somewhat obscure issue you might encounter when upgrading from a legacy version of SQL Server to SQL Server 2016 or later is a change to how some data type conversions are handled. Microsoft describes it like this: “Microsoft SQL Server 2016 and Microsoft Azure SQL Database include improvements in data type conversions and in […]

Upgrading SQL Server– Useful Improvements in SQL Server 2017

Even though much of the development effort and resources in SQL Server 2017 has been used to get SQL Server running as a first-class citizen on Linux, there are some interesting new features and improvements in SQL Server 2017 regardless of what operating system you are going to use. One example are changes in the […]

Upgrading SQL Server– Top Five Intel Xeon Scalable Processors for SQL Server Usage

On July 11, 2017, Intel formally released their new Xeon Scalable Processor Family of processors for data center usage. This new processor family has a somewhat ridiculous 58 different SKUs in the line. They are broken up into Platinum 8xxx, Gold 6xxx, Gold 5xxx, Silver 4xxx, and Bronze 3xxx sub-categories. New two-socket server models(which are […]

Upgrading SQL Server– Windows Server 2016 Licensing Issues

If you are planning a complete data platform upgrade, you should be planning on using Windows Server 2016 as your operating system (assuming you are planning on using Windows rather than Linux). Windows Server 2016 has a number of advantages over older versions of Windows Server, including higher license limits for memory, better networking and […]

Upgrading SQL Server– SQL Server Support Dates

One valid argument for considering a SQL Server upgrade sooner rather than later is the fact that the version(s) of SQL Server you are using in Production may be out of mainstream or even extended support. But what does that mean exactly? It does NOT mean that out of support versions suddenly stop working. It […]

Upgrading SQL Server– Database Compatibility Levels

One important factor to contemplate during an upgrade and migration effort are your database compatibility levels, which control certain database behaviors to be compatible with a particular version of SQL Server. Unlike the database file version (which get changed when you restore a database backup in a newer version or attach an older database to […]

Upgrading SQL Server–Replacing Slow Hardware Part 2

Previously, we talked about the single-threaded performance, CPU capacity and hardware cost difference between an extremely poor server model and processor SKU choice and a much better server model and processor SKU choice from about three and a half years ago. In that case, the bad choice only had about 34% of the single-threaded CPU […]

Upgrading SQL Server–Replacing Slow Hardware

One unfortunately common scenario is a situation where someone made a very poor processor choice sometime in the past for a database server that runs a mission critical workload. When I do health checks for SQLskills, I encounter both good processor choices and some pretty bad processor choices. One recent example was a 22nm, quad-core […]

Upgrading SQL Server–Compelling Upgrade Arguments

Over the course of my career, I can’t remember too many people coming to me and initiating the conversation about upgrading to a new version of SQL Server, a new version of Windows Server or a new set of server hardware and storage. As a DBA, Data Architect, or Consultant, I have generally had to […]

Upgrading SQL Server–Mitigating Performance Regressions

As I discussed yesterday, it is sometimes possible to experience performance regressions after upgrading to a new version of SQL Server, even when it is running on a new, presumably faster database server. This is much less likely to happen if you have done a good job of analysis, planning, testing, benchmarking, and post-migration configuration […]

Upgrading SQL Server–Diagnosing Performance Regressions

One fairly common issue you might encounter when upgrading to SQL Server 2014 or later are performance regressions with some queries. SQL Server 2014 introduced a new cardinality estimator (CE) that is used by the query optimizer by default when you change the compatibility level of a database to 120 or higher. Most of the […]

Upgrading SQL Server–Delayed Durability

One of the interesting new features that was added in SQL Server 2014 was delayed durability. This feature lets you trade some transaction durability for increased log write performance. This is possible because delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are flushed and […]

Upgrading SQL Server–Separate Version of SSMS

One upgrade opportunity that you can take advantage of pretty easily, whether or not you are actually upgrading your actual database servers, is to use a modern version of SQL Server Management Studio (SSMS). Starting with SQL Server 2016, SSMS was separated from the rest of the product, and was not even included with the […]

Upgrading SQL Server–Migrating with Database Mirroring

Another relatively quick and easy way to do a Production migration of data from one place to another or from an older version of SQL Server to a newer version of SQL Server is to use database mirroring. You have to have each user database in Full recovery model before you can set up a […]

Upgrading SQL Server–Migrating with Log Shipping

One easy and quick way to do a Production migration of data from one place to another or from an older version of SQL Server to a newer version of SQL Server is to use log shipping. Log shipping may not be new and shiny like availability groups, but it has very few blockers for […]

Upgrading SQL Server–Preferred AMD EPYC 7000 Processors

Yesterday I talked about the critical importance of thoughtful processor selection for SQL Server as part of the upgrade and migration process, and I supplied a list of “preferred” Intel Scalable Processor family (Skylake-SP) processors at various physical core counts. Today, I am going to cover a similar list for the new AMD EYPC 7000 […]

Upgrading SQL Server–Preferred Intel Xeon Scalable Processors

One crucial part of doing a complete data platform upgrade is proper hardware and processor selection. Because of how SQL Server 2012 and newer core-based licensing works, processor selection is extremely important from a performance/scalability perspective and from a license cost perspective. Often, your SQL Server core license costs can far outweigh the hardware and […]

Upgrading SQL Server–August 8, 2017 Updates

Part of the upgrade and migration process is keeping your existing SQL Server instances properly patched. Being on a minimum Service Pack level is often required in order for some types of upgrade and migration operations. It is also required to be in a fully supported state from Microsoft. It is also just a good […]

Upgrading SQL Server–Upgrade Methods

Back in December of 2012, I wrote a fairly long article called Making the Case for a SQL Server Platform Refresh that made the argument that late 2012 was a good time to make a compelling case for a complete platform refresh, where you would get new server hardware and storage, with a new operating […]

SQL Server Diagnostic Information Queries for August 2017

This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. I often make additional minor updates to the queries periodically during the month, so if you are in doubt, downloading the latest version is always a good idea. Rather than having a separate […]

Upgrading SQL Server–Supported Migration Methods

When it comes down to actually migrating from a legacy version of SQL Server to SQL Server 2016/2017, there are several different methods for performing the migration, depending on what legacy version of SQL Server your are using. Here is a high-level overview of the various migration methods, by version: Migrating from SQL Server 2005 […]

Upgrading SQL Server–Standard Edition License Limits

SQL Server Standard Edition License Limits One thing you need to consider as you are contemplating an upgrade and migration effort from a legacy version of SQL Server Standard Edition to a new version of SQL Server Standard Edition are the per instance license limits for memory, sockets, and physical cores. This is far from […]

Upgrading SQL Server–Generational Performance Increases for Two-Socket Servers

As I noted yesterday, Lenovo recently submitted a TPC-E benchmark result for a Lenovo ThinkSystem SR650 two-socket system, with two 14nm Intel Xeon Platinum 8180 28-core Skylake-SP processors and 1.5TB of DDR4 RAM. This system had a raw score of 6,598.36, and it has 56 physical processor cores. This flagship processor has a relatively low […]

Upgrading SQL Server–Server Hardware

For Day 4 of this series, I want to talk briefly about server hardware. If you are running a legacy version of SQL Server, it is very likely running on old, probably out of warranty hardware. This presents several problems. First, older server hardware is less reliable and is more apt to have component failures. […]

Upgrading SQL Server–Operating System Support for SQL Server Versions

For Day 3, I’ll be talking about operating system support dates and SQL Server version support for various operating system versions. If you are running a legacy version of SQL Server, it is probably running on a legacy version of Windows Server, which may also be out of mainstream support or even extended support from […]

Upgrading SQL Server–Top Five Upgrade Reasons

For Day 2, lets talk about the top five reasons that will help you make the case for an upgrade and migration effort to SQL Server 2016 or SQL Server 2017. Useful new features that are not available in legacy versions of SQL Server Enhancements for existing features compared to legacy versions of SQL Server […]

Upgrading SQL Server–Introduction

Today is the start of a new series of blog posts about upgrading and migrating to SQL Server 2016/2017 from an older version of SQL Server. It is also my birthday, so I figured it was a good time to start this series! For Day 1, lets talk about why you might want to go […]

Upgrading to SQL Server 2016

Many organizations are running older and possibly out of support versions of SQL Server, running on older versions of Windows Server, on old, slower, out of warranty hardware and storage. SQL Server 2005 fell out of extended support on April 12, 2016. SQL Server 2008 and SQL Server 2008 R2 fell out of mainstream support […]

SQLskills SQL101: Creating SQL Server Databases

As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of […]

SQL Server Diagnostic Information Queries for July 2017

This month, there are more minor updates to the all of the versions of the queries. I usually make additional minor updates to the queries during the month, so if you are in doubt, downloading the latest version is always a good idea. Rather than having a separate blog post for each version, I have […]

SQL Server Diagnostic Information Queries for June 2017

This month, there are some minor updates to the all of the versions of the queries. I usually make additional minor updates to the queries during the month, so if you are in doubt, downloading the latest version is always a good idea. Rather than having a separate blog post for each version, I have […]

SQL Server Diagnostic Information Queries for May 2017

This month, there are two new queries for SQL Server 2017, along with more minor updates for several of the SQL Server 2012 and newer version queries. I have also made some minor updates to the SQL Server 2005, 20087, and 2008 R2 queries. I usually make minor updates to the queries during the month, […]

SQLskills SQL101: Using DDL Triggers

Over the past couple of months, SQLskills has embarked on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find […]