1. 问题描述，分析
从阿里云日志捞出来的慢sql,每天出现100多次
跟踪了这个超时的sql,最近在死锁里也比较多，和top 200的 类似 （http://pm.zhcxkj.com/zentao/task-view-26776.html）
问题分析：
进一步分析sql后发现：原sql里的left,in都可以去掉，改为下面的语句发现执行计划更简洁
select top (50) QE.LockGuid, QE.QueueState, QE.ExecTime
FROM RB_Ebay_Listing_Queue_V2 QE
INNER JOIN RB_Ebay_Listing_Basic_V2 B
ON B.CustomerID = QE.CustomerID
AND B.ListingBasicID = QE.ListingBasicID
AND B.OrderSourceID = QE.OrderSourceID
AND B.SiteCode = QE.SiteCode
WHERE QE.QueueState = 0 AND QE.QueueType =6
2.解决方案：
此改造 减少死锁的概率
UPDATE TOP(50) QE
SET QE.LockGuid = '3cac6a5ce66446c2bce1b5fb8939485e',QE.QueueState = 1, QE.ExecTime = Getdate()
FROM RB_Ebay_Listing_Queue_V2 QE
INNER JOIN RB_Ebay_Listing_Basic_V2 B WITH(nolock)
ON B.CustomerID = QE.CustomerID AND B.ListingBasicID = QE.ListingBasicID AND B.OrderSourceID = QE.OrderSourceID AND B.SiteCode = QE.SiteCode
WHERE QE.QueueState = 0 AND QE.QueueType = 6;
逻辑和之前是一样的，改造后执行计划更简单，添加了索引，速度更快，减少了死锁的概率
3.原sql
UPDATE QE
SET QE.LockGuid = '25d3b1ae33814b3490adff69882fbdaa',
QE.QueueState = 1,
QE.ExecTime = Getdate()
FROM RB_Ebay_Listing_Queue_V2 QE
INNER JOIN (SELECT QueueID
FROM (SELECT TOP (50) Q.QueueID
FROM RB_Ebay_Listing_Queue_V2 Q WITH (NOLOCK)
INNER JOIN RB_Ebay_Listing_Basic_V2 B WITH (NOLOCK)
ON B.CustomerID = Q.CustomerID
AND B.ListingBasicID = Q.ListingBasicID
AND B.OrderSourceID = Q.OrderSourceID
AND B.SiteCode = Q.SiteCode
LEFT JOIN RB_Ebay_Listing_Product_V2 P WITH (NOLOCK)
ON Q.ListingBasicID = P.ListingBasicID
AND Q.CustomerID = P.CustomerID
AND P.OrderSourceID = Q.OrderSourceID
AND Q.SiteCode = P.SiteCode
AND P.IsVariation = 0
LEFT JOIN RB_OrderSource OS WITH (NOLOCK)
ON OS.ID = Q.OrderSourceID
AND OS.CustomerID = Q.CustomerID
WHERE Q.QueueState = 0
AND Q.QueueType = 5
ORDER BY Q.AddTime)#TMP)tmp
ON QE.QueueID = tmp.QueueID
--PARAMS:
--MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
