SQL SERVER – Session Counts by Database Are Not Lock Row Counts

Total Sessions and total lock rows answer different questions. My original query counted lock rows under a session label.

-- Sessions grouped by their current database context:
SELECT DB_NAME(database_id) AS DatabaseName,COUNT_BIG(*) AS UserSessions
FROM sys.dm_exec_sessions WHERE is_user_process=1
GROUP BY database_id ORDER BY DatabaseName;
-- Distinct sessions with locks in a database, a different question:
SELECT DB_NAME(resource_database_id) AS DatabaseName,
       COUNT(DISTINCT request_session_id) AS SessionsWithLocks
FROM sys.dm_tran_locks WHERE request_session_id>0
GROUP BY resource_database_id ORDER BY DatabaseName;

One session can own many locks. The first replacement counts user sessions by current database context, including visible idle sessions. The second counts distinct positive session IDs owning locks in each database.

Sessions can change context or access several databases. Activity also changes between snapshots. The lock-owner count excludes orphaned distributed transactions and other non-session identifiers.

Choose connection context, active requests or lock ownership according to the question. Use the required DMV permissions. The consulting and video resources provide related context.

Related reading

• Comprehensive Database Performance Health Check
• Consulting 101 – Why Do I Never Take Control of Computers Remotely?
• Consulting 102 – Why Do I Give 100% Guarantee of My Services?
• Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?
• Consulting 104 – Why Do I Give All of the Performance-Tuning Scripts to My Customers?
• Consulting 105 – Why Don’t I Want My Customers to Return Because of the Same Problem?
• Consulting Wrap Up – What Next and How to Get Started
• SQL in Sixty Seconds series
• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

A lock count is not a session count, it is a different measure of current resource ownership.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.