[MSSQL] มาปรับปรุง Query การตรวจสอบเรื่อง Lock ให้ดีขึ้น

จาก Blog ตอนที่แล้ว ผมได้เขียนไปเกี่ยวกับ [MSSQL] ตรวจสอบ Table ว่าถูก Lock หรือใช้งานโดย Application หรือไม่ ? จาก Query เดิม เรารู้แค่ว่า Table ไหน Lock เท่านั้น คราวนี้มาลองเพิ่มข้อมูลที่จำเป็น เพื่อให้ Dev หรือ DBA มาวิเคราะห์กันได้ง่ายขึ้นครีบ โดยข้อมูลที่มเพิ่มใน Query ได้แก่

• ชื่อ Database
• SQL ที่มีปัญหา
• รูปแบบการ Lock
• เครื่องไหนที่ทำ DB Lock และใช้ Application อะไร
• ใช้ User อะไรเข้ามาทำรายการ และเข้า DB มาตอนเวลาไหน

Let' s Go - ลองมาปรับ Query กัน

SELECT OBJECT_NAME(P.OBJECT_ID) AS TABLENAME
     , RESOURCE_TYPE
     , RESOURCE_DESCRIPTION
FROM SYS.DM_TRAN_LOCKS L
  JOIN SYS.PARTITIONS P 
    ON L.RESOURCE_ASSOCIATED_ENTITY_ID = P.HOBT_ID

จากตัว Query เดิม เราพบว่าข้อมูลหลายๆส่วน ในตัว DMV ของ MS SQL Server ไม่มีให้นะครับ ต้องเอา DMV ตัวอื่นๆ เข้ามาช่วยครับ ดังนี้

• SYS.DM_EXEC_SESSIONS
• SYS.DM_EXEC_CONNECTIONS
• SYS.DM_TRAN_SESSION_TRANSACTIONS
• SYS.DM_TRAN_ACTIVE_TRANSACTIONS
• SYS.DM_EXEC_SQL_TEXT

หลังจากเอา DMV ต่างๆ เข้ามาปรับปรุง โดย Query Version ใหม่ ดังนี้ครับ

SELECT CASE TL.REQUEST_SESSION_ID
		WHEN -2 THEN 'ORPHANED DISTRIBUTED TRANSACTION'
		WHEN -3 THEN 'DEFERRED RECOVERY TRANSACTION'
		ELSE TL.REQUEST_SESSION_ID END		AS REQUESTSPID					--REQUEST ID
     , TL.RESOURCE_DATABASE_ID
	 , ES.HOST_NAME															--เครื่องไหนที่ทำ DB Lock 
	 , ES.LOGIN_NAME														--เข้า DB โดยใช้ User อะไร
	 , ES.LOGIN_TIME														--เข้ามาเวลาไหน
     , DB_NAME(TL.RESOURCE_DATABASE_ID)		AS DBNAME						--ชื่อ Database
	 , ES.PROGRAM_NAME														--Application อะไร ที่มีปัญหา เช่น โปรแกรม Microsoft SQL Server Management Studio - Query
	 , ES.CLIENT_INTERFACE_NAME												--เชื่อมผ่านอะไร เช่น ODBC, .NET CLIENT
     , OBJECT_NAME(P.OBJECT_ID)				AS LOCKTABLENAME				--Table ที่ถูก LOCK
	 , TL.REQUEST_TYPE
	 , TL.REQUEST_MODE						AS LOCKTYPE						--รูปแบบการ LOCK
	 , ST.TEXT								AS LOCKSQLCOMMAND				--SQL ที่ทำให้ DB LOCK
	 , CASE TST.IS_USER_TRANSACTION
		WHEN 0 THEN 'SYSTEM TRANSACTION'
		WHEN 1 THEN 'USER TRANSACTION' END	AS USER_OR_SYSTEM_TRANSACTION	--TX ของ USER (ระบบต่างๆ) หรือ SYSTEM
	 , AT.NAME								AS TRANSACTIONNAME
	 , TL.RESOURCE_TYPE
	 , TL.RESOURCE_DESCRIPTION
FROM SYS.DM_TRAN_LOCKS TL
	INNER JOIN SYS.PARTITIONS P 
		ON TL.RESOURCE_ASSOCIATED_ENTITY_ID = P.HOBT_ID
	INNER JOIN SYS.DM_EXEC_SESSIONS ES
		ON TL.REQUEST_SESSION_ID = ES.SESSION_ID
	INNER JOIN SYS.DM_EXEC_CONNECTIONS EC 
		ON EC.SESSION_ID = ES.SESSION_ID
	INNER JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS TST 
		ON ES.SESSION_ID = TST.SESSION_ID
	INNER JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS AT 
		ON TST.TRANSACTION_ID = AT.TRANSACTION_ID
	CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EC.MOST_RECENT_SQL_HANDLE) AS ST
WHERE TL.RESOURCE_DATABASE_ID = DB_ID()

มาดูผลลัพธ์กันเลย มีหลาย Column เลย เนื่องจาก SELECT มาตาม Requirement ที่กำหนดไว้นะครับ สำหรับใครอยากรู้ว่า Column แค่ละตัว มันตรงกับ Requirement ไหน ลองดูจาก Comment ประกอบได้ครับ

NOTE: Tools ของ SQL Server ที่ช่วยในการดู Lock นะ เข่น SQL Server Profiler หรือ Activity Monitor เป็นต้น แต่อันนี้เป็นอีกแนวทางหนึ่งครับ เผื่อกรณีที่ว่าไม่สามารถใช้ Tools ได้ครับ