1

I am trying to understand if the result I am seeing is normal for a long running process or an indication of a problem.

When I run select * from sys.dm_tran_locks

I am getting over 60k rows showing all from different session ids.

  • resource_type = PAGE
  • resource_description = same value

  • resource_associated_entity_id = same value

  • request_mode = S

  • request_status = GRANT

I'm trying to understand if this means that the query currently running needs tuning or if it is normal to see this much rows showing different 'request_session_id' with a shared lock on the same object?

0

You said, there are different 'request_session_id' showing up, so there are many sessions, not one, to read the same table. Shared locks are compatible with other shared locks so there is no problem at all. If you are warring about the memory comsumed by locks, every lock takes about 96 bytes thes is quite small and server can decide to escalate to table lock when:

  • A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
  • A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET
    LOCK_ESCALATION option is set to AUTO.
  • The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.

More about lock escalation here: Lock Escalation (Database Engine)

Your situation is normal, no lock escalation takes place but just many sessions are reading the same table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.