5 Tips for Troubleshooting Azure SQL

Database Performance
Gurvinder Singh on January, 22, 2020

Azure SQL database is a promising cloud-based, fully managed, relational database as a service
(DBaaS), many organizations want to migrate on. Azure SQL o ers the best in-class features like,
built-in machine learning for auto performance tuning, auto backups and restore options, high
scalability and availability, advanced data security, vulnerability assessment and advanced threat
detection.

Although looking at these wide varieties of features o ered by Azure SQL database, there are few
who may not be convinced that it o ers a seamless experience especially when it comes to
measuring performance post-migration from On-premises SQL to Azure SQL database (Single,
Pooled or Managed instance). If you too have similar concerns, then you are at the right place. Here
in this guide, we will walk you through TOP-5 practical Tips or Tricks to validate and remediate
performance issues if any.

Prerequisites
The article requires you to have basis knowledge of SQL database.

Migration Options
Microsoft’s Azure database migration guide gives a detailed walkthrough of various use cases of
database migration options to choose from. The most prominent ones are

Online or o ine migration using managed Database Migration Service (DMS)

 O ine assessment and migration using Database Migration Assistant (DMA)

Since the scope of this guide is limited to key tips and steps to troubleshoot performance issues (if
any) post migration to Azure SQL database, I will not go into much detail of migration methods. You
can refer to the detailed documentation on the links given above.

An important step after a successful migration is to validate the quality of data and ensuring it
functions properly and e ciently. If it isn’t, then it is advisable to reconcile data or probe for
performance related issues.

Before I go further, I would like to give my 2-cents that after a migration it is worth checking if the
identity column on the new Azure SQL database tables is working as expected. If you nd problem
inserting a new records, then you can RESEED (reset) them using DBCC CHECKINDENT command.

1 - How do I choose the database compatibility

level?
When you are migrating a SQL database running on SQL Server older than SQL Server 2019 to Azure
SQL database, you need to be mindful of query processing behavior, compatibility level on the
source and destination database. The following table gives a snapshot of the default and supported
compatibility level values of di erent database engines.

Database Default Supported

Database Product Engine Compatibility Compatibility

SQL Server 2019 (15.x) 15 150 150, 140, 130, 120,

110, 100

SQL Server 2017 (14.x) 14 140 140, 130, 120, 110,

100

Azure SQL Database single 12 150 150, 140, 130, 120,

database/elastic pool 110, 100

Azure SQL Database managed 12 150 150, 140, 130, 120,

instance 110, 100

SQL Server 2016 (13.x) 13 130 130, 120, 110, 100

SQL Server 2014 (12.x) 12 120 120, 110, 100

SQL Server 2012 (11.x) 11 110 110, 100 ,90

SQL Server 2008 R2 10.5 100 100 ,90 ,80

SQL Server 2019 (15.x) 10 100 100, 90, 80

By default, a new Azure SQL database is set to its default compatibility level of 150, which uses the
latest Cardinality Estimation (CE) for improved performance and new features. But, if your on-
premises source database is running at a lower compatibility level, a query execution plan may be
exposed to a risk of performance regression. Therefore, if you nd any of your queries that perform
fast on the source database which runs on the older version of Compat level (140 or lower) are less
performant on the new Azure SQL database (Compat Level 150), you may consider upgrading to the
latest Compat level in the steps mentioned below.

Step 1- While you are on the latest SQL Server engine (a new Azure SQL database), you can choose
to be on source database legacy Cardinality Estimation (CE). Use the following db scoped
con guration command to sets the whole database to use the legacy (older) CE while retaining the
latest improvements. The idea is to identify the best known query execution plan for queries
running under the legacy CE level.

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON ;

Step 2- Enable the Query Store using the following command. It is a handy-dandy tool to monitor
the performance of database queries and their execution plan right from SQL Server management
studio (you must install the latest version)

ALTER DATABASE [your database name] SET QUERY_STORE CLEAR;

ALTER DATABASE [your database name]

SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
DATA_FLUSH_INTERVAL_SECONDS = 900,
QUERY_CAPTURE_MODE = AUTO,
MAX_STORAGE_SIZE_MB = 1000,
INTERVAL_LENGTH_MINUTES = 60,
SIZE_BASED_CLEANUP_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200,
WAIT_STATS_CAPTURE_MODE = ON
);

After you have enabled the Query Store, refresh the database explorer pane to see the sections as
shown below.

