22,206
社区成员
发帖
与我相关
我的任务
分享一个奇怪的SQL超时
平台每天都对每用户有一次计算任务，一年来都正常运行，这几天出现了奇怪的问题：
1、对个别用户某天进行计算时提示数据库计算超时（30秒），检查发现当时服务器内存耗尽（从1.6G飙升到3.8G/共4G内存）；
2、其它用户，或者该用户其它日期计算正常（2秒内，服务器内存没多大变化）；
3、将库（MSSQL2008R2）拷贝到测试机测试（直接8G内存LOCALDB2014打开/直接附加到2G内存MSSQL2016）没有超时情况，一切正常；
生产机上没有调试环境，测试机上又重现不出问题。请问该怎么去分析问题？MSSQL2008R2有没有分析查找这个超时问题的功能？
1、对个别用户某天进行计算时提示数据库计算超时（30秒），检查发现当时服务器内存耗尽（从1.6G飙升到3.8G/共4G内存）；
2、其它用户，或者该用户其它日期计算正常（2秒内，服务器内存没多大变化）；
3、将库（MSSQL2008R2）拷贝到测试机测试（直接8G内存LOCALDB2014打开/直接附加到2G内存MSSQL2016）没有超时情况，一切正常；
生产机上没有调试环境，测试机上又重现不出问题。请问该怎么去分析问题？MSSQL2008R2有没有分析查找这个超时问题的功能？
...全文
请发表友善的回复…
发表回复
风声3 2018-09-09
- 打赏
- 举报
确实，解决了就好，不就结了。
吉普赛的歌 2018-09-08
- 打赏
- 举报
你的语句有点复杂， 帮你格式化了一下：
其实， 生产环境的库和你把库备份后放到自己电脑上执行， 区别还是比较大的。
因为生产环境是动态的， 每秒都可能有成千上万需要执行的SQL， update 语句会与其它进程产生冲突。
而你把库放到了本机， 即使是一模一样的库， 那也是静态的， 只有你一个人在执行， 没有其它进程来干扰你。
生产环境， 比你想象的复杂。
对应的 select 语句， 你查一下 query 下面的语句， 看下需要多长时间？
--在我发出之前， 你已经查了： 100 ms
--所以， 并不慢， 而是其它进程阻塞了这个 update 语句。
下面的语句， 你试一下：
其它不重要的查询语句(比如分页显示)， 都加上 with(nolock)， 避免不必要的锁。
类似：
--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操作有影响。
这才是生产环境与测试环境的区别。
能解决就好吧，不用那么纠结
你一次更新，牵扯到4个表，影响很大
其他人或其他任务(包括定时作业等)对这4个表的任何的一个增删改查操作都可能对你的update操作有影响。
这才是生产环境与测试环境的区别。
能解决就好吧，不用那么纠结
风声3 2018-09-08
- 打赏
- 举报
通过 SqlProfiler 跟踪，发现这个语句直接超时：
修改为对应的SELECT语句100毫秒就执行好了
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 跟踪，发现这个语句直接超时：
修改为对应的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
修改为对应的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左右
按这个执行没有问题，正常
但是去掉临时表，就不行了
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上有工具能找到超时的语句吗？
2、资源冲突可能性也很小，同样的计算任务，甚至数据量大10倍的其它用户同一日期，可以正常运行。
3、目标机器上我不能调试，不知道是哪个语句慢，换在测试机上又不慢。
我不会什么分析查询工具，目标机器上的MSSQL2008R2上有工具能找到超时的语句吗？
吉普赛的歌 2018-09-06
- 打赏
- 举报
内存并不是关键， 数据量大了分析复杂，占内存是必须的。关键是CPU还有IO。
你直接在那台机执行对应的SQL ， 需要多少秒？
把慢SQL贴出来， 让大家帮你看看。
你直接在那台机执行对应的SQL ， 需要多少秒？
把慢SQL贴出来， 让大家帮你看看。
丰云 2018-09-06
- 打赏
- 举报
多半是资源冲突导致的，数据库执行这个任务时，机器上还有什么其他任务在执行？
应用有没有做并行处理的优化？？
应用有没有做并行处理的优化？？
lich2005 2018-09-06
- 打赏
- 举报
试试跟踪一下SQL语句，如果知道某一个特定的用户和时间，最好用这个用户账号和日期作为筛选条件检查一下相应的数据，超时一般都是数据量比较大或者数据内容出现异常造成的（比如运算出现死循环等）。
