New Pluralsight Course Published

By: Glenn Berry
Posted on: January 17, 2019 3:31 pm

On January 10, 2019, Pluralsight published my latest course, SQL Server 2017: Diagnosing Performance Issues with DMVs. This makes fifteen courses that I have done for Pluralsight. Here is the official course description:

Learn how to easily query SQL Server 2017 for performance information to help identify and fix issues that can affect performance and stability. This course is applicable to anyone responsible for SQL Server 2017 and earlier versions.

Essentially, I walk you though the activity and performance-related queries from my SQL Server 2017 Diagnostic Queries. I do this by discussing and demonstrating each query and talking about how to interpret the results of each query. Knowing how to understand what each diagnostic query reveals is extremely useful as you are trying to determine what is going on with your SQL Server instance or database.

This course is a companion to my earlier SQL Server 2017: Diagnosing Configuration Issues with DMVs course that Pluralsight published on July 19, 2018.

Despite the title, this course is still applicable for older versions of SQL Server. This is because many of the queries that I demonstrate and discuss will work on older versions of SQL Server. Ideally, you should be using the correct version of my SQL Server Diagnostic Queries that matches your version of SQL Server so that all of the queries will work. Regardless of that, the core concepts are still relevant for older versions of SQL Server.

You can see all of my Pluralsight courses here.

Posted in: DMV Queries, Pluralsight, Teaching
Be the first to Reply!

The Importance of Database Compatibility Level in SQL Server

By: Glenn Berry
Posted on: January 14, 2019 2:55 pm

(New: we’ve published a range of SQL Server interview candidate screening assessments with our partner Kandio, so you can avoid hiring an ‘expert’ who ends up causing problems. Check them out here.)

Prior to SQL Server 2014, the database compatibility level of your user databases was not typically an important property that you had to be concerned with, at least from a performance perspective. Unlike the database file level (which gets automatically upgraded when you restore or attach a down-level database to an instance running a newer version of SQL Server, and which can never go back to the lower level), the database compatibility level can be changed to any supported level with a simple ALTER DATABASE SET COMPATIBILITY LEVEL = xxx command.

You are not stuck at any particular supported database compatibility level, and you can change the compatibility level back to any supported level that you wish. In many cases, most user databases never had their compatibility levels changed after a migration to a new version of SQL Server. This usually didn’t cause any issues unless you actually needed a new feature that was enabled by the latest database compatibility level.

With SQL Server 2012 and older, the database compatibility level was mainly used to control whether new features introduced with a particular version of SQL Server were enabled or not and whether non-supported old features were disabled or not. The database compatibility level was also used as a method to maintain better backwards application compatibility with old versions of SQL Server. If you didn’t have time to do full regression testing with the newest compatibility level, you could just use the previous compatibility level until you could test and modify your applications if needed.

Table 1 shows the major versions of SQL Server and their default and supported database compatibility levels.

SQL Server Version             Database Engine Version        Default Compatibility Level           Supported Compatibility Levels

SQL Server 2019                   15                                            150                                                  150, 140, 130, 120, 110, 100

SQL Server 2017                   14                                            140                                                  140, 130, 120, 110, 100

SQL Server 2016                   13                                            130                                                  130, 120, 110, 100

SQL Server 2014                   12                                            120                                                  120, 110, 100

SQL Server 2012                   11                                            110                                                  110, 100, 90

SQL Server 2008 R2              10.5                                         100                                                  100, 90, 80

SQL Server 2008                   10                                            100                                                  100, 90, 80

SQL Server 2005                     9                                              90                                                  90, 80

SQL Server 2000                     8                                              80                                                  80

Table 1: SQL Server Versions and Supported Compatibility Levels

New Database Creation

When you create a new user database in SQL Server, the database compatibility level will be set to the default compatibility level for that version of SQL Server. So for example, a new user database that is created in SQL Server 2017 will have a database compatibility level of 140. The exception to this is if you have changed the compatibility level of the model system database to a different supported database compatibility level, then a new user database will inherit its database compatibility level from the model database.

Database Restore or Attach

If you restore a full database backup that was taken on an older version of SQL Server to an instance that is running a newer version of SQL Server, then the database compatibility level will stay the same as it was on the older version of SQL Server, unless the old database compatibility level is lower than the minimum supported database compatibility level for the newer version of SQL Server. In that case, the database compatibility level will be changed to the lowest supported version for the newer version of SQL Server.

For example, if you were to restore a SQL Server 2005 database backup to a SQL Server 2017 instance, the database compatibility level for that restored database would be changed to 100. You will get the same behavior if you detach a database from an older version of SQL Server, and then attach it to a newer version of SQL Server.

