Lessons Learned #556: Finding the SQL Statement Behind a Query Hash in Azure SQL Database
Recently, I worked on a performance investigation in which several queries were identified by their query hashes. The hashes helped us discuss the workload, but the customer still needed to answer a practical question: which SQL statements were behind them? A value such as 0x0123456789ABCDEF does not tell an application developer which statement to review. Fortunately, if Query Store captured the query and still retains it, the customer can use that hash to find the SQL text and the query ID in their own database. In this article, I will show that lookup and explain what to do with the result. The hash used below is a placeholder; replace it with a hash from your investigation. A hash is a lookup key rather than the SQL text A query hash is an eight-byte value derived from the shape of a query. It cannot be decoded back into the original statement. We need a source that stores the relationship between the hash and the text. Query Store provides that relationship through two catalog views. sys.query_store_query contains the query hash and query ID. Its query_text_id links to sys.query_store_query_text, where we can retrieve the SQL text. That gives us a small, read-only query to answer the customer’s question. Find the statement in the correct database Connect directly to the Azure SQL Database that executed the workload, rather than master or another database on the same logical server. Use an account with the permissions required to read Query Store information. Then run the following query, replacing the example hash: DECLARE @QueryHash binary(8) = 0x0123456789ABCDEF; SELECT CONVERT(varchar(18), q.query_hash, 1) AS query_hash, q.query_id, q.context_settings_id, qt.query_sql_text FROM sys.query_store_query AS q JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id WHERE q.query_hash = @QueryHash ORDER BY q.query_id; Use the hexadecimal value with its 0x prefix, as shown, without quotation marks. The returned hash is formatted in the same hexadecimal notation so you can compare it with the original value. This lookup does not need a date filter: it searches the query records that Query Store currently retains. Reviewing performance during a particular incident is the next step, after identifying the matching queries. One hash can return more than one query ID Suppose the lookup returns query IDs 125 and 206. Those are illustrative IDs, but they show an important point: we should inspect both rows rather than select the first one automatically. Each query ID can also have more than one execution plan. This simple lookup intentionally returns query records without joining the plan table, so multiple plans do not repeat the SQL text in the results. Use the query ID to continue in SSMS Once the text is identified, the hash has done its job. In SQL Server Management Studio, expand the database, open Query Store, and select Tracked Queries. Enter a returned query ID and choose the period you want to investigate. From there, review the available execution plans and compare CPU, duration, reads, and execution frequency. Repeat the review for other matching query IDs where necessary. Finding the statement does not prove that it caused a particular resource spike. That conclusion still requires performance statistics and an appropriate time correlation. The useful outcome here is that the customer now knows which SQL statement to review. What if the lookup returns no rows An empty result does not prove that the query never ran. First confirm the database and the hash. Then check whether Query Store captured the query and whether its history remains available. Capture policy, cleanup, or Query Store being disabled during execution can explain a missing record. Enabling Query Store now cannot recreate a statement that was never captured. If the workload runs again, capture it and repeat the lookup after verifying that Query Store is collecting the relevant activity. References sys.query_store_query and the query hash. sys.query_store_query_text and SQL text. Monitor performance using Query Store.
