A few ways to monitor performance for the geo replica of an Azure SQL Database

Azure SQL Database, one of the PaaS offerings on the Azure platform, offers many features that differ from those available in on-premises SQL Server, allowing you to focus on your business logic and requirements. For example, Query Performance Insight provides intelligent query analysis for single and pooled databases, and Auto-Tune 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 Query Store data being in read-only mode in geo-replicas of Azure SQL Database. This limitation limits your ability to capture information or execution statistics about all queries that run against a geo-replica. All Query Store data on geo-replicas comes from the primary Query Store. As a result, the performance insights provided by the mentioned features are invalid and automatic scaling is not available for geo-replicas. A warning appears on the Capabilities page in the Azure portal, as shown below.

This annoys users who are accustomed to leveraging these features to diagnose performance issues. Without Query Store collecting and storing data about geo-replicas, it becomes difficult to analyze past performance issues, such as identifying queries that caused CPU spikes within a specific time period.

Therefore, in this blog post, I will share some ways to capture query information that can help you troubleshoot performance issues with Geo Replicas.

Continuously collects query wait type and blocking information from multiple DMVs

• Download the script from: Main AzureSQLQueries/README.md · mchangchien/AzureSQLQueries(github.com)
• Follow the instructions in the readme file to collect query data. (I also posted it here)
  • Download the script locally (e.g. C:/temp).
  • Open Command Prompt in Administrator Mode on your PC
  • To start collecting query data, run the command: (SQLCMD must have been previously installed on your PC, and you must replace the server name, DB name, user name, and password below with your own.) Sqlcmd -S servername.database.windows.net -U azureuser -P xxxxx -d db1 -i SQL_Azure_Perf_Stats.sql -o Blocking.out
  • Wait until something goes wrong.
  • Stop the sqlcmd.exe command using Ctrl+C.
  • Examine the output file and look at how long the performance issue occurred.

Starting an extended events session on a Geo replica

As described in Read queries on replicas – Azure SQL Database and Azure SQL Managed Instance | microsoft runYou can monitor geo-replicas through extended events sessions. The steps are as follows:

• Create an Azure Storage account container or use an existing container for your Xevent session.
• 

• Go to the ‘Shared Access Tokens’ tab in your container, check the required permissions (read/write/list) to generate a SAS token, set an appropriate expiration date, and click ‘Generate SAS Token and URL’.

• Copy the ‘Blob SAS Token’ and paste it somewhere. I’ll use it later.

• Start SSMS and connect to your default Azure SQL database. (Extended events can only be generated on the primary and are then replicated on the secondary.)

• You will need to run the script below to create a session on your desired database and enter the SAS token (generated in the previous step) and Azure Storage container URL into the script.

—- Transact-SQL code for event file destination in SQL Database (database level)

Set NOCOUNT to ON.

go

—— Step 1. Key generation and ————

—— Create credentials (Azure Storage container must already exist).

If it doesn’t exist

(SELECT * FROM sys.symmetric_keys

where symmetric_key_id = 101)

start

Generate master key encryption with password = ‘Pa$$w0rd’ — or newid().

end

go

if exists

(SELECT * FROM sys.database_scoped_credentials

where name=”https://removed.blob.core.windows.net/xevents“)

start

Delete database scoped credentials [https://removed.blob.core.windows.net/xevents] ;

end

go

Create database scoped credentials

[https://removed.blob.core.windows.net/xevents]

with

IDENTITY = ‘Shared Access Signature’, — “SAS” token.

SECRET = ‘SAS Token’;

go

—— Step 2. Create (define) an event session. ——–

—— Events Sessions contain events that contain actions.

—— And a has a goal.

if exists

(Select * from sys.database_event_sessions.

WHERE name=”AzureDBTrace1″)

start

Delete the event session AzureDBTrace1 from the database.

end

go

Create an event session [AzureDBTrace1] from database

Add event query_post_compilation_showplan(ACTION(event_sequence, request_id, session_id, data_name, client_app_name, client_hostname, username, client_pid,sql_text,query_plan_hash,query_plan_hash_signed,query_hash,query_hash_signed));

Additional event query_pre_execution_showplan(ACTION(event_sequence, request_id, session_id, data_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, data_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 file name=”https://removed.blob.core.windows.net/xevents/AzureDBTrace_1.xel“

)

with(

MAX_MEMORY = 10 MB;

MAX_DISPATCH_LATENCY = 3 seconds)

go

• If there are no errors, the event session was created. You can start/stop a session by running the query below:

Change AzureDBTrace1 event session in database

state = start;

go

Change AzureDBTrace1 event session in database

state = stop;

go

• When the event session is stopped, you will see the xel file generated in the container.

• Check query execution information (query plan, execution statistics, etc.) through Trace.

Hope this helps!