SQL PI captures the Top 100 locks and low priority locks do not get captured.
Questions for troubleshooting:
- What is under Activity | Block (Current) based on the instance in question?
- What is under SQL PI | Locks | Overview tab based on the time and date of concern?
- What is under under SQL PI | Locks | Resource Break down graph on the right hand side? Also, check the "Lock related Metrics" based on the Total column for the one with the highest locks.
- What is under SQL PI | Locks | Blocking History tab? The Time Pyramid applies to the Blocking History based on KB 205309.
Blocking may be collecting meaningful locks (long locks) only.
For example, if Foglight captures "shared locks". This indicates that a select is locking the query preventing a DDL or DML statement from running.
Shared locks seem to be low priority:
https://www.sqlskills.com/blogs/paul/low-priority-locking-wait-types/
These are good Knowledge Base articles for a customer to reference from our quest.com site.
KB 141568
"Historical Lock Analysis" has been split with locked object added to the performance tree as a dimension, and blocking history added as a tab in the 'Instance Level'
for SQL PI.
Lock
The Lock panel displays the database’s lock related wait events.
Default metrics displayed:
- Lock Update
- Lock Shared
- Average Lock Duration
- Lock Wait
- Lock Intent
- Lock Timeouts
- Lock Exclusive
- Deadlocks
- Lock Schema
- Lock Bulk Update
Blocking History
The Lock Analysis displays all locks that took place within the selected time range.
This pane comprises the following sections:
- Locks Activity — a chart that displays all of the lock wait events, plotted over time. This chart can be viewed in either of the following views:
- Baseline — displaying only lock wait events
- Breakdown — displaying both lock and non-lock wait events
- Blocks exceeding a predefined threshold — displays blocks that reached or exceeded the minimal duration, in seconds, which a block should reach or exceed in order to be collected. By selecting a row from the table a pop-up with a lock map of this specific time will be presented.
KB 114619
The Locks view allows setting parameters for displaying data in the SQL Server's Locks panel, under the SQL Activity drilldown, as well as configuring the minimal duration, in seconds, which a block should reach or exceed in order to be collected and displayed in the Blocking (History) panel.
KB 205309
The display of historical data collected by PI (Performance Investigator) is controlled by the time pyramid for History
One minute resolution is only kept for six hours.
If users are trying to look at data that is older than six hours the best resolution will be of 15 minutes (and that will only be available for three days after the event).
Please, provide Foglight support bundle, FGLAM support bundle for the DB agents, FGLAM support bundle with the SQL PI repository to Support if further investigation is needed.