This general behavior is not new, but what is new and important is what else happens when you change a user database to database compatibility level 120 or newer. These additional changes, that can have a huge impact on performance, don’t seem to be widely known and understood in the wider SQL Server community. I still see many database professionals and their organizations just doing what I call “blind upgrades” where they go from SQL Server 2012 or older to SQL Server 2014 or newer (especially SQL Server 2016 and SQL Server 2017), where they don’t do any serious performance regression testing to understand how their workload will behave on the new native compatibility level and whether the additional configuration options that are available will have a positive effect or not.

Database Compatibility Level 120

This was when the “new” cardinality estimator (CE) was introduced. In many cases, most of your queries ran faster when using the new cardinality estimator, but it was fairly common to run into some queries that had major performance regressions with the new cardinality estimator. Using database compatibility level 120 means that you will be using the “new” CE unless you use an instance-wide trace flag or a query-level query hint to override it.

Joe Sack wrote the classic whitepaper “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” that explains the background and behavior of this change back in April of 2014.  If you saw performance regressions on some queries with the new CE, SQL Server 2014 did not have that many options for alleviating the performance issues caused by the new CE. Joe’s whitepaper covers those options in great detail, but essentially, you were limited to instance-level trace flags or query-level query hints to control which cardinality estimator was used by the query optimizer, unless you wanted to revert back to database compatibility level 110 or lower.

The reason I called it the “new” CE in quotes is because there is now no single “new” CE. Each new version of SQL Server since SQL Server 2014 has CE and query optimizer changes tied to the database compatibility level. The new, more accurate terminology that is relevant on SQL Server 2016 and newer is CE120 for compatibility level 120, CE130 for for compatibility level 130, CE140 for for compatibility level 140, and CE150 for for compatibility level 150.

Database Compatibility Level 130

When you are on SQL Server 2016 or newer, using database compatibility level 130 will use CE130 by default, and will enable a number of other performance related changes. The effects of global trace flags 1117, 1118, and 2371 are enabled with database compatibility level 130. You will also get the effect of global trace flag 4199 for all query optimizer hotfixes that were released before SQL Server 2016 RTM.

SQL Server 2016 also introduced database scoped configuration options, which give you the ability to control some behaviors that were formerly configured at the instance level, using an ALTER DATABASE SCOPED CONFIGURATION command. The two most relevant database scoped configuration options for this discussion are LEGACY_CARDINALITY ESTIMATION and QUERY_OPTIMIZER_HOTFIXES.

LEGACY_CARDINALITY ESTIMATION enables the legacy CE (CE70) regardless of the database compatibility level setting. It is equivalent to trace flag 9481, but it only affects the database in question, not the entire instance. It allows you to set the database compatibility level to 130 in order to get the other functional and performance benefits, but use the legacy CE database-wide (unless overridden by a query-level query hint).

The QUERY_OPTIMIZER_HOTFIXES option is equivalent to trace flag 4199 at the database level. SQL Server 2016 will enable all query optimizer hotfixes before SQL Server 2016 RTM when you use the 130 database compatibility level (without enabling trace flag 4199). If you do enable TF 4199 or enable QUERY_OPTIMIZER_HOTFIXES, you will also get all of the query optimizer hotfixes that were released after SQL Server 2016 RTM.

SQL Server 2016 SP1 also introduced the USE HINT query hints that are easier to use and understand than the older QUERYTRACEON query hints that you had to use in SQL Server 2014 and older. This gives you even more fine-grained control over optimizer behavior that is related to database compatibility level and the version of the cardinality estimator that is being used. You can query sys.dm_exec_valid_use_hints to get a list of valid USE HINT names for the exact build of SQL Server that you are running.

Database Compatibility Level 140

When you are on SQL Server 2017 or newer, using database compatibility level 140 will use CE140 by default. You also get all of the other performance related changes from 130, plus new ones as detailed here. SQL Server 2017 introduced the new adaptive query processing features, and they are enabled by default when you are using database compatibility level 140. These include batch mode memory grant feedback, batch mode adaptive joins, and interleaved execution.

Database Compatibility Level 150

When you are on SQL Server 2019 or newer, using database compatibility level 150 will use CE150 by default. You also get all of the other performance related changes from 130 and 140, plus new ones as detailed here. SQL Server 2019 is adding even more performance improvements and behavior changes that are enabled by default when a database is using compatibility mode 150. A prime example is scalar UDF inlining, which automatically inline many scalar UDF functions in your user databases. This may be one of the most important performance improvements for some workloads.

Another example is the intelligent query processing feature, which is a superset of the adaptive query processing feature in SQL Server 2017. New features include table variable deferred compilation, approximate query processing, and batch mode on rowstore.

There are also sixteen new database scoped configuration options (as of CTP 2.2) that give you database-level control of more items that are also affected by trace flags or the database compatibility level. It gives you even more fine-grained control of these higher level changes that are enabled by default with database compatibility level 150.

Conclusion

