Since Azure SQL Database is database-scoped, there are some significant differences when it comes to performance tuning.
Microsoft provides some ways to leverage and find out details about how your database is performing in Azure. Let 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 need to configure a storage account (to persist the extended event data).
There are some great articles on how to troubleshoot 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, 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 through REST API (Programmatic access).
It helps to understand in an easy way where your DTUs are being spent. Understanding DTU measures are 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 detail, see this post about how to choose the right service database.
4) DMV’s or Dynamic Management Views
Dynamic Management Views (DMVs) are virtual views that expose SQL Server deployment’s internal states. They are an essential tool 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 on-line 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 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 DMV’s, 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 drop 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 process and automatically adapts optimization strategies to runtime conditions of your SQL workload.
AQP offers three techniques for adapting to application workload characteristics:
There are currently three techniques in Azure 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 upon 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-design the Query Execution Plan on the actual number of the rows.
The compiler pauses on facing a candidate for interleaved execution during 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.
SQL Server performance tuning can be complicated, frustrating, and time-consuming. Make SQL Performance Tuning someone else’s problem.
