Showing posts with label locking. Show all posts
Showing posts with label locking. Show all posts

Wednesday, March 28, 2012

Replication subscriber views, locks and blocks.

Quick question, do views on the subscription server create locking and
blocking issues?
I have 2 servers set-up with transactional replication. We have scheduled
reports running on the subscribing server, most of those reports using views.
The views seem to create performance, locking and blocking issues.
Thank you very much in advance.
Message posted via http://www.droptable.com
Yes they can. You might want to consider using indexed views depending on
your version and edition of SQL Server.
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
"Frank N via droptable.com" <u10790@.uwe> wrote in message
news:5a40019273826@.uwe...
> Quick question, do views on the subscription server create locking and
> blocking issues?
> I have 2 servers set-up with transactional replication. We have scheduled
> reports running on the subscribing server, most of those reports using
> views.
> The views seem to create performance, locking and blocking issues.
> Thank you very much in advance.
> --
> Message posted via http://www.droptable.com
|||A (non-dirty) read of data takes out a shared lock and this is not peculiar
to views. If these views are used for reporting purposes, you might want to
have a replica to be used for reporting eg using transactional replication,
or database mirroring with database snapshots.
Alternatively you could allow dirty reads (NOLOCK) on the tables in
question - depends on the business constraints.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||So if we place (nolock) hints on all the tables in all the views that should
eliminate the locking?
Paul Ibison wrote:
>A (non-dirty) read of data takes out a shared lock and this is not peculiar
>to views. If these views are used for reporting purposes, you might want to
>have a replica to be used for reporting eg using transactional replication,
>or database mirroring with database snapshots.
>Alternatively you could allow dirty reads (NOLOCK) on the tables in
>question - depends on the business constraints.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Message posted via http://www.droptable.com
|||Yes - or more easily set the transaction isolation-level to read
uncommitted.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Friday, March 23, 2012

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:

Saturday, February 25, 2012

replication locking (sql2000 / transactional)

Currently, we have a table replicated from one database to another on the
same server.
If I update 127 records based on the primary key, the subscription process
locks all the records until all the 127 commands have been executed.
Seamingly regardless of how they were updated.
eg.
CREATE TABLE Orgs ( OrganisationID INT PRIMARY KEY CLUSTERED ,
TimeLastUpdated DATETIME NOT NULL )
DECLARE @.SelectedOrgs ( OrganisationID INT PRIMARY KEY CLUSTERED )
/* ... insert 127 organisations to update here ... */
-- Process Type 1
UPDATE Orgs SET TimeLastUpdated = GETDATE()
FROM Orgs INNER JOIN @.SelectedOrgs SO ON Orgs.OrganisationID =
SO.OrganisationID
-- Process Type 2
WHILE ( blah )
BEGIN
-- one at a time in asc order
SELECT @.OrgID = OrganisationID FROM @.SelectedOrgs WHERE ( blah )
UPDATE Orgs SET TimeLastUpdated = GETDATE() WHERE OrganisationID =
@.OrgID
END
Using the default isolation level and no explicit transactions the table in
the subscriber database was locked until the commands had all been executed.
What gives - both types lock the whole table on the subscriber.
Surely it's not meant to use the same locks as were applied when the data
was updated?
Is there any to stop it locking the whole thing while it processes the
commands?
Ideally we'd upgrade to 2k5 and use row versioning... but that wont happen
for a while.

I need to implement a similar thing on a much larger database which needs to
be up 24/7 and can't be locked for long periods of time, mean while on the
publisher, 5% of the data may be changed in one go - but if we can't get it
to use optimistic locking on the subscriber we can't use replication.
Rebecca,
an update statement always has an implicit transaction. if you use
lumigent's Logexplorer you can see how this works. Your update statement will
be logged to the transaction log as :
BEGIN TRAN
Update Orgs SET TimeLastUpdated = GETDATE() where OrganizationID = 1
Update Orgs SET TimeLastUpdated = GETDATE() where OrganizationID = 2
..
..
..
Update Orgs SET TimeLastUpdated = GETDATE() where OrganizationID = 127
COMMIT TRAN
This is read by the log reader agent and converted to stored procedure
calls, but also with the transaction (AFAIR same XACT in msrepl_commands) .
The distribution agent applies it in the same way.
The table itself shouldn't be locked (lock escalation shouldn't occur on
such a low # rows), and inserting a record at the same time as the
distribution agent runs should prove this to be true.
Anyway, to answer your question more directly, -MaxCmdsInTran should allow
you to break the ACID properties and commit separate parts of the update as
separate transactions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)