一个奇怪的SQL超时

[风声3]

平台每天都对每用户有一次计算任务，一年来都正常运行，这几天出现了奇怪的问题：
1、对个别用户某天进行计算时提示数据库计算超时（30秒），检查发现当时服务器内存耗尽（从1.6G飙升到3.8G/共4G内存）；
2、其它用户，或者该用户其它日期计算正常（2秒内，服务器内存没多大变化）；
3、将库（MSSQL2008R2）拷贝到测试机测试（直接8G内存LOCALDB2014打开/直接附加到2G内存MSSQL2016）没有超时情况，一切正常；
生产机上没有调试环境，测试机上又重现不出问题。请问该怎么去分析问题？MSSQL2008R2有没有分析查找这个超时问题的功能？

[lich2005]

试试跟踪一下SQL语句，如果知道某一个特定的用户和时间，最好用这个用户账号和日期作为筛选条件检查一下相应的数据，超时一般都是数据量比较大或者数据内容出现异常造成的（比如运算出现死循环等）。

[丰云]

多半是资源冲突导致的，数据库执行这个任务时，机器上还有什么其他任务在执行？
应用有没有做并行处理的优化？？

[吉普赛的歌]

内存并不是关键， 数据量大了分析复杂，占内存是必须的。关键是CPU还有IO。
你直接在那台机执行对应的SQL ， 需要多少秒？
把慢SQL贴出来， 让大家帮你看看。

[风声3]

1、第一时间我也怀疑数据问题，但同样的数据（100%库复制过去）在另外的机器上完全没有问题，目测数据也没有什么异常。
2、资源冲突可能性也很小，同样的计算任务，甚至数据量大10倍的其它用户同一日期，可以正常运行。
3、目标机器上我不能调试，不知道是哪个语句慢，换在测试机上又不慢。
我不会什么分析查询工具，目标机器上的MSSQL2008R2上有工具能找到超时的语句吗？

[qq_25073223]

会不会是 统计信息出错了啊！
或者说 索引要重整一下啊！

[OwenZeng_DBA]

把语句抓出来，分析语句的执行计划，如果内存飙高那么逻辑读也肯定会很高，

[吉普赛的歌]

用 sqlprofiler 跟踪一下， 在操作之前开启， 操作之后停止， 这样就清楚了

[风声3]

这是一个业务系统，生产环境也没有什么并发，比较耗时的操作都做了并发限制
customer_id 换成其它的运行正常，甚至数据量大10倍
waybill表数据多一点，大约800W，其它表1W左右

按这个执行没有问题，正常

BEGIN TRAN
BEGIN TRY
    --表变量的主键，字段类型你按实际的来修改
    DECLARE @t TABLE (
        w主键 INT PRIMARY KEY,
        price2 DECIMAL(18,4),
        price2_time BIGINT
    )
    INSERT INTO @t(w主键,price2,price2_time)
    SELECT W.主键,    --自己改
       CASE 
            WHEN W.is_ew = 0 THEN E.price
            ELSE E.price -2.0000
       END AS price2,
       CASE 
            WHEN W.price2_time = 0 THEN -1536366010
            ELSE W.price2_time
       END AS price2_time
    FROM   [waybill] W WITH (XLOCK,ROWLOCK) 
           INNER JOIN [area] A WITH (UPDLOCK,ROWLOCK) 
                ON  W.customer_id = 331
                AND W.daybill_id = 591
                AND W.destination = A.destination
                AND W.real_weight > 0
                AND W.real_weight <= 1000
           INNER JOIN [price_p] P WITH (UPDLOCK,ROWLOCK) 
                ON  P.price_id = 317
                AND A.province_id = P.province_id
           INNER JOIN [price_w] E WITH (UPDLOCK,ROWLOCK) 
                ON  E.price_id = 317
                AND E.[catalog] = P.[catalog]
                AND E.[weight] = 1000
                AND E.price > 0
    UPDATE W 
    SET price2 = t.price2,price2_time = t.price2_time
    FROM  [waybill] W INNER JOIN @t t ON w.主键=t.w主键
    COMMIT TRAN;
END TRY
BEGIN CATCH
    DECLARE @errMsg NVARCHAR(MAX)
    SET @errMsg=ERROR_MESSAGE()
    RAISERROR(16,1,@errMsg)
    ROLLBACK TRAN;
END CATCH 

但是去掉临时表，就不行了

