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?

enter image description here

  • Is Query Store enabled on the database? – Denis Rubashkin Feb 7 at 15:23
  • @DenisRubashkin Good question, not sure to be honest. I would say likely not (and it's not something I've used before) but how can I check that just in case? I see the "Query Store (READ_ONLY)" node in SSMS, but not sure where to go from there? – J.D. Feb 7 at 16:20
  • Probably won't work as QS is read only but worth trying SELECT OBJECT_NAME(q.object_id), * FROM sys.query_store_plan qsp JOIN sys.query_store_query q ON qsp.query_id = q.query_id WHERE query_plan_hash = whatever in case it was stored before it became read only – Martin Smith Feb 7 at 16:32
  • Why can't you locate the procedure using the "specific query text, the parent entity text of that query"? – David Browne - Microsoft Feb 7 at 16:39
  • @DavidBrowne-Microsoft I usually use RedGate's SQL Search on the text but it's not coming back with anything. It's very possible this query doesn't live on my server and instead is ran inline via the mock ORM Dapper, but I was hoping I could find some more information about it if I had the Plan Handle or Query Plan Hash that would help me conclude it truly doesn't live on the server. But I also didn't see it in my source code repository of Dapper queries either. (Also the Query Text and Parent Entity Text are both cutoff in my trace results, so it's harder to search for them in this case.) – J.D. Feb 7 at 19:03

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');
|improve this answer|||||
  • Good to know for the future, but I'm currently on SQL Server 2016. Thanks anyway though. – J.D. Feb 7 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.