SQL Server Replication怎么老RECOMPILE啊 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SQL Server Replication怎么老RECOMPILE啊

SQL Server Replication怎么老RECOMPILE啊# Database - 数据库

o*g2011-02-08 08:02

1 楼

宝贝，宝贵的贝壳
·方舟子·
当你亲切地称呼某一个心爱的人“宝贝”的时候，很少有人会意识
到，你其实是把她叫做一种贝壳。宝贝的本义是宝贵的贝，是宝螺科
（又称宝贝科）贝类的统称。全世界的宝贝大约有200种，共同特征是
贝壳为卵圆形，极其光滑，背面布满各种斑点和花纹，腹面有一条缝状
开口，开口的两侧各有一排齿纹。甲骨文的“贝”字画的就是宝贝腹面，
两侧各画两颗牙齿。后来两侧的牙齿连接成了两条线，下面又伸出两根
触角，就成了繁体的“贝”字。
为什么把这种贝壳称为宝贝呢？不仅因为它是最漂亮的贝壳，而且
因为在远古时期它是做为货币使用的，是人类最早使用的货币。所以我
们的祖先用“贝”造出了很多与财富有关的字：财、货、贪、贫、贾、
资……有的经过简化已看不出和“贝”的关系，例如买、卖。有的在繁
体字中也难以觉察与“贝”的关系，在甲骨文中才露出贝的影子，例如：
“得”，本字只有右半部分，上面是“贝”下面是“手”，意思是拿到
了财富；“贯”，上下部分都是“贝”，是一根绳子把两个贝穿在了一
起，本义指的是穿钱的绳子；“朋”，是两串贝

wh2011-02-08 08:02

2 楼

【以下文字转载自 LeisureTime 讨论区】
发信人: wh (wh), 信区: LeisureTime
标题: 哇，给你们听个笛子
发信站: BBS 未名空间站 (Thu Jun 24 12:23:14 2010, 美东)
有人知道这个陈红吗？是杭州人哎。《乱红》不知道什么意思。看虾米上还有她的其他
几个曲子：岫壑浮云、苦雪烹茶、梅花三弄、妆台秋思、绿野仙踪。都很古色古香。待
会儿听。有人有这个《乱红》的mp3吗？谢谢！
陈悦，浙江杭州人，自幼随父学习笛箫。后师从于著名笛子演奏家、教育家赵松庭先生
，成为其关门弟子。
1993年考入中国音乐学院附中，师从著名笛箫演奏家张维良教授。
1996年考入中国音乐学院大学本科，并代表学校出访美、日、韩等国家和地区。曾录制
个人专辑《情竹》，并多次参加电台、电视台的录音录象活动。曾获中国音乐学院奖学
金，2000年被保送本院硕士研究生。曾参加“凤之声”、“盛兰七馨”、“现代女子乐
团”等多个重奏小组的演出活动。
陈悦的音乐风格细腻婉约、深情蕴籍，这位江南女子在演奏中那好似春光乍泄的迷人风
采赋予了这样一件古董式的乐器以新鲜的生命。在

b*n2011-02-08 08:02

3 楼

【以下文字转载自 Military 讨论区】
发信人: superphase (多情应笑我), 信区: Military
标题: 学者:“最牛高考满分作文”漏洞百出,满分很悲哀（ZZ)
发信站: BBS 未名空间站 (Thu Jul 2 12:53:46 2009, 美东)
“最牛高考满分作文”原来“漏洞百出”
诗词专家称其不懂用韵,结构凌乱
一首名为《站在黄花岗陵园的门口》的古体长诗,在湖北省今年语文高考中被阅卷老师
评为“最牛满分作文”。该长诗歌共51行102句,被媒体报道之后在网上广泛转载,作者
周海洋也因此出名。
但昨日中山大学古典文献学博士徐晋如在博客发文称,“阅卷老师把一篇应该得零分的
作文评了满分,这是中国教育的悲哀。”中山大学教授彭玉平在接受记者采访时则表示,
这位学生虽然显示出了潜力,但其作文确实是一个“漏洞百出”的作品。
阅卷老师称其为“最牛满分作文”
“ 湖北省的这篇满分作文,其实是一篇根本没有‘体’的‘自由诗’,在行家看来,根本
不及格”。徐晋如是中山大学的博士,目前为本科生开设《大学诗词写作教程》。在博
客里,徐晋如逐句指出“最牛作文”不合韵,不合规

g*l2011-02-08 08:02

4 楼

