replication process locking up the PFS page

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.
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: