Methods for Performance Tuning on Azure SQL Database

Since Azure SQL Database is database-scoped, there are some significant differences in performance tuning.

Microsoft provides some ways to leverage and find out details about how your database is performing in Azure.

Let’s see a few of these.

1. Extended Events

Extended Events (also called XEvent) provide a powerful tool to troubleshoot.

An XEvent session in Azure SQL Database is not the same as a typical SQL Server.

Since you don’t have access to the server’s file system, you must configure a storage account (to persist the Extended Events data).

There are some great articles on troubleshooting a specific issue with Extended Events.

Microsoft’s page Extended Events in Azure SQL Database provides samples and more details about using Extended Events to improve performance issues.

In short, you need to create:

• A storage account (or use an existing one)
• A database-scoped credential so you can use the storage account
• And finally, the Extended Event session.

2. Query Store

Enabled by default on Azure SQL Server, the Query Store automatically captures a history of queries, plans, and runtime statistics.

It acts as a “flight data recorder” for the database, continuously collecting critical information about the queries.

Using it, you can review a list of the highest resource-consuming queries and drill down further by selecting individual queries to view more details.

In short, the Query Store is going to improve your productivity significantly:

• Finding and fixing application performance regressions. With Query Store, you can force an optimal plan for future query execution.
• Identifying the most expensive queries considering different consumption metrics (CPU time, elapsed time, used memory, read operations, etc.);
• Keeping performance stability with compatibility level 130 in Azure SQL Database (more details in this blog article);
• Comparing workload performance before and after the application change you plan to introduce (A/B testing);
• Identifying and improving ad-hoc workloads (more details here);

3. Query performance insight

Query performance gets insight into resource consumption.

You can access the query performance insight using the web portal or REST API (programmatic access).

It helps to understand easily where your database transaction units (DTU) are being spent.

Understanding DTU measures is essential to determine the right service tier/performance level needs (scale up/down).

When the server DTU hits the limit, latencies for the requests increase. In some cases, new requests can get rejected with an error when the concurrent request limit for DB is reached.

For more details, see this post about how to choose the right service database.

4. DMVs or Dynamic Management Views

Dynamic Management Views (DMVs) are virtual views that expose SQL Server deployment’s internal states.

They are essential to monitor and troubleshoot any SQL Server deployments from on-premise to cloud-enabled versions.

Unfortunately, real-time troubleshooting of certain conditions is not possible. You quickly find lots of online resources from the SQL community for troubleshooting.

Azure SQL Database partially supports three categories of dynamic management views:

• Session-related dynamic management views:
  • sys.dm_exec_connections: Active connections to the database.
  • sys.dm_exec_sessions: Active sessions to the database.
  • sys.dm_exec_requests Active requests to the database.
• Execution-related dynamic management views:
  • sys.dm_db_missing_index_details: Missing indexes that would increase the query performance.
  • sys.dm_db_missing_index_columns: Missing table columns for a given index.
  • sys.dm_db_missing_index_groups: Missing indexes are contained in a specific missing index group, excluding spatial indexes.
  • sys.dm_db_missing_index_group_stats: Groups of missing indexes, excluding spatial indexes.
  • sys.dm_db_index_usage_stats: Information about the usage of an index.
  • sys.dm_db_index_physical_stats: Information about the physical layout for a given index (space consumption etc).
  • sys.dm_db_index_operational_stats: Information about the performance for a given index.
  • sys.dm_exec_procedure_stats: Usage of stored procedures in the database.
  • sys.dm_exec_trigger_stats: Usage of triggers in the database.
  • sys.dm_exec_query_memory_grants: Queries waiting for memory before they can be executed.
  • sys.dm_exec_cached_plans: Execution plans that are currently in the case.
  • sys.dm_db_partition_stats: Information about the storage of tables/indexes.
  • sys.dm_db_wait_stats: Waits in the database (new DMV specific to SQL Server Database).
  • sys.dm_tran_locks: Active locks in the database.
• Transaction-related dynamic management views:
  • sys.dm_tran_active_transactions: Active user transactions to the database.
  • sys.dm_tran_database_transactions: Active transactions in the database.
  • sys.dm_tran_session_transactions: Active transactions in the session.

After reviewing the appropriate DMVs, it is then possible to troubleshoot any performance issues you encounter.

For more details, see Dynamic Management Views and Functions in SQL Server Books Online.

5. Database advisor (automatic index management)

Azure SQL Database continuously monitors the queries and identifies the indexes that could improve performance.

It creates indexes by estimating the performance gain the index would bring through time. The index management also removes unused or duplicate indexes.

Any index recommendations are captured in a DMV to be reviewed. You can see which indexes are auto-created by looking at the sys.indexes view.

Database advisor continuously analyzes the performance of an existing index. So, if an index is not used, the Azure SQL Database recommends dropping it.

We recommend only dropping an index in two cases:

• Duplicated index (same indexed and included column, partition schema, and filters).
• Index unused for a prolonged period (93 days).

6. Adaptive Query Processing

Starting with SQL Server 2017 (14.x)) and Azure SQL Database, SQL Server offers a feature family called Adaptive Query Processing (AQP).

AQP improves the cardinality estimator (CE) process and automatically adapts optimization strategies to runtime conditions of your SQL Server workload.

AQP offers three techniques for adapting to application workload characteristics:

Batch mode memory grant feedback

Insufficient memory grants cause expensive spills to disk, bringing performance issues. This feature enables the SQL Server Query Processing Engine to learn that memory grants are not sufficient.

The batch mode memory grant feedback feature is useful for recalculating the actual memory that a query requires and updating the grant value appropriately.

Batch mode adaptive joins

The SQL Server typically chooses among three types of physical join operators: nested loop joins, merge joins, and hash joins.

Each type of join has strengths and weaknesses.

The batch mode adaptive defines a threshold used to decide when to switch between nested loop joins or hash joins. The threshold is calculated for individual statements depending on input data.

Consequently, a plan can dynamically switch to a better join strategy during execution.

Interleaved execution

Multi-statement table-valued functions (MSTVFs) are popular among developers, although their cardinality issues can cause performance slowdowns.

Interleaved execution helps to fight cardinality issues.

Suppose the estimates are way off than the actual ones. In that case, SQL Server Query Optimizer adjusts the execution plan by executing a part of the query execution plan first and re-designing the query execution plan on the actual number of rows.

The compiler pauses on facing a candidate for interleaved execution during a query optimization phase. Next, it executes a query subtree for the MSTVF.

In the end, the compiler captures the correct cardinality estimation and resumes previously paused operations.