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.Lesson Learned #513: Using SQL Copilot and Python Retry Logic to Resolve Deadlocks in Azure SQL DB
A few weeks ago, I worked with a service request where our customer has a Python application that was reporting a deadlock with the following error message: INFO:root:Connected to the Database in dbname - Thread id 21132 - 0.0454 seconds --- INFO:root:Connected to the Database in dbname - Thread id 39856 - 0.4066 seconds --- ERROR:root:Transaction 2 failed: ('40001', '[40001] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)') INFO:root:Transaction 1 completed successfully. In many of our articles and public information like these ones Analyze and prevent deadlocks in Azure SQL Database or Lesson Learned #19: How to obtain the deadlocks of your Azure SQL Database or Managed Instance?, we review ways to detect and resolve deadlocks by analyzing their details. At this time, I would like to share my experience using Copilot in Azure SQL Database to pinpoint the deadlock. After the error received, we started using with several prompts like Give me the deadlock that occurred in my database recently or Provide detailed information about the last deadlock in my database, including involved tables and queries and we got the following information: Based on the internal Azure SQL telemetry, the following deadlock query hashes have been identified in your database within the past 2 hours: Deadlock Details Query Hash 1: 0xD57AFD9584A2C053 Query Text 1: (@2 tinyint,@1 varchar(8000))UPDATE [dbo].[MS_TableA_MS] SET [ValueA] = @1 WHERE [ID]=@2; Query Hash 2: 0xBEAB84B27CE1F534 Query Text 2: (@2 tinyint,@1 varchar(8000))UPDATE [dbo].[MS_TableB_MS] SET [ValueB] = @1 WHERE [ID]=@2; At this point we identified the queries involved on this deadlock and we executed a second prompt that we used was: Suggest options to prevent or mitigate the recent deadlock in my database and we got very useful information about multiple options to prevent or mitigate the deadlock, like, Index Optimization, Force a Query Plan, Rewriting Transactions, Retry Logic in Application or T-SQL Code, etc.. For application retry logic, we traced the source code that triggered the issue and added a retry mechanism to handle the deadlock. Here’s a simple example of what we implemented. Basically, this small code, creates two tables, runs two transactions in two different threads and depending on the value of retry parameter retry or not the deadlock operation. # Constants for table name used in the deadlock scenario TABLE_A_NAME = "dbo.MS_TableA_MS" TABLE_B_NAME = "dbo.MS_TableB_MS" TABLE_A_COLUMN = "ValueA" TABLE_B_COLUMN = "ValueB" ITEM_ID = 1 TABLE_A_INITIAL_VALUE = 'Value A1' TABLE_B_INITIAL_VALUE = 'Value B1' def simulate_deadlock(retry=False, retry_attempts=3): """ Simulates a deadlock by running two transactions in parallel that lock resources. If retry is enabled, the transactions will attempt to retry up to a specified number of attempts. """ setup_deadlock_tables() thread1 = threading.Thread(target=run_transaction_with_retry, args=(deadlock_transaction1, retry_attempts,retry)) thread2 = threading.Thread(target=run_transaction_with_retry, args=(deadlock_transaction2, retry_attempts,retry)) thread1.start() thread2.start() thread1.join() thread2.join() def run_transaction_with_retry(transaction_func, attempt_limit,retry): attempt = 0 while attempt < attempt_limit: try: transaction_func(retry) break except pyodbc.Error as e: if 'deadlock' in str(e).lower() and retry: attempt += 1 logging.warning(f"Deadlock detected. Retrying transaction... Attempt {attempt}/{attempt_limit}") time.sleep(1) else: logging.error(f"Transaction failed: {e}") break def setup_deadlock_tables(): """ Sets up the tables required for the deadlock simulation, using constants for table and column names. """ conn, dbNameReturn = ConnectToTheDB() if conn is None: logging.info('Error establishing connection to the database. Exiting setup.') return cursor = conn.cursor() try: cursor.execute(f""" IF OBJECT_ID('{TABLE_A_NAME}', 'U') IS NULL CREATE TABLE {TABLE_A_NAME} ( ID INT PRIMARY KEY, {TABLE_A_COLUMN} VARCHAR(100) ); """) cursor.execute(f""" IF OBJECT_ID('{TABLE_B_NAME}', 'U') IS NULL CREATE TABLE {TABLE_B_NAME} ( ID INT PRIMARY KEY, {TABLE_B_COLUMN} VARCHAR(100) ); """) cursor.execute(f"SELECT COUNT(*) FROM {TABLE_A_NAME}") if cursor.fetchone()[0] == 0: cursor.execute(f"INSERT INTO {TABLE_A_NAME} (ID, {TABLE_A_COLUMN}) VALUES ({ITEM_ID}, '{TABLE_A_INITIAL_VALUE}');") cursor.execute(f"SELECT COUNT(*) FROM {TABLE_B_NAME}") if cursor.fetchone()[0] == 0: cursor.execute(f"INSERT INTO {TABLE_B_NAME} (ID, {TABLE_B_COLUMN}) VALUES ({ITEM_ID}, '{TABLE_B_INITIAL_VALUE}');") conn.commit() logging.info("Tables prepared successfully.") except Exception as e: logging.error(f"An error occurred in setup_deadlock_tables: {e}") conn.rollback() finally: conn.close() def deadlock_transaction1(retry=False): conn, dbNameReturn = ConnectToTheDB() if conn is None: logging.info('Error establishing connection to the database. Exiting transaction 1.') return cursor = conn.cursor() try: cursor.execute("BEGIN TRANSACTION;") cursor.execute(f"UPDATE {TABLE_A_NAME} SET {TABLE_A_COLUMN} = 'Transaction 1' WHERE ID = {ITEM_ID};") time.sleep(2) cursor.execute(f"UPDATE {TABLE_B_NAME} SET {TABLE_B_COLUMN} = 'Transaction 1' WHERE ID = {ITEM_ID};") conn.commit() logging.info("Transaction 1 completed successfully.") except pyodbc.Error as e: logging.error(f"Transaction 1 failed: {e}") conn.rollback() if 'deadlock' in str(e).lower() and retry: raise e # Rethrow the exception to trigger retry in run_transaction_with_retry finally: conn.close() def deadlock_transaction2(retry=False): conn, dbNameReturn = ConnectToTheDB() if conn is None: logging.info('Error establishing connection to the database. Exiting transaction 2.') return cursor = conn.cursor() try: cursor.execute("BEGIN TRANSACTION;") cursor.execute(f"UPDATE {TABLE_B_NAME} SET {TABLE_B_COLUMN} = 'Transaction 2' WHERE ID = {ITEM_ID};") time.sleep(2) cursor.execute(f"UPDATE {TABLE_A_NAME} SET {TABLE_A_COLUMN} = 'Transaction 2' WHERE ID = {ITEM_ID};") conn.commit() logging.info("Transaction 2 completed successfully.") except pyodbc.Error as e: logging.error(f"Transaction 2 failed: {e}") conn.rollback() if 'deadlock' in str(e).lower() and retry: raise e # Rethrow the exception to trigger retry in run_transaction_with_retry, finally: conn.close()
