RECOMPILE appears to prevent query capture

HugoKornelis

I'm trying to investigate the root cause of a very long running data load of the DW server. But even after allowing it to run to completion, the "TOP 50 QUERIES" list does not show any of the offending queries (I tried both "average per execution" and "totals", and in both cases sorted by elapsed time).

After doing some further investigation I found that most of the load time was spent in a stored procedure that has the RECOMPILE hint. I know that this hint disables some of the commonly used options to capture query execution plans, and that these stored procedures will not appear in sys.dm_exec_procedure_stats.

Is SQLMonitor perhaps getting its information from DMVs that do not store data on queries and procedures with a recompile hint? In that case, please consider changing that going forward.

If SQLMonitor does normally catch everything, then did I perhaps set up something wrong? Can anyone tell me what to do to not run into this again?

Russell D

Hi Hugo, we sample sys.dm_exec_query_stats and sys.dm_exec_procedure_stats, and these contain information about plans before/after recompilation (plan_generation_num).

Its possible that we don't take this into account - I'll need to dig into it.

Russell D

Ok - this isn't a problem with the Query plans being recompiled. We allow for that and this area doesn't have any known issues.

This is actually a limitation of the Top Query Sampler, that is limited to queries running under 25 hours. For queries that are longer, you would need to look at Long Running Queries.