Azure SQL Database, one of the PaaS offerings on the Azure platform, provides several features that are distinct from those available for on-premises SQL Servers, allowing users to focus on their business logic and needs. For instance, Query Performance Insight offers intelligent query analysis for single and pooled databases, and Automatic Tuning provides continuous performance tuning based on AI and machine learning. Both features help users recognize performance bottlenecks and identify unusual or problematic queries.
However, the mentioned features depend on the Query Store data, which is in read-only mode on the geo-replica of an Azure SQL Database. This limitation restricts its ability to capture information or execution stats for any query run against the geo-replica. All the Query Store data on the geo-replica comes from the primary Query Store. Consequently, the performance insights provided by the mentioned features are not valid, and Automatic Tuning is not available for geo-replicas. You should see warnings on the feature pages in the Azure Portal as shown below:
This bothers users who are accustomed to utilizing these features to diagnose their performance issues. Without Query Store collecting and storing data for geo replicas, it becomes difficult to analyze past performance issues, such as identifying which query caused CPU spikes within a certain time period.
Hence, in this blog post, I will share a few methods to capture such query information, aimed at helping you troubleshoot performance issues on Geo replicas.
Collecting query wait types and blocking information continuously from several DMVs
- Download the scripts from AzureSQLQueries/README.md at main · mchangchien/AzureSQLQueries (github.com)
- Follow the instructions in Readme file to collect query data. (I have put them here as well)
- Download the scripts to local (for example C:/temp)
- Open Command Prompt with Administrator mode on your PC
- Run command to start collecting query data (You need to have SQLCMD installed on your PC prior to this, and remember to replace the servername, db name, username and password below with yours): Sqlcmd -S servername.database.windows.net -U azureuser -P xxxxx -d db1 -i SQL_Azure_Perf_Stats.sql -o blocking.out
- Wait until the problem occurs
- Stop the command sqlcmd.exe by Ctrl+C
- Examine the out file and look at the period when you had the performance issue.
Starting an Extended Event session on Geo replica
As described in Read queries on replicas - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn, you can monitor geo replica with an Extended Event session. The steps are listed below
- Create an Azure Storage account container or use existing one for Xevent session
-
- Go to the ‘Shared access tokens’ tab in the container to generate the SAS tokens by checking the necessary permissions (read/write/list), set the suitable expiry date, and click ‘Generate SAS token and URL’
- copy the ‘Blob SAS token’ and paste it to somewhere – we will use it later
- Start SSMS and connect to the primary Azure SQL database. (Extended Event can only be created on the primary, it will be replicated to the secondary afterwards)
- Run the script below to create session in the desired database, and remember to put your SAS token (generated from the previous step) and Azure storage container URL in the script
---- Transact-SQL code for Event File target on SQL Database.(database level)
SET NOCOUNT ON;
GO
------ Step 1. Create key, and ------------
------ Create credential (your Azure Storage container must already exist).
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys
WHERE symmetric_key_id = 101)
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd' -- Or any newid().
END
GO
IF EXISTS
(SELECT * FROM sys.database_scoped_credentials
WHERE name = 'https://removed.blob.core.windows.net/xevents')
BEGIN
DROP DATABASE SCOPED CREDENTIAL [https://removed.blob.core.windows.net/xevents] ;
END
GO
CREATE DATABASE SCOPED CREDENTIAL
[https://removed.blob.core.windows.net/xevents]
WITH
IDENTITY = 'SHARED ACCESS SIGNATURE', -- "SAS" token.
SECRET = 'Your SAS token';
GO
------ Step 2. Create (define) an event session. --------
------ The event session has an event with an action,
------ and a has a target.
IF EXISTS
(SELECT * from sys.database_event_sessions
WHERE name = 'AzureDBTrace1')
BEGIN
DROP EVENT SESSION AzureDBTrace1 ON DATABASE;
END
GO
CREATE EVENT SESSION [AzureDBTrace1] ON DATABASE
ADD EVENT query_post_compilation_showplan(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT query_pre_execution_showplan(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sp_statement_completed(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sp_statement_starting(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sql_batch_completed(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sql_batch_starting(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sql_statement_completed(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sql_statement_recompile(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sql_statement_starting(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sqlos.wait_info(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sqlserver.blocked_process_report(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sqlserver.blocked_process_report_filtered(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sqlserver.rpc_completed(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed)),
ADD EVENT sqlserver.rpc_starting(ACTION(event_sequence, request_id, session_id, database_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed))
ADD TARGET
package0.event_file(
SET filename ='https://removed.blob.core.windows.net/xevents/AzureDBTrace_1.xel'
)
WITH(
MAX_MEMORY = 10 MB,
MAX_DISPATCH_LATENCY = 3 SECONDS)
GO
- If there’s no error, the event session has been created. You can run below query to start/stop the session.
ALTER EVENT SESSION AzureDBTrace1 ON DATABASE
STATE = START;
GO
ALTER EVENT SESSION AzureDBTrace1 ON DATABASE
STATE = STOP;
GO
- After the event session is stop, you should see the xel file generated in the container:
- Examine the trace to check the query execution information (query plan, execution stats,,etc)
Hope this helps!
Microsoft
