譯者 | 涂承燁
審校 | 重樓
SQL Server是一個功能強大的關系型數據庫管理系統（RDBMS），但是隨著數據集的大小和復雜性的增長，優化它們的性能變得至關重要。利用AI可以徹底改變查詢優化和預測性維護，確保數據庫保持高效、安全和響應性。
在本文中，我們將探討AI如何在這些領域提供幫助，并提供處理復雜查詢的代碼示例。
用于查詢優化的AI
由于低效的令人興奮的計劃或糟糕的索引策略，復雜的查詢可能會很慢。人工智能可以分析查詢執行指標，識別瓶頸，并提供優化建議。
示例：復雜查詢優化
讓我們從一個運行緩慢的查詢開始：
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上未優化索引
- 查詢了大量不必要的數據范圍
解決方案：基于AI的查詢計劃分析
使用SQL Server Query Store等工具并集成基于AI的分析，可以發現效率低下的問題：
1、啟用查詢存儲
MS SQL
ALTER DATABASE AdventureWorks
SET QUERY_STORE = ON;2、捕獲查詢性能指標
使用Python和PyODBS等庫以及AI框架來分析查詢的執行和統計數據。
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、優化查詢
根據分析，添加適當的索引：
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用于預測性維護
AI可以在系統問題發生之前進行預測，例如查詢超時的磁盤I/O瓶頸。
示例：預測性能瓶頸
1、收集性能指標
使用SQL Server的DMV（動態管理視圖）來檢索指標。
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分析指標
使用Python和回歸模型預測瓶頸：
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、主動維護
（1）根據預測的工作負載調度優化
（2）添加資源（例如，磁盤I/O容量）或重新平衡工作負載以減輕未來的問題
AI驅動前后的SQL Server查詢分析
指標 | 優化前 | 用AI優化后 | 改善 |
數據集大小 | 5000萬行 | 5000萬行 | - |
查詢執行時間 | 120秒 | 35秒 | 減少70% |
CPU利用率（%） | 85% | 55% | 降低35% |
I/O讀取操作 （每次查詢） | 1,500,000 | 850,000 | 減少43% |
邏輯讀取（頁） | 120,000 | 55,000 | 減少54% |
指標利用率 | 最小 | 完全優化 | 改進索引策略 |
并發查詢的延遲 | 高（查詢排隊） | 低（并行處理） | 等待時間顯著減少 |
資源爭用 | 頻繁 | 少有 | 具有更好的查詢和資源管理 |
總吞吐量 （查詢數/小時） | 20 | 60 | 3倍改進 |
錯誤率（超時或失敗） | 5% | 1% | 降低80% |
主要分析結果
- 查詢執行時間使用AI分析執行計劃并推薦索引，大大縮短了復雜查詢的執行時間。
- CPU和I/O效率優化的索引和改進的查詢結構減少了資源消耗。
- 并發處理增強的索引和優化的執行計劃提高了處理并發查詢的能力，減少了延遲。
- 吞吐量隨著執行時間的減少和資源利用率的提高，系統每小時處理的查詢更多。
- 錯誤率
AI驅動的優化通過最大限度地減少資源爭用和改進執行計劃來減少查詢超時和失敗。
結論
將AI驅動的解決方案整合到SQL Server的優化中，可以顯著增強對大量數據集的管理和查詢，特別是在處理數百萬行數據時。通過對優化前后的性能指標進行比較分析，可以發現在執行時間、資源效率和整體系統吞吐量方面有了顯著的改善。通過利用AI工具進行查詢優化、索引方法和預測分析，組織可以減少延遲、提高并發性和減少錯誤，從而確保可靠和高效的數據庫環境。
通過采用復雜的索引技術和基于AI的查詢分析，執行時間減少了大約70%，CPU和I/O資源消耗減少，查詢吞吐量增加了三倍。此外，預測性維護促進了主動資源管理，大大減少了潛在的瓶頸和系統停機時間。這些增強功能提高了性能，并為未來的擴展提供了可伸縮性和彈性。
譯者介紹
涂承燁，51CTO社區編輯，省政府采購專家、省綜合性評標專家、公 E 采招標采購專家，獲得信息系統項目管理師、信息系統監理師、PMP，CSPM-2等認證，擁有15年以上的開發、項目管理、咨詢設計等經驗。對項目管理、前后端開發、微服務、架構設計、物聯網、大數據、咨詢設計等較為關注。
原文標題：Optimizing SQL Server Performance With AI: Automating Query Optimization and Predictive Maintenance，作者：Vijay Panwar
