Re: strange Problem
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 30 May 2008 21:26:27 +0000 (UTC)
DaveL (dvs_bis@xxxxxxxxxxxxx) writes:
Hello, hope u can help or point me in the right direction
I have 2 Services on our production System
1 Service is Vb6 (not the problem)
2 nd service is on another maching c# (not the problem)
these service insert and update a common Table
that links 2 separate databases (and there tables)
database1 and associated tables
database2 and associated tables
main database table PK
ive set up local running the services 30,000 files both services on
separate machings hitting the same table
this takes place in a Stored Proc
but in Production it locks up the system with a blocking
so web site is usless and internal processing is Useless
In the c#side and proc that i uses ive removed all begin trans, and on the
updates and selects i use (nolock)
and updates use (rowlocks)
still the same problem in production
First put back those BEGIN TRANSACTION, and remove those NOLOCK, so
that you don't put the integrity of your database at stake.
Next, you need to analyse what is blocking what, and what the resources
the blocked processes are waiting for. Presumably, you need to review
your indexing.
On my web site you can find a stored procedure beta_lockinfo that
helps you to analyse to your blocking:
http://www.sommarskog.se/sqlutil/beta_lockinfo.html.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- strange Problem
- From: DaveL
- strange Problem
- Prev by Date: Re: SQL Server 2000 UDF Intermittent Slow Execution
- Next by Date: Re: Modeling/Constraint question
- Previous by thread: strange Problem
- Next by thread: Strange select
- Index(es):
Relevant Pages
|
