1

I'm trying to understand why this Performance counter shows it is 0.

   "Mssql$instance1:locks(_total)\lock wait time (ms)"

I tried with wild card to check all instance_name and they're all 0.

   "Mssql$instance1:locks(*)\lock wait time (ms)"

But if I run the query

SELECT cntr_value 
FROM master..sysperfinfo 
WHERE (object_name LIKE '%:Locks%'  
  AND counter_name = 'Lock Wait Time (ms)'  
  AND instance_name = '_Total')

shows a different value (which is a count of "Object", "Key", and "Metadata")

1 Answer 1

2

You should use the sys.dm_os_performance_counters DMV instead of the sysperfinfo backward compatibility view. That will report the same value, though.

The DMV counter value is cumulative since the SQL instance was started. Counter values reported by Performance Monitor for this counter are per second so you'll see non-zero values only when the total lock wait time is 1 ms or greater over the last second.

To verify the counters are working as expected run the scripts below to create a blocking lock for the duration of a transaction:

--Run this script in SSMS query window #1
USE tempdb;
CREATE TABLE dbo.LockTest(
    Col1 int NOT NULL IDENTITY CONSTRAINT PK_LockTest PRIMARY KEY
    );
BEGIN TRAN;
INSERT INTO dbo.LockTest DEFAULT VALUES;
WAITFOR DELAY '00:00:10';
COMMIT;
GO
DROP TABLE dbo.LockTest;
GO

--Run this in SSMS query window #2 while the first script is running
SELECT * FROM dbo.LockTest; 

PMON will show a non-zero value for both the 'KEY' and '_Total' instances of Lock Wait Time(ms) after running these scripts.

2
  • @SQL_, if you only see zero counters, that suggests you don't have locks with significant duration. I added scripts to my answer you can use to test.
    – Dan Guzman
    Commented Jun 17, 2017 at 16:59
  • Thank you, I understand it now and yes it showed a different value when I tested the script.
    – SQL_
    Commented Jun 17, 2017 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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