Query Hash message in SQL PI instead of a SQL Statement (4230298)

Cause 1

*This is the most common occurrence* 

Cause 3

Resolution 1

*This is the most common resolution*

It is normal and expected that PI may not be able to show the full text of SQL statement in either of these cases: SQL handle is identified but the full text cannot be retrieved because it is not found in collected instance cache, the procedure cache is too small, and in cases where a Batch full text cannot be retrieved. Sometimes, if the user review the commands of these unresolved handles, it will be clear why they are not resolved - for example, they the user discover it runs a backup command that is not cached etc.

The SQL text may not be available in the SQL Server database. The investigate the issue, the user can try to research it by expanding the Query Hash > TSQL Batches then look for the SQL Handle in the performance tree.

Then run the following query in SSMS, with the SQL handle in between the parenthesis (replace the example below in italics).

select * from sys.dm_exec_sql_text(0x0200000040f67b0a35329594ae31b3965230240970a5635a0000000000000000000000000000000000000000)

NOTE: It is important to use the SQL Handle and not the SQL Hash. Hashing is a one-way process that is used to identify the batch/query being submitted to the server... you can't get back to the exact original value if you know only its hashed value. 

In addition to try to resolve the SQL handle, the user can also try to expand the command below this hash. it may shed some light as to why this can't be resolved.

The user can also look at the databases, programs, users, client machines, context infos, and sessions (after turning on some of the hidden columns) to have a better idea what the command is running.

For execute statements it is common that text will not be available to display since SQL Server will not show it.  If the execute was activating a procedure then SQL PI show the information about that procedure within a different batch

Attached are some walkthrough scenarios for investigation. Users must be logged into the Supportlink to access the download.

STATUS

Enhancement FOG-8742 was logged by R&D to add a message into PI to indicate that "The text for this specific query isn't available, the query text not found in SQL Server plan cache. This might be because the plan cache is full and has cycled out older plans." This is planned for a future release of the SQL Server cartridge and will be visible in the following scenarios.

• Batch Dimension

  • When hoovering on batch in dimension tree show message on tooltip

  • When clicking on “View Batch Text“ in the text popup show message

• Statement Dimension

  • When hoovering on statement in dimension tree show message on tooltip

  • When clicking on “View SQL Text“ in the text popup show message

Resolution 2

The DBSS agent is unable to resolve the actual query by using sys.dm_exec_sql_text.   

SQL Performance Investigator (PI) works in the following manner:

1. Detect top requests.
2. Investigate these top requests and (only now) find the SQL text related to these requests.

At this step PI may lose some SQL statements for two reasons:

• Text that has already been removed by the SQL Server. Since the SQL statements have already finished execution and SQL Server cannot provide them as they may have been flushed away.
• SQL Server has not yet registered the statement (so there will be some delay before we can understand what the statement is).

In the agent log files there will be error messages such as:
[DBSS_Top_SQLs][YYYY/MM/DD HH:MM:SS]] com.quest.qsi.fason.core.common.utils.SQLTextExecutor - The processor receive an empty string for the SQL text. Details : [ {sql handle='01000500F6CB1A374078A19C0100000000000000000000000000000000000000000000000000000000000000', start offset=0}] SQL hash value. [DEFAULT_INSTANCE instance] . [DBSS_Top_SQLs] collection. Possibly that SQL has been flushed out from the cache.

This indicates that the SQL text has been flushed away by SQL Server between agent collections.

So there is no error or bug on the DBSS agent side in this case. This is just a side effect of 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.

In addition, DBSS agent does collect Top SQLs / Batch execution SQLs every few minutes, based on collection frequency of each collection. This is explained in the manual - Foglight for SQL Server User / Tuning guide - section - Adjusting Collection frequencies

For example for this DB SQL  Server agent, the "Top_SQLs" collection frequency is:

colDisplayName=Top SQLs
colName=DBSS_Top_SQLs
colMaxRows=0
colEnable=1
colTimeout=180  (Query Timeout)                                            
colDescription=Retrieves top SQLs
colFrequencyHigh=300  (Online frequency)
colFrequencyVSQL=120
colFrequencyLow=900  (Offline frequency - Seconds)
colFrequencyFast=120  (Real time frequency)

Which means, the SQL collection cycle runs:

• Real time: if a user enters to the database specific screen where the collection output is being shown, SQL collection runs every 2 minutes (120 seconds)
• Online: if at least one user is logged into FMS console (and may be inactive or viewing any other dashboard), SQL collection frequency is  5 minutes
• Offline: no user currently logged into FMS console, SQL collection frequency is every 15 minutes.

SQL collections happen all the time in the background, whether a user is connected to application or not. These collected SQL statements are stored in the FMS Repository and displayed when a user is viewing a dashboard, for a specific time period.

However, the incidence of "Query Hash" occurs when the SQL statement details are flushed away from the cache by SQL Server engine between two collections of Foglight_DB_SQL_Server agent.  By default, Top SQLs offline collection frequency is 15 minutes. To decrease the incidence of "Query Hash" happening in a given agent dashboard, customer can opt to increase this offline frequency to run, say  every10 minutes or even every 5 minutes. There will be some performance hit on the SQL Server as the DB_SQL_Server agent will execute its own "collecting SQL" that much more often. But it is a trade off between slightly increased performance hit vs. more detailed and complete SQL statements collection for a given time period.

Resolution 3

Please review the Foglight for Databases deployment guide confirm that the Foglight system sizing requirements have been met.

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.