过程死锁，貌似跟tempdb有关

[shawN_h]

执行一个过程，对一张表的数据进行更新，用到很多临时表。。
执行过程中，观察活动监视器，发现任务状态为running的只有如下这段代码：

SET NOCOUNT ON;

DECLARE @previous_collection_time datetime;
DECLARE @previous_request_count bigint;
DECLARE @current_collection_time datetime;
DECLARE @current_request_count bigint;
DECLARE @batch_requests_per_sec bigint;
DECLARE @interval_sec bigint;

-- Get the previous snapshot's time and batch request count
SELECT TOP 1 @previous_collection_time = collection_time, @previous_request_count = request_count
FROM #am_request_count
ORDER BY collection_time DESC;

-- Get the current total time and batch request count
SET @current_collection_time = GETDATE();
SELECT @current_request_count = cntr_value
FROM sys.sysperfinfo
WHERE counter_name = 'Batch Requests/sec' COLLATE Latin1_General_BIN;

SET @interval_sec =
-- Avoid divide-by-zero
CASE
WHEN DATEDIFF (second, @previous_collection_time, @current_collection_time) = 0 THEN 1
ELSE DATEDIFF (second, @previous_collection_time, @current_collection_time)
END;

-- Calc the Batch Requests/sec rate for the just-completed time interval.
SET @batch_requests_per_sec = (@current_request_count - @previous_request_count) / @interval_sec;

-- Save off current batch count
INSERT INTO #am_request_count (collection_time, request_count)
VALUES (@current_collection_time, @current_request_count);

-- Return the batch requests/sec rate for the just-completed time interval.
SELECT ISNULL (@batch_requests_per_sec, 0) AS batch_requests_per_sec;

-- Get rid of all but the most recent snapshot's data
DELETE FROM #am_request_count WHERE collection_time < @current_collection_time;

以上这段代码是干嘛用的？？
我真正要执行的代码，一直在suspended，一点都没执行。。。

在维护几个中心端数据库的时候，发现性能的瓶颈总是发生在tempdb，运行一段时间之后，总得通过重启服务，或重建tempdb来进行优化。
鉴于系统有点大，而且基本成型，去修改那些过程工作量略大，求大神推荐一些优化tempdb性能方面的资料~~

[Mr_Nice]

优化tempdb官方资料： http://msdn.microsoft.com/zh-cn/library/ms175527(v=sql.105).aspx

[SQL777]

如果没DEADLOCK说明就没死锁。更多的可能是等待。确定是死锁了再跟踪死锁信息。

[shawN_h]

等待类型有sleep_task 和 CXPACKET两种。。。

[發糞塗牆]

先把tempdb搞大一点，http://blog.csdn.net/dba_huangzj/article/details/7761193可以看看我翻译的。

[以学习为目的]

查下资料吧，楼上推荐的都研究下