[Azure] Performance troubleshooting in Azure SQL Database

## Useful queries

```sql
-- check current running queries
SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.total_elapsed_time/ (1000 * 60) AS 'duration_ms(min)'
    , req.cpu_time AS cpu_time_ms
    , req.cpu_time/ (1000 * 60) AS 'cpu_tim(min)'
    , req.wait_time AS current_wait_time
    , req.wait_time/ (1000 * 60) AS 'current_wait_time(min)'
    , req.total_elapsed_time - req.cpu_time AS minus_wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

-- overall check runtime stat
SELECT qsq.query_id, qsq.query_hash, qst.query_sql_text, qrs.execution_type, qrs.execution_type_desc, qpx.query_plan_xml, qrs.count_executions, qrs.last_execution_time
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp on qsq.query_id=qsp.query_id
JOIN sys.query_store_query_text AS qst on qsq.query_text_id=qst.query_text_id
OUTER APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
ORDER BY qrs.last_execution_time DESC;
GO

-- get query ID/hash from statement keyword
SELECT 
    qsq.query_id,
    qsq.query_hash,
    qsq.last_execution_time,
    qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt
        ON qsq.query_text_id = qsqt.query_text_id
WHERE
    qsqt.query_sql_text LIKE '%SELECT%'; -- modify
		
		
-- Get query text from query ID
SELECT q.query_id, qt.query_sql_text, q.query_hash
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
where q.query_id = <QueryID> -- modify

-- check sepcific query
SELECT Txt.query_text_id, Txt.query_sql_text, Run.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS Run
    on Run.plan_id = Pl.plan_id
WHERE Qry.query_id = <QueryID> -- modify

-- check execution numbers
SELECT
    YEAR(start_time) AS 'year', MONTH(start_time) AS 'month', DAY(start_time) AS 'day', DATEPART(hour, start_time) AS 'hour',
    SUM (rs.count_executions) AS 'execution_count',
    SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS 'regular_execution_count',
    SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS 'aborted_execution_count',
    SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS 'exception_execution_count',
    ROUND(CONVERT(float, SUM(rs.avg_cpu_time * rs.count_executions)) / NULLIF(SUM(rs.count_executions), 0) * 0.001, 0) AS 'avg_cpu_time',
    ROUND(CONVERT(float, SUM(rs.avg_duration * rs.count_executions)) / NULLIF(SUM(rs.count_executions), 0) * 0.001, 0) AS 'avg_duration',
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads * rs.count_executions)) / NULLIF(SUM(rs.count_executions), 0) * 8, 0) AS 'avg_logical_io_reads'
FROM sys.query_store_runtime_stats rs (NOLOCK)
INNER JOIN sys.query_store_runtime_stats_interval i ON rs.runtime_stats_interval_id = i.runtime_stats_interval_id
JOIN sys.query_store_plan AS qp ON rs.plan_id = qp.plan_id
JOIN sys.query_store_query AS qq ON qp.query_id = qq.query_id
-- WHERE qq.query_id = 1234
-- limit to time window as appropriate:
-- WHERE start_time >= '2023-01-15 09:00:00' AND start_time <= '2023-01-18 18:00:00' 
GROUP BY YEAR(start_time), MONTH(start_time), DAY(start_time), DATEPART(hour, start_time)
ORDER BY YEAR(start_time), MONTH(start_time), DAY(start_time), DATEPART(hour, start_time);

-- check for stored procedure
-- https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/monitoring-performance-of-natively-compiled-stored-procedures?view=sql-server-ver17#sample-queries
SELECT object_id, object_name(object_id) AS 'object name',
       cached_time, last_execution_time, execution_count,
       total_worker_time, last_worker_time,
       min_worker_time, max_worker_time,
       total_elapsed_time, last_elapsed_time,
       min_elapsed_time, max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()

-- check timed out(aborted) queries
SELECT
  qst.query_sql_text,
  qrs.execution_type,
  qrs.execution_type_desc,
  qpx.query_plan_xml,
  qrs.count_executions,
  qrs.last_execution_time
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
  ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text AS qst
  ON qst.query_text_id = qsq.query_text_id
OUTER APPLY
(
    SELECT
        query_plan_xml = TRY_CONVERT(XML, qsp.query_plan)
) AS qpx
JOIN sys.query_store_runtime_stats AS qrs
  ON qrs.plan_id = qsp.plan_id
WHERE qrs.execution_type = 3
ORDER BY
    qrs.last_execution_time DESC;
GO
 
```

## Useful link

General guide 
- [Troubleshoot slow-running queries - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-slow-running-queries#introduction)
- [Troubleshoot performance issues with Intelligent Insights - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn](https://learn.microsoft.com/en-us/azure/azure-sql/database/intelligent-insights-troubleshoot-performance?view=azuresql)
-[Performance Tuning Guidance for Applications and Databases - Azure SQL Database | Microsoft Learn](https://learn.microsoft.com/en-us/azure/azure-sql/database/performance-guidance?view=azuresql)

Query Store
- [Use the Regressed Queries feature](https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver17#use-the-regressed-queries-feature)

identify bottleneck:
- [Detectable types of query performance bottlenecks - Azure SQL Database | Microsoft Learn](https://learn.microsoft.com/en-us/azure/azure-sql/database/identify-query-performance-issues?view=azuresql&source=recommendations)
- high CPU: [Diagnose and Troubleshoot High CPU - Azure SQL Database | Microsoft Learn](https://learn.microsoft.com/en-us/azure/azure-sql/database/high-cpu-diagnose-troubleshoot?view=azuresql)

### CPU bound

[Troubleshoot high-CPU-usage issues in SQL Server - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues)

Although there are many possible causes of high CPU usage that occur in SQL Server, the following ones are the most common causes:
- High logical reads that are caused by table or index scans because of the following conditions:
   - Out-of-date statistics
   - Missing indexes
   - Parameter sensitive plan (PSP) issues
   - Poorly designed queries
- Increase in workload

#### About MAXDOP

[Thread and task architecture guide - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/thread-and-task-architecture-guide?view=sql-server-ver17#workers)
The max degree of parallelism (MAXDOP) limit is set per task, not per request. This means that during a parallel query execution, a single request can spawn multiple tasks up to the MAXDOP limit, and each task will use one worker. 
The number of worker threads spawned for each task depends on:
- Whether the request was eligible for parallelism as determined by the Query Optimizer.
- What the actual available degree of parallelism (DOP) in the system is, based on current load. This may differ from estimated DOP, which is based on the server configuration for max degree of parallelism (MAXDOP). For example, the server configuration for MAXDOP may be 8 but the available DOP at runtime can be only 2, which affects query performance. Memory pressure and lack of workers are two conditions which reduce available DOP at runtime.

```sql
-- Check for DOP values in Query Store 
SELECT q.query_id,  q.query_sql_text,  qs.count_executions,  qs.avg_dop,  qs.max_dop  
FROM sys.query_store_query q  
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id  
JOIN sys.query_store_plan qsp ON q.query_id = qsp.query_id  
JOIN sys.query_store_runtime_stats qs ON qsp.plan_id = qs.plan_id
-- WHERE q.query_id = 
ORDER BY  qs.max_dop DESC;

-- Check for DOP values in DMV
SELECT  
    qs.execution_count,  
    qs.total_worker_time / qs.execution_count AS avg_worker_time,  
    qs.max_dop,  -- MAXDOP used
    st.text AS sql_text  
FROM sys.dm_exec_query_stats qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  
ORDER BY qs.total_worker_time DESC;
```

### Plan analysis: optimizer timeouts

Recently I handled a query performance issue which is quite interesting. The query is good when it retrieves several months data. But when set the parameter to retrieve one month data, the performance degradated a lot even with less data.

We found that it's using different plans. The operations are quite similar, and the overall time took on each operation in bad plan is higher than the good plan, no apparent bottleneck on specific operation. The most suspicious point is that the "Reason for early termination of Statement Optimization" marked "Timed out" in bad plan. There are warnings for type conversion and missing index in both plans.

This blog shared insights on optimizer timeouts:  [Understanding Optimizer Timeout and how Complex queries can be Affected in SQL Server | Microsoft Community Hub](https://techcommunity.microsoft.com/blog/sqlserversupport/understanding-optimizer-timeout-and-how-complex-queries-can-be-affected-in-sql-s/319188)

The query itself is a very complex query and involves lots of joined tables. As mentioned in the blog above, **given example 10 tables to join, the possible permutations are in millions.** Therefore, we can see that a query with lots of joins is more likely to reach the optimizer timeout threshold than one with fewer joins.

I recommended the following quick mitigations and long-run improvement actions.
- Update statistics
[UPDATE STATISTICS (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver17#sample-number--percent--rows%2D)
> For most workloads, a full scan isn't required, and default sampling is adequate. However, certain workloads that are sensitive to widely varying data distributions might require an increased sample size, or even a full scan.

`UPDATE STATISTICS Tablename WITH FULLSCAN;`

- Add query hint `OPTION(RECOMPILE)` to recompile the query and see if improves.
- Force a good plan with [sp_query_store_force_plan (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql?view=sql-server-ver17).

Improvements for the long run:
- Review the warnings for type conversion and missing index and determine whether corrective actions are needed.
- Rewrite the query to reduce complexity and reduce joins.

### Timeouts

Useful links
- [Troubleshoot query time-out errors](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-timeouts)
- [QTip: Getting timeouts and exceptions using Azure SQL DB Query Store](https://techcommunity.microsoft.com/blog/azuredbsupport/qtip-getting-timeouts-and-exceptions-using-azure-sql-db-query-store/4427310)

```sql
declare @datestart as datetime = dateadd(D,-2,getdate());
declare @datefinish as datetime = getdate();

/* if you want to set to specific time */
--set @datestart = '2025-04-09 00:00:00';
--set @datefinish = '2025-04-09 23:59:59';

select rs.last_execution_time, rs.execution_type_desc, qt.query_sql_text, q.query_id, CONVERT(VARCHAR(1000), q.query_hash, 1) as strqueryhash, p.plan_id, 
rs.last_cpu_time, rs.last_duration, rs.count_executions, rs.last_rowcount,
rs.last_logical_io_reads, rs.last_physical_io_reads, rs.last_query_max_used_memory, rs.last_tempdb_space_used, rs.last_dop,
p.is_forced_plan, p.last_force_failure_reason, p.last_force_failure_reason_desc
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
where rs.last_execution_time>= @datestart and rs.last_execution_time<=@datefinish 
and (rs.execution_type=3 or rs.execution_type=4)  -- 3 timeout, 4 error
--and  qt.query_sql_text like '%actual%'
--and q.query_hash=0x009C458D20394C37
--and p.plan_id=12
ORDER BY rs.last_execution_time DESC
```

### Compilation timeouts

If we found query/command timed out in audit logs, but there is no matching records in DMV and query store,  it could be compilation timeouts.

### Plan regression due to statistics changes

同じクエリハッシュが複数のプランハッシュにコンパイルされ、論理読み取りや経過時間に大きな変動がある事象が発生しました。統計が変更されて再コンパイルされた場合、クエリオプティマイザーは変化した時点から統計を使用します。ただし、カーディナリティ推定が異なる結果を導き、最適化されていない実行プランが選択される可能性がございます。

以下は、よく利用される対策をまとめた内容です。公式ドキュメント[検出可能なクエリのパフォーマンス ボトルネックの種類 - Azure SQL Database | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/azure-sql/database/identify-query-performance-issues?view=azuresql#compilation-problems-resulting-in-a-suboptimal-query-plan) を併せてご参照ください。
 
・統計とインデックスの管理

主なテーブルについて統計を再更新し、カーディナリティ推定を正確化してください。

`UPDATE STATISTICS <table> WITH FULLSCAN;`
 
インデックスの断片化確認と再構築

[インデックスを最適に維持してパフォーマンスを向上させ、リソース使用率を削減する - SQL Server | Microsoft Learn](https://learn.microsoft.com/ja-jp/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver17)
 
・最適なプランの強制
クエリストアを使用して、過去の最適なプランを特定し、該当プランを強制することでパフォーマンスの安定化を図ります。
以下の手順をご参考ください。
 
```sql
-- クエリIDおよびプランハッシュを取得
SELECT q.query_id, p.plan_id, p.query_plan_hash,
       SUM(rs.count_executions * rs.avg_duration) AS total_duration_ms,
       SUM(rs.count_executions * rs.avg_cpu_time) AS total_cpu_ms
FROM sys.query_store_query AS q
JOIN sys.query_store_plan  AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE q.query_hash = xxxxx
GROUP BY q.query_id, p.plan_id, p.query_plan_hash
ORDER BY total_duration_ms;
 
-- 最適なプランを強制
EXEC sp_query_store_force_plan @query_id = <query_id>, @plan_id = <plan_id>;
  ```
	
・不足しているインデックスの特定
[インデックス候補が見つからない非クラスター化インデックスを調整する - SQL Server | Microsoft Learn](https://learn.microsoft.com/ja-jp/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions?view=sql-server-ver17#view-missing-index-recommendations)
 
・クエリステートメントの最適化
下記のリンクはSELECT ステートメントの処理で説明した基本的な手順ですが、INSERT、UPDATE、DELETE などの他の Transact-SQL ステートメントにも適用されます。 
[クエリ処理アーキテクチャガイド - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver17#optimizing-select-statements)