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

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

Query Hash message in SQL PI instead of a SQL Statement
Description

When accessing the SQL PI Dashboard, in the column SQL Text, Foglight shows SQL Handles and Query Hashes instead of the SQL statements.

Navigate to the Databases dashboard | SQL PI | SQL PI | Performance Tree | SQL Statements | Click on the tab "Blocking History"|
Cause
Cause 1

*This is the most common occurrence*

1.The screenshot shows some occurrences of Unknown Batch and SQL Handles for Blocking History. There is nothing wrong with PI engine and the mechanism of resolving collected SQLs to the full text of the SQL statement.

Log message from the agent log on Query Completion - stats look like this -

collected tops [214], misses [80],so the miss ratio [37.38317757009346].

This shows that many SQL statements were resolved to the full text of the SQL statement but there are some misses - this implies that SPI engine was not able to complete all needed data for collected statements since they were not found in the collected instance cache.
2. Another reason that some SQL statements text is missing can be some of the T-SQL batches full text may not have been retrieved.

The text is gathered from SQL Server at the batch level. Then the exact SQL Statement text is extracted from that.
- If the batch is not retrieved in whole due to text size limitation, sometimes the text for the statement can not be found and hence the "batch too long" message appears.
- If the batch itself is not identified, then "unknown batch" message appears, which we do see in the screenshot.
We can see that almost all the SQL statements are visible at the Instance View. Most likely the missing SQL statements are coming from a T-SQL batch whose text was not fully retrieved.

Cause 2

This happens when the DBSS agent is unable to resolve the actual query by using sys.dm_exec_sql_text.
Cause 3

Insufficient PI environment sizing may cause the handling of the query cache analysis to be affected.
Resolution
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.
Defect ID

FOG-8742
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.