SQL Server Diagnostic Information Queries for July 2018

This month, there are improvements to seven of the the SQL Server 2012 and newer versions of the queries, with a new column that shows whether there is a missing index warning in the cached query plan. A reader named Håkan Winther made this useful suggestion. I have also developed a T-SQL script that you […]

SQL Server Diagnostic Information Queries for June 2018

This month, there are more minor updates to the SQL Server 2012 and newer versions of the queries, primarily in the comments and documentation. I have added some additional columns to many existing queries, and tried to get the newer versions uniformly updated and synchronized. I have also developed a T-SQL script that you can […]

SQL Server Diagnostic Information Queries for May 2018

May 3, 2018 Update: I have added a SQL Server 2016 SP2 version of the queries, since Microsoft back-ported so many new queries from SQL Server 2017 to SQL Server 2016 SP2. This month, there are more minor updates to the SQL Server 2012 and newer versions of the queries, primarily in the comments and […]

SQL Server 2017 Cumulative Update 6

On April 17, 2018, Microsoft released SQL Server 2017 CU6, which is Build 14.0.3025.34 I count 39 fixes or improvements in the public fix list. Remember, there are not going to be any Service Packs for SQL Server 2017. We are still in the monthly release cycle for SQL Server 2017 Cumulative Updates which will […]

SQL Server Diagnostic Information Queries for April 2018

This month, there are more minor updates to the SQL Server 2014 and newer versions of the queries, primarily in the comments and documentation. I have added some additional columns to many existing queries, and tried to get the newer versions uniformly updated and synchronized. I have also developed a T-SQL script that you can […]

SQL Server 2017 Cumulative Update 5

On March 20, 2018, Microsoft released SQL Server 2017 Cumulative Update 5, which is Build 14.0.3023.8. I count 14 hotfixes in the public fix list. It is good to see Microsoft getting back on schedule for their first year CU releases for SQL Server 2017, which are supposed to happen every month. There are quite […]

Recent SQL Server Software Updates

The third week of March 2018 has seen a plethora of SQL Server related software updates from Microsoft. These include: SQL Server 2017 CU5 SQL Server 2016 SP1 CU8 SQL Server 2014 SP2 CU11 This list covers all of the currently supported branches of these major versions of SQL Server, meaning that if you are […]

SQL Server Diagnostic Information Queries for March 2018

This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. I have made the most changes to the SQL Server 2017 version this month. I have also developed a T-SQL script that you can use to check whether your instance of SQL Server […]

SQL Server 2017 Cumulative Update 4

On February 20, 2018, Microsoft released SQL Server 2017 Cumulative Update 4, which is Build 14.0.3022.28. I count 55 hotfixes in the public fix list. There is a special T-SQL script in the release notes that you need to run if you are using Query Store and previously ever had SQL Server 2017 CU2 installed […]

SQL Server Diagnostic Information Queries for February 2018

This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. I have developed a T-SQL script that you can use to check whether your instance of SQL Server has been patched to mitigate against the Spectre/Meltdown CPU vulnerability. This works for SQL Server […]

Checking Your SQL Server Instance for Spectre/Meltdown Patches

If you are running SQL Server 2008 through SQL Server 2017, you should be thinking about what you should be doing to protect your systems from the Meltdown and Spectre vulnerabilities. Microsoft has a number of KB articles that address this issue from several different perspectives. This is a good starting list: SQL Server Guidance […]

SQL Server 2017 CU3 Released on January 4, 2018

On January 4, 2018, Microsoft released SQL Server 2017 CU3, which is Build 14.0.3015.40. By my count, this CU has sixteen public hotfixes, many of which are for the SQL Engine or SQL performance. There are also some new manageability and programmability features that have been added, such as support for the MAXDOP option for […]

Microsoft SQL Server Updates for Meltdown and Spectre Exploits

Over the last couple of days, you have probably heard quite a bit of chatter and speculation about some newly disclosed ways to attack various processors. The initial reports were that only Intel processors were affected, but some sources indicate that some AMD and ARM processors are also vulnerable. Security researchers at Graz University (who […]

SQL Server Missing Indexes Feature and Trace Flag 2392

Background Since the SQL Server 2005 release, SQL Server has had the default ability to collect metrics about what the query optimizer thinks are “missing” row store indexes, i.e. indexes that the query optimizer thinks would reduce the cost of particular individual queries that have been executed since SQL Server was last restarted. The word […]

SQL Server Diagnostic Information Queries for December 2017

This month, there are more minor updates to the all of the versions of the queries, primarily in the comments and documentation. There is also a new query for the SQL Server 2017 set of queries. I often make additional minor updates to the queries periodically during the month, so if you are in doubt, […]

Performance and Stability Fixes in SQL Server 2017 CU Builds

As of July 19, 2018, there have been nine Cumulative Updates (CU) for SQL Server 2017. There have been a large number of hotfixes in each of these cumulative updates. If you are running on SQL Server 2017, I really think you should be running the latest SQL Server 2017 Cumulative Update as soon as […]

SQL Server 2017 CU2 Is Available

On November 28, 2017, Microsoft released SQL Server 2017 CU2, which is build 14.0.3008.27.  I count 33 hotfixes in this CU. What is interesting is that there is a new column in the fix list called Platform, which will have a value of All, Linux, or Windows to indicate which platform the fix is relevant […]

Recent TPC-E Results on SQL Server 2017

Lenovo has submitted the two most recent TPC-E OLTP benchmark results, both using SQL Server 2017 running on Windows Server 2016 Standard Edition, using 28-core Intel Xeon Platinum 8180 processors. The most recent result was for a four-socket Lenovo ThinkSystem SR950 with 3TB of RAM using a 48TB initial database size. This system had an […]

SQL Server Diagnostic Information Queries for November 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 […]

SQL Server 2017 CU1 is Available

On October 24, 2017, Microsoft released SQL Server 2017 Cumulative Update 1, which is Build 14.0.3006.16. This CU has 68 hot fixes by my count, which is a fairly high count. This shows that Microsoft has been busy fixing issues that have shown up since the RTM build. I think this is a good thing. […]

SQL Server Diagnostic Information Queries for October 2017

This month, I have added a set of diagnostic queries for Azure SQL Database. These are very much a work in progress, and they will be changing (and hopefully improving) quite a bit over the next few months There are more minor updates to the all of the versions of the queries, primarily in the […]

Modern SQL Server Servicing Model

Microsoft has announced a pretty big change for how they are going to service SQL Server, starting with SQL Server 2017. I am very much in favor of the changes in this new model.   Background Historically, Microsoft has used a combination of General Distribution Releases (GDRs), Cumulative Updates (CUs), and Service Packs (SPs) to […]

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 […]