先查长事务：
SELECT transaction_id,session_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
再看这些会话运行了多久：
SELECT
es.session_id,
--database_name ='HKERP',
login_name,
er.status,
wait_type,
individual_query = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2),
parent_query = qt.text,
program_name,
host_name,
nt_domain,
start_time
FROM
sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE
es.session_id > 50
AND es.session_Id NOT IN (@@SPID)
ORDER BY
1, 2
看看有没阻塞：
SELECT s.loginame
,[Individual Query] = SUBSTRING (qr.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qr.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)
,qs.session_id ,s.counts AS [进程个数],qs.status ,qs.blocking_session_id
,qs.wait_type ,qs.wait_time ,qs.wait_resource
,qs.transaction_id
FROM SYS.DM_EXEC_REQUESTS qs (nolock)
LEFT JOIN (
SELECT spid,MAX(loginame)AS loginame,COUNT(0)AS counts FROM SYS.SYSPROCESSES (nolock) GROUP BY spid
) s ON qs.session_id=s.spid
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qr
WHERE qs.status = N'suspended'
--and s.loginame<>''
ORDER BY qs.wait_time DESC
--查找阻塞源头v3.0
SELECT SP.spid
,CASE WHEN ST1.text IS NULL THEN ST2.text
ELSE SUBSTRING (ST1.text,SR.statement_start_offset/2,
(
CASE WHEN SR.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), ST1.text)) * 2
ELSE SR.statement_end_offset
END - SR.statement_start_offset)/2
)
END AS [T-sql]
,SP.loginame
,DB_NAME(SP.dbid) AS [db_name]
,SP.open_tran,SP.hostname,SP.program_name,SP.waitresource,SP.*
FROM SYS.SYSPROCESSES SP (nolock)
LEFT JOIN SYS.DM_EXEC_REQUESTS SR (nolock) ON SP.spid=SR.session_id
LEFT JOIN SYS.DM_EXEC_CONNECTIONS SC (nolock) ON SP.spid=SC.session_id
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SC.most_recent_sql_handle) AS ST2
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SR.sql_handle) AS ST1
WHERE SP.spid IN
(
SELECT BLOCKED FROM SYS.SYSPROCESSES (nolock) WHERE BLOCKED<>0 and lastwaittype<>'MISCELLANEOUS'
)
AND SP.BLOCKED=0
杀了阻塞源，或者是长事务，一般就能解决问题了。
同时对比tempdb前后的使用情况。
