Deadlock Issues When performing reports on subscriber

We have transactional replication being performed from our TX Database Server
to our Reporting Database Server. All of our reports are run off of our
Reporting Database Server. We have no queries that are performing Update or
Exclusive locks except for the sp_MS stored procedures being used for the
Transactional replication.
I'm trying to identify the root cause of the issue. We don't want to apply
the trace flags to our production servers for capturing the deadlock
information. We have the profiler gathering all the report requests and we
were even lucky enough to capture a deadlock victim error, but we don't have
the process that was allowed to continue. We also have a job set up to
report spids that are marked as blocked, but we didn't have this set up in
time to catch our last deadlock. We experience most our deadlocks on days
that we have high transaction volume (Fridays) and also days that reports are
more frequently requested (Mondays).
Potential Issue #1:
Our reporting server is a Dell PowerEdge 1750, 2 Processors, and only 2GB
RAM. We are looking at upgrading this to a HP 4 Proc dual core with 16GB RAM
which I'm sure will help out but I don't want to just mask what the real
issue is. I'm thinking potentially there could be lock escalation occurring
during high TX volume days by the replication sp_MSIns* procedures due to the
fact that we only have 2GB RAM. Does this sound likely?
Potential Issue #2:
After analyzing one of the reports I have noticed that the query is
accessing a view that has a union all. We cannot index this view due to the
union all, so a table scan is being performed on close to 4 Million rows. My
idea is to break this into two different views and then index each view by
order date to shorten the duration of the Shared Lock. Could the length of
time the Shared Lock being held be the main reason for our deadlock issues
especially on high volume TX days? I'm also considering either setting the
reports Isolation level to Read Uncommitted or use the ReadPast hint to avoid
the Shared Lock all together.
Any other suggestions?
I would have a look at replicating the execution of stored procedures and
using the nolock hints.
run sp_replcounters to get an idea of how many commands per transaction you
have. If its a one to one, replicating the execution of stored procedures
won't help you much, but if its out by several orders of magnitude you will
get a huge improvement in performance and considerably reduced locking.
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
"Larry Herbinaux" <LarryHerbinaux@.discussions.microsoft.com> wrote in
message news:B006BDC6-5B21-4E3E-97D7-80AB6BB6056B@.microsoft.com...
> We have transactional replication being performed from our TX Database
> Server
> to our Reporting Database Server. All of our reports are run off of our
> Reporting Database Server. We have no queries that are performing Update
> or
> Exclusive locks except for the sp_MS stored procedures being used for the
> Transactional replication.
> I'm trying to identify the root cause of the issue. We don't want to
> apply
> the trace flags to our production servers for capturing the deadlock
> information. We have the profiler gathering all the report requests and
> we
> were even lucky enough to capture a deadlock victim error, but we don't
> have
> the process that was allowed to continue. We also have a job set up to
> report spids that are marked as blocked, but we didn't have this set up in
> time to catch our last deadlock. We experience most our deadlocks on days
> that we have high transaction volume (Fridays) and also days that reports
> are
> more frequently requested (Mondays).
> Potential Issue #1:
> Our reporting server is a Dell PowerEdge 1750, 2 Processors, and only 2GB
> RAM. We are looking at upgrading this to a HP 4 Proc dual core with 16GB
> RAM
> which I'm sure will help out but I don't want to just mask what the real
> issue is. I'm thinking potentially there could be lock escalation
> occurring
> during high TX volume days by the replication sp_MSIns* procedures due to
> the
> fact that we only have 2GB RAM. Does this sound likely?
> Potential Issue #2:
> After analyzing one of the reports I have noticed that the query is
> accessing a view that has a union all. We cannot index this view due to
> the
> union all, so a table scan is being performed on close to 4 Million rows.
> My
> idea is to break this into two different views and then index each view by
> order date to shorten the duration of the Shared Lock. Could the length
> of
> time the Shared Lock being held be the main reason for our deadlock issues
> especially on high volume TX days? I'm also considering either setting
> the
> reports Isolation level to Read Uncommitted or use the ReadPast hint to
> avoid
> the Shared Lock all together.
> Any other suggestions?
|||One thing to try is to use WITH (NOLOCK) option on your table selects.
Since you are not updating the data on the subscriber it may help. I
have banged my head against these union views in the past and its
always a killer. Also checking to make sure the underlaying tables
have indexes is another thing...
Hope it helps..
Aravin.
|||Union all is also faster than a union, but it may return duplicates.
Evaluate your union to discover if the potential for duplicates exist and
whether this is problematic for you.
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
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1176397959.616208.3310@.y80g2000hsf.googlegrou ps.com...
> One thing to try is to use WITH (NOLOCK) option on your table selects.
> Since you are not updating the data on the subscriber it may help. I
> have banged my head against these union views in the past and its
> always a killer. Also checking to make sure the underlaying tables
> have indexes is another thing...
> Hope it helps..
> Aravin.
>
|||Larry - I'd personally identify the cause of the deadlock before proceeding.
If you are using profiler then I'd turn it off and just set up the trace
flags. Are you using SQL 2005? If so you can use trace flag 1222 which is
lightweight and provides more easily digestible info than before.
Paul Ibison