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

Performance and Stability Related Fixes in Post-SQL Server 2016 SP1 Builds

As of March 20, 2017, there have been two Cumulative Updates (CU) for the Service Pack 1 branch of SQL Server 2016. There have been a large number of hotfixes in each of these cumulative updates. If you are running on the SQL Server 2016 SP1 branch (which you should be by now), I really […]

SQL Server 2016 Service Pack 1 CU2 Released

On March 20, 2017, Microsoft released SQL Server 2016 Service Pack 1 CU2, which is Build 13.0.4422.0. This CU has 101 fixes in the public fix list, by my count. This is a pretty large CU, and if you look at the fix list in more detail, many of them are for pretty significant issues […]

SQL Server Diagnostic Information Queries for March 2017

This month, there are minor updates for the SQL Server 2012 and newer version queries. Rather than having a separate blog post for each version, I have just put the links for all seven major versions in this single post. There are two separate links for each version. The first one on the top left […]

SQL Server 2016 Cumulative Updates Available

Microsoft has released SQL Server 2016 SP1 CU1 (Build 13.0.4411.0). According to the associated cumulative KB article, KB3208177, 63 issues have been resolved with this update. A number of them look to be pretty significant. Microsoft has also released SQL Server 2016 RTM CU4 (Build 13.0.2193.0). According to the associated cumulative KB article, KB3205052, 65 […]

Operating System Support for SQL Server Versions

There are currently six major versions of SQL Server that I commonly see being used in Production, along with five major versions of Windows Server. Only certain combinations of SQL Server and Windows Server are officially supported by Microsoft, but tracking down this information is a little tedious. Table 1 shows these possible combinations and […]

SQL Server Diagnostic Information Queries for January 2017

This month, I have updated the legacy queries for SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. There are also minor updates for the SQL Server 2012 and newer queries. Rather than having a separate blog post for each version, I have just put the links for all six major versions in […]

SQL Server Diagnostic Information Queries for December 2016

This month, I have added a separate set of queries for SQL Server vNext. Right now, they are almost identical to the SQL Server 2016 queries, but I will make changes to surface new functionality in SQL Server vNext over time. I have also added support for new functionality in SQL Server 2016 Service Pack […]

SQL Server 2016 Service Pack 1 and SQL Server 2016 RTM CU3

Microsoft has released SQL Server 2016 RTM CU3 (Build 13.0.2186.6), with 31 fixes in the public fix list. They have also released SQL Server 2016 Service Pack 1 (Build 13.0.4001.0). As they previously did for SQL Server 2014 SP2, Microsoft has managed to include all of the hotfixes from the latest SQL Server 2016 RTM […]

Major Changes for SQL Server 2016 Standard Edition

