ss_LockChecker is a diagnostic stored procedure used to investigate SQL Server locking, blocking and wait activity that may occur while a SQL-Sales ss_Loader operation is running.
It is intended primarily as a troubleshooting tool. You would normally use it where a ss_Loader operation appears to pause, run unusually slowly, remain blocked, or where you suspect another SQL Server session or transaction may be interfering with the SQL-Sales payload or working tables.
The procedure does not perform a load and does not change the payload data. Instead, it samples SQL Server activity for a configurable period and produces a set of diagnostic result grids.
Why would you use ss_LockChecker?
SQL-Sales ss_Loader activity involves more than just communication with Salesforce. During processing, SQL Server may also be reading from or writing to:
- the ss_Loader payload table
- the associated _Working table
- the SQL-Sales ss_Log table
- the SQL-Sales ss_Log_Working table
ss_LockChecker monitors those tables while also capturing wider SQL Server request, wait and transaction information. This is useful because the session causing a blockage may not itself currently be executing against the payload table – it may simply still be holding a lock from an earlier operation or an open transaction.
The procedure specifically monitors the supplied payload table, its corresponding _Working table, ss_Log and ss_Log_Working.
Typical reasons for using it include:
- a ss_Loader operation appears to pause unexpectedly
- a Loader operation is much slower than expected
- SQL Server reports blocking or lock waits
- a previous SQL session may have left an open transaction
- you want to determine which SQL Server session is holding or waiting for locks against SQL-Sales tables
- SQL-Sales Support has asked for additional SQL Server locking diagnostics
Usage
exec ss_LockChecker
@PayloadSchema = 'dbo'
,@PayloadTable = 'Account_Load'
,@MonitorSeconds = 30
,@SampleEverySeconds = 2The parameters are:
| Parameter | Required | Default | Description |
|---|---|---|---|
@PayloadSchema | No | dbo | Schema containing the Loader payload table |
@PayloadTable | Yes | – | Name of the Loader payload/source table to monitor |
@MonitorSeconds | No | 10 | Total period, in seconds, during which SQL Server activity will be sampled |
@SampleEverySeconds | No | 2 | Number of seconds between each diagnostic sample |
These are the parameters and defaults defined by the procedure.
For example:
exec ss_LockChecker
@PayloadTable = 'Contact_Update'uses the default dbo schema and monitors activity for 10 seconds, sampling every 2 seconds.
For a problem that occurs less predictably, you may want a longer monitoring period:
exec ss_LockChecker
@PayloadSchema = 'dbo'
,@PayloadTable = 'Contact_Update'
,@MonitorSeconds = 60
,@SampleEverySeconds = 2When should I run ss_LockChecker?
The important point is that ss_LockChecker should normally be started before ss_Loader.
Use two SSMS query windows.
Window 1, start ss_LockChecker
For example:
exec ss_LockChecker
@PayloadSchema = 'dbo'
,@PayloadTable = 'Contact_Update'
,@MonitorSeconds = 30
,@SampleEverySeconds = 2Once started, ss_LockChecker displays:
Monitoring started - now run the SQL-Sales operation in the other SSMS window...and begins sampling SQL Server activity.
Window 2, run ss_Loader
Immediately switch to the second query window and start the Loader operation you are investigating.
For example:
EXEC ss_Loader Do not wait for ss_Loader to become blocked before starting ss_LockChecker if the problem is reproducible. Starting the checker first gives it the opportunity to capture the activity leading up to the problem as well as the blockage itself.
This is also important because the SQL-Sales _Working table may not exist when monitoring begins. SQL-Sales can create it after the Loader starts, and it may also be dropped and recreated during processing. ss_LockChecker deliberately refreshes the table object IDs on every sample so that these transient working tables can still be detected.
Choosing the monitoring period
The default monitoring period is only 10 seconds. This is useful for a quick test where the Loader operation reaches the suspected problem immediately.
For troubleshooting an intermittent or slower problem, increase @MonitorSeconds.
For example:
exec ss_LockChecker
@PayloadTable = 'Opportunity_Update'
,@MonitorSeconds = 120
,@SampleEverySeconds = 2would monitor for two minutes.
A 2-second sampling interval is normally appropriate. Reducing the interval produces more samples, while increasing it produces fewer samples over the same monitoring period.
While monitoring is active, the procedure periodically reports that it is still running.
What does ss_LockChecker report?
At the end of the monitoring period, several result sets are returned.
1. Blocking / Waiting Activity Observed
Shows SQL Server requests that were observed waiting or being blocked during the monitoring period, including:
- session ID
- blocking session ID
- wait type
- wait time
- wait resource
- open transaction count
- elapsed and CPU time
- reads and writes
- host, application and login
- current SQL statement
- complete SQL batch
2. Locks Observed on SQL-Sales / Payload Tables
Shows locks observed specifically against:
- the payload table
- the payload _Working table
- ss_Log
- ss_Log_Working
It includes the session, lock mode, lock status and SQL Server connection information.
3. Lock Summary
Summarises the locks captured during the test and shows how many samples each lock was observed in, together with the first and last time it was seen.
Locks whose status is not GRANT are prioritised in the output.
4. Current Sessions Holding / Waiting for Target Table Locks
Shows sessions that still hold or are waiting for locks when the diagnostic completes.
This can be particularly useful for identifying a sleeping SQL Server session which still has an open transaction.
5. Open Transactions
Lists open transactions in the current database, including:
- session
- host and application
- login
- transaction start time
- age of the transaction
- transaction state
- transaction log usage
- most recent SQL
A long-running or abandoned open transaction is a common source of SQL Server blocking.
6. Cumulative Lock / Latch Statistics
Shows SQL Server row, page and latch statistics for the monitored tables.
These counters are cumulative SQL Server statistics and are not restricted to the current ss_LockChecker monitoring period. They are therefore useful as supporting evidence rather than proof that a particular wait occurred during the current Loader run.
7. Longest SQL Requests Captured
Shows the longest-running sample captured for each SQL Server session during the monitoring period, together with CPU, reads, writes, waits and SQL text.
8. Final Target Table Status
Shows whether the payload, working and SQL-Sales logging tables are still present when monitoring finishes.
This is useful because _Working tables can be temporary and may have been created or removed while the Loader was running.
Sending diagnostic results to SQL-Sales Support
If ss_LockChecker is being used for a support investigation, allow the procedure to complete and retain all Results grids together with the SSMS Messages tab.
The procedure explicitly finishes with:
SQL-SALES DIAGNOSTIC COMPLETEand requests that all result grids and the Messages tab are supplied.
In summary
ss_LockChecker is not normally required during everyday SQL-Sales operation. It is a focused diagnostic utility for investigating SQL Server-side locking, blocking, waits and open transactions around an ss_Loader operation.
The normal troubleshooting sequence is therefore:
1. Open two SSMS query windows.
2. In Window 1:
start ss_LockChecker.
3. As soon as monitoring starts, in Window 2:
run the ss_Loader operation being investigated.
4. Allow ss_LockChecker to complete.
5. Review or provide all returned Results grids
and the Messages output.
The most important practical rule is simply: start ss_LockChecker first, then run ss_Loader while the checker is monitoring.
