使用AI优化SQL Server性能：自动化查询优化和预测性维护

diannao

译者 | 涂承烨
审校 | 重楼
SQL Server是一个功能强大的关系型数据库管理系统（RDBMS），但是随着数据集的大小和复杂性的增长，优化它们的性能变得至关重要。利用AI可以彻底改变查询优化和预测性维护，确保数据库保持高效、安全和响应性。

在本文中，我们将探讨AI如何在这些领域提供帮助，并提供处理复杂查询的代码示例。
用于查询优化的AI

由于低效的令人兴奋的计划或糟糕的索引策略，复杂的查询可能会很慢。人工智能可以分析查询执行指标，识别瓶颈，并提供优化建议。
示例：复杂查询优化

让我们从一个运行缓慢的查询开始：
MS SQL
SELECT  p.ProductID,  SUM(o.Quantity) AS TotalQuantity FROM  Products pJOIN  Orders o ON  p.ProductID = o.ProductIDWHERE  o.OrderDate >= '2023-01-01'GROUP BY  p.ProductIDHAVING  SUM(o.Quantity) > 1000ORDER BY  TotalQuantity DESC;

• 1.
  
• 2.
  
• 3.
  
• 4.
  
• 5.
  
• 6.
  
• 7.
  
• 8.
  
• 9.
  
• 10.
  
• 11.
  
• 12.
  
• 13.
  
• 14.
  
• 15.
  
• 16.
  
• 17.
  

此查询存在性能问题的原因如下：

• 在OrderDate和ProductID上未优化索引
  
• 查询了大量不必要的数据范围
  

解决方案：基于AI的查询计划分析

使用SQL Server Query Store等工具并集成基于AI的分析，可以发现效率低下的问题：
1、启用查询存储

MS SQL
ALTER DATABASE AdventureWorksSET QUERY_STORE = ON;

• 1.
  
• 2.
  

2、捕获查询性能指标

使用Python和PyODBS等库以及AI框架来分析查询的执行和统计数据。
Python
import pyodbcimport pandas as pdfrom sklearn.ensemble import IsolationForest# Connect to SQL Serverconn = pyodbc.connect( "Driver={SQL Server};" "Server=your_server_name;" "Database=AdventureWorks;" "Trusted_Connection=yes;")# Retrieve query execution statsquery = """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

• 1.
  
• 2.
  
• 3.
  
• 4.
  
• 5.
  
• 6.
  
• 7.
  
• 8.
  
• 9.
  
• 10.
  
• 11.
  
• 12.
  
• 13.
  
• 14.
  
• 15.
  
• 16.
  
• 17.
  
• 18.
  
• 19.
  
• 20.
  
• 21.
  
• 22.
  
• 23.
  
• 24.
  
• 25.
  
• 26.
  
• 27.
  

3、优化查询

根据分析，添加适当的索引：
MS SQL
CREATE NONCLUSTERED INDEX IDX_Orders_OrderDate_ProductIDON Orders(OrderDate, ProductID);

• 1.
  
• 2.
  

以下是AI建议后更新的Query，减少了不必要的查询范围：
MS SQL
SELECT  p.ProductID,  SUM(o.Quantity) AS TotalQuantity FROM  Products pJOIN  Orders o ON  p.ProductID = o.ProductIDWHERE  o.OrderDate >= '2023-01-01' AND EXISTS ( SELECT 1 FROM Orders o2 WHERE o2.ProductID = p.ProductID AND o2.Quantity > 1000 )GROUP BY  p.ProductIDORDER BY  TotalQuantity DESC;

• 1.
  
• 2.
  
• 3.
  
• 4.
  
• 5.
  
• 6.
  
• 7.
  
• 8.
  
• 9.
  
• 10.
  
• 11.
  
• 12.
  
• 13.
  
• 14.
  
• 15.
  
• 16.
  
• 17.
  
• 18.
  

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_writesFROM  sys.dm_io_virtual_file_stats(NULL, NULL);

• 1.
  
• 2.
  
• 3.
  
• 4.
  
• 5.
  
• 6.
  
• 7.
  
• 8.
  

2、使用AI分析指标

使用Python和回归模型预测瓶颈：
Python
from sklearn.linear_model import LinearRegressionimport numpy as np# Example I/O dataio_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 modelmodel = LinearRegression()model.fit(X, y)# Predict for future workload levelsfuture_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")

• 1.
  
• 2.
  
• 3.
  
• 4.
  
• 5.
  
• 6.
  
• 7.
  
• 8.
  
• 9.
  
• 10.
  
• 11.
  
• 12.
  
• 13.
  
• 14.
  
• 15.
  
• 16.
  
• 17.
  
• 18.
  
• 19.
  
• 20.
  

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