如何讓SQL Server像MySQL一樣擁有慢查詢日誌（Slow Query Log慢日誌）

来源:https://www.cnblogs.com/lyhabc/p/18322758/Let-SQLServer-have-slowquerylogging-like-MySQL
-Advertisement-
Play Games

如何讓SQL Server像MySQL一樣擁有慢查詢日誌（Slow Query Log慢日誌） SQL Server一直以來被人詬病的一個問題是缺少了像MySQL的慢日誌功能，程式員和運維無法知道資料庫過去歷史的慢查詢語句。 因為SQLServer預設是不捕獲過去歷史的長時間阻塞的SQL語句，導致大 ...


如何讓SQL Server像MySQL一樣擁有慢查詢日誌（Slow Query Log慢日誌）

SQL Server一直以來被人詬病的一個問題是缺少了像MySQL的慢日誌功能，程式員和運維無法知道資料庫過去歷史的慢查詢語句。

因為SQLServer預設是不捕獲過去歷史的長時間阻塞的SQL語句，導致大家都認為SQL Server沒有歷史慢日誌功能

其實SQLServer提供了擴展事件讓用戶自己去捕獲過去歷史的長時間阻塞的SQL語句，但是因為不是預設出廠配置並且設置擴展事件對初級用戶有一定難度，這裡可以說不得不是一個遺憾，希望後續版本的SQL Server可以預設設置好慢日誌的相關擴展事件，用初級用戶也可以快速上手。


話不多說，這個文章主要講述設置慢日誌的擴展事件的步驟，並且把慢日誌提供第三方程式讀取以提供報表功能。

 

擴展事件介紹

SQL Server 擴展事件（Extended Events，簡稱 XE）是從 SQL Server 2008 開始引入的一種輕量級、高度可定製的事件處理系統，
旨在幫助資料庫管理員和開發人員更好地監控、調試和優化 SQL Server 的性能。
擴展事件可以用於捕獲和分析 SQL Server 內部發生的各種事件，以便識別和解決性能瓶頸和問題。

擴展事件優點包括輕量級、統一事件處理框架和集成性。事件設計對系統性能影響最小，確保在高負載環境下也能穩定運行。
擴展事件可以與 SQL Server Profiler 和 SQL Server Audit 結合使用，為用戶提供全面的診斷和監控工具。

 

 

 

實驗步驟

創建環境所需的資料庫和表

--視窗1
--建表

USE testdb
GO

CREATE TABLE Account(id INT, name NVARCHAR(200))


INSERT INTO [dbo].[Account]
SELECT 1,'Lucy'
UNION ALL
SELECT 2,'Tom'
UNION ALL
SELECT 3,'Marry'

--查詢
SELECT * FROM [dbo].[Account]

 

 創建擴展事件

 

 輸入擴展事件名稱

 

 不要使用模版

 

事件庫搜索block，選擇blocked_process_report

 

確認事件

 

選擇你需要的欄位

這裡選擇client_app_name、client_hostname、database_id、database_name、plan_handle、query_hash、request_id、session_id、sql_text欄位

當然你可以勾選自己想要的欄位，這裡只是拋磚引玉

續

直接下一步

這裡需要註意的是，擴展事件日誌不能全量保存，所以用戶需要考慮好保留多長時間的擴展事件，假設一天可以產生的擴展事件大小為1GB，那麼每個擴展事件文件大小1GB，最多5個擴展事件文件意味著你不能查詢到5天之前的數據

比如你不能查詢到前面第8天的擴展事件，擴展事件是滾動利用的。

擴展事件創建情況預覽

小提示：你可以點擊script生成這個擴展事件的create腳本，那麼其他伺服器就不用這樣用界面去創建這麼繁瑣了。

生成出來的擴展事件

CREATE EVENT SESSION [slowquerylog]
ON SERVER
    ADD EVENT sqlserver.blocked_process_report
    (ACTION
     (
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.plan_handle,
         sqlserver.query_hash,
         sqlserver.request_id,
         sqlserver.session_id,
         sqlserver.sql_text
     )
    )
    ADD TARGET package0.event_file
    (SET filename = N'E:\DBExtentEvent\slowquerylog.xel')
WITH
(
    STARTUP_STATE = ON
);
GO

完成

你可以勾選

a.擴展事件創建完成之後立刻啟動

b.查看實時捕獲的數據

立刻啟動擴展事件

 

一定要設置locked process threshold，否則無辦法捕獲慢SQL語句，這個選項類似於MySQL的long_query_time參數

locked process threshold是SQL Server2005推出的一個選項，下麵設置阻塞10秒就會記錄

--視窗2
--locked process threshold是SQL Server2005推出的一個選項

--設置阻塞進程閾值
sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 10 ;   --10秒
GO  
RECONFIGURE ;  
GO

執行一個update語句，不要commit

--視窗3
USE testdb;
GO

BEGIN tran
update Account
set name ='Test'
where ID = 2

--commit

查詢數據

-- 視窗4
USE testdb;
GO

-- 這個查詢會被視窗3中的事務阻塞
SELECT * FROM Account
WHERE ID = 2

 

執行完畢之後，你可以看到擴展事件已經記錄下來了 

雙擊查看詳細的會話裡面的語句

可以很清楚的看到誰是被blocked的語句，誰是主動blocking的語句也就是源頭

同時可以看到擴展事件已經記錄到xel文件

 

 

使用其他編程語言製作慢查詢日誌報表

微軟提供了使用 SQL Server Management Studio (SSMS) 和 T-SQL 查詢擴展事件 XEL 文件內容的 API。

我們可以使用 sys.fn_xe_file_target_read_file 函數來讀取 XEL 文件中的內容。
然後，你可以將這些數據導出為其他編程語言可以處理的格式

SQL語句

-- 查詢擴展事件 XEL 文件內容
SELECT 
    event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'INT') AS duration,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(255)') AS client_app_name,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(255)') AS client_hostname,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'VARCHAR(255)') AS database_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS sql_text
FROM 
    sys.fn_xe_file_target_read_file('E:\DBExtentEvent\slowquerylog*.xel', NULL, NULL, NULL) AS t
CROSS APPLY 
    t.event_data.nodes('event') AS XEvent(event_data);



使用 Python 讀取 XEL 文件內容
使用 pandas 庫和pyodbc驅動程式從 SQL Server 導出數據併在 Python 中進行處理。
以下是一個示例腳本

import pyodbc
import pandas as pd

# 設置資料庫連接
conn = pyodbc.connect(
    'DRIVER={SQL Server};'
    'SERVER=your_server_name;'
    'DATABASE=your_database_name;'
    'UID=your_username;'
    'PWD=your_password'
)

# 查詢 XEL 文件內容
query = """
SELECT 
    event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'INT') AS duration,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(255)') AS client_app_name,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(255)') AS client_hostname,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'VARCHAR(255)') AS database_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS sql_text
FROM 
    sys.fn_xe_file_target_read_file('E:\DBExtentEvent\slowquerylog*.xel', NULL, NULL, NULL) AS t
CROSS APPLY 
    t.event_data.nodes('event') AS XEvent(event_data);
"""

# 使用 pandas 讀取數據
df = pd.read_sql(query, conn)

# 關閉資料庫連接
conn.close()

# 顯示數據
print(df)

# 將數據保存為 CSV 文件
df.to_csv('slowquerylog.csv', index=False)

 

這裡的一個問題是，你不能直接讀取XEL文件，本身XEL文件是一個二進位文件，必須掛接到線上SQL Server實例（任何SQL Server實例都可以，不一定是生產庫的那一臺SQL Server實例，只要是XEL文件所在的機器）

 

 

另外一個方法是使用 PowerShell 中的 Microsoft.SqlServer.XEvent.Linq.QueryableXEventData 類直接解析 XEL 文件，不用掛接到SQL Server實例

直接讀取 XEL 文件的內容，然後導出CSV文件，讓其他編程語言處理

 

Step 1: 創建 PowerShell 腳本 ReadXELFile.ps1

# 載入所需的程式集
Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.XEvent.Linq.dll"

# 定義XEL文件路徑
$xelFilePath = "E:\DBExtentEvent\slowquerylog*.xel"

# 創建XEventData對象
$events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($xelFilePath)

# 初始化一個空數組來存儲事件數據
$eventDataList = @()

# 遍歷每個事件並提取所需的欄位
foreach ($event in $events) {
    $eventData = New-Object PSObject -Property @{
        EventName      = $event.Name
        Timestamp      = $event.Timestamp
        Duration       = $event.Fields["duration"].Value
        ClientAppName  = $event.Actions["client_app_name"].Value
        ClientHostname = $event.Actions["client_hostname"].Value
        DatabaseName   = $event.Actions["database_name"].Value
        SqlText        = $event.Actions["sql_text"].Value
    }
    $eventDataList += $eventData
}

# 將事件數據導出為CSV文件
$eventDataList | Export-Csv -Path "E:\DBExtentEvent\slowquerylog.csv" -NoTypeInformation

 

Step 2: Python 腳本 ReadCSVFile.py讀取導出的 CSV 文件

import pandas as pd

# 定義CSV文件路徑
csv_file_path = "E:\\DBExtentEvent\\slowquerylog.csv"

# 使用pandas讀取CSV文件
df = pd.read_csv(csv_file_path)

# 顯示數據
print(df)