BEGIN TRAN
BEGIN TRY
    UPDATE W
       SET    W.price2 = CASE 
                       WHEN W.is_ew = 0 THEN E.price
                       ELSE E.price -2.0000
                  END,
       W.price2_time = CASE 
                            WHEN W.price2_time = 0 THEN -1536366010
                            ELSE W.price2_time
                       END
    FROM   [waybill] W WITH (XLOCK,ROWLOCK) 
           INNER JOIN [area] A WITH (UPDLOCK,ROWLOCK) 
                ON  W.customer_id = 331
                AND W.daybill_id = 591
                AND W.destination = A.destination
                AND W.real_weight > 0
                AND W.real_weight <= 1000
           INNER JOIN [price_p] P WITH (UPDLOCK,ROWLOCK) 
                ON  P.price_id = 317
                AND A.province_id = P.province_id
           INNER JOIN [price_w] E WITH (UPDLOCK,ROWLOCK) 
                ON  E.price_id = 317
                AND E.[catalog] = P.[catalog]
                AND E.[weight] = 1000
                AND E.price > 0
    COMMIT TRAN;
END TRY
BEGIN CATCH
    DECLARE @errMsg NVARCHAR(MAX)
    SET @errMsg=ERROR_MESSAGE()
    RAISERROR(16,1,@errMsg)
    ROLLBACK TRAN;
END CATCH 

[风声3]

通过 SqlProfiler 跟踪，发现这个语句直接超时：

UPDATE W SET W.price2=CASE WHEN W.is_ew=0 THEN E.price ELSE E.price-2.0000 END,W.price2_time=CASE WHEN W.price2_time=0 THEN -1536366010 ELSE W.price2_time END FROM [waybill] W 
INNER JOIN [area] A ON W.customer_id=331 AND W.daybill_id=591 AND W.destination=A.destination AND W.real_weight>0 AND W.real_weight<=1000 
INNER JOIN [price_p] P ON P.price_id=317 AND A.province_id=P.province_id 
INNER JOIN [price_w] E ON E.price_id=317 AND E.[catalog]=P.[catalog] AND E.[weight]=1000 AND E.price>0

修改为对应的SELECT语句

UPDATE W SET W.price2=CASE WHEN W.is_ew=0 THEN E.price ELSE E.price-2.0000 END,W.price2_time=CASE WHEN W.price2_time=0 THEN -1536366010 ELSE W.price2_time END FROM [waybill] W 
INNER JOIN [area] A ON W.customer_id=331 AND W.daybill_id=591 AND W.destination=A.destination AND W.real_weight>0 AND W.real_weight<=1000 
INNER JOIN [price_p] P ON P.price_id=317 AND A.province_id=P.province_id 
INNER JOIN [price_w] E ON E.price_id=317 AND E.[catalog]=P.[catalog] AND E.[weight]=1000 AND E.price>0

[风声3]

通过 SqlProfiler 跟踪，发现这个语句直接超时：

UPDATE W SET W.price2=CASE WHEN W.is_ew=0 THEN E.price ELSE E.price-2.0000 END,W.price2_time=CASE WHEN W.price2_time=0 THEN -1536366010 ELSE W.price2_time END FROM [waybill] W 
INNER JOIN [area] A ON W.customer_id=331 AND W.daybill_id=591 AND W.destination=A.destination AND W.real_weight>0 AND W.real_weight<=1000 
INNER JOIN [price_p] P ON P.price_id=317 AND A.province_id=P.province_id 
INNER JOIN [price_w] E ON E.price_id=317 AND E.[catalog]=P.[catalog] AND E.[weight]=1000 AND E.price>0

修改为对应的SELECT语句100毫秒就执行好了

SELECT CASE WHEN W.is_ew=0 THEN E.price ELSE E.price-2.0000 END,CASE WHEN W.price2_time=0 THEN -1536366010 ELSE W.price2_time END FROM [waybill] W 
INNER JOIN [area] A ON W.customer_id=331 AND W.daybill_id=591 AND W.destination=A.destination AND W.real_weight>0 AND W.real_weight<=1000 
INNER JOIN [price_p] P ON P.price_id=317 AND A.province_id=P.province_id 
INNER JOIN [price_w] E ON E.price_id=317 AND E.[catalog]=P.[catalog] AND E.[weight]=1000 AND E.price>0

[吉普赛的歌]

你还没搞明白，并不一定是完全相同操作的那种〃并发〃才有影响。
你一次更新，牵扯到4个表，影响很大
其他人或其他任务(包括定时作业等)对这4个表的任何的一个增删改查操作都可能对你的update操作有影响。

这才是生产环境与测试环境的区别。

能解决就好吧，不用那么纠结

