Sql-server – Is it possible to get the name of a procedure by it’s plan handle or query plan hash if the execution plan is already cleared from the plan cache

dmvplan-cachequery-cachesql serversql-server-2016

I ran a home-brew trace for the length of 1 day that captured a few things that were running. The trace includes the specific query text, the parent entity text of that query, the Plan Handle, and the Query Plan Hash.

Usually I can use RedGate's SQL Search on the query text to find the entity I'm looking for but that was a no-go in this case.
Unfortunately that was a day ago when the trace ran, and when I check the sys.dm_exec_cached_plans DMV, nothing is turning up for my plan handle.

Are there any other ways I can backtrace this query?

Update: Here's how the Query Store is currently configured on the database in question. Note sure if it can be useful for my case?

Best Answer

If (and that's a big IF) you're running SQL Server 2019 (CTP 2.4) then you can get the last known ACTUAL execution plan for a previously cached query plan:

SELECT qps.query_plan
FROM   sys.dm_exec_procedure_stats AS ps
       CROSS APPLY sys.dm_exec_query_plan_stats(deps.plan_handle) AS qps
WHERE  ps.object_id = OBJECT_ID('dbo.spMyProcedure');

Related Solutions

SQL Server – Identifying Execution Plans with Probe Residual

What you want to do is to use nodes() Method (xml Data Type) in a cross apply to shred on RelOp nodes so you then can use value() Method (xml Data Type) to get the property values you are looking for.

You are specifically looking for the RelOp nodes that have a Hash/ProbeResidual node so you should use that in a predicate in the XQuery parameter to the nodes() function.

WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DEQP.query_plan,
       DEST.text,
       RO.X.value('@PhysicalOp', 'nvarchar(50)') as PhysicalOp,
       RO.X.value('@LogicalOp', 'nvarchar(50)') as LogicalOp,
       RO.X.value('@EstimatedTotalSubtreeCost', 'float') as EstimatedTotalSubtreeCost
FROM sys.dm_exec_cached_plans AS DECP
  CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
  CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) as DEST
  CROSS APPLY DEQP.query_plan.nodes('//RelOp[Hash/ProbeResidual]') as RO(X);

SQL Server 2016 – Queries Recompiling Due to Plan Cache Corruption

You should enable Lock Pages in Memory (LPIM).

On SQL Server 2016+, I've encountered a similar scenario that I believe to be a bug. When the server experiences memory pressure, and LPIM is not be enabled, SQL Server will clear the plan cache as part of it's process to release memory back to the OS. This much in normal. SQL Server should only do this when a low memory condition exists.

In some cases, SQL Server will continue to clear the plan cache, even after the low memory condition is resolved. This results in the behavior you are seeing. On a busy server, you will see some plans entering the plan cache, only to get purged seconds later. This is not the proper behavior.

Enabling LPIM will resolve the issue. Enabling LPIM is a best practice for all SQL Server installations, so I would recommend enabling this for every SQL Server instance, regardless of version or whether you've experienced this problem.

To enable the lock pages in memory option

On the Start menu, click Run. In the Open box, type gpedit.msc.
On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account.
Restart the SQL Server Service for this setting to take effect.

Best Answer

Related Solutions

SQL Server – Identifying Execution Plans with Probe Residual

SQL Server 2016 – Queries Recompiling Due to Plan Cache Corruption

To enable the lock pages in memory option

Related Question