At today’s Connect() event, Microsoft announced some pretty major changes for SQL Server 2016 Standard Edition, which are implemented in SQL Server 2016 Service Pack 1 (which was also released today). Many very useful SQL Server features that were previously only available in Enterprise Edition will now be available in Standard Edition (and also in […]

SQL Server Diagnostic Information Queries for November 2016

This month, I have added several new queries to SQL Server 2016 set, along with adding additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. I have gotten quite a bit of interest about making a special version of these queries for SQL Database in Microsoft Azure. So, I will be […]

SQL Server Management Studio Changes and Servicing

For the SQL Server 2016 release cycle, Microsoft has separated SQL Server Management Studio (SSMS) from the rest of the SQL Server 2016 product. This new standalone version of SSMS (which is completely free) is designed to work with SQL Server 2008 through SQL Server 2016. It may also work with SQL Server 2000 and […]

SQL Server Diagnostic Information Queries for October 2016

This month, I have spent quite a bit of time trueing up the queries for SQL Server 2012, 2014 and 2016 sets, along with adding additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets.  I have gotten quite a bit of interest about making a special version of these queries for […]

SQL Server Diagnostic Information Queries for September 2016

This month, I have a couple of new queries in the SQL Server 2012, 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets.  I have gotten quite a bit of interest about making a special version of these queries for SQL Database in Microsoft Azure. […]

SQL Server Diagnostic Information Queries for August 2016

This month, I have a new AlwaysOn AG query for SQL Server 2016 and several new improvements in the SQL Server 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets.  I have gotten quite a bit of interest about making a special version of these […]

New TPC-E Results for SQL Server 2016

There have been two recent TPC-E OLTP benchmark results published for SQL Server 2016. These include one from Fujitsu and one from Lenovo. The most recent result, from July 12, 2016 is for a four-socket FUJITSU Server PRIMERGY RX4770 M3 server that is using the latest generation, 14nm 2.2GHz Intel Xeon E7-8890 v4 processor (Broadwell-EX), […]

SQL Server Diagnostic Information Queries for July 2016

This month, I have several new improvements in the SQL Server 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets.  I have gotten quite a bit of interest about making a special version of these queries for SQL Database in Microsoft Azure. So, I will […]

AES-NI Encryption Support in SQL Server 2016

Microsoft’s Bob Dorr has recently consolidated a series of CSS posts about many low-level performance improvements in SQL Server 2016. One of these posts, SQL 2016 – It Just Runs Faster – AlwaysOn AES-NI Encryption describes how SQL Server 2016 improved and simplified Endpoint creation for AlwaysOn AGs to default to AES, and to better […]

Hardware Extension Support in SQL Server 2016

Microsoft’s Bob Dorr has recently consolidated a series of CSS posts about many low-level performance improvements in SQL Server 2016. One of these, How It Works: SQL Server 2016 SSE/AVX Support talks about how SQL Server 2016 added the use of SSE and AVX instructions in various code paths to improve performance when your processor […]

SQL Server Diagnostic Information Queries for June 2016

This month, I have several improvements in the SQL Server 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. One new feature is a series of six dashes after each query to make them easier to parse by people who use PowerShell to run the […]

SQL Server Diagnostic Information Queries for May 2016

This month, I have a new query in the SQL Server 2014 and 2016 sets, along with additional comments and documentation in the SQL Server 2012, 2014 and 2016 sets. One thing I am considering is making a special version of these queries for SQL Database in Microsoft Azure. Does anybody want me to do […]

Eight Different Ways to Clear the SQL Server Plan Cache

Nearly anytime you see the command DBCC FREEPROCCACHE mentioned in a blog post, magazine article or book, you usually get some sort of a scary warning about how you should not use it on a production system, or else life as we know it will end. For example, Books Online says this: Use DBCC FREEPROCCACHE […]

New TPC-H Benchmarks Comparing SQL Server 2016 to SQL Server 2014

There are two new TPC-H benchmark submissions on SQL Server 2016. This is interesting, because one of these new submissions (from March 9, 2016) is from Lenovo, for a System x3850 X6 running on SQL Server 2016. Lenovo has a previous submission, from May 1, 2015, for an identical model system running on SQL Server […]

SQL Server Countdown

Microsoft is putting out some silly, but funny videos to promote their Data Driven Event on March 10, 2016 at 10AM EST. The very short videos have a collection of well-known Microsoft employees, and other SQL Server notables, such as our own Kimberly Tripp, being “blown away” as Microsoft SQL Server 2016 goes supersonic. You […]

Using ALTER DATABASE SCOPED CONFIGURATION in SQL Server 2016

On March 7, 2016, Microsoft announced the availability of the RC0 Build of SQL Server 2016. One of the new features in the RC0 Build is Database Scoped Configurations, which gives you the ability to easily make several database-level configuration changes for things that were previously configured at the instance-level. These include: Setting MAXDOP for […]

Countdown to DEVintersection/SQLintersection in Orlando

As I have recently posted, I will be presenting three regular sessions and a full-day workshop at the DEVintersection/SQLintersection conference in Orlando, FL on April 16-22, 2016. As the conference get’s closer, I am starting to get more excited about it, reminding me of how I used to feel in my younger days as a […]

SQL Server Diagnostic Information Queries for January 2016

As I discussed yesterday,  I have done some fairly significant reorganization work for the SQL Server 2016, SQL Server 2014, and SQL Server 2012 versions of these queries this month. I have also made some very minor changes to the SQL Server 2008 R2 and older versions of these queries. Rather than having a separate […]

SQL Server Diagnostic Query Reorganization

After much thought, and some procrastination, I have decided to implement a fairly significant reorganization of my SQL Server Diagnostic Information Queries, starting with the SQL Server 2016 version for January 2016. I use these queries on a daily basis during my consulting work at SQLskills.com, and I have been planning on making some changes […]

Building a Desktop Workstation for SQL Server Development and Testing

Back in March of 2014, I wrote a fairly long blog post called Building a Workstation for SQL Server 2014 Development and Testing, which still generates quite a bit of hits and e-mail. Since it is about eighteen months later, I thought it was about time to update this information to cover the latest available […]

SQL Server Diagnostic Information Queries for September 2015

This month, I spent quite a bit of time synchronizing the scripts between the SQL Server 2016, 2014, and 2012 versions, making sure the small differences that were cropping up between the versions were cleaned up as much as possible. The SQL Server 2016 version will continue to be improved with more SQL Server 2016-specific […]

SQL Server Diagnostic Information Queries for August 2015

This month, I have added a new query for SQL Server 2016, that checks your QueryStore options. The SQL Server 2016 version will continue to be improved with more SQL Server 2016-specific queries and new columns in existing queries over the next few months. I am also continuing to add more relevant comments and links […]

SQL Server Diagnostic Information Queries for June 2015

This month, I have added a new, separate version of these queries for SQL Server 2016. This version will be improved with more SQL Server 2016-specific  queries and columns in existing queries in the near future. Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my […]

Intel Xeon E7 v3 Product Family Released

On May 5, 2015, Intel announced the new E7 v3 Product Family, which is also known as the Haswell-EX, which is meant for four and eight-socket servers. This 22nm processor is a Tock release (which means that it is using a new processor microarchitecture compared to the previous 22nm Ivy Bridge-EX). An updated graphic that […]