环境:server2003+.net3.5+sql 2005开发版
症状:同样的SQL语句,同一个数据库,不知原因的情况下,用事件探查器跟踪的时候出现
---------------------------------
reads | writes | duration
679019| 18 | 17359
---------------------------------把SQL语句拷贝到查询分析器中执行
---------------------------------
reads | writes | duration
9476 | 28 | 93
---------------------------------这个情况我跟踪好几天了,一直没查出什么原因(可能是小弟水平有限,请高手多多指教)
以下是我检查的几个情况,写出来,以便大家帮我分析原因
1.数据当前无未结束的事务导致锁表
2.出现该情况的有点偶然,局域网的服务器上,我刚发现reads好几十万的情况,刚要去查原因~ 结果就好了~
3.外网的服务器,一次我从上午10点,一直跟踪到下午2点,情况突然转好
请大家帮我分析下原因,可能限于本人水平,一些要点环境因素未能反应出来;请大家说说可能产生这种情况的主要原因,次要原因,和一些极端原因。
在线等答案,QQ12666954,如有一些环境因素不太明白的,我们可以QQ交流
症状:同样的SQL语句,同一个数据库,不知原因的情况下,用事件探查器跟踪的时候出现
---------------------------------
reads | writes | duration
679019| 18 | 17359
---------------------------------把SQL语句拷贝到查询分析器中执行
---------------------------------
reads | writes | duration
9476 | 28 | 93
---------------------------------这个情况我跟踪好几天了,一直没查出什么原因(可能是小弟水平有限,请高手多多指教)
以下是我检查的几个情况,写出来,以便大家帮我分析原因
1.数据当前无未结束的事务导致锁表
2.出现该情况的有点偶然,局域网的服务器上,我刚发现reads好几十万的情况,刚要去查原因~ 结果就好了~
3.外网的服务器,一次我从上午10点,一直跟踪到下午2点,情况突然转好
请大家帮我分析下原因,可能限于本人水平,一些要点环境因素未能反应出来;请大家说说可能产生这种情况的主要原因,次要原因,和一些极端原因。
在线等答案,QQ12666954,如有一些环境因素不太明白的,我们可以QQ交流
应该是执行计划不同.执行计划不一样的原因有可能有很多种,sqlserver会缓存一些语句的执行计划,但如果统计信息发生变化的时候这个执行计划不是最优的.如果把语句复制到ssms执行的话这个语句会重新编译,走新的执行计划.
如果慢的很明显的话我感觉是字段的饮食转换导致不走索引了.
t1.TransWorkID,t1.TransWorkNO, t1.ORGID, t1.CustomerID, t1.TransWorkState,
t1.GoTime, t1.ArriveTime, t1.FeedBackTime, t6.Name AS CustomerName,
t3.Name AS CreateUserName, t7.Name AS ORGName,t1.CreateDate,
t4.Name AS StartCityName,t14.Name AS EndCityName,t5.OrderTruckID,t8.DriverName,
t9.TruckLicense, t5.TruckOwerShip,t10.BillReviewState,t10.FeeBillCheckState, t10.ContractCheckState,t10.ReceiptCheckState,t10.ContractID,
t10.LeaderRequestState,t15.Name AS SubmitUserName,t1.SubmitTime,
t13.BillReviewState AS TeamBillReviewState,
t13.FeeBillCheckState AS TeamFeeBillCheckState,
t13.ContractCheckState AS TeamContractCheckState,
t13.ReceiptCheckState AS TeamReceiptCheckState,
t13.TeamContractID,t13.LeaderRequestState AS TeamLeaderRequestState,
TAllowViewData.AllowOperate
FROM (SELECT DISTINCT B_TransWork.TransWorkID, MAX(R_UserPageRight.AllowOperate) AS AllowOperate
FROM R_UserPageRight INNER JOIN
B_TransWork ON (B_TransWork.CustomerID = R_UserPageRight.SourceID
AND R_UserPageRight.SourceType = @CstSB) OR
(B_TransWork.ORGID = R_UserPageRight.SourceID AND R_UserPageRight.SourceType = @OrgSB)
WHERE R_UserPageRight.UserID=@CrtUserID
AND R_UserPageRight.StartTime <= @beginCrtTime
AND R_UserPageRight.EndTime > @endCrtTime
AND B_TransWork.TransWorkFlg=@transWorkFlg
GROUP BY B_TransWork.TransWorkID) AS TAllowViewData
LEFT JOIN B_TransWork AS t1 ON TAllowViewData.TransWorkID=t1.TransWorkID
INNER JOIN C_Customer AS t6 ON t1.CustomerID = t6.CustomerID
LEFT JOIN U_User AS t15 ON t1.SubmitUserID=t15.UserID
LEFT JOIN C_CityArea AS t4 ON t1.StartCityID = t4.CityID
LEFT JOIN C_CityArea AS t14 ON t1.EndCityID = t14.CityID
LEFT JOIN B_OrderTruck AS t5 ON t5.TransWorkID = t1.TransWorkID
LEFT JOIN S_Driver AS t8 ON t8.DriverID = t5.DriverID
LEFT JOIN S_Truck AS t9 ON t9.TruckID = t5.TruckID
INNER JOIN U_Organization AS t7 ON t5.LastORGID = t7.ORGID
LEFT JOIN U_User AS t3 ON t5.LastOrderUserID = t3.UserID
LEFT JOIN
(SELECT ContractID,OrderTruckID,ContractNO,BillReviewState,
FeeBillCheckState,ContractCheckState,ReceiptCheckState,
LeaderRequestState
FROM B_TransContract
WHERE B_TransContract.ContractFlg<>@contractFlg
) AS t10 ON t10.OrderTruckID = t5.OrderTruckID
LEFT JOIN
(SELECT
TeamContractID,OrderTruckID,TeamContractNO,BillReviewState,
FeeBillCheckState,ContractCheckState,ReceiptCheckState,
LeaderRequestState
FROM B_TeamContract
WHERE B_TeamContract.IsUsed<>@isUsed
) AS t13 ON t13.OrderTruckID = t5.OrderTruckID
LEFT JOIN B_OrderShipRecord AS t11 ON t1.TransWorkID = t11.TransWorkID
INNER JOIN B_ClientOrder AS t12 ON t11.ClientOrderID = t12.ClientOrderID
WHERE t5.OrderState=@orderState
AND (@contractNO='''' OR @contractNO<>'''' AND t10.ContractNO LIKE ''%''+@contractNO+''%'')
AND (@teamContractNO='''' OR @teamContractNO<>''''
AND t13.TeamContractNO LIKE ''%''+@teamContractNO+''%'')
AND (@clientOrderNO='''' OR @clientOrderNO<>''''
AND t12.ClientOrderNO LIKE ''%''+@clientOrderNO+''%'')
AND (@transWorkNO='''' OR @transWorkNO<>'''' AND t1.TransWorkNO LIKE ''%''+@transWorkNO+''%'')
AND (@truckLicense='''' OR @truckLicense<>''''
AND t9.TruckLicense LIKE ''%''+@truckLicense +''%'')
AND (@startCityID=@defaultGuid OR @startCityID<>@defaultGuid
AND t1.StartCityID=@startCityID)
AND (@endCityID=@defaultGuid OR @endCityID<>@defaultGuid AND t1.EndCityID=@endCityID)
AND (@driverName='''' OR @driverName<>'''' AND t8.DriverName
LIKE ''%''+@driverName+''%'')',N'@orderState varchar(1),@contractNO varchar(50),@teamContractNO varchar(50),@clientOrderNO nvarchar(50),@transWorkNO nvarchar(50),@truckLicense
nvarchar(50),@startCityID uniqueidentifier,@defaultGuid varchar(1024),@endCityID uniqueidentifier,@driverName nvarchar(50),@CstSB varchar(2),@OrgSB varchar(2),@CrtUserID
uniqueidentifier,@beginCrtTime datetime,@endCrtTime datetime,@transWorkFlg nvarchar(1),@contractFlg varchar(2),@isUsed
varchar(1)',@orderState='1',@contractNO='',@teamContractNO='',
@clientOrderNO=N'130',@transWorkNO=N'',@truckLicense=N'',
@startCityID='00000000-0000-0000-0000-000000000000',
@defaultGuid='00000000-0000-0000-0000-000000000000',
@endCityID='00000000-0000-0000-0000-000000000000',@driverName=N'',
@CstSB='1',@OrgSB='0',@CrtUserID='D64A9481-E4F4-DE11-87C9-001372F8D63A',
@beginCrtTime='2011-02-21 15:20:05:590',@endCrtTime='2011-02-20 15:20:05:590',@transWorkFlg=N'1',@contractFlg='2',@isUsed='0'
PS:请注意,不要去讨论数据库结构设计之类的话题
求解