执行一个过程,对一张表的数据进行更新,用到很多临时表。。 执行过程中,观察活动监视器,发现任务状态为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性能方面的资料~~ ------解决方案-------------------- 优化tempdb官方资料: http://msdn.microsoft.com/zh-cn/library/ms175527(v=sql.105).aspx ------解决方案-------------------- 如果没DEADLOCK说明就没死锁。更多的可能是等待。确定是死锁了再跟踪死锁信息。 ------解决方案-------------------- 先把tempdb搞大一点,http://blog.csdn.net/dba_huangzj/article/details/7761193可以看看我翻译的。 (由QQ7V整理发布) |
|