Compare environment settings on SQL Server and Azure SQL that may impact performance

When you migrate your databases from one SQL Server instance to another or from SQL Server to cloud (for example Azure SQL Managed Instance), one of the first things you would like to do is to compare the workload performance between source and target environment. Sometime you might be surprised by the results because you are getting different performance although you believe that source and target environment are the same.

There are several factors that might cause different performance on source and target instances, such as:

1. Different server/database properties on source and target instance (compatibility levels, cardinality estimator, encryption, etc.)
2. Different trace flag settings
3. Different tempdb settings (number of files, encryption)

Sometime it might be hard to notice the difference between two environments because you would need to cross-compare many different views and properties, unless if you have some tool that is doing this.

In this article you will see the scripts that can enable you to more easily compare environment settings on source and target instance.

Source code

Scripts used in this article are placed on SQL Server GitHub account:

1. Get-properties.sql that reads various instance, database, tempdb, trace flag values and produces one XML document with all properties. you should execute this script one on the source database and then on destination.
2. Compare-properties.sql that compares the values generated by the previous script and shows the differences.

Getting the instance properties

As a first step you should download the Get-properties.sql script from SQL server Github account:

Script that gets the environment properties

Get this script, put it in SQL Server Management Studio or some other query IDE, change the value of local variable @db_name to the name of the database that you moved (otherwise it will compare the properties of master database), and run the script (I’m running it agains WideWorldImporters database):

Copy the XML result, run the query on target instance, and get the XML result from target.

Now when you have two XML documents describing the source and target instance properties, you can compare them.

Comparing the environment settings

In order to compare results of the queries from the previous step, you should download the second script that compares two files.

You can run this query on any instance (source, target, some other) because it just uses SQL Server XML functions to compare the XML documents produced by the previous queries.

Open the .sql file and put the XML documents generated in the first step in the lines 1 and 2 results of the query:

Comparison of environment settings

Once you place XML documents and run the query, you will get the report with the differences.

In the result, we can see that MAXDOP was limited to 3 on my source instance, that tempdb is encrypted on destination (which might slow down tempdb spills), and that my target instance has tempdb with more files. These might be answers why performance are different.

Note that the script will compare only the properties that exist both on source and target and they are different. Probably you would also need to take a look at the properties are are set on one instance but not on the another. You can do this easily by removing the where clause:

By removing this where clause, you might see that there are a lot of database configurations and trace flags that are set on the target instance, while they are not present on the source. In this case, I’m comparing SQL Server with Managed Instance that has more options and pre-configured settings.

Conclusion

The script shown in this article can help you to identify and compare environment settings and differences between them that might cause the performance differences between your source and destination instance. This is one of the first step that you should do to compare source and target instance if you are getting different performance.

These script will not tell you why there is a difference in performance or why some different setting impact the performance. You would need to look at the results and try to identify some settings that might cause the performance difference. The main value of this script is the fact that it enables you to easily get a list of environment differences without cross-comparing individual settings using SSMS or DMVs.