g***l
发帖数: 18555 | 1 sys.sp_replmonitorrefreshagentdata老在RECOMPILE，REPLICATION是从2005到2008
PUSH。现在RECOMPILE是25RECOMPILES/SEC PROCEDURE HIT RATE只有60% | S*****0
发帖数: 538 | 2 not enough memory?
【在 g***l 的大作中提到】
: sys.sp_replmonitorrefreshagentdata老在RECOMPILE，REPLICATION是从2005到2008
: PUSH。现在RECOMPILE是25RECOMPILES/SEC PROCEDURE HIT RATE只有60%
| g***l
发帖数: 18555 | 3 6GB,有可能低，另外一个SERVER 30G的内存，也有类似的问题，不过HIT RATE 99%
【在 S*****0 的大作中提到】
: not enough memory?
| z***y
发帖数: 7151 | 4 这个不奇怪啊。
你可以看一看那个procedure, 你看里面有几个对temp table 的改变， 这些数据的改
变都会触发
recompilation。
我这个版本是2008 SP2的。
create procedure sys.sp_replmonitorrefreshagentdata
as
begin
set nocount on
declare @retcode int
,@agent_id int
,@agent_id2 int
,@publisher_id int
,@xact_seqno varbinary(16)
,@logreader_latency int
,@publisher_db sysname
,@publication sysname
-- security check
-- Has to be executed from distribution database
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_replmonitorrefreshagentdata',
'distribution')
return (1)
end
-- logreader specific
if exists (select agent_id from #tmp_replmonitorrefresh where agent_type
= 2)
begin
-- worst_latency in seconds
-- best_latency in seconds
-- avg_latency in seconds
update #tmp_replmonitorrefresh
set worst_latency = latencyview.worst/1000
,best_latency = latencyview.best/1000
,avg_latency = latencyview.average/1000
from
(
select agent_id
,max(delivery_latency) as worst
,min(delivery_latency) as best
,cast(avg(cast(delivery_latency as bigint)) as int) as
average
from dbo.MSlogreader_history with (nolock)
where runstatus = 2
group by agent_id
) as latencyview
where #tmp_replmonitorrefresh.agent_id = latencyview.agent_id
and agent_type = 2
-- cur_latency in seconds
update #tmp_replmonitorrefresh
set cur_latency = h.delivery_latency/1000
from
(
dbo.MSlogreader_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSlogreader_history with (nolock)
where runstatus = 2
and delivered_commands > 0
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and agent_type = 2
-- agentstoptime
update #tmp_replmonitorrefresh
set agentstoptime = h.time
from
(
dbo.MSlogreader_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSlogreader_history with (nolock)
where (runstatus = 2 or runstatus > 4)
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and agent_type = 2
end
-- Distribution agent specific
if exists (select agent_id from #tmp_replmonitorrefresh where
(agent_type & 3) = 3)
begin
-- retention (same for all logbased and snapshot publications)
-- Use the max_retention value for this distribution db
update #tmp_replmonitorrefresh
set retention = dbs.max_distretention
from msdb.dbo.MSdistributiondbs as dbs
where dbs.name = distdb
and (agent_type & 3) = 3
-- worst_latency in seconds
-- best_latency in seconds
-- avg_latency in seconds
update #tmp_replmonitorrefresh
set worst_latency = latencyview.worst/1000
,best_latency = latencyview.best/1000
,avg_latency = latencyview.average/1000
from
(
select agent_id
,max(delivery_latency) as worst
,min(delivery_latency) as best
,cast(avg(cast(delivery_latency as bigint)) as int) as
average
from dbo.MSdistribution_history with (nolock)
where runstatus = 2
group by agent_id
) as latencyview
where #tmp_replmonitorrefresh.agent_id = latencyview.agent_id
and (agent_type & 3) = 3
-- last_distsync (this essentially records the last time
-- some activity happened on the history of the agent)
update #tmp_replmonitorrefresh
set last_distsync = h.time
from
(
dbo.MSdistribution_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSdistribution_history with (nolock)
where runstatus in (2,3,4)
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and (agent_type & 3) = 3
-- agentstoptime
update #tmp_replmonitorrefresh
set agentstoptime = h.time
from
(
dbo.MSdistribution_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSdistribution_history with (nolock)
where (runstatus = 2 or runstatus > 4)
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and (agent_type & 3) = 3
-- cur_latency
-- this value is the ("time of sub commit" - "distribution entry
time"/1000
-- basically this is the distrib agent latency in seconds (pre-
computed by add hist)
-- if we are looking at a idle (runstatus = 4) entry then it means
there are
-- no pending messages and we cannot reliably compute cur_latency.
We will set it
-- to zero. This will resolve the cases when some high latency
processing happens
-- and then the latency never goes down if there is no further
activity (example:
-- Initial Snapshot processing followed by no other activity should
not spike the
-- latency for a unduly long time)
update #tmp_replmonitorrefresh
set cur_latency = case when (distlatency.runstatus = 4) then 0 else
distlatency.latency/1000 end
from
(
(
select agent_id, max(xact_seqno) as maxseqno, max(time) as
maxtime
from dbo.MSdistribution_history with (nolock)
where runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-
idle
group by agent_id
) as xactview
join
(
select agent_id, isnull(delivery_latency, 0) as latency,
xact_seqno, time, runstatus
from dbo.MSdistribution_history with (nolock)
where runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-
idle
) as distlatency
on xactview.agent_id = distlatency.agent_id
and xactview.maxseqno = distlatency.xact_seqno
and xactview.maxtime = distlatency.time
)
where #tmp_replmonitorrefresh.agent_id = distlatency.agent_id
and (agent_type & 3) = 3
-- compute the logreader latency for logbased publications
declare #hcrefreshmonitor cursor local fast_forward for
select publisher_srvid, publisher_db, publication, agent_id
from #tmp_replmonitorrefresh
where publication_type = 0
and (agent_type & 3) = 3
open #hcrefreshmonitor
fetch #hcrefreshmonitor into @publisher_id, @publisher_db,
@publication, @agent_id
while (@@fetch_status != -1)
begin
-- initialize
select @agent_id2 = NULL
,@xact_seqno = NULL
,@logreader_latency = NULL
select top 1 @xact_seqno = xact_seqno
from dbo.MSdistribution_history with (nolock)
where agent_id = @agent_id
and runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
order by xact_seqno desc, time desc
-- here we are attempting to find the last transaction in
logreader history
-- that is closest to the last transaction delivered to the
subscriber.
-- do logreader latency computation only for logbased
publications
-- In the event we find a an idle history log, we will set
logreader latency to 0
select @agent_id2 = max(id)
from dbo.MSlogreader_agents with (nolock)
where publisher_id = @publisher_id
and publisher_db = @publisher_db
if (@agent_id2 is null)
begin
raiserror('could not find agent entry', 16,1)
return (1)
end
select top 1 @logreader_latency = case when (runstatus = 4) then
0 else delivery_latency/1000 end
from MSlogreader_history with (nolock)
where agent_id = @agent_id2
and xact_seqno >= @xact_seqno
and runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
order by xact_seqno asc, time desc
if @logreader_latency is NULL
begin
-- if we did not have a tran in front then we will use the
-- previous one note the difference in order by clause and
range
select top 1 @logreader_latency = case when (runstatus = 4)
then 0 else delivery_latency/1000 end
from MSlogreader_history with (nolock)
where agent_id = @agent_id2
and xact_seqno < @xact_seqno
and runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-
idle
order by xact_seqno desc, time desc
end
-- add the latency
if @logreader_latency is not NULL
begin
update #tmp_replmonitorrefresh
set cur_latency = cur_latency + @logreader_latency
where agent_id = @agent_id
end
-- fetch next agent
fetch #hcrefreshmonitor into @publisher_id, @publisher_db,
@publication, @agent_id
end
close #hcrefreshmonitor
deallocate #hcrefreshmonitor
end
-- Queuereader agent specific
if exists (select agent_id from #tmp_replmonitorrefresh where agent_type
= 9)
begin
-- @agentstoptime datetime output
update #tmp_replmonitorrefresh
set agentstoptime = h.time
from
(
dbo.MSqreader_history as h with (nolock)
join
(
select agent_id, max(timestamp) as maxtimestamp
from dbo.MSqreader_history with (nolock)
where (runstatus = 2 or runstatus > 4)
group by agent_id
) as latest
on h.agent_id = latest.agent_id
and h.timestamp = latest.maxtimestamp
)
where #tmp_replmonitorrefresh.agent_id = h.agent_id
and agent_type = 9
end
-- Merge agent specific
if exists (select agent_id from #tmp_replmonitorrefresh where
(agent_type & 4) = 4)
begin
update #tmp_replmonitorrefresh
set mergelatestsessionconnectiontype = h.connection_type
,mergelatestsessionrunduration = h.duration
,mergelatestsessionrunspeed=h.delivery_rate
,isagentrunningnow=case when (h.runstatus in (1, 3, 4, 5)) then
1 else 0 end
,last_distsync = h.start_time
from dbo.MSmerge_sessions as h with (nolock),
#tmp_replmonitorrefresh
where session_id =
(
select top 1 ms2.session_id
from dbo.MSmerge_sessions ms2 with (nolock)
where ms2.agent_id = h.agent_id
order by ms2.session_id desc
)
and #tmp_replmonitorrefresh.agent_id = h.agent_id
and ((agent_type & 4) = 4)
-- runspeed
update #tmp_replmonitorrefresh
set mergePerformance=
case when (rates.avg_mergerunspeed is not null and
rates.avg_mergerunspeed != 0)
then
CAST((mergelatestsessionrunspeed*100)/rates.avg_mergerunspeed as int)
else NULL end
from
(
(
select agent_id, connection_type, delivery_rate
from dbo.MSmerge_sessions as ms1 with (nolock)
where (upload_inserts + upload_deletes + upload_updates +
download_inserts
+ download_deletes + download_updates) >= 50
and delivery_rate is not null
and session_id = (select top 1 ms2.session_id from
dbo.MSmerge_sessions ms2 with (nolock)
where ms1.agent_id = ms2.agent_id
and ms1.connection_type = ms2.connection_type
order by ms2.session_id desc)
) as latest
join
(
select connection_type
,isnull(avg(delivery_rate),0) as avg_mergerunspeed
from dbo.MSmerge_sessions with (nolock)
where delivery_rate is not null
and (upload_inserts + upload_deletes + upload_updates +
download_inserts
+ download_deletes + download_updates) >= 50
group by connection_type
having count(*) >= 5 -- compare with min 5 sessions of
same conn type and each having
-- replicated at least
50 rows.
) as rates
on latest.connection_type = rates.connection_type
)
where #tmp_replmonitorrefresh.agent_id = latest.agent_id
and mergelatestsessionconnectiontype = latest.connection_type
and ((agent_type & 4) = 4)
end
-- all done
return 0
end
【在 g***l 的大作中提到】
: sys.sp_replmonitorrefreshagentdata老在RECOMPILE，REPLICATION是从2005到2008
: PUSH。现在RECOMPILE是25RECOMPILES/SEC PROCEDURE HIT RATE只有60%
| g***l
发帖数: 18555 | | g***l
发帖数: 18555 | 6 昨天加了13个TABLE UPDATABLE TRANSACTIONAL REPLICATION，COMPILES/S就从昨天的
20跳到50了，CPU的USAGE也明显增加，到底什么在RECOMPILE，难道是REPLICATION自己
的SP?还是REPLICATION CALL的那些TRIGGERS |
|
