譯者 | 涂承燁
審校 | 重樓
SQL Server是一個功能強(qiáng)大的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS），但是隨著數(shù)據(jù)集的大小和復(fù)雜性的增長，優(yōu)化它們的性能變得至關(guān)重要。利用AI可以徹底改變查詢優(yōu)化和預(yù)測性維護(hù)，確保數(shù)據(jù)庫保持高效、安全和響應(yīng)性。
在本文中，我們將探討AI如何在這些領(lǐng)域提供幫助，并提供處理復(fù)雜查詢的代碼示例。
用于查詢優(yōu)化的AI
由于低效的令人興奮的計劃或糟糕的索引策略，復(fù)雜的查詢可能會很慢。人工智能可以分析查詢執(zhí)行指標(biāo)，識別瓶頸，并提供優(yōu)化建議。
示例：復(fù)雜查詢優(yōu)化
讓我們從一個運行緩慢的查詢開始：
MS SQL
SELECT
p.ProductID,
SUM(o.Quantity) AS TotalQuantity
FROM
Products p
JOIN
Orders o
ON
p.ProductID = o.ProductID
WHERE
o.OrderDate >= '2023-01-01'
GROUP BY
p.ProductID
HAVING
SUM(o.Quantity) > 1000
ORDER BY
TotalQuantity DESC;此查詢存在性能問題的原因如下：
- 在OrderDate和ProductID上未優(yōu)化索引
- 查詢了大量不必要的數(shù)據(jù)范圍
解決方案：基于AI的查詢計劃分析
使用SQL Server Query Store等工具并集成基于AI的分析，可以發(fā)現(xiàn)效率低下的問題：
1、啟用查詢存儲
MS SQL
ALTER DATABASE AdventureWorks
SET QUERY_STORE = ON;2、捕獲查詢性能指標(biāo)
使用Python和PyODBS等庫以及AI框架來分析查詢的執(zhí)行和統(tǒng)計數(shù)據(jù)。
Python
import pyodbc
import pandas as pd
from sklearn.ensemble import IsolationForest
# Connect to SQL Server
conn = pyodbc.connect(
"Driver={SQL Server};"
"Server=your_server_name;"
"Database=AdventureWorks;"
"Trusted_Connection=yes;"
)
# Retrieve query execution stats
query = """
SELECT TOP 1000
qs.query_id, qs.execution_type, qs.total_duration,
qs.cpu_time, qs.logical_reads, qs.physical_reads
FROM
sys.query_store_runtime_stats qs
"""
df = pd.read_sql(query, conn)
# Use AI for anomaly detection (e.g., identifying slow queries)
model = IsolationForest(n_estimators=100, contamination=0.1)
model.fit(df[['total_duration', 'cpu_time', 'logical_reads']])
df['anomaly'] = model.predict(df[['total_duration', 'cpu_time', 'logical_reads']])
print(df[df['anomaly'] == -1]) # Anomalous slow queries3、優(yōu)化查詢
根據(jù)分析，添加適當(dāng)?shù)乃饕?/span>
MS SQL
CREATE NONCLUSTERED INDEX IDX_Orders_OrderDate_ProductID
ON Orders(OrderDate, ProductID);以下是AI建議后更新的Query，減少了不必要的查詢范圍：
MS SQL
SELECT
p.ProductID,
SUM(o.Quantity) AS TotalQuantity
FROM
Products p
JOIN
Orders o
ON
p.ProductID = o.ProductID
WHERE
o.OrderDate >= '2023-01-01'
AND EXISTS (
SELECT 1 FROM Orders o2 WHERE o2.ProductID = p.ProductID AND o2.Quantity > 1000
)
GROUP BY
p.ProductID
ORDER BY
TotalQuantity DESC;AI用于預(yù)測性維護(hù)
AI可以在系統(tǒng)問題發(fā)生之前進(jìn)行預(yù)測，例如查詢超時的磁盤I/O瓶頸。
示例：預(yù)測性能瓶頸
1、收集性能指標(biāo)
使用SQL Server的DMV（動態(tài)管理視圖）來檢索指標(biāo)。
MS SQL
SELECT
database_id,
io_stall_read_ms,
io_stall_write_ms,
num_of_reads,
num_of_writes
FROM
sys.dm_io_virtual_file_stats(NULL, NULL);2、使用AI分析指標(biāo)
使用Python和回歸模型預(yù)測瓶頸：
Python
from sklearn.linear_model import LinearRegression
import numpy as np
# Example I/O data
io_data = {
'read_stall': [100, 150, 300, 500, 800],
'write_stall': [80, 120, 280, 480, 750],
'workload': [1, 2, 3, 4, 5] # Hypothetical workload levels
}
X = np.array(io_data['workload']).reshape(-1, 1)
y = np.array(io_data['read_stall'])
# Train model
model = LinearRegression()
model.fit(X, y)
# Predict for future workload levels
future_workload = np.array([6]).reshape(-1, 1)
predicted_stall = model.predict(future_workload)
print(f"Predicted read stall for workload 6: {predicted_stall[0]} ms")3、主動維護(hù)
（1）根據(jù)預(yù)測的工作負(fù)載調(diào)度優(yōu)化
（2）添加資源（例如，磁盤I/O容量）或重新平衡工作負(fù)載以減輕未來的問題
AI驅(qū)動前后的SQL Server查詢分析
指標(biāo) | 優(yōu)化前 | 用AI優(yōu)化后 | 改善 |
數(shù)據(jù)集大小 | 5000萬行 | 5000萬行 | - |
查詢執(zhí)行時間 | 120秒 | 35秒 | 減少70% |
CPU利用率（%） | 85% | 55% | 降低35% |
I/O讀取操作 （每次查詢） | 1,500,000 | 850,000 | 減少43% |
邏輯讀?。摚?/span> | 120,000 | 55,000 | 減少54% |
指標(biāo)利用率 | 最小 | 完全優(yōu)化 | 改進(jìn)索引策略 |
并發(fā)查詢的延遲 | 高（查詢排隊） | 低（并行處理） | 等待時間顯著減少 |
資源爭用 | 頻繁 | 少有 | 具有更好的查詢和資源管理 |
總吞吐量 （查詢數(shù)/小時） | 20 | 60 | 3倍改進(jìn) |
錯誤率（超時或失敗） | 5% | 1% | 降低80% |
主要分析結(jié)果
- 查詢執(zhí)行時間使用AI分析執(zhí)行計劃并推薦索引，大大縮短了復(fù)雜查詢的執(zhí)行時間。
- CPU和I/O效率優(yōu)化的索引和改進(jìn)的查詢結(jié)構(gòu)減少了資源消耗。
- 并發(fā)處理增強(qiáng)的索引和優(yōu)化的執(zhí)行計劃提高了處理并發(fā)查詢的能力，減少了延遲。
- 吞吐量隨著執(zhí)行時間的減少和資源利用率的提高，系統(tǒng)每小時處理的查詢更多。
- 錯誤率
AI驅(qū)動的優(yōu)化通過最大限度地減少資源爭用和改進(jìn)執(zhí)行計劃來減少查詢超時和失敗。
結(jié)論
將AI驅(qū)動的解決方案整合到SQL Server的優(yōu)化中，可以顯著增強(qiáng)對大量數(shù)據(jù)集的管理和查詢，特別是在處理數(shù)百萬行數(shù)據(jù)時。通過對優(yōu)化前后的性能指標(biāo)進(jìn)行比較分析，可以發(fā)現(xiàn)在執(zhí)行時間、資源效率和整體系統(tǒng)吞吐量方面有了顯著的改善。通過利用AI工具進(jìn)行查詢優(yōu)化、索引方法和預(yù)測分析，組織可以減少延遲、提高并發(fā)性和減少錯誤，從而確?？煽亢透咝У臄?shù)據(jù)庫環(huán)境。
通過采用復(fù)雜的索引技術(shù)和基于AI的查詢分析，執(zhí)行時間減少了大約70%，CPU和I/O資源消耗減少，查詢吞吐量增加了三倍。此外，預(yù)測性維護(hù)促進(jìn)了主動資源管理，大大減少了潛在的瓶頸和系統(tǒng)停機(jī)時間。這些增強(qiáng)功能提高了性能，并為未來的擴(kuò)展提供了可伸縮性和彈性。
譯者介紹
涂承燁，51CTO社區(qū)編輯，省政府采購專家、省綜合性評標(biāo)專家、公 E 采招標(biāo)采購專家，獲得信息系統(tǒng)項目管理師、信息系統(tǒng)監(jiān)理師、PMP，CSPM-2等認(rèn)證，擁有15年以上的開發(fā)、項目管理、咨詢設(shè)計等經(jīng)驗。對項目管理、前后端開發(fā)、微服務(wù)、架構(gòu)設(shè)計、物聯(lián)網(wǎng)、大數(shù)據(jù)、咨詢設(shè)計等較為關(guān)注。
原文標(biāo)題：Optimizing SQL Server Performance With AI: Automating Query Optimization and Predictive Maintenance，作者：Vijay Panwar
