Which means, the SQL collection cycle runs:
#Backup exec 16 schedule and occurrences manual
This is explained in the manual - Foglight for SQL Server User / Tuning guide - section - Adjusting Collection frequenciesįor example for this DB SQL Server agent, the "Top_SQLs" collection frequency is:ĬolFrequencyLow=900 (Offline frequency - Seconds)ĬolFrequencyFast=120 (Real time frequency) In addition, DBSS agent does collect Top SQLs / Batch execution SQLs every few minutes, based on collection frequency of each collection. 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. So there is no error or bug on the DBSS agent side in this case. This indicates that the SQL text has been flushed away by SQL Server between agent collections. Possibly that SQL has been flushed out from the cache. ]. - The processor receive an empty string for the SQL text. In the agent log files there will be error messages such as: SQL Server has not yet registered the statement (so there will be some delay before we can understand what the statement is).Since the SQL statements have already finished execution and SQL Server cannot provide them as they may have been flushed away. Text that has already been removed by the SQL Server.Investigate these top requests and (only now) find the SQL text related to these requests.Īt this step PI may lose some SQL statements for two reasons:.SQL Performance Investigator (PI) works in the following manner: The DBSS agent is unable to resolve the actual query by using sys.dm_exec_sql_text.
#Backup exec 16 schedule and occurrences full
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.
0 kommentar(er)
