SQL Server一直以來被人詬病的一個問題是缺少了像MySQL的慢日志功能，程序員和運維無法知道數(shù)據(jù)庫過去歷史的慢查詢語句。

因為SQLServer默認是不捕獲過去歷史的長時間阻塞的SQL語句，導(dǎo)致大家都認為SQL Server沒有歷史慢日志功能

其實SQLServer提供了擴展事件讓用戶自己去捕獲過去歷史的長時間阻塞的SQL語句，但是因為不是默認出廠配置并且設(shè)置擴展事件對初級用戶有一定難度，這里可以說不得不是一個遺憾，希望后續(xù)版本的SQL Server可以默認設(shè)置好慢日志的相關(guān)擴展事件，用初級用戶也可以快速上手。

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

擴展事件介紹

SQL Server 擴展事件（Extended Events，簡稱 XE）是從 SQL Server 2008 開始引入的一種輕量級、高度可定制的事件處理系統(tǒng)，
旨在幫助數(shù)據(jù)庫管理員和開發(fā)人員更好地監(jiān)控、調(diào)試和優(yōu)化 SQL Server 的性能。
擴展事件可以用于捕獲和分析 SQL Server 內(nèi)部發(fā)生的各種事件，以便識別和解決性能瓶頸和問題。

擴展事件優(yōu)點包括輕量級、統(tǒng)一事件處理框架和集成性。事件設(shè)計對系統(tǒng)性能影響最小，確保在高負載環(huán)境下也能穩(wěn)定運行。
擴展事件可以與 SQL Server Profiler 和 SQL Server Audit 結(jié)合使用，為用戶提供全面的診斷和監(jiān)控工具。

實驗步驟

創(chuàng)建環(huán)境所需的數(shù)據(jù)庫和表

--窗口1--建表USE testdbGOCREATE TABLE Account(id INT, name NVARCHAR(200))INSERT INTO [dbo].[Account]SELECT 1,'Lucy'UNION ALLSELECT 2,'Tom'UNION ALLSELECT 3,'Marry'--查詢SELECT * FROM [dbo].[Account]

 創(chuàng)建擴展事件

 輸入擴展事件名稱

 不要使用模版

事件庫搜索block，選擇blocked_process_report

確認事件

選擇你需要的字段

這里選擇client_app_name、client_hostname、database_id、database_name、plan_handle、query_hash、request_id、session_id、sql_text字段

當(dāng)然你可以勾選自己想要的字段，這里只是拋磚引玉

續(xù)

直接下一步

這里需要注意的是，擴展事件日志不能全量保存，所以用戶需要考慮好保留多長時間的擴展事件，假設(shè)一天可以產(chǎn)生的擴展事件大小為1GB，那么每個擴展事件文件大小1GB，最多5個擴展事件文件意味著你不能查詢到5天之前的數(shù)據(jù)

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

擴展事件創(chuàng)建情況預(yù)覽

小提示：你可以點擊script生成這個擴展事件的create腳本，那么其他服務(wù)器就不用這樣用界面去創(chuàng)建這么繁瑣了。

生成出來的擴展事件

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.擴展事件創(chuàng)建完成之后立刻啟動

b.查看實時捕獲的數(shù)據(jù)

立刻啟動擴展事件

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

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

--窗口2
--locked process threshold是SQL Server2005推出的一個選項
--設(shè)置阻塞進程閾值
sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 10 ;   --10秒
GO  
RECONFIGURE ;  
GO

執(zhí)行一個update語句，不要commit

--窗口3
USE testdb;
GO
BEGIN tran
update Account
set name ='Test'
where ID = 2
--commit

查詢數(shù)據(jù)

-- 窗口4
USE testdb;
GO
-- 這個查詢會被窗口3中的事務(wù)阻塞
SELECT * FROM Account
WHERE ID = 2

執(zhí)行完畢之后，你可以看到擴展事件已經(jīng)記錄下來了 

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

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

同時可以看到擴展事件已經(jīng)記錄到xel文件

使用其他編程語言制作慢查詢?nèi)罩緢蟊?/h2>

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

我們可以使用 sys.fn_xe_file_target_read_file 函數(shù)來讀取 XEL 文件中的內(nèi)容。
然后，你可以將這些數(shù)據(jù)導(dǎo)出為其他編程語言可以處理的格式

SQL語句

-- 查詢擴展事件 XEL 文件內(nèi)容
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 文件內(nèi)容
使用 pandas 庫和pyodbc驅(qū)動程序從 SQL Server 導(dǎo)出數(shù)據(jù)并在 Python 中進行處理。
以下是一個示例腳本

import pyodbc
import pandas as pd
# 設(shè)置數(shù)據(jù)庫連接
conn = pyodbc.connect(
    'DRIVER={SQL Server};'
    'SERVER=your_server_name;'
    'DATABASE=your_database_name;'
    'UID=your_username;'
    'PWD=your_password'
)
# 查詢 XEL 文件內(nèi)容
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 讀取數(shù)據(jù)
df = pd.read_sql(query, conn)
# 關(guān)閉數(shù)據(jù)庫連接
conn.close()
# 顯示數(shù)據(jù)
print(df)
# 將數(shù)據(jù)保存為 CSV 文件
df.to_csv('slowquerylog.csv', index=False)

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

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

直接讀取 XEL 文件的內(nèi)容，然后導(dǎo)出CSV文件，讓其他編程語言處理

Step 1: 創(chuàng)建 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"
# 創(chuàng)建XEventData對象
$events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($xelFilePath)
# 初始化一個空數(shù)組來存儲事件數(shù)據(jù)
$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
}
# 將事件數(shù)據(jù)導(dǎo)出為CSV文件
$eventDataList | Export-Csv -Path "E:\DBExtentEvent\slowquerylog.csv" -NoTypeInformation

Step 2: Python 腳本 ReadCSVFile.py讀取導(dǎo)出的 CSV 文件

import pandas as pd
# 定義CSV文件路徑
csv_file_path = "E:\\DBExtentEvent\\slowquerylog.csv"
# 使用pandas讀取CSV文件
df = pd.read_csv(csv_file_path)
# 顯示數(shù)據(jù)
print(df)

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

總結(jié)

本文介紹了利用【SQL Server的擴展事件】捕獲慢查詢語句的功能，也就是我們常說的開源數(shù)據(jù)庫的慢日志

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

總體來說，SQL Server作為一個企業(yè)級數(shù)據(jù)庫，確實不像MySQL這種開源數(shù)據(jù)庫簡單直接

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

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

本文版權(quán)歸作者所有，未經(jīng)作者同意不得轉(zhuǎn)載。

到此這篇關(guān)于如何讓SQL Server像MySQL一樣擁有慢查詢?nèi)罩荆⊿low Query Log慢日志）的文章就介紹到這了,更多相關(guān)SQL Server慢查詢?nèi)罩緝?nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論