事务快照,SQL企业版时:
SELECT name,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on FROM sys.databases

方案一:
ALTER DATABASE 数据库名 SET Allow_snapshot_isolation ON
ALTER DATABASE 数据库名 SET READ_COMMITTED_SNAPSHOT ON
GO

ALTER DATABASE 数据库名 SET Allow_snapshot_isolation ON WITH ROLLBACK IMMEDIATE
ALTER DATABASE 数据库名 SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

TEMPDB优化
--1 确定 tempdb 数据库的逻辑文件名称以及在磁盘上的当前位置。
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');
GO
--2使用 ALTER DATABASE 更改每个文件的位置
USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'f:\tempdb\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'f:\tempdb\templog.ldf');
GO
--3重新启动 SQL Server。
--4验证文件更改。
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

资源调试观察
SELECT session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';
GO

select er.session_id, CAST(csql.text AS varchar(255)) AS CallingSQL
from master.sys.dm_exec_requests er WITH (NOLOCK)
CROSS APPLY fn_get_sql (er.sql_handle) csql
where er.session_id =309

select * from master..sysprocesses where blocked<>0