一个奇怪的SQL超时

风声3 2018-09-06 03:42:52

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

...全文

502 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

风声3 2018-09-09

打赏
举报

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

吉普赛的歌 2018-09-08

打赏
举报

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

--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

吉普赛的歌 2018-09-08

打赏
举报

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

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

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

风声3 2018-09-08

打赏
举报

通过 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

风声3 2018-09-08

打赏
举报

通过 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 2018-09-08

打赏
举报

这是一个业务系统，生产环境也没有什么并发，比较耗时的操作都做了并发限制
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

吉普赛的歌 2018-09-07

打赏
举报

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

OwenZeng_DBA 2018-09-07

打赏
举报

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

qq_25073223 2018-09-06

打赏
举报

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

风声3 2018-09-06

打赏
举报

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

吉普赛的歌 2018-09-06