The query plan for SQL Server was not collected.

Why was a query plan not collected?

SQL Sentry employs a sophisticated algorithm to match cached execution plans from SQL Server to Top SQL events. This article discusses some more common reasons for not discovering a match for a given batch or statement.

SQL is dynamic

Because EXEC (‘tsql string’) and sp execute SQL do not compile a query until after it has been completed, the only method to retrieve the plan for the call is to get the plan handle while it is ongoing. Because the default polling time is 5 seconds, it may not capture a project if the dynamic SQL query runs faster.

Batches with Multiple Statements

In some circumstances, utilizing the SET SHOWPLAN XML ON set option is the only way to retrieve a plan. We don’t try to capture projects using this way if the batch has more than one statement to avoid plan cache bloat. As a result, if the collection executes a large number of words quickly, the chances of capturing a plan are minimal for the same reasons as Dynamic SQL.

Errors that match

The plan analysis can sometimes fail, and this can be traced to race circumstances within the DMVs, which result in bad data points for the study. DMVs such as dm exec requests and dm exec query stats pull data from numerous SQL Server sources. As a result, the information contained inside them is not necessarily consistent across all sources. These discrepancies will result in the abovementioned issues, preventing a plan from being matched to a Top SQL event.

Stored Procedures with Encryption

Encrypted stored procedures do not return query plans with valuable data. It is because query plans display statements; if encrypted programs depicted statements, the goal of encryption, which is to conceal them, would be defeated.

System Stored Procedures are not available.

Some system stored procedures are secret, which can cause issues on occasion. We can acquire the query plan if we capture the actual handle while the statement is running. Still, sometimes the object or plan lookup will return results that don’t point to genuine plan information.

Plans That Aren’t Important

Some plans are simple and contain statements that don’t generate plan data. The following sentence, for example, will never create a query plan:

IF (1 = 1)
WAITFOR DELAY '00:00:10'

If you think you’re having trouble, try the following steps:

add key="useTextBasedQueryPlanFunction" value="true"

Find the file “SQLSentryServer.exe.config” in the SQL Sentry Monitoring Service’s install directory. Use any text editor to find the XML element for “app settings” in this file. Copy the existing format of other settings, including any opening and closing tags, and add the following key between the opening and closing tags for “app settings”:

Restart your SQL Sentry Monitoring Service after saving the file.

Enabled Optimize For Ad Hoc Workloads

Because SQL Sentry collects plans from the SQL Server plan cache, this is the case. Ad hoc plans are not cached until they have been utilized more than once when “optimize for ad hoc workloads” is enabled. Instead, a plan stub is kept to see if the plan is ever needed again.

There will be no plan to capture if nothing other than a plan stub is kept for execution, and SQL Sentry cannot match the SQL event with a plan.

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.