Predictive Detection and Resolution of SQL Server Performance Bottlenecks Using Generative AI And Dynamic Management Views (DMVS)

Abstract

Resource pressure is a recurring issue in SQL Server performance management because users only notice it when they experience slow response times, blocking, timeouts, or service instability. Manual interpretation of Dynamic Management Views (DMVs) is typically reactive and requires specialist database-administration expertise to interpret the details of the telemetry, which can be accessed via DMVs This study proposes and tests a predictive model to identify and resolve SQL Server performance issues based on the DMVs telemetry, supervised machine learning, and guarded Generative AI suggestions. The records were gathered from a balanced SQL Server 2025 Developer environment in five different operational states: normal, CPU bottleneck, I/O bottleneck, blocking bottleneck, and missing-index bottleneck with the AdventureWorks2022 database. Logistic Regression, Random Forest, Gradient Boosting and XGBoost classifiers were built using Encoded DMV features. Random Forest and Gradient Boosting obtained the highest test accuracy (83.64%) and macro F1-score (83.89%), and the five-fold cross-validation showed good stability. The blocking session count, the number of tasks waiting, wait time, elapsed time, number of logical reads, worker time, and physical reads were the most important features identified by the feature-importance analysis. The GenAI recommendation layer generated root cause explanations and safe resolution actions that are understandable to the DBA. Contribution is a proactive and explainable framework for SQL Server performance management.