My SQL Toolkit

--************************<耗时>***************************************

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