Database Management

Query Plan Was Not Collected in SQL Sentry

This article describes some common reasons why a query plan match may not have been found for a specific batch or statement in SQL Sentry.

First published date

5/26/2021 1:27 PM

Last published date

7/31/2025 8:59 PM

Overview

Users may experience incomplete or spotty collection of query plans for some queries within Top SQL. This can be due to the query type or several other reasons outlined below.

Product section

SQL Sentry (SQLS)

Cause

Why was a query plan not collected?

SQL Sentry uses a complex algorithm to match cached execution plans from SQL Server to Top SQL events. Various factors can prevent a plan from being collected or matched.

Resolution

Dynamic SQL

  • EXEC ('tsql_string') and sp_executesql do not compile queries until execution.
  • The only way to gather the plan is to capture the plan handle while the query is running.
  • The default polling interval is 5 seconds; the plan may not be captured if the dynamic SQL runs faster.

 

Batches with More Than One Statement

  • Retrieving a plan sometimes requires SET SHOWPLAN_XML ON.
  • SQL Sentry does not capture plans this way for batches with multiple statements to avoid plan cache bloat.
  • If the batch runs many 

Matching Errors

  • Plan analysis can fail due to race conditions within SQL Server DMVs (dm_exec_requests, dm_exec_query_stats).
  • These DMVs pull data from multiple sources, which may not always be consistent, causing matching errors and preventing plan matches.

Encrypted Stored Procedures

  • Encrypted stored procedures do not return meaningful query plan data.
  • Since query plans reveal statements, encrypted plans hide this information by design.

Unavailable System Stored Procedures

  • Some system-stored procedures are hidden, which can cause issues.
  • Capturing the handle during execution may retrieve the plan, but object or plan lookups might not yield valid 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

  • Under memory pressure, SQL Server may evict plans from the cache to free memory.
  • The plan will be unavailable if a plan is evicted between SQL Sentry capturing the query and plan collection.
  • Manual cache clearing via DBCC FREEPROCCACHE can also cause this.

Very Large Plans

  • Execution plans larger than 4MB may not be matched due to SQL Server function limitations in gathering plan XML.
  • SQL Sentry versions 6.2.60.0 and higher include a configuration option to mitigate this issue:

To enable the mitigation:

  1. Locate SQLSentryServer.exe.config in the SQL Sentry Monitoring Service install directory.
  2. Open the file in a text editor.
  3. Find the <appSettings> XML element.
  4. Add the following key inside <appSettings> (following the existing format):

add key="useTextBasedQueryPlanFunction" value="true"

  1. Save the file.
  2. Restart the SQL Sentry Monitoring Service.

Optimize For Ad Hoc Workloads Enabled

  • When "optimize for ad hoc workloads" is enabled on SQL Server, ad hoc plans are not cached until used more than once.
  • Only a plan stub is stored initially, which means no full plan is available to capture.
  • SQL Sentry cannot match SQL events with plans if only stubs exist.