這個方法需要使用PowerShell ，對於PowerShell 不熟悉的朋友也是一個問題

 

 

總結

本文介紹了利用【SQL Server的擴展事件】捕獲慢查詢語句的功能，也就是我們常說的開源資料庫的慢日誌

另外，一定要設置“blocked process threshold”參數，否則設置了擴展事件也沒有效果

總體來說，SQL Server作為一個企業級資料庫，確實不像MySQL這種開源資料庫簡單直接

需要設置比較繁瑣的擴展事件，對新手用戶不太友好，門檻比較高，但是因為擴展事件功能非常強大

除了捕獲慢查詢語句還可以捕獲死鎖，索引缺失等性能問題，所以這個是在所難免的

 

 

參考文章

https://www.sqlshack.com/using-sql-server-extended-events-to-monitor-query-performance/

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-slow-running-queries

https://erikdarling.com/monitoring-sql-server-for-query-timeouts-with-extended-events/

https://sqlmaestros.com/free-sql-video-troubleshoot-slow-running-query-sql-server-extended-events-wait-stats/

 

 

本文版權歸作者所有，未經作者同意不得轉載。


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

-Advertisement-
Play Games
更多相關文章
  • Linux工作原理10網路應用程式和服務
    本章將和大家分享Linux中的許可權控制。廢話不多說，下麵我們直接進入主題。 一、基礎知識 Linux作為一種多用戶的操作系統(伺服器系統)，允許多個用戶同時登陸到系統上，並響應每個用戶的請求。 任何需要使用操作系統的用戶，都需要一個系統賬號，賬號分為：管理員賬號與普通用戶賬號。 在Linux中，操作 ...
  • 全面指南：備份和遷移Docker容器
    在現代軟體開發和部署中，Docker容器已成為一種流行的技術。然而，隨著容器的廣泛使用，數據保護和遷移也變得至關重要。本文將詳細介紹如何備份和遷移Docker容器，確保你的應用和數據在任何時候都是安全的。 一、為什麼需要備份和遷移Docker容器？ 在某些情況下，你可能需要備份和遷移Docker容器 ...
  • SQLServer 事務複製在發佈端（publication）和訂閱端（subscription）對應的資料庫還原之後複製異常問題的解決
    書接上文，在一個正常的事務複製環境中，如果發生了資料庫還原，事務複製會不會出問題，出問題之後又如何恢復，如果在不刪除訂閱發佈重建的情況下，如何在現有基礎上修複事務複製的異常，這個問題可以分為兩部分看： 1，如果publisher資料庫發生了還原操作，事務複製會出現什麼異常，該如何恢復？ 2，如果是s ...
  • React 的 KeepAlive 實戰指南：深度解析組件緩存機制
    Vue 的 Keep-Alive 組件是用於緩存組件的高階組件，可以有效地提高應用性能。它能夠使組件在切換時仍能保留原有的狀態信息，並且有專門的生命周期方便去做額外的處理。該組件在很多場景非常有用，比如： · tabs 緩存頁面 · 分步表單 · 路由緩存 在 Vue 中，通過 KeepAlive ...
  • 我用Awesome-Graphs看論文：解讀Pregel
    上次向大家分享了論文圖譜項目Awesome-Graphs的介紹文章，這次我們就拿圖計算系統的奠基文章Pregel開篇，沿著論文圖譜的主線，對圖計算系統的論文內容進行解讀。 ...
  • mongo副本集開啟安全認證
    1. 事物的四大特性 事務是邏輯上的一組操作，要麼都執行，要麼都不執行 原子性（Atomicity）：事務是最小的執行單位，不允許分割。事務的原子性確保動作要麼全部完成，要麼完全不起作用； 一致性（Consistency）：執行事務前後，數據保持一致，例如轉賬業務中，無論事務是否成功，轉賬者和收款人 ...
  • GreatSQL 的刷新鎖
    GreatSQL 的刷新鎖 前言 因為運維小伙伴執行dump備份命令，導致資料庫卡住，很多會話都在waiting for table flush，基於這一故障，我對GreatSQL的刷新鎖進行了研究。感興趣的小伙伴請隨我一探究竟吧。 刷新鎖的癥狀 刷新鎖問題的主要癥狀是資料庫會進入嘎然而止的狀態，所 ...
  • ElasticSearch第4篇（億級中文數據量 ElasticSearch與Sphinx建索引速度、查詢速度、併發性能、實測對比）
    經過實測：1.09億的數據量進行中文檢索。ElasticSearch單機的檢索性能在0.005~5.6秒之間，此檢索速度可滿足95%的業務場景（註意：每條ES文檔平均65個漢字，數據源取自幾千本小說，大部分文檔在15~300個漢字之間，不然字數太多索引太大電腦存不下）。 前置文章 由於本文章的前置操 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類