一个奇怪的SQL超时

风声3 2018-09-06 03:42:52
平台每天都对每用户有一次计算任务,一年来都正常运行,这几天出现了奇怪的问题:
1、对个别用户某天进行计算时提示数据库计算超时(30秒),检查发现当时服务器内存耗尽(从1.6G飙升到3.8G/共4G内存);
2、其它用户,或者该用户其它日期计算正常(2秒内,服务器内存没多大变化);
3、将库(MSSQL2008R2)拷贝到测试机测试(直接8G内存LOCALDB2014打开/直接附加到2G内存MSSQL2016)没有超时情况,一切正常;
生产机上没有调试环境,测试机上又重现不出问题。请问该怎么去分析问题?MSSQL2008R2有没有分析查找这个超时问题的功能?
...全文
500 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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
  • 打赏
  • 举报
回复
内存并不是关键, 数据量大了分析复杂,占内存是必须的。关键是CPU还有IO。
你直接在那台机执行对应的SQL , 需要多少秒?
把慢SQL贴出来, 让大家帮你看看。
丰云 2018-09-06
  • 打赏
  • 举报
回复
多半是资源冲突导致的,数据库执行这个任务时,机器上还有什么其他任务在执行?
应用有没有做并行处理的优化??
lich2005 2018-09-06
  • 打赏
  • 举报
回复
试试跟踪一下SQL语句,如果知道某一个特定的用户和时间,最好用这个用户账号和日期作为筛选条件检查一下相应的数据,超时一般都是数据量比较大或者数据内容出现异常造成的(比如运算出现死循环等)。

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