-
Title
Why Foglight SQL PI Displays Query Hash Instead of SQL Text in the SQL PI Dashboard -
Description
When navigating the SQL PI dashboard, the SQL Text column displays Query Hash values or SQL Handles instead of the actual SQL statement text.
Example:
Navigate to Databases > SQL PI > SQL Statements > Blocking History
You may observe entries labeled as "Unknown Batch" or see only a SQL Handle/Query Hash with no associated SQL text.
-
Cause
Cause 1: SQL Text Not Resolved From SQL Server Cache (Most Common)
-
SQL PI resolves statements using SQL Server's procedure cache (via
sys.dm_exec_sql_text). -
SQL statements may not be available in the cache at the time of collection.
-
The following scenarios prevent full SQL text retrieval:
-
The batch was too long and truncated.
-
The batch or statement was not available in memory during collection.
-
The SQL statement was not cached (e.g., simple
BACKUPorEXECstatements). -
SQL execution completed and the cache was flushed before PI retrieved the text.
-
There may be a log message from the agent log on Query Completion with stats that looks like this:collected tops [214], misses [80],so the miss ratio [37.38317757009346].Cause 2: SQL Handle Not Resolvable
-
The DBSS agent attempts to query
sys.dm_exec_sql_text, but the text is unavailable, often due to cache flush or delayed collection. This is not a bug or defect but rather is just a side effect of a defined time gap between Top SQL collections and SQL Server clearing away cache and sometimes PI misses getting results from SQL Server when querying sys.dm_exec_sql_text. -
The agent log may contain messages like the following that indicates the SQL text has been flushed away by SQL Server between agent collections
The processor received an empty string for the SQL text...
Cause 3: Insufficient Environment Sizing
-
Inadequate resources (e.g., memory or CPU) for Foglight or SQL PI components may affect timely and complete data collection.
-
Collection delays increase the chance of missing SQL text between execution and collection.
-
-
Resolution
-
Review Performance Tree:
-
Navigate to the SQL PI dashboard.
-
Expand Query Hash > TSQL Batches to locate the associated SQL Handle.
-
-
Manually Query SQL Handle in SSMS:
Run the following command in SQL Server Management Studio, replacing the SQL handle:SELECT * FROM sys.dm_exec_sql_text(0x0200000040f67b0a35329594ae31b3965230240970a5635a...)Use the SQL Handle, not the Query Hash. SQL Hashing is a one-way function and cannot retrieve the original text.
-
Review Additional Command Metadata:
-
Expand the Command Types node in the Performance Tree.
-
Enable hidden columns to view additional session details such as program names, users, or client machines.
-
For
EXECUTEstatements, check if the stored procedure is shown in a separate batch.
-
-
Common Cases:
-
Text for stored procedures invoked by
EXECmay appear in a separate batch. -
Backup or maintenance commands are often not cached, hence not retrievable.
-
Resolution 2: Understand Collection Timing and Cache Behavior
SQL PI captures top queries periodically, and SQL Server may remove execution plans from the cache before PI collects them.
Foglight agent collection frequency (example):
Mode Collection Frequency Real-time Every 2 minutes (120 sec) when viewing the dashboard Online Every 5 minutes (300 sec) when any user is logged in Offline Every 15 minutes (900 sec) when no users are logged in To reduce missed collections:
-
Decrease the Offline Collection Frequency from 15 minutes to 10 or 5 minutes.
-
Note that increasing collection frequency may slightly impact SQL Server performance.
Resolution 3: Confirm Environment Sizing
Verify that the Foglight environment meets all sizing and performance requirements:
-
Refer to the Foglight for Databases Deployment Guide.
-
Ensure adequate memory and CPU are allocated to the FMS, FglAM, and SQL PI repository.
-
Review the Tuning and Sizing section in the Foglight for SQL Server user guide.
Enhancement Request:
Enhancement ID: FOG-8742
An enhancement has been logged to improve user experience in these cases. Planned updates include:
-
Tooltip messages in the SQL PI dimension tree:
"Query text not found in SQL Server plan cache. This might be because the plan cache is full and has cycled out older plans." -
Display message in View SQL Text pop-up when text is unavailable.
This enhancement is targeted for a future release of the SQL Server cartridge.
-
-
Defect ID
FOG-8742 -
Attachments
-
Additional Information
When running a cluster setup (mirroring) the flushing of the procedure cache when faling over could also have an impact.
See look at this article.