【Azure SQL】資料庫性能分析

来源:https://www.cnblogs.com/WilsonPan/archive/2020/06/18/13153400.html
-Advertisement-
Play Games

前置條件 用戶有查詢數據統計許可權 GRANT VIEW DATABASE STATE TO database_user; CPU性能問題 正在發生 查看前X個CPU消耗查詢 (彙總) SELECT TOP 10 GETDATE() runtime, * FROM ( SELECT query_sta ...


前置條件

  • 用戶有查詢數據統計許可權
GRANT VIEW DATABASE STATE TO database_user;

CPU性能問題

正在發生

  • 查看前X個CPU消耗查詢 (彙總)
SELECT TOP 10 GETDATE() runtime, * FROM (
	SELECT query_stats.query_hash,
				SUM (query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms',
				SUM (logical_reads) 'Total_Request_Logical_Reads',
				MIN (start_time) 'Earliest_Request_start_Time',
				COUNT (*) 'Number_Of_Requests',
				SUBSTRING (REPLACE(REPLACE(MIN (query_stats.statement_text),CHAR (10),' '),CHAR (13),' '),1,256) AS "Statement_Text" FROM (
				SELECT req.*,
				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) AS statement_text 
				FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) AS ST) AS query_stats 
				GROUP BY query_hash) AS t 
ORDER BY Total_Request_Cpu_Time_Ms DESC;
  • 查看前X個CPU消耗查詢(按會話)
PRINT '--top 10 Active CPU Consuming Queries by sessions--'; 
SELECT TOP 10 req.session_id,req.start_time,cpu_time 'cpu_time_ms',OBJECT_NAME(ST.objectid,ST.dbid) 'ObjectName',
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 cpu_time DESC;
GO

歷史發生

  • 統計某個時間段內前X CPU耗時查詢
DECLARE @nums int = 15;
DECLARE @beginTime datetime2 = DATEADD(DAY, -1,GETUTCDATE());
DECLARE @endTime datetime2 = GETUTCDATE();

WITH AggregatedCPU AS (
	SELECT q.query_hash,
	SUM (count_executions*avg_cpu_time/1000.0) AS total_cpu_millisec,
	SUM (count_executions*avg_cpu_time/1000.0)/SUM (count_executions) AS avg_cpu_millisec,
	MAX (rs.max_cpu_time /1000.00) AS max_cpu_millisec,
	MAX (max_logical_io_reads) max_logical_reads,
	COUNT (DISTINCT p.plan_id) AS number_of_distinct_plans,
	COUNT (DISTINCT p.query_id) AS number_of_distinct_query_ids,
	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='Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count,
	SUM (CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count,
	SUM (count_executions) AS total_executions,MIN (qt.query_sql_text) AS sampled_query_text 
	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 rs.plan_id=p.plan_id 
	JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id 
	WHERE rs.execution_type_desc IN ('Regular','Aborted','Exception') AND rsi.start_time>= @beginTime AND rsi.start_time < @endTime AND count_executions > 1
	GROUP BY q.query_hash),OrderedCPU AS (
		SELECT query_hash,
		total_cpu_millisec,
		avg_cpu_millisec,
		max_cpu_millisec,
		max_logical_reads,
		number_of_distinct_plans,
		number_of_distinct_query_ids,
		total_executions,Aborted_Execution_Count,
		Regular_Execution_Count,Exception_Execution_Count,
		sampled_query_text,ROW_NUMBER () OVER (ORDER BY total_cpu_millisec DESC,query_hash ASC) AS RN 
		FROM AggregatedCPU) 


SELECT OD.query_hash,OD.total_cpu_millisec,OD.avg_cpu_millisec,OD.max_cpu_millisec,OD.max_logical_reads,OD.number_of_distinct_plans,OD.number_of_distinct_query_ids,OD.total_executions,OD.Aborted_Execution_Count,OD.Regular_Execution_Count,OD.Exception_Execution_Count,OD.sampled_query_text,OD.RN 
FROM OrderedCPU AS OD 
WHERE OD.RN <= @nums
ORDER BY avg_cpu_millisec DESC;

IO性能問題

當前發生

  • 查看一個小時內每15S , IO使用情況
SELECT end_time, avg_data_io_percent, avg_log_write_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

歷史發生

  • 統計時間段IO等待情況
-- top queries that waited on buffer
-- note these are finished queries
WITH Aggregated AS (SELECT q.query_hash, SUM(total_query_wait_time_ms) total_wait_time_ms, SUM(total_query_wait_time_ms / avg_query_wait_time_ms) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text, MIN(wait_category_desc) AS wait_category_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_wait_stats AS waits ON waits.plan_id=p.plan_id
                         JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=waits.runtime_stats_interval_id
                    WHERE wait_category_desc='Buffer IO' AND rsi.start_time>=DATEADD(HOUR, -24, GETUTCDATE())
                    GROUP BY q.query_hash), Ordered AS (SELECT query_hash, total_executions, total_wait_time_ms, sampled_query_text, wait_category_desc, ROW_NUMBER() OVER (ORDER BY total_wait_time_ms DESC, query_hash ASC) AS RN
                                                        FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.RN
FROM Ordered AS OD
WHERE OD.RN<=15
ORDER BY total_wait_time_ms DESC;
GO
  • 寫入日誌最多查詢
-- Top transaction log consumers
-- Adjust the time window by changing
-- rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
WITH AggregatedLogUsed
AS (SELECT q.query_hash,
           SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec,
           SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_millisec,
           SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
           MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec,
           MAX(max_logical_io_reads) max_logical_reads,
           COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans,
           COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids,
           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 = 'Regular' THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Regular_Execution_Count,
           SUM(   CASE
                      WHEN rs.execution_type_desc = 'Exception' THEN
                          count_executions
                      ELSE
                          0
                  END
              ) AS Exception_Execution_Count,
           SUM(count_executions) AS total_executions,
           MIN(qt.query_sql_text) AS sampled_query_text
    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 rs.plan_id = p.plan_id
        JOIN sys.query_store_runtime_stats_interval AS rsi
            ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE rs.execution_type_desc IN ( 'Regular', 'Aborted', 'Exception' )
          AND rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
    GROUP BY q.query_hash),
     OrderedLogUsed
AS (SELECT query_hash,
           total_log_bytes_used,
           number_of_distinct_plans,
           number_of_distinct_query_ids,
           total_executions,
           Aborted_Execution_Count,
           Regular_Execution_Count,
           Exception_Execution_Count,
           sampled_query_text,
           ROW_NUMBER() OVER (ORDER BY total_log_bytes_used DESC, query_hash ASC) AS RN
    FROM AggregatedLogUsed)
SELECT OD.total_log_bytes_used,
			 (OD.total_log_bytes_used / OD.total_executions) avg_log_bytes_used,
       OD.number_of_distinct_plans,
       OD.number_of_distinct_query_ids,
       OD.total_executions,
       OD.Aborted_Execution_Count,
       OD.Regular_Execution_Count,
       OD.Exception_Execution_Count,
       OD.sampled_query_text,
       OD.RN
FROM OrderedLogUsed AS OD
WHERE OD.RN <= 15
ORDER BY total_log_bytes_used DESC;
GO

連接數查詢

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;

伺服器使用情況

SELECT  
    AVG(avg_cpu_percent) AS 'Average CPU use in percent',
    MAX(avg_cpu_percent) AS 'Maximum CPU use in percent',
    AVG(avg_data_io_percent) AS 'Average data IO in percent',
    MAX(avg_data_io_percent) AS 'Maximum data IO in percent',
    AVG(avg_log_write_percent) AS 'Average log write use in percent',
    MAX(avg_log_write_percent) AS 'Maximum log write use in percent',
    AVG(avg_memory_usage_percent) AS 'Average memory use in percent',
    MAX(avg_memory_usage_percent) AS 'Maximum memory use in percent'
FROM sys.dm_db_resource_stats;

前X個消耗最多平均CPU時間查詢

SELECT TOP 10 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
     MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
        SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
            ((CASE statement_end_offset
                WHEN -1 THEN DATALENGTH(ST.text)
                ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

轉載請標明出處 : https://www.cnblogs.com/WilsonPan/p/13153400.html


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MySQL基礎筆記整理
    MySQL安裝和啟動 Windows下安裝和啟動服務 下載地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads 安裝 1 在MySQL官網下載zip文件， 2 在任意位置保存解壓。這裡在E盤創建MySQL目錄 E:\MySQL\ 3 ...
  • Redis 數據結構 之 SDS
    SDS(simple dynamic string)，簡單動態字元串。s同時它被稱為 Hacking String。hack 的地方就在 sds 保存了字元串的長度以及剩餘空間。sds 的實現在 sds.c 中。 C語言字元串使用長度為n+1的字元數組來表示長度為n的字元串，並且字元數組的最後一個元 ...
  • oracle編譯函數卡死問題
    SELECT * FROM V$DB_OBJECT_CACHE WHERE name=upper('Fn_JS_DBlink_BM') AND LOCKS!='0';select SID from V$ACCESS WHERE object=upper('Fn_JS_DBlink_BM'); --S ...
  • redis 存儲之 redisObject
    redisObject redis 是 key-value 存儲系統，其中key類型一般為字元串，而 value 類型則為 redis 對象（redisObject）。Redis 對象可以綁定各種類型的數據，譬如 string、list 和set。因此他能很好的將屬性和數據分離開。 typedef ...
  • redis 集群(文檔整理)
    Redis集群 ·Redis集群提供了一種運行Redis安裝的方法，在該安裝中，數據會在多個Redis節點之間自動分片。 Redis集群在分區期間還提供了一定程度的可用性，這實際上是在某些節點出現故障或無法通信時有繼續工作的能力。但是，如果發生較嚴重故障（例如，大多數主節點不可用時），集群將停止運行 ...
  • Oracle鎖-共用鎖（二）
    一個事務執行dml操作，就會自動加上行共用表鎖，以防止其他需要排他鎖的事務訪問。 一個事務對錶新增數據，另一個事務修改表報錯顯示“資源正忙...”，因為修改該表時需要排他鎖。 一個事務修改表數據，或刪除表數據，就是真正的影響記錄數為0，另一個事務依然無法修改該表，因為修改該表時需要排他鎖。 ...
  • ignite affinity key 並置 究竟是個啥？
    先上個pgsql操作的對比，然後引出ignite的並置處理規則 ddl: drop table student;CREATE TABLE student(sid int PRIMARY KEY ,sname varchar(64), cid int); drop table course;CREAT ...
  • MySQL學習筆記（3）：SQL
    本文章更新於2020-06-14，使用MySQL 5.7，操作系統為Deepin 15.9。 說明：下文中，小寫為自定義變數，根據實際情況填寫（個別小寫的參數除外）。使用[]引起表示內容可選，使用{}引起表示內容為一個整體，|表示使用左側或右側內容，...表示重覆之前內容。 實際上，所有語句和命令的 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類