*This is the most common occurrence*
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.
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.
This happens when the DBSS agent is unable to resolve the actual query by using sys.dm_exec_sql_text.
*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 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:
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:
colTimeout=180 (Query Timeout)
colDescription=Retrieves top SQLs
colFrequencyHigh=300 (Online frequency)
colFrequencyLow=900 (Offline frequency - Seconds)
colFrequencyFast=120 (Real time frequency)
Which means, the SQL collection cycle runs:
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.
Please review the Foglight for Databases deployment guide confirm that the Foglight system sizing requirements have been met. KB article 232137 details common sizing suggestions for a SQL PI environment.
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.