Lessons Learned #560: SQL Is Slow — What Can Query Store and SSMS Copilot Uncover?
A recent session demonstrated how to investigate SQL performance issues by combining Query Store and SSMS Copilot. The demo involved a PHP program that grew a table, causing performance changes and different query execution plans. A key observation was a query's execution time increasing dramatically under the same Plan ID, indicating degradation within a plan. The session showed how Copilot could guide investigations, evolving from Ask mode to Agent mode, and how these processes could be captured as reusable skills. Query Store provided historical performance data, while Copilot aided in analyzing this data and deciding on next steps. The practical demonstration used a PHP program to simulate various performance problems by adding records to a table in stages. As data volume increased, the execution plans and performance of the dbo.GiveNotes query evolved. A detailed comparison revealed a significant increase in CPU, duration, and logical reads for one Plan ID, even before a new plan was introduced. The investigation focused on building a timeline of changes and understanding the aggregated impact of workload contributors. Weighted averages were crucial for accurately assessing performance across different intervals and execution counts. The increase in logical reads pointed towards data processing and access path issues. The session explored three ways to interact with Copilot: Ask mode for guided questions, Agent mode for autonomous diagnostics, and reusable Agent Skills for standardized investigations. The SQL Performance Investigator pack was developed, instructing the agent to identify contributors, analyze plan history, check for degradation within plans, and investigate selectivity, statistics, and indexes. A proposed index was evaluated, but its actual improvement required further validation against a comparable workload. The core lesson was that understanding performance changes requires examining both differences between plans and variations within a single plan's history.
dbo.GiveNotesquery evolved. A detailed comparison revealed a significant increase in CPU, duration, and logical reads for one Plan ID, even before a new plan was introduced. The investigation focused on building a timeline of changes and understanding the aggregated impact of workload contributors. Weighted averages were crucial for accurately assessing performance across different intervals and execution counts. The increase in logical reads pointed towards data processing and access path issues. The session explored three ways to interact with Copilot: Ask mode for guided questions, Agent mode for autonomous diagnostics, and reusable Agent Skills for standardized investigations. The SQL Performance Investigator pack was developed, instructing the agent to identify contributors, analyze plan history, check for degradation within plans, and investigate selectivity, statistics, and indexes. A proposed index was evaluated, but its actual improvement required further validation against a comparable workload. The core lesson was that understanding performance changes requires examining both differences between plans and variations within a single plan's history.