Re: Deadlock Issues When performing reports on subscriber

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:B006BDC6-5B21-4E3E-97D7-80AB6BB6056B@xxxxxxxxxxxxxxxx
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?


.

Relevant Pages

  • Re: Identity Column
    ... the end use is using Crystal Reports - where is the magic? ... The user will connect using Active Directory from their desktop to the database server or do please explain the magic.... ... Using stored procedures you permission on the stored proc only; the user can only execute the stored procedure with the parameters defined, there is no select * from to get all the information in your database. ...
    (microsoft.public.sqlserver.programming)
  • Re: Data Transformation in Replication
    ... Make sure you uncheck the Create the stored procedures during the initial ... I believe so, however, replication processes may be better tuned for this ... Should I create trigger on the reporting server and have the replication> procedure only do the insert and trigger do rest of the business rules> implementaion. ...
    (microsoft.public.sqlserver.replication)
  • Re: Debating Views vs Stored Procedures for reports and applications
    ... I choose usp's (user stored procedures) because I can send back multiple ... ProductName from dbo.Products p where exists (select null ... Now I use nested repeaters alot with basic reports. ... Since I have a list of all Customers, I'm able to display all Customers ...
    (microsoft.public.sqlserver.programming)
  • Re: stack overflow locking sysprocplan
    ... When users run reports in Cognos's Impromptu that use views with stored procedures calls with them, the user will lock sysprocplan and prevent other users from doing anything. ... IBM support said the cause of the locking of sysprocplan we are ...
    (comp.databases.informix)
  • Re: Best replication model?
    ... I think you should look at extended stored procedures or even the CLR ... Looking for a SQL Server replication book? ... Then any> updates to the db produced would get replicated back to all the other> databases. ... I was thinking Queued Updating Transactional. ...
    (microsoft.public.sqlserver.replication)