Detecting Lock Escalation

To detect lock escalation in SQL Server, you can use SQL Profiler or SQL Trace to capture Lock:Escalation event. But this blog post wanna shows you another way, by comparing the 'Table Lock Escalations/sec' counter value in sys.dm_os_performance_counters DMV, before and after your query execution. Demo as below:
SET NOCOUNT ON;
-- create testing table and add 10,000 rows
IF EXISTS (SELECT 1 FROM sys.objects WHERE [type] = 'U' AND name = 'T_Escalation') DROP TABLE T_Escalation;
GO
CREATE TABLE T_Escalation (pk int PRIMARY KEY, col varchar(50));
GO
DECLARE @i int = 0;
WHILE @i < 10000
BEGIN
    INSERT T_Escalation (pk, col) VALUES (@i, 'aaa');
    SET @i+=1;
END
GO

DECLARE @v1 int, @v2 int;

-- get "lock escalation" counter before statement execution
SET @v1 = (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Table Lock Escalations/sec');
UPDATE T_Escalation SET col = 'XXX' WHERE pk <= 1000;
SELECT @@ROWCOUNT AS [updated rows];
-- get it again after statement executed
SET @v2 = (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Table Lock Escalations/sec');
SELECT @v2 - @v1 AS [Table Lock Escalation];

-- get "lock escalation" counter before statement execution
SET @v1 = (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Table Lock Escalations/sec');
UPDATE T_Escalation SET col = 'XXX' WHERE pk <= 7000;
SELECT @@ROWCOUNT AS [updated rows];
-- get it again after statement executed
SET @v2 = (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Table Lock Escalations/sec');
SELECT @v2 - @v1 AS [Table Lock Escalation];