--************************<耗时>***************************************
DBCC DROPCLEANBUFFERS
declare @timediff datetime
select @timediff=getdate()
--some statements
select 耗时=datediff(ms,@timediff,getdate())
--************************<锁定>****************************************
select name,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on
from sys.databases
sp_lock
select * FROM sys.dm_tran_locks where resource_database_id=db_id() and resource_type in ('Object')
select * from sys.dm_exec_sessions where session_id>50
select * from sys.sysprocesses where blocked<>0
select * from sys.sysprocesses where dbid=db_id()
--and hostname=<hostname>
select a.*,b.blocking_session_id
from sys.dm_tran_locks a,sys.dm_os_waiting_tasks b where a.lock_owner_address=b.resource_address
SELECT spid=request_session_id,restype=resource_type,
dbname=DB_NAME(resource_database_id),objid=resource_associated_entity_id,
objname=OBJECT_NAME(case when resource_associated_entity_id<=2147483647 then resource_associated_entity_id else 0 end),
resdescrip=resource_description,
request_mode,request_type,request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
--and request_session_id=<spid>
--and resource_type in ('Object')
order by request_session_id,resource_type
SELECT spid=request_session_id,restype=resource_type,
dbname=DB_NAME(resource_database_id),objid=resource_associated_entity_id,
objname=OBJECT_NAME(case when resource_associated_entity_id<=2147483647 then resource_associated_entity_id else 0 end),
锁数=count(1),request_mode,request_type,request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
--and request_session_id=<spid>
group by request_session_id,resource_type,resource_database_id,
resource_associated_entity_id,request_mode,request_type,request_status
order by request_session_id,resource_type