Blog Post

usp_SQLServerCarpenter_Tools_Get_Objects_Performing_Committed_Read

,

/*
Author:Brahmanand Shukla (SQLServerCarpenter.com)
Date:27-May-2022
Purpose:To get all the objects performing committed reads. 
This proceduere will fetch all the objects (Procedures, Functions, Triggers, View etc.) 
that doesn't uses the NOLOCK and SET ISOLATION LEVEL READ UNCOMMITTED.
This procedure may be helpful in performance tuning assignments and to resolve / minimize the frequent blockage and deadlock issues.
Example: 
EXEC usp_SQLServerCarpenter_Tools_Get_Objects_Performing_Committed_Read
*/CREATE OR ALTER PROCEDURE usp_SQLServerCarpenter_Tools_Get_Objects_Performing_Committed_Read
AS
BEGIN
SET NOCOUNT ON;
; WITH cte_objects
AS
(
SELECT 'Stored Procedure' AS ObjectType
 , OBJECT_NAME(OBJECT_ID) AS ObjectName
, PATINDEX('%NOLOCK%', OBJECT_DEFINITION(OBJECT_ID)) AS NoLock_Pat_Index
, PATINDEX('%ISOLATION%LEVEL%READ%UNCOMMITTED%', OBJECT_DEFINITION(OBJECT_ID)) AS Isolation_Pat_Index
FROM sys.procedures
UNION ALL
SELECT 'View' AS ObjectType
, OBJECT_NAME(OBJECT_ID) AS ObjectName
, PATINDEX('%NOLOCK%', OBJECT_DEFINITION(OBJECT_ID)) AS NoLock_Pat_Index
, PATINDEX('%ISOLATION%LEVEL%READ%UNCOMMITTED%', OBJECT_DEFINITION(OBJECT_ID)) AS Isolation_Pat_Index
FROM sys.views
UNION ALL
SELECT 'Trigger' AS ObjectType
, OBJECT_NAME(OBJECT_ID) AS ObjectName
, PATINDEX('%NOLOCK%', OBJECT_DEFINITION(OBJECT_ID)) AS NoLock_Pat_Index
, PATINDEX('%ISOLATION%LEVEL%READ%UNCOMMITTED%', OBJECT_DEFINITION(OBJECT_ID)) AS Isolation_Pat_Index
FROM sys.triggers
UNION ALL
SELECT 'Function' AS ObjectType
, OBJECT_NAME(OBJECT_ID) AS ObjectName
, PATINDEX('%NOLOCK%', OBJECT_DEFINITION(OBJECT_ID)) AS NoLock_Pat_Index
, PATINDEX('%ISOLATION%LEVEL%READ%UNCOMMITTED%', OBJECT_DEFINITION(OBJECT_ID)) AS Isolation_Pat_Index
FROM sys.objects
WHERE type in ('FN', 'IF', 'FN', 'AF', 'FS', 'FT')
)
SELECT ObjectType, ObjectName
FROM cte_objects
WHERE (NoLock_Pat_Index <> 0 OR Isolation_Pat_Index <> 0) 
END

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate