Description from The SentryOne community
Why was a query plan not collected?
SQL Sentry uses a complex algorithm to perform matching of cached execution plans from SQL Server to Top SQL events. This article describes some of the more common reasons that a match may not have been found for a specific batch or statement.
Dynamic SQL
EXEC (‘tsql_string’) and sp_executesql do not actually compile a query until they execute it, so the only way to gather the plan for the call is to get the plan handle while it is running. The default polling interval is 5 seconds, so if the dynamic SQL query runs faster than this a plan may not be captured.
Batches with More Than One Statement
In some cases the only way to retrieve a plan is by using the SET SHOWPLAN_XML ON set option. In order to prevent plan cache bloat, we do not try to capture plans using this method if the batch has more than one statement. Because of this, if the batch runs many statements and runs quickly, the odds of capturing a plan can be low for the same reasons as Dynamic SQL.
Matching Errors
There are times where the plan analysis can fail, and sometimes this can be attributed to race conditions within the DMVs themselves causing bad data points for the analysis. Note that dm_exec_requests and dm_exec_query_stats are DMVs that pull data from multiple sources in SQL Server. Because of this, the data contained within them is not always 100% consistent across all sources. These inconsistencies will cause the matching errors mentioned above, preventing a plan from being matched to a Top SQL event.
Encrypted Stored Procedures
Encrypted stored procedures do not return query plans with meaningful data. This is because query plans show statements, and if encrypted plans showed statements it would defeat the purpose of encryption, which is to hide them.
Unavailable System Stored Procedures
Some system stored procedures are hidden and this can occasionally cause problems. If we capture the actual handle while the statement is running, we can get the query plan, but sometimes the object or plan lookup will yield results that don’t point to actual plan information.
Trivial Plans
Some plans are trivial and contain only statements that do not generate plan information. For instance the following statement will never generate a query plan:
IF (1 = 1)
WAITFOR DELAY ’00:00:10’Plan Eviction
If a server is experiencing memory pressure, SQL Server may evict items from the plan cache to free memory for newer cached items sooner than is optimal. If an execution plan is evicted between the time SQL Sentry captures the query and the time plans are collected, the plan will not be available for the matching algorithm. This can also happen if the plan cache is freed manually using DBCC FREEPROCCACHE.
Very Large Plans
Very large execution plans (larger than 4MB) may not be matched due to a limitation with the SQL Server function for gathering the plan XML. In versions of SQL Sentry 6.2.60.0 and higher, we have added a configuration option which can mitigate this issue.
If you believe that you are experiencing the issue, try the following:
In the install directory of your SQL Sentry Monitoring Service, find the file named “SQLSentryServer.exe.config”. Open this file in any text editor, and find the XML element for “appsettings”. Add the following key between the opening and closing tags for “appsettings” (copy the existing format of other settings including any opening and closing tags):
add key="useTextBasedQueryPlanFunction" value="true"Save the file, and restart your SQL Sentry Monitoring Service.
Optimize For Ad Hoc Workloads Enabled
This is because SQL Sentry plan collection acts on the SQL Server plan cache. When “optimize for ad hoc workloads” is enabled, ad hoc plans are not cached until they have been used more than once. Instead, a plan stub is stored in order to determine if the plan is needed again.
If nothing is stored for an execution other than a plan stub, there is no plan to capture, and SQL Sentry will not be able to match the SQL event with a plan.