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)