分析問題
將數(shù)據(jù)庫備份到一臺測試的服務(wù)器上，查看一下數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件情況，發(fā)現(xiàn)日志文件比較大，貌似這也是一個SQL Server 存在的問題，具體原因不清楚，但是解決方法已經(jīng)非常成熟，該問題暫時不影響使用，先不管它。
上一篇定位了問題，找出了多條Top SQL，其中這條最過分。
exec oa_SWLIST
’glzyf’,
’(s.fileSerialNumber like ’’%%’’ or s.title like ’’%%’’ or s.keywords like ’’%%’’ or s.fileZi like ’’%%’’) and ’,
’ ( ft.userid=’’glzyf’’ ) ’
分析一下這個存儲過程，我習(xí)慣是先看看SQL語句的結(jié)構(gòu)，而不是馬上看執(zhí)行計劃，或者直接跑語句獲得統(tǒng)計信息。
ALTER PROCEDURE [dbo].[oa_SWLIST]
@userID varchar(20),
@sql varchar(1000),
@userIDs varchar(1000)
AS
DECLARE @SQL1 nchar(4000) ;
DECLARE @SQL2 nchar(4000) ;
create table #employees(
[id] [int] IDENTITY(1,1) NOT NULL,parentId int,pkId int,status int,title nvarchar(1500),comeOrg nvarchar(100),
fileDate DateTime,fileName nvarchar(4000),filePath nvarchar(4000),readStatus nvarchar(10),optionStatus nvarchar(10),
depId nvarchar(20),urgencyLevel nvarchar(10));
set @SQL1=’insert into #employees (parentId,pkId,status,title,comeOrg,fileDate,fileName,filePath,readStatus,optionStatus,depId,urgencyLevel)
select distinct s.parentId,s.pkId,0,s.title,s.comeOrg,s.fileDate,
s.fileName,s.filePath,ft.readStatus,0,s.remark3,
case
when urgencyLevel=’’普通’’ then 0
when urgencyLevel=’’急件’’ then 1
when urgencyLevel=’’特辦’’ then 2
when urgencyLevel=’’特急件’’ then 3
when urgencyLevel=’’’’ then 4
else 0
end as urgencyLevel
from ShouWen as s ,
FlowTurning as ft where ’+@sql+’ ft.status=0 and ft.type=’’sw’’
and s.pkid=ft.pkid and s.status<>’’4’’ and ’+@userIDs+’ order by urgencyLevel desc,s.filedate desc’
set @SQL2=’insert into #employees (parentId,pkId,status,title,comeOrg,fileDate,
fileName,filePath,readStatus,optionStatus,depId,urgencyLevel)
select distinct s.parentId,s.pkId,1,s.title,s.comeOrg,s.fileDate,
s.fileName,s.filePath,1,l.optionstatus,s.remark3,urgencyLevel
from shouwen as s, log as l where ’+@sql+’ s.status<>’’4’’ and s.pkid in
(
select distinct(mid) from log where uid=’’’+@userID+’’’ and typeid=’’shouwen’’
)and l.mid=s.pkid and uid=’’’+@userID+’’’ and typeid=’’shouwen’’
order by s.fileDate desc’
print (@SQL1);
exec (@SQL1)
print (’+++++++++++++++++++++++++++++++++++’);
print (@SQL2);
exec (@SQL2)
select * from #employees
delete from #employees
從結(jié)果來看該存儲過程其實就是執(zhí)行了兩條動態(tài)SQL，分別存在@SQL1和SQL2。稍微修改了一下存儲過程，加入了一些調(diào)試信息，打開統(tǒng)計器。
SET STATISTICS IO on;
SET STATISTICS TIME on;
執(zhí)行存儲過程，這里由于信息量比較大。
可見@SQL1的語句耗時并不多，@SQL2資源占用是非常厲害的。其中 Log 表掃描530次，這個表的數(shù)據(jù)量有257417條，說大不大，說小也不小了，而且還得掃描530次，唉，啥也不說了，而shouwen這張表就小很多也有25000+條記錄。
我將@SQL2的語句整理出來，去掉那個討厭的 insert into #employees。
select distinct s.parentId,s.pkId,1,s.title,s.comeOrg,s.fileDate,
s.fileName,s.filePath,1,l.optionstatus,s.remark3,urgencyLevel
from shouwen as s,
log as l
where
(s.fileSerialNumber like ’%%’ or s.title like ’%%’
or s.keywords like ’%%’ or s.fileZi like ’%%’)
and s.status<>’4’
and s.pkid in
(select distinct(mid) from log where uid=’glzyf’ and typeid=’shouwen’)
and l.mid=s.pkid and uid=’glzyf’ and typeid=’shouwen’
order by s.fileDate desc
看看這個select 語句的執(zhí)行計劃啦。
以下才是重點，兩個在Log表上面的“聚集索引掃描”：
無論哪個RDBMS的語句調(diào)優(yōu)，絕大部分的情況下都是將執(zhí)行計劃中的“掃描”轉(zhuǎn)變?yōu)椤安檎摇薄?
將數(shù)據(jù)庫備份到一臺測試的服務(wù)器上，查看一下數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件情況，發(fā)現(xiàn)日志文件比較大，貌似這也是一個SQL Server 存在的問題，具體原因不清楚，但是解決方法已經(jīng)非常成熟，該問題暫時不影響使用，先不管它。
上一篇定位了問題，找出了多條Top SQL，其中這條最過分。
exec oa_SWLIST
’glzyf’,
’(s.fileSerialNumber like ’’%%’’ or s.title like ’’%%’’ or s.keywords like ’’%%’’ or s.fileZi like ’’%%’’) and ’,
’ ( ft.userid=’’glzyf’’ ) ’
分析一下這個存儲過程，我習(xí)慣是先看看SQL語句的結(jié)構(gòu)，而不是馬上看執(zhí)行計劃，或者直接跑語句獲得統(tǒng)計信息。
ALTER PROCEDURE [dbo].[oa_SWLIST]
@userID varchar(20),
@sql varchar(1000),
@userIDs varchar(1000)
AS
DECLARE @SQL1 nchar(4000) ;
DECLARE @SQL2 nchar(4000) ;
create table #employees(
[id] [int] IDENTITY(1,1) NOT NULL,parentId int,pkId int,status int,title nvarchar(1500),comeOrg nvarchar(100),
fileDate DateTime,fileName nvarchar(4000),filePath nvarchar(4000),readStatus nvarchar(10),optionStatus nvarchar(10),
depId nvarchar(20),urgencyLevel nvarchar(10));
set @SQL1=’insert into #employees (parentId,pkId,status,title,comeOrg,fileDate,fileName,filePath,readStatus,optionStatus,depId,urgencyLevel)
select distinct s.parentId,s.pkId,0,s.title,s.comeOrg,s.fileDate,
s.fileName,s.filePath,ft.readStatus,0,s.remark3,
case
when urgencyLevel=’’普通’’ then 0
when urgencyLevel=’’急件’’ then 1
when urgencyLevel=’’特辦’’ then 2
when urgencyLevel=’’特急件’’ then 3
when urgencyLevel=’’’’ then 4
else 0
end as urgencyLevel
from ShouWen as s ,
FlowTurning as ft where ’+@sql+’ ft.status=0 and ft.type=’’sw’’
and s.pkid=ft.pkid and s.status<>’’4’’ and ’+@userIDs+’ order by urgencyLevel desc,s.filedate desc’
set @SQL2=’insert into #employees (parentId,pkId,status,title,comeOrg,fileDate,
fileName,filePath,readStatus,optionStatus,depId,urgencyLevel)
select distinct s.parentId,s.pkId,1,s.title,s.comeOrg,s.fileDate,
s.fileName,s.filePath,1,l.optionstatus,s.remark3,urgencyLevel
from shouwen as s, log as l where ’+@sql+’ s.status<>’’4’’ and s.pkid in
(
select distinct(mid) from log where uid=’’’+@userID+’’’ and typeid=’’shouwen’’
)and l.mid=s.pkid and uid=’’’+@userID+’’’ and typeid=’’shouwen’’
order by s.fileDate desc’
print (@SQL1);
exec (@SQL1)
print (’+++++++++++++++++++++++++++++++++++’);
print (@SQL2);
exec (@SQL2)
select * from #employees
delete from #employees
從結(jié)果來看該存儲過程其實就是執(zhí)行了兩條動態(tài)SQL，分別存在@SQL1和SQL2。稍微修改了一下存儲過程，加入了一些調(diào)試信息，打開統(tǒng)計器。
SET STATISTICS IO on;
SET STATISTICS TIME on;
執(zhí)行存儲過程，這里由于信息量比較大。
可見@SQL1的語句耗時并不多，@SQL2資源占用是非常厲害的。其中 Log 表掃描530次，這個表的數(shù)據(jù)量有257417條，說大不大，說小也不小了，而且還得掃描530次，唉，啥也不說了，而shouwen這張表就小很多也有25000+條記錄。
我將@SQL2的語句整理出來，去掉那個討厭的 insert into #employees。
select distinct s.parentId,s.pkId,1,s.title,s.comeOrg,s.fileDate,
s.fileName,s.filePath,1,l.optionstatus,s.remark3,urgencyLevel
from shouwen as s,
log as l
where
(s.fileSerialNumber like ’%%’ or s.title like ’%%’
or s.keywords like ’%%’ or s.fileZi like ’%%’)
and s.status<>’4’
and s.pkid in
(select distinct(mid) from log where uid=’glzyf’ and typeid=’shouwen’)
and l.mid=s.pkid and uid=’glzyf’ and typeid=’shouwen’
order by s.fileDate desc
看看這個select 語句的執(zhí)行計劃啦。
以下才是重點，兩個在Log表上面的“聚集索引掃描”：
無論哪個RDBMS的語句調(diào)優(yōu)，絕大部分的情況下都是將執(zhí)行計劃中的“掃描”轉(zhuǎn)變?yōu)椤安檎摇薄?