sys.sp_replmonitorrefreshagentdata老在RECOMPILE，REPLICATION是从2005到2008
PUSH。现在RECOMPILE是25RECOMPILES/SEC PROCEDURE HIT RATE只有60%

o*g2011-02-08 08:02

5 楼

The website below has some very nice samples.
I was always wondering how could ancient people use shell as money. It is
only possible to understand it after you look at the beautiful pictures.
http://www.rbridges.com/

h*x2011-02-08 08:02

6 楼

好听，好听。我大约5年前第一次听到这个曲子，到现在仍然很喜欢，百听不厌。
笛子可以和钢琴配合的如此默契！...忧伤的感觉如同虐恋

l*r2011-02-08 08:02

7 楼

原诗贴一下吧

S*02011-02-08 08:02

8 楼

not enough memory?

【在 g***l 的大作中提到】

: sys.sp_replmonitorrefreshagentdata老在RECOMPILE，REPLICATION是从2005到2008
: PUSH。现在RECOMPILE是25RECOMPILES/SEC PROCEDURE HIT RATE只有60%

o*g2011-02-08 08:02

9 楼

A pile of money. 货贝

wh2011-02-08 08:02

10 楼

哈哈这么敏锐，我觉得美，但还没想到虐恋。这个专辑别的曲子我好像都不怎么感冒，
就这个喜欢。

【在 h*****x 的大作中提到】

: 好听，好听。我大约5年前第一次听到这个曲子，到现在仍然很喜欢，百听不厌。
: 笛子可以和钢琴配合的如此默契！...忧伤的感觉如同虐恋

g*l2011-02-08 08:02

11 楼

6GB,有可能低，另外一个SERVER 30G的内存，也有类似的问题，不过HIT RATE 99%

【在 S*****0 的大作中提到】

: not enough memory?

o*g2011-02-08 08:02

12 楼

Antique
http://auction.artxun.com/paimai-401-2000911.shtml
http://img4.artxun.com/img?sp=33605080badf6f6fe5a80c3748d8260e&id=18b17098f2f6da8b28d4c5a473506c8a&s=BnanYuNDAvND4vb2Rycn9hbi5jaSRocn5hdyd3L3ovcDR0aH&is=thumbnail_400_400.jpg

d*l2011-02-08 08:02

13 楼

幽怨，好听，要是能下载就好了。

z*y2011-02-08 08:02

14 楼

这个不奇怪啊。
你可以看一看那个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%

w*b2011-02-08 08:02

15 楼

将来全世界实现共产主义了，大家各取所需，这些金子银子美钞贝壳就都没用了。

【在 o*******g 的大作中提到】

: Antique
: http://auction.artxun.com/paimai-401-2000911.shtml
: http://img4.artxun.com/img?sp=33605080badf6f6fe5a80c3748d8260e&id=18b17098f2f6da8b28d4c5a473506c8a&s=BnanYuNDAvND4vb2Rycn9hbi5jaSRocn5hdyd3L3ovcDR0aH&is=thumbnail_400_400.jpg

wh2011-02-08 08:02

16 楼

你到浙版找这个帖子，needforspeed回帖里贴过一个下载链接。

【在 d*****l 的大作中提到】

: 幽怨，好听，要是能下载就好了。

g*l2011-02-08 08:02

17 楼

掐点重点的说吧，这个SP好长啊

l*e2011-02-08 08:02

18 楼

fangzhouzi还整这个?

【在 o*******g 的大作中提到】

: 宝贝，宝贵的贝壳
: ·方舟子·
: 当你亲切地称呼某一个心爱的人“宝贝”的时候，很少有人会意识
: 到，你其实是把她叫做一种贝壳。宝贝的本义是宝贵的贝，是宝螺科
: （又称宝贝科）贝类的统称。全世界的宝贝大约有200种，共同特征是
: 贝壳为卵圆形，极其光滑，背面布满各种斑点和花纹，腹面有一条缝状
: 开口，开口的两侧各有一排齿纹。甲骨文的“贝”字画的就是宝贝腹面，
: 两侧各画两颗牙齿。后来两侧的牙齿连接成了两条线，下面又伸出两根
: 触角，就成了繁体的“贝”字。
: 为什么把这种贝壳称为宝贝呢？不仅因为它是最漂亮的贝壳，而且

g*l2011-02-08 08:02

19 楼

昨天加了13个TABLE UPDATABLE TRANSACTIONAL REPLICATION，COMPILES/S就从昨天的
20跳到50了，CPU的USAGE也明显增加，到底什么在RECOMPILE，难道是REPLICATION自己
的SP?还是REPLICATION CALL的那些TRIGGERS