Lessons Learned #537: Copilot Prompts for Troubleshooting on Azure SQL Database

We had the opportunity to share our experience in several community sessions how SSMS Copilot can help across multiple phases of troubleshooting. In this article, I would like to share a set of prompts we found in those sessions and show how to apply them to an example query.

During a performance incident, we captured the following query, generated by PowerBI. 

SELECT 
TOP (1000001) *
FROM 
(
    SELECT [t2].[Fiscal Month Label] AS [c38],
           SUM([t5].[Total Excluding Tax]) AS [a0],
           SUM([t5].[Total Including Tax]) AS [a1]
    FROM 
    (
        SELECT [$Table].[Sale Key] as [Sale Key],
               [$Table].[City Key] as [City Key],
               [$Table].[Customer Key] as [Customer Key],
               [$Table].[Bill To Customer Key] as [Bill To Customer Key],
               [$Table].[Stock Item Key] as [Stock Item Key],
               [$Table].[Invoice Date Key] as [Invoice Date Key],
               [$Table].[Delivery Date Key] as [Delivery Date Key],
               [$Table].[Salesperson Key] as [Salesperson Key],
               [$Table].[WWI Invoice ID] as [WWI Invoice ID],
               [$Table].[Description] as [Description],
               [$Table].[Package] as [Package],
               [$Table].[Quantity] as [Quantity],
               [$Table].[Unit Price] as [Unit Price],
               [$Table].[Tax Rate] as [Tax Rate],
               [$Table].[Total Excluding Tax] as [Total Excluding Tax],
               [$Table].[Tax Amount] as [Tax Amount],
               [$Table].[Profit] as [Profit],
               [$Table].[Total Including Tax] as [Total Including Tax],
               [$Table].[Total Dry Items] as [Total Dry Items],
               [$Table].[Total Chiller Items] as [Total Chiller Items],
               [$Table].[Lineage Key] as [Lineage Key]
        FROM [Fact].[Sale] as [$Table]
    ) AS [t5]
    INNER JOIN 
    (
        SELECT [$Table].[Date] as [Date],
               [$Table].[Day Number] as [Day Number],
               [$Table].[Day] as [Day],
               [$Table].[Month] as [Month],
               [$Table].[Short Month] as [Short Month],
               [$Table].[Calendar Month Number] as [Calendar Month Number],
               [$Table].[Calendar Month Label] as [Calendar Month Label],
               [$Table].[Calendar Year] as [Calendar Year],
               [$Table].[Calendar Year Label] as [Calendar Year Label],
               [$Table].[Fiscal Month Number] as [Fiscal Month Number],
               [$Table].[Fiscal Month Label] as [Fiscal Month Label],
               [$Table].[Fiscal Year] as [Fiscal Year],
               [$Table].[Fiscal Year Label] as [Fiscal Year Label],
               [$Table].[ISO Week Number] as [ISO Week Number]
        FROM [Dimension].[Date] as [$Table]
    ) AS [t2] 
    ON [t5].[Delivery Date Key] = [t2].[Date]
    GROUP BY [t2].[Fiscal Month Label]
) AS [MainTable]
WHERE 
(
    NOT([a0] IS NULL) OR 
    NOT([a1] IS NULL)
)

I structure the investigation in three areas:

Analysis – understand the data model, sizes, and relationships.

List all tables in the 'Fact' and 'Dimension' schemas with space usage in MB and number of rows.
The name of the tables and their relations among them.
Please, provide a textual representation for all relations.
List all foreign key relationships between tables in the 'Fact' and 'Dimension' schemas, showing the cardinality and referenced columns.
Could you please let me know what is the meaning of every table?
Describe all schemas in this database, listing the number of tables and views per schema.
Create a textual data model (ER-style) representation showing how all Fact and Dimension tables are connected.

Maintenance Plan Check – verify statistics freshness, index health/fragmentation, partition layout, and data quality.

List all statistics in the database that have not been updated in the last 7 days, showing table name, number of rows, and last update date.
List all indexes in the database with fragmentation higher than 30%, including table name, index name, and page count.
Please, provide the T-SQL to rebuild all indexes in ONLINE mode and UPDATE STATISTICS for all tables that are automatic statistics.
Check for fact table rows that reference dimension keys which no longer exist (broken foreign key integrity).
Find queries that perform table scans on large tables where no indexes are used, based on recent execution plans.

Performance Improvements – simplify/reshape the query and consider indexed views, columnstore, partitioning, and missing indexes.

In this part, I would like to spend more time about these prompts, for example the following ones, help me to understand the performance issue, simplify the query text and also, explains what the query is doing. 

Identify the longest-running query in the last 24 hours provide the full text of the query
Please simplify the query
Explain me the query
Explain in plain language what the following SQL query does, including the purpose of each subquery and the final WHERE clause.
Show a histogram of data distribution for key columns used in joins or filters, such as SaleDate, ProductCategory, or Region.

Finally, using this prompt I could find a lot of useful information how to improve the execution of this query:

Analyze the following SQL query and provide a detailed performance review tailored for Azure SQL Database Hyperscale and Power BI DirectQuery scenarios. For each recommendation, estimate the potential performance improvement as a percentage (e.g. query runtime reduction, I/O savings, etc.).

1. Could this query benefit from a schemabound indexed view or a materialized view? Estimate the performance gain if implemented.
2. Is there any missing index on the involved tables that would improve join or filter efficiency? Include the suggested index definition and expected benefit.
3. Would using a clustered or nonclustered columnstore index on the main fact table improve performance? Estimate the potential gain in query time or storage.
4. Could partitioning the fact table improve performance by enabling partition elimination? If so, suggest the partition key and scheme, and estimate improvement.
5. Are current statistics sufficient for optimal execution plans? Recommend updates if needed and estimate impact.
6. Does this query preserve query folding when used with Power BI DirectQuery? If not, identify what breaks folding and suggest how to fix it.
7. Recommend any query rewrites or schema redesigns, along with estimated performance improvements for each.

I got a lot of improvements suggestions about it: 

• Evaluated a schemabound indexed view that pre‑aggregates by month (see Reference Implementations), then pointed Power BI to the view.
• Ensured clustered columnstore on Fact.Sale; considered a targeted rowstore NCI on [Delivery Date Key] INCLUDE ([Total Excluding Tax], [Total Including Tax]) when columnstore alone wasn’t sufficient.
• Verified statistics freshness on join/aggregate columns and enabled incremental stats for partitions.
• Checked partitioning by date to leverage elimination for common slicers.