server Views causing blocking

We have 2 SQL Server 2000 machines. One has a star-schema data
warehouse with fact table, dimension tables, etc. The other SQL Server
has a data mart. The second SQL Server has a linked server set up to
the first server and we have views that grab information from some of
the dimension tables.
Occasionally, when we are doing stuff to the fact table in the data
warehouse (inserts, drop indexes, etc), and we try to do a select from
one of the views on the other sever that only select against the
dimension tables, it causes blocking.
Does it make sense that a linked-server view accessing one table could
cause blocking for an action going against another table?Sure, that makes perfect sense. The view applies a shared lock while
reading the data, and it's blocking your writes.
Unfortunately, there's not much you can do, if you're performing data
modifications during production hours. In a lot of DW scenarios, data would
be loaded at an off-time (e.g. in the middle of the night), and data would
not change at all during the day. In those situations, blocking is not an
issue. In more real-time situations, when working with SQL Server 2000, you
have a choice: Do I want to increase concurrency (decrease blocking), or do
I want to make sure all results are consistent (that is, return committed
data)? If you don't care about consistency, you can try the READ
UNCOMMITTED isolation level, which does not take locks for reads or honor
locks for writes. The danger is that you can get some data back that may not
be consistent -- for instance, if you're doing a query to return both detail
and aggregate data, and an update is occurring during the query, your
aggregation and detail data may not match. Some applications can live with
this, however...
Your other choice may be to replicate or log ship the data over to your data
mart instead of relying on the linked view. Would that be an option for
you?
Luckily, in SQL Server 2005, this will become much less of a problem thanks
to the SNAPSHOT isolation level, which provides similar non-blocking
benefits to the READ UNCOMMITTED isolation level, with consistent results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
<tadams@.generalcable.com> wrote in message
news:1123849107.004880.142490@.o13g2000cwo.googlegroups.com...
> We have 2 SQL Server 2000 machines. One has a star-schema data
> warehouse with fact table, dimension tables, etc. The other SQL Server
> has a data mart. The second SQL Server has a linked server set up to
> the first server and we have views that grab information from some of
> the dimension tables.
> Occasionally, when we are doing stuff to the fact table in the data
> warehouse (inserts, drop indexes, etc), and we try to do a select from
> one of the views on the other sever that only select against the
> dimension tables, it causes blocking.
> Does it make sense that a linked-server view accessing one table could
> cause blocking for an action going against another table?
>|||Adam,
Thanks for the advice.
I will look into whether we can use the READ UNCOMMITTED isolation
level.
We are also looking at moving the dimensions local to each server. Not
what we would like to do, but it may be our only hope of improving
performance.
Thanks,
Tommy