欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQL Server像MySQL一樣擁有慢查詢日志的操作方法(Slow Query Log慢日志)

 更新時間：2024年07月29日 10:11:50   作者：樺仔  
SQL Server一直以來被人詬病的一個問題是缺少了像MySQL的慢日志功能,程序員和運維無法知道數(shù)據(jù)庫過去歷史的慢查詢語句,本文介紹了利用SQL Server的擴展事件捕獲慢查詢語句的功能,也就是我們常說的開源數(shù)據(jù)庫的慢日志,感興趣的朋友一起看看吧

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

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

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

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

擴展事件介紹

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

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

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

續(xù)

直接下一步

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

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

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

小提示：你可以點擊script生成這個擴展事件的create腳本，那么其他服務器就不用這樣用界面去創(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ù)

立刻啟動擴展事件

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

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

執(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中的事務阻塞
SELECT * FROM Account
WHERE ID = 2

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

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

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

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

使用其他編程語言制作慢查詢日志報表

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

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

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 導出數(shù)據(jù)并在 Python 中進行處理。
以下是一個示例腳本

import pyodbc
import pandas as pd
# 設置數(shù)據(jù)庫連接
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 讀取數(shù)據(jù)
df = pd.read_sql(query, conn)
# 關閉數(shù)據(jù)庫連接
conn.close()
# 顯示數(shù)據(jù)
print(df)
# 將數(shù)據(jù)保存為 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: 創(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ù)導出為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)
# 顯示數(shù)據(jù)
print(df)

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

總結

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

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

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

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

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

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

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

相關文章

最新評論