Azure SQL Database (26) 使用Query Store对Azure SQL Database监控

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.如果我们发现数据库发生死锁，可以尝试以下语句（master库）执行查看死锁，更多信息可参考：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.当我们需要手动Kill死锁的Session时候，需要注意：当前执行完kill 会话后，为什么执行kill语句完成，但查看会话进程还在？

　　在执行kill杀会话时候，命令执行完成并不代表会话即时被kill掉，会话中有大事务操作的话，为保证数据的一致性，未提交的事务首先要做回滚，执行回滚时间的依据事务操作的大小。
　　建议：一般在Kill会话，建议采用KILL session ID WITH STATUSONLY 方式，这样我们在kill动作操作结束，可以实时看到当前处理的进度百分比。
　　详细介绍可参考：https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017

　　

　　

Azure SQL Database (26) 使用Query Store对Azure SQL Database监控

标签：结构   char   [1]   mic   gic   str   sql   HERE   lan