[吉普赛的歌]

你的语句有点复杂， 帮你格式化了一下：

--update
UPDATE W
SET    W.price2 = CASE 
                       WHEN W.is_ew = 0 THEN E.price
                       ELSE E.price -2.0000
                  END,
       W.price2_time = CASE 
                            WHEN W.price2_time = 0 THEN -1536366010
                            ELSE W.price2_time
                       END
FROM   [waybill] W
       INNER JOIN [area] A
            ON  W.customer_id = 331
            AND W.daybill_id = 591
            AND W.destination = A.destination
            AND W.real_weight > 0
            AND W.real_weight <= 1000
       INNER JOIN [price_p] P
            ON  P.price_id = 317
            AND A.province_id = P.province_id
       INNER JOIN [price_w] E
            ON  E.price_id = 317
            AND E.[catalog] = P.[catalog]
            AND E.[weight] = 1000
            AND E.price > 0

--query
SELECT W.price2,
       CASE 
            WHEN W.is_ew = 0 THEN E.price
            ELSE E.price -2.0000
       END,
       W.price2_time,
       CASE 
            WHEN W.price2_time = 0 THEN -1536366010
            ELSE W.price2_time
       END
FROM   [waybill] W
       INNER JOIN [area] A
            ON  W.customer_id = 331
            AND W.daybill_id = 591
            AND W.destination = A.destination
            AND W.real_weight > 0
            AND W.real_weight <= 1000
       INNER JOIN [price_p] P
            ON  P.price_id = 317
            AND A.province_id = P.province_id
       INNER JOIN [price_w] E
            ON  E.price_id = 317
            AND E.[catalog] = P.[catalog]
            AND E.[weight] = 1000
            AND E.price > 0

其实， 生产环境的库和你把库备份后放到自己电脑上执行， 区别还是比较大的。
因为生产环境是动态的， 每秒都可能有成千上万需要执行的SQL， update 语句会与其它进程产生冲突。
而你把库放到了本机， 即使是一模一样的库， 那也是静态的， 只有你一个人在执行， 没有其它进程来干扰你。
生产环境， 比你想象的复杂。

对应的 select 语句， 你查一下 query 下面的语句， 看下需要多长时间？
--在我发出之前， 你已经查了： 100 ms
--所以， 并不慢， 而是其它进程阻塞了这个 update 语句。

下面的语句， 你试一下：

BEGIN TRAN
BEGIN TRY
	--表变量的主键，字段类型你按实际的来修改
	DECLARE @t TABLE (
		w主键 INT PRIMARY KEY,
		price2 DECIMAL(18,4),
		price2_time BIGINT
	)
	INSERT INTO @t(w主键,price2,price2_time)
	SELECT W.主键,	--自己改
       CASE 
            WHEN W.is_ew = 0 THEN E.price
            ELSE E.price -2.0000
       END AS price2,
       CASE 
            WHEN W.price2_time = 0 THEN -1536366010
            ELSE W.price2_time
       END AS price2_time
	FROM   [waybill] W WITH (XLOCK,ROWLOCK) 
		   INNER JOIN [area] A WITH (UPDLOCK,ROWLOCK) 
				ON  W.customer_id = 331
				AND W.daybill_id = 591
				AND W.destination = A.destination
				AND W.real_weight > 0
				AND W.real_weight <= 1000
		   INNER JOIN [price_p] P WITH (UPDLOCK,ROWLOCK) 
				ON  P.price_id = 317
				AND A.province_id = P.province_id
		   INNER JOIN [price_w] E WITH (UPDLOCK,ROWLOCK) 
				ON  E.price_id = 317
				AND E.[catalog] = P.[catalog]
				AND E.[weight] = 1000
				AND E.price > 0
	UPDATE W 
	SET price2 = t.price2,price2_time = t.price2_time
	FROM  [waybill] W INNER JOIN @t t ON w.主键=t.w主键
	COMMIT TRAN;
END TRY
BEGIN CATCH
	DECLARE @errMsg NVARCHAR(MAX)
	SET @errMsg=ERROR_MESSAGE()
	RAISERROR(16,1,@errMsg)
	ROLLBACK TRAN;
END CATCH 

其它不重要的查询语句(比如分页显示)， 都加上 with(nolock)， 避免不必要的锁。
类似：

SELECT * 
FROM sys.tables AS A WITH(NOLOCK) INNER JOIN 
	sys.columns AS B WITH(NOLOCK) ON a.object_id=b.object_id

[风声3]

确实，解决了就好，不就结了。