Migrating to a modern version of SQL Server (meaning SQL Server 2016 or newer) is significantly more complicated than it was with legacy versions of SQL Server. Because of the changes associated with the various database compatibility levels and various cardinality estimator versions, it is actually very important to put some thought, planning, and actual testing into what database compatibility level you want to use on the new version of SQL Server that you are migrating your existing databases to.

Microsoft’s recommended upgrade process is to upgrade to the latest SQL Server version, but keep the source database compatibility level. Then, enable Query Store on each database and collect baseline data on the workload. Next, you set the database compatibility level to the latest version, and then use Query Store to fix your performance regressions by forcing the last known good plan.

You really want to avoid a haphazard “blind” migration where you are blissfully unaware of how this works and how your workload will react to these changes. Changing the database compatibility level to an appropriate version and using the appropriate database scoped configuration options, along with appropriate query hints where absolutely necessary, is extremely important with modern versions of SQL Server.

Another thing to consider (especially for ISVs) is that Microsoft is starting to really push the idea that you should think about testing and certifying your databases and applications to a particular database compatibility level rather than a particular version of SQL Server. Microsoft provides query plan shape protection when the new SQL Server version (target) runs on hardware that is comparable to the hardware where the previous SQL Server version (source) was running and the same supported database compatibility level is used both at the target SQL Server and source SQL Server.

The idea here is that once you have tested and certified your applications on a particular database compatibility level, such as 130, you will get the same behavior and performance if you move that database to a newer version of SQL Server (such as SQL Server 2017 or SQL Server 2019) as long as you are using the same database compatibility level and you are running on equivalent hardware.

   

 

Posted in: Microsoft, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019
2 Comments

More CPU Competition Coming for Intel

By: Glenn Berry
Posted on: January 11, 2019 4:40 pm

On January 9, 2019, AMD CEO and President Dr. Lisa Su presented a CES 2019 Keynote. During the keynote, she demonstrated a new desktop processor, (at 1:25:00 in the video). This was a 7nm, 8C/16T, 3rd Generation AMD Ryzen 3000 series “Matisse” desktop processor running the Cinebench R15 Multithreaded (MT) benchmark vs. a 14nm, 8C/16T Intel Core i9-9900K “Coffee Lake” desktop processor.

System Comparison

These two systems were as identical as possible (outside of the motherboard and processor), meaning identical 2666MHz memory, video card, and storage. The Intel system was running at stock clock speeds vs. an engineering sample Ryzen running at lower than final clock speeds. The Cinebench MT score for Intel was 2040, while the Cinebench MT score for the AMD Ryzen 2 was 2057. The Intel system was using 179.8 watts, while the AMD system was using 133.4 watts during benchmark. This benchmark pegs all of the cores in the system, so this is extremely significant!

Dr. Su held up one of these Ryzen 2 processors, showing a 14nm I/O die on the left and the 7nm 8C/16T Zen 2 processor die on the top right. It was pretty obvious that there was room on the package for another identical Zen 2 processor die on the bottom right. During interviews over the next couple of days, Dr. Su basically confirmed that the Zen 2 family had room for an extra processor die and that we should expect a higher core count SKU. AMD purposely used an eight core CPU for the demo so that they would have the same core count as Intel’s current top of the line processor.

Processor Details

It appears that what AMD demonstrated was actually a mid-range Ryzen 5 class SKU, running with artificially slow memory, at a non-final lower clock speed that still had a slightly higher Cinebench MT score (with the same core/thread count) as the current best mainstream desktop processor that Intel has available. Since the core/thread counts were the same between the two systems, this means that the single-threaded performance should be about the same. If this is true, then this would be the first time in an extremely long time where AMD has better single-threaded performance than Intel. The final version of these Zen 2 processors should perform even better than this early sample.

Conclusion

You may be wondering what this has to do with server processors and with SQL Server. It turns out that the upcoming 7nm AMD EPYC “Rome” processors use the same Zen 2 architecture and 7nm manufacturing process as these Zen 2 mainstream desktop processors. If the 7nm AMD EYPC Rome processors end up having better single-threaded performance than the upcoming 14nm Intel Cascade Lake-SP processors (which I think is pretty likely), then AMD is going to be extremely competitive in the server market and for SQL Server usage. This is especially true if you consider AMD’s advantage in memory density, PCIe lanes (which will be Gen 4.0) and hardware cost. Dr. Su actually did a demonstration of a one-socket AMD EPYC Rome system vs. a two-socket Intel Xeon 8180 system, showing the AMD system winning.

Both the desktop Ryzen 2 and the server EPYC Rome processors are due to be released in mid-2019.

AdoredTV has their analysis here, while UFD Tech has their analysis here. Anandtech has a good writeup here.
 

 

Posted in: AMD, Hardware, Intel
Be the first to Reply!