譯者 | 涂承燁

審校 | 重樓

SQL Server是一個(gè)功能強(qiáng)大的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)（RDBMS），但是隨著數(shù)據(jù)集的大小和復(fù)雜性的增長(zhǎng)，優(yōu)化它們的性能變得至關(guān)重要。利用AI可以徹底改變查詢(xún)優(yōu)化和預(yù)測(cè)性維護(hù)，確保數(shù)據(jù)庫(kù)保持高效、安全和響應(yīng)性。

在本文中，我們將探討AI如何在這些領(lǐng)域提供幫助，并提供處理復(fù)雜查詢(xún)的代碼示例。

用于查詢(xún)優(yōu)化的AI

由于低效的令人興奮的計(jì)劃或糟糕的索引策略，復(fù)雜的查詢(xún)可能會(huì)很慢。人工智能可以分析查詢(xún)執(zhí)行指標(biāo)，識(shí)別瓶頸，并提供優(yōu)化建議。

示例：復(fù)雜查詢(xún)優(yōu)化

讓我們從一個(gè)運(yùn)行緩慢的查詢(xún)開(kāi)始：

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;

此查詢(xún)存在性能問(wèn)題的原因如下：

1. 在OrderDate和ProductID上未優(yōu)化索引
2. 查詢(xún)了大量不必要的數(shù)據(jù)范圍

解決方案：基于AI的查詢(xún)計(jì)劃分析

使用SQL Server Query Store等工具并集成基于AI的分析，可以發(fā)現(xiàn)效率低下的問(wèn)題：

1、啟用查詢(xún)存儲(chǔ)

MS SQL

ALTER DATABASE AdventureWorks
SET QUERY_STORE = ON;

2、捕獲查詢(xún)性能指標(biāo)

使用Python和PyODBS等庫(kù)以及AI框架來(lái)分析查詢(xún)的執(zhí)行和統(tǒng)計(jì)數(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 queries

3、優(yōu)化查詢(xún)

根據(jù)分析，添加適當(dāng)?shù)乃饕?/span>

MS SQL

CREATE NONCLUSTERED INDEX IDX_Orders_OrderDate_ProductID
ON Orders(OrderDate, ProductID);

以下是AI建議后更新的Query，減少了不必要的查詢(xún)范圍：

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ù)測(cè)性維護(hù)

AI可以在系統(tǒng)問(wèn)題發(fā)生之前進(jìn)行預(yù)測(cè)，例如查詢(xún)超時(shí)的磁盤(pán)I/O瓶頸。

示例：預(yù)測(cè)性能瓶頸

1、收集性能指標(biāo)

使用SQL Server的DMV（動(dòng)態(tài)管理視圖）來(lá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ù)測(cè)瓶頸：

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、主動(dòng)維護(hù)

（1）根據(jù)預(yù)測(cè)的工作負(fù)載調(diào)度優(yōu)化

（2）添加資源（例如，磁盤(pán)I/O容量）或重新平衡工作負(fù)載以減輕未來(lái)的問(wèn)題

AI驅(qū)動(dòng)前后的SQL Server查詢(xún)分析

主要分析結(jié)果

• 查詢(xún)執(zhí)行時(shí)間使用AI分析執(zhí)行計(jì)劃并推薦索引，大大縮短了復(fù)雜查詢(xún)的執(zhí)行時(shí)間。
• CPU和I/O效率優(yōu)化的索引和改進(jìn)的查詢(xún)結(jié)構(gòu)減少了資源消耗。
• 并發(fā)處理增強(qiáng)的索引和優(yōu)化的執(zhí)行計(jì)劃提高了處理并發(fā)查詢(xún)的能力，減少了延遲。
• 吞吐量隨著執(zhí)行時(shí)間的減少和資源利用率的提高，系統(tǒng)每小時(shí)處理的查詢(xún)更多。
• 錯(cuò)誤率

AI驅(qū)動(dòng)的優(yōu)化通過(guò)最大限度地減少資源爭(zhēng)用和改進(jìn)執(zhí)行計(jì)劃來(lái)減少查詢(xún)超時(shí)和失敗。

結(jié)論

將AI驅(qū)動(dòng)的解決方案整合到SQL Server的優(yōu)化中，可以顯著增強(qiáng)對(duì)大量數(shù)據(jù)集的管理和查詢(xún)，特別是在處理數(shù)百萬(wàn)行數(shù)據(jù)時(shí)。通過(guò)對(duì)優(yōu)化前后的性能指標(biāo)進(jìn)行比較分析，可以發(fā)現(xiàn)在執(zhí)行時(shí)間、資源效率和整體系統(tǒng)吞吐量方面有了顯著的改善。通過(guò)利用AI工具進(jìn)行查詢(xún)優(yōu)化、索引方法和預(yù)測(cè)分析，組織可以減少延遲、提高并發(fā)性和減少錯(cuò)誤，從而確保可靠和高效的數(shù)據(jù)庫(kù)環(huán)境。

通過(guò)采用復(fù)雜的索引技術(shù)和基于AI的查詢(xún)分析，執(zhí)行時(shí)間減少了大約70%，CPU和I/O資源消耗減少，查詢(xún)吞吐量增加了三倍。此外，預(yù)測(cè)性維護(hù)促進(jìn)了主動(dòng)資源管理，大大減少了潛在的瓶頸和系統(tǒng)停機(jī)時(shí)間。這些增強(qiáng)功能提高了性能，并為未來(lái)的擴(kuò)展提供了可伸縮性和彈性。

譯者介紹

涂承燁，51CTO社區(qū)編輯，省政府采購(gòu)專(zhuān)家、省綜合性評(píng)標(biāo)專(zhuān)家、公 E 采招標(biāo)采購(gòu)專(zhuān)家，獲得信息系統(tǒng)項(xiàng)目管理師、信息系統(tǒng)監(jiān)理師、PMP，CSPM-2等認(rèn)證，擁有15年以上的開(kāi)發(fā)、項(xiàng)目管理、咨詢(xún)?cè)O(shè)計(jì)等經(jīng)驗(yàn)。對(duì)項(xiàng)目管理、前后端開(kāi)發(fā)、微服務(wù)、架構(gòu)設(shè)計(jì)、物聯(lián)網(wǎng)、大數(shù)據(jù)、咨詢(xún)?cè)O(shè)計(jì)等較為關(guān)注。

原文標(biāo)題：Optimizing SQL Server Performance With AI: Automating Query Optimization and Predictive Maintenance，作者：Vijay Panwar