Step 3- Wait for some time for the Query Store to collect some of data so that you can review the
execution plan under Top Resource Consuming Queries.
Step 4- Force the best-known query execution plan under the legacy (older) CE or consider query
tuning manually. For more information, refer the article Upgrading Databases by using the Query
Tuning Assistant.

Step 5- After you have completed query performance tuning that you identi ed in the query store,
turn OFF the LEGACY_CARDINALITY_ESTIMATION database con guration and make sure the
database is on the latest CE using the command below. You can check the compatibility level of the
databases using the following command.

SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

Step 6- Now that you have xed the low performant queries and switched back to the latest CE
level. Test your application behavior under the latest CE.

Alternatively, you can also choose the backward compatibility option and stick with the source
database compatibility level at your discretion. However, this is not a recommended option.
Switching database to the desired CE level, use the following command.

ALTER DATABASE [your_database_name] SET COMPATIBILITY_LEVEL = {150,140,130,120,110}

2 - Statistics and Query execution plan

The query optimizer by default updates the statistics of database tables and index views as needed
for the better query execution plan. UPDATE STATISTICS command OR store procedure
sp_updatestats ensure that queries compile with the latest statistics and use the enhanced query
plan. Consider updating the statistics after migration to ensure the database uses the improved
query plan.

3 - Index Tuning
Although the Azure SQL database is a managed PaaS database as a service that saves you from
lots of administrative maintenance tasks, you still need to do database tuning like rebuilding and
reorganizing the database indexing regularly for better database performance. The best part is, you
can automate this task using an Azure automation runbook or by using Azure Function apps.
Please refer to the document best practice to rebuild or reorganize indexes.

4 - Azure Database Automatic Tuning

Azure SQL database, has a built-in machine learning and AI to monitor your queries overtime and
provide recommendations to improve the performance of your database. The Auto tuning option
when enabled, either at the server or individual database level, automatically identi es and applies
the actions (Create Index or Drop Index) to enhance performance.

You also need to consider the fact that non-clustered index does not necessarily improve the
performance Or may have a negative impact on the performance . Therefore, you may consider an
option to opt-out of the Azure Auto tuning option from applying the actions automatically and just
 continue with the default behavior of identifying performance recommendations instead. You can
review recommendations in Azure portal database pane and apply them wisely assuming you have
adequate RBAC access to do so.

5 - ASYNC_NETWORK_IO
Lastly, the issue of network latency is often an ignored aspect, which is very likely when your
resources are not close to each other. For instance, you may have your application in the Azure
East US datacenter and your database is on-premises or vice versa. The ASYNC_NETWORK_IO is
the time SQL database takes to send the network packet to the application.

While you are troubleshooting the database performance, run the below query to see the current
request that the database is executing and look at WAIT_TYPE. If it is ASYNC_NETWORK_IO and is
greater than 2000ms, then it is worth looking at the reason for it.

SELECT SESSION_ID, START_TIME, BLOCKING_SESSION_ID, STATUS,COMMAND, WAIT_TYPE, LAST_WAIT_TYPE,

WAIT_TIME, tsql.text, qp.query_plan
FROM SYS.DM_EXEC_REQUESTS as requests CROSS APPLY sys.dm_exec_sql_text(requests.sql_handle) as
tsql
CROSS APPLY sys.dm_exec_text_query_plan(requests.plan_handle,0,-1) as qp

Conclusion
In this article, some common scenarios to consider to troubleshoot Azure SQL database
performance after you have migrated from SQL Server either running on-premises or a SQL Server
database running on the VM in the cloud have been discussed.

The rst crucial step is to look at the compatibility level of your source database if it is older version
of SQL Server (2017 or lower). Given the fact, you always get the latest greatest compatibility level
when you create a new database server in Azure, the change in CE level could potentially have an
impact on your query performance. We also saw the step by step guide on how to x the query
performance regression (if any) using Query Store.

We also touched upon some key recommendations on Index Tuning and Auto tuning feature of SQL
database and how it is helpful to tune the database performance. Lastly we talked about an
important part worth checking the database query response WAIT_TIME for any network IO latency.

Gurvinder Singh is a Microsoft certi ed Azure Solutions Architect with 12 years of diversi ed IT
experience, with a strong programming background and hands-on experience in .NET and C#. His
passion for technology includes Microsoft Azure platform (PaaS, IaaS and Serverless).

How to Make the Transition to DevOps Engineer

Discover a proven step-by-step game plan to move into a rewarding
career in DevOps and automation.

WATCH NOW
WATCH NOW

    

HOME PROGRAM BLOG LOG IN CONTACT PRIVACY TERMS

Built with  in Scottsdale, AZ

© 2020 CloudSkills.io