Friday, March 23, 2012
replication process locking up the PFS page
of the subscription DB? The blocked process shows that it is waiting on
replication SPID and waitresource is "KEY: 11:1:1 (540080683243)" (11 is the
DB id of the subscription DB)
thanks.
What is the pfs page?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MorDeRor" <MorDeRor@.discussions.microsoft.com> wrote in message
news:EFD676CB-BD3C-41C7-A42E-43D2C05A11EC@.microsoft.com...
> Can anyone tell my why does replication sometimes hold a lock on the PFS
> page
> of the subscription DB? The blocked process shows that it is waiting on
> replication SPID and waitresource is "KEY: 11:1:1 (540080683243)" (11 is
> the
> DB id of the subscription DB)
> thanks.
|||It's the Page Free Space page, which SQL Server uses to track the available
space on each data and index page in the database. It's always page ID 1 in
a data file.
Referring back to the initial question, the only scenario where I've heard
about contention on the PFS page refers to heavy creation of objects in
tempdb. Large deallocations could also be a factor.
Regards
Andrew Pike
SQL Server DBA
UBS IB
"Hilary Cotter" wrote:
> What is the pfs page?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "MorDeRor" <MorDeRor@.discussions.microsoft.com> wrote in message
> news:EFD676CB-BD3C-41C7-A42E-43D2C05A11EC@.microsoft.com...
>
>
|||Thanks Andrew, I now what you're talking about. I monitor our servers fairly
closely and see very little contention (if any) for the PFS page. The
blocking that I mentioned does not occur in the tempDB rather in the
subscription DB, and it does not happen consistently. There are a lot of
costom SPs that get triggered by replication which move data around, may be
I'll set up something to check for page splits and new extent utilization,
but even even so, it does not make sense, because the blocked query is just a
bunch of select statements, so why should it care that there is a lock on the
PFS page?
"Andrew Pike" wrote:
[vbcol=seagreen]
> It's the Page Free Space page, which SQL Server uses to track the available
> space on each data and index page in the database. It's always page ID 1 in
> a data file.
> Referring back to the initial question, the only scenario where I've heard
> about contention on the PFS page refers to heavy creation of objects in
> tempdb. Large deallocations could also be a factor.
> Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Hilary Cotter" wrote:
|||Maybe you could monitor the Latch Wait Time (ms) counter? I think the PFS,
GAM and SGAM pages are synchronised via latching (UP latch?). If there's
memory pressure on the server and the Latch Wait Time is high (>500 ms) that
may be a source of the problem.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
"Mor DeRor" wrote:
[vbcol=seagreen]
> Thanks Andrew, I now what you're talking about. I monitor our servers fairly
> closely and see very little contention (if any) for the PFS page. The
> blocking that I mentioned does not occur in the tempDB rather in the
> subscription DB, and it does not happen consistently. There are a lot of
> costom SPs that get triggered by replication which move data around, may be
> I'll set up something to check for page splits and new extent utilization,
> but even even so, it does not make sense, because the blocked query is just a
> bunch of select statements, so why should it care that there is a lock on the
> PFS page?
> "Andrew Pike" wrote:
Tuesday, March 20, 2012
Replication performance degrade in unidirectional Direction and lock time out (Update are high t
We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.
1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).
2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..
on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.
can any one can also suggest me server level settings for aviding locking time out.
looking for any experieced solution/suggestion.
Thanks in advance.
Hi adrshen,
Need more info. What performance has degraded? You mean user transactions. What was the response time like before and what is it like now.
With merge replication, it would affect the performance as it uses triggers to capture the changes.
regards
Jag
|||plz read my question again .. I just edit it :)|||If it is unidirectional always from publisher to subscriber, then you should look at download_only_articles. This is a special type of setting on an article to indicate that the subscriber will not do DML and it will be more performant. However, note that the performance of the merge agent will increase, but I am not sure if your subscriber itself will start performing better. You can give it a try.