5 Tips for Troubleshooting Azure

SQL Database Performance

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)

Gurvinder Singh on January, 22, 2020
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 Product Supported Compatibility

Database Engine Supported Compatibility
Default Compatibility Supported Compatibility
Default 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 Azure SQL Database managed

database/elastic pool instance
12 150 150, 140, 130, 120, 12 150 150, 140, 130, 120,
110, 100 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