SQL Server Average Lock Wait time Historical data

by zapcon   Last Updated June 19, 2017 11:06 AM

Monitoring software has indicated average lock wait times rising to 500ms during the night.

Is there any way of confirming which locks these were as we currently do not log any performance data to a table so would be relying just on DMV's.

Will be looking at writing performance data to a table for future reference.



Answers 1


@zapcon, there is DMV master.sys.dm_exec_requests;

USE Master
GO
SELECT * 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

The master.sys.dm_exec_requests; DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

as well as Stored procedure such as sp_who2, & sp_who; through you can identify the blocked spid.

USE Master;
GO
EXEC sp_who2;
GO

The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated users, application, database, CPU time, etc. The information returned can be filtered to return only the active processes by using the ‘active' parameter.

For your further Ref How to identify blocking in SQL Server

Md Haidar Ali Khan
Md Haidar Ali Khan
June 19, 2017 10:25 AM

Related Questions




Optimizing key lookup in subquery with UNION ALL

Updated May 13, 2015 17:02 PM

T-SQL performance tuning - Query running slower

Updated August 12, 2017 17:06 PM