Azure SQL Database 使用Query Store對Azure SQL Database監(jiān)控

我們在使用Azure SQL Database的時候，需要對數(shù)據(jù)庫的性能進行監(jiān)控，這時候就可以有兩種方法：

1.第一種方法，是通過Azure SQL Database的監(jiān)控界面，來查看數(shù)據(jù)庫的性能，在本章會簡單的介紹一下

2.第二種方法，是通過Query Store來進行監(jiān)控，在本章會詳細介紹

首先，我們介紹一下使用Azure SQL Database的監(jiān)控界面。

1.我們登錄Azure Portal: https://portal.azure.cn/

2.查看到我們使用的Azure SQL Database，選擇概述，然后點擊下圖紅色部分

3.頁面跳轉(zhuǎn)后，我們可以在下圖的Last Hour，設(shè)置監(jiān)控的時間段

在Add Metric里面，增加新的監(jiān)控指標(biāo)，比如CPU Percentage, Data IO Percentage等

4.我們還可以在性能概述里面，查看到微軟云Azure對我們當(dāng)前數(shù)據(jù)的優(yōu)化建議

接下來，我們詳細介紹一下使用Query Store來進行監(jiān)控，實際上我們在上面看到的通過Azure Portal的可視化監(jiān)控，其實也是通過Query Store來進行監(jiān)控的。

Query Store是SQL Server 2016里面新的功能，同時在微軟云Azure平臺上，也提供了該功能

Query Store是從內(nèi)存中讀取數(shù)據(jù)，并異步寫入到Azure SQL Database的磁盤上的

這里我們假設(shè)一個場景，如果Azure SQL Databse的DTU利用率很高，我們?nèi)绾尾樵兂鼍唧w是哪些語句，占用了過多的資源呢？

1.首先，我們通過Azure Portal，查看到問題發(fā)生的時間，如下圖在9月2日的凌晨開始，發(fā)生了該問題

我們點擊下圖的紅色部分

2.DTU和CPU Time，DataIO都有關(guān)。我們點擊下圖的Add Metric

3.DTU是和CPU Time，Data IO疊加的因素，我們可以看到下面的CPU Time和DataIO都很高，

8點以后都是DATA IO

4.我們在本地PC上安裝SQL Server Management Studio，訪問上面的數(shù)據(jù)庫，并且找到Query Store

我們點擊下圖的Top Resource Consuming Queries

5.點擊上圖右上角的Config，設(shè)置查詢時間

6.在彈出的窗口中，選擇查詢時間，我們也可以使用默認的

7.我們查詢CPU Time，Static 選擇Avg?？梢圆榭吹饺鄙偎饕?/span>

8.在下圖，我們右鍵Miss Index，設(shè)置索引

9.如果我們需要查詢所有缺少索引的表結(jié)構(gòu)，可以在SSMS執(zhí)行下面的語句

--Search Missing Index Directly

SELECT

    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,

    SUM(qrs.count_executions) AS sum_executions,

    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,

    SUM(qsq.count_compiles) AS sum_compiles,

    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt

        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    

    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2

        WHERE qsp2.query_id=qsq.query_id

        ORDER BY qsp2.plan_id DESC)) AS query_plan,

    qsq.query_id,

    qsq.query_hash

FROM sys.query_store_query qsq

JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id

CROSS 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

JOIN sys.query_store_runtime_stats_interval qsrsi on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id

WHERE    

    qsp.query_plan like N'%<MissingIndexes>%'

    and qsrsi.start_time >= DATEADD(HH, -24, SYSDATETIME())

GROUP BY qsq.query_id, qsq.query_hash

ORDER BY est_logical_reads DESC

GO

10.如果我們發(fā)現(xiàn)數(shù)據(jù)庫發(fā)生死鎖，可以嘗試以下語句（master庫）執(zhí)行查看死鎖，更多信息可參考：https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/04/19/deadlock-analysis-for-sql-azure-database/

WITH CTE AS (

       SELECT CAST(event_data AS XML)  AS [target_data_XML] 

   FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)

)

SELECT target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,

target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,

target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(100)') AS db_name

FROM CTE

11.當(dāng)我們需要手動Kill死鎖的Session時候，需要注意：當(dāng)前執(zhí)行完kill 會話后，為什么執(zhí)行kill語句完成，但查看會話進程還在？

在執(zhí)行kill殺會話時候，命令執(zhí)行完成并不代表會話即時被kill掉，會話中有大事務(wù)操作的話，為保證數(shù)據(jù)的一致性，未提交的事務(wù)首先要做回滾，執(zhí)行回滾時間的依據(jù)事務(wù)操作的大小。

建議：一般在Kill會話，建議采用KILL session ID WITH STATUSONLY 方式，這樣我們在kill動作操作結(jié)束，可以實時看到當(dāng)前處理的進度百分比。

詳細介紹可參考：https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017