How can the Blocking History Collection parameters be changed so that the blocks taking less than specified time will appear in the dashboard.
In the SQL Activity | Blocking History users can see all blocks which exceeded a pre-defined period of time (90 seconds by default). If a block exceeds this period, the data is collected and the “Long Lock Running” alarm is raised and is alternative separate to the blocking collected by SQL PI.
When SQL PI is enabled for a database agent, the SQL PI collections are used as the mechanism for collecting Blocking information.
Setting the minimal blocking duration for non-SQL PI blocking collections.
To change value of the block wait time from 90 seconds to 300 seconds, for cartridges with a Global Administration panel, the user can go to the Databases dashboard, select target database, and select Administration | Settings. This will take the user to the Global Administration screen, where you can select "Locks" and edit the value for the blocking history.
This same value can also be adjusted in the Agent Status Properties Blocking History Collection Parameters value
cfgSvc = server.ConfigService LOG = org.apache.log4j.Logger.getLogger('ModifyASP') agent = server.AgentService.findByName('AGENTNAME') asp = cfgSvc.getAgentInstancePrimaryAsp('DB_SQL_Server', 'DB_SQL_Server', agent.id) asp.setValueByString('bhWaitInSeconds', '15') cfgSvc.saveConfig(asp) LOG.info("bhWaitInSeconds changed to 15 on AGENTNAME (DB_SQL_Server)")
The alarm is set in the Long Lock Running rule. The minimal duration, is set in seconds.
By default, this rule is:
#blocked_history_summary#condition = (#blocked_time from $scope.blocked_history_summary# >= registry("DBSS_LongLockRunning_Low"));return Dbwc_Common_SQL_Server_IsNeedToSendAlarm(scope,"DBSS - Long Lock Running","2",condition);
The lock is managed through the #DBSS_LongLockRunning_Low# registry value which is maintained automatically by the system.
Both #DBSS_LongLockRunning_Medium# and #DBSS_LongLockRunning_High# registry values are created by Foglight is needed.