将Isolate级别设为Read Uncommitted的两种方式 （无锁读，但允许读取脏数据）

--giving it the NOLOCK hint.
SELECT *
FROM Production.Product WITH (NOLOCK)
WHERE ProductID = 1
...


--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *
FROM Production.Product
WHERE ProductID = 1
...

查询系统表

SELECT * FROM sys.tables
SELECT * FROM INFORMATION_SCHEMA.TABLES

查询计划

SELECT cp.plan_handle, qp.query_plan, st.[text]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE qp.[dbid] = DB_ID('AdventureWorks2014')

当前阻塞的会话

SELECT *
FROM sys.dm_tran_locks tl
JOIN sys.dm_os_waiting_tasks AS owt ON tl.lock_owner_address =
owt.resource_address

当前阻塞的链接

SELECT *
FROM sys.dm_tran_locks tl
JOIN sys.dm_os_waiting_tasks AS owt ON tl.lock_owner_address =
owt.resource_address
JOIN sys.dm_exec_connections ec_waiting ON ec_waiting.session_id =
tl.request_session_id
JOIN sys.dm_exec_connections ec_blocking ON ec_blocking.session_id =
owt.blocking_session_id

当前阻塞的查询

SELECT tl.request_session_id
,owt.blocking_session_id
,tl.resource_type
,waiting_text.[text] AS WaitingText
,blocking_text.[text] AS BlockingTest
,OBJECT_NAME(p.object_id) AS ObjectName
FROM sys.dm_tran_locks tl
JOIN sys.dm_os_waiting_tasks AS owt ON tl.lock_owner_address =
owt.resource_address
JOIN sys.dm_exec_connections ec_waiting ON ec_waiting.session_id =
tl.request_session_id
JOIN sys.dm_exec_connections ec_blocking ON ec_blocking.session_id =
owt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec_waiting.most_recent_sql_handle) AS
waiting_text
CROSS APPLY sys.dm_exec_sql_text(ec_blocking.most_recent_sql_handle) AS
blocking_text
JOIN sys.partitions p ON p.hobt_id = tl.resource_associated_entity_id

强行关闭会话

EXEC sp_who
EXEC sp_who2
KILL 54