## 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 = -- 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 = -- 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 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 = , @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) [Azure] Troubleshoot blocking issues in Azure SQL ... [Azure] Execution plan and query design in SQL ser... 返回 顶部 底部