This is the 4th in a series of posts about query plans. The first post covered what a query plan is and how to read it. The second post described the effort required to make a query plan. The third entry explained how the plans are stored in a cache to be reused. This post will cover how the optimizer is able to quickly determine whether a plan in the cache exists for any given query.
When a query is executed the database engine quickly creates a hash of the query code. This hash is called a query handle. The engine then compares this handle to a list of query handles for which a query plan exists. If the query handle is found in the list then it will refer the engine to a plan handle and eventually the plan XML. If the query handle is not found in the list then a new plan is compiled and added to the list.
Let’s see that in action. To follow along open an SSMS window and point at a WideWorldImporters database. These examples were done with SSMS 17 and SQL Server 2017 RTM.
Run this query and make sure to include the GO 5. This will execute the query batch 5 times.
SELECT TOP 10 a.CustomerName , a.CreditLimit , b.InvoiceDate , b.DeliveryInstructions FROM Sales.Customers a INNER JOIN Sales.Invoices b ON a.CustomerID = b.BillToCustomerID; GO 5
We would expect the first execution to create a query plan and the subsequent executions to use the plan generated during the first. The following query will shows the sql handles of recently executed queries. Running it will return some really ugly handles for both the sql code and the query plan.
SELECT queryStats.sql_handle, queryStats.plan_handle, creation_time, last_execution_time, execution_count FROM sys.dm_exec_query_stats queryStats WHERE queryStats.execution_count BETWEEN 4 and 5 AND last_execution_time > DATEADD(n, -5, GETDATE())
On my system I get exactly 1 result. This is probably the query above, but we want to be sure.
We can become sure by converting the sql and query handles back into their human readable text form. Adding 2 DMVs to the query will accomplish that feat.
SELECT planXML.query_plan, sqlText.text, creation_time, last_execution_time, execution_count FROM sys.dm_exec_query_stats queryStats CROSS APPLY sys.dm_exec_query_plan(queryStats.plan_handle) planXML CROSS APPLY sys.dm_exec_sql_text(queryStats.sql_handle) sqlText WHERE planXML.dbid = DB_ID() AND queryStats.execution_count BETWEEN 4 and 5 AND last_execution_time > DATEADD(n, -10, GETDATE())
That sure looks like the query from the top of the post! Clicking on the query_plan XML column will cause open a new tab to open in SSMS and show the visual representation of the query plan. The query text can be expanded. A copy/paste of that column will include the entire query, not just the visible portion.
Note: What if my DMV query says that it ran 4 times instead of 5 even though I absolutely ran it 5 times? There are many settings that can affect the query plan cache and enabling one popular setting, “optimize for ad hoc workloads”, will cause this to return 4 executions instead of 5.
It’s important to note that the query text must be an EXACT match with a prior execution to hash to the same handle, get found in the list, and reuse an existing plan. In the following example I will run the exact query as above, but this time with the commas in the back instead of in the front. To us humans this query is clearly the same as the earlier query and should be able to reuse the plan. But does it? Run this query including the “GO 5” and then run the DMV query again.
SELECT TOP 10 a.CustomerName, a.CreditLimit, b.InvoiceDate, b.DeliveryInstructions FROM Sales.Customers a INNER JOIN Sales.Invoices b ON a.CustomerID = b.BillToCustomerID; GO 5
The output of the DMV shows the same basic query with 2 separate rows — each with 5 executions. The query text is similar, but not identical in the 2 rows. Keep this in mind when writing T-SQL code that will run repeatedly to make sure you get the most out of the plan cache.
The next post of this series will show how dynamic SQL and parameters can be used or misused and how that affects the plan cache.