CODEWITHSUNDEEP
sybasesap-ase
Guoping Zhang
Guoping Zhang

Reputation: 131

query ASE DB lock and the duration the lock is held

I am looking for a sql that prints out all DB locks currently are held and also the duration the locks have been held for ASE. I would like to run that sql periodically so that we can monitor the DB health for lock issues.

Upvotes: 0

Views: 1538

Answers (2)

Ben Slade
Ben Slade

Reputation: 508

There's also the system stored proc sp_object_stats. From the docs:

"Shows lock contention, lock wait-time, and deadlock statistics for tables and indexes"

The output for a single object looks like:

    Object Name: pubtune..titles (dbid=7, objid=208003772,lockscheme=Datapages)

  Page Locks     SH_PAGE                UP_PAGE               EX_PAGE$
  ----------    ----------             ----------            ----------
  Grants:         94488                  4052                  4828
  Waits:               532                    500                   776
  Deadlocks:             4                      0                    24
  Wait-time:      20603764 ms           14265708 ms           2831556 ms
  Contention:         0.56%                 10.98%                13.79%

 *** Consider altering pubtune..titles to Datarows locking.

Displays the top 10 objects by default.

Upvotes: 0

markp-fuso
markp-fuso

Reputation: 35106

See the master..monLocks table for a list of granted locks and pending (blocked) lock requests.

The WaitTime column will give you the number of seconds a process has been waiting for a requested lock. You should be able to use the rest of the columns in that table to build the desired query (NOTE: you may need to join with other tables ... depends on what info you're looking for).

Upvotes: 1

Related Questions