【Azure SQL】数据库性能分析

前置条件

  • 用户有查询数据统计权限
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 @start_hours INT = -8;
DECLARE @end_hours INT = -0;

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,
                SUM (count_executions *  avg_logical_io_reads) / SUM ( count_executions ) AS avg_io_reads,
        MAX ( max_logical_io_reads ) max_logical_reads,
        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 rsi.start_time >= DATEADD( HOUR,@start_hours,GETUTCDATE())
        AND rsi.start_time <=  DATEADD( HOUR,@end_hours,GETUTCDATE())
        AND count_executions > 10
    GROUP BY
        q.query_hash 
    ),
    OrderedCPU AS (
    SELECT
        query_hash,
        total_cpu_millisec,
        avg_cpu_millisec,
        max_cpu_millisec,
                avg_io_reads,
        max_logical_reads,
        total_executions,
        sampled_query_text,
        ROW_NUMBER ( ) OVER ( ORDER BY avg_cpu_millisec DESC, query_hash ASC ) AS RN 
    FROM
        AggregatedCPU 
    ) SELECT
    OD.total_cpu_millisec,
    OD.avg_cpu_millisec,
    OD.max_cpu_millisec,
        OD.avg_io_reads,
    OD.max_logical_reads,
    OD.total_executions,
    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等待情况
DECLARE @begin_hours INT = - 48;
DECLARE @end_hours INT = - 0;

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, @begin_hours, GETUTCDATE( ) ) 
        AND rsi.start_time <= DATEADD( HOUR, @end_hours, 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.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
  • 写入日志最多查询
DECLARE @begin_hours INT = - 45;
DECLARE @end_hours INT = - 0;
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,
        SUM ( count_executions * avg_logical_io_reads ) / SUM ( count_executions ) AS avg_logical_io_reads,
        MAX ( max_logical_io_reads ) max_logical_reads,
        SUM ( count_executions ) AS total_executions,
        MAX ( 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, @begin_hours, GETUTCDATE( ) ) 
        AND rsi.start_time <= DATEADD( HOUR, @end_hours, GETUTCDATE( ) ) 
    GROUP BY
        q.query_hash 
    ),
    OrderedLogUsed AS (
    SELECT
        query_hash,
        total_log_bytes_used,
        total_executions,
        avg_logical_io_reads,
        max_logical_reads,
        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.total_executions,
    OD.avg_logical_io_reads,
    OD.max_logical_reads,
    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;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,686评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,668评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,160评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,736评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,847评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,043评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,129评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,872评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,318评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,645评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,777评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,470评论 4赞 333
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,126评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,861评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,095评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,589评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,687评论 2赞 351