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 offers 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 offered by Azure SQL database, there are few who may not be convinced that it offers 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

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 efficiently. 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 find 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 different database engines.

Database Product Database Engine Default Compatibility Supported 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 database/elastic pool 12 150 150, 140, 130, 120, 110, 100
Azure SQL Database managed instance 12 150 150, 140, 130, 120, 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 find 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 configuration 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.

Alt Text

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 identified in the query store, turn OFF the LEGACY_CARDINALITY_ESTIMATION database configuration 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 fixed 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 identifies 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 first 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 fix 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 certified Azure Solutions Architect with 12 years of diversified 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.