Table Locking

Question

Post reply

Table Locking

Sean Bowen

SSC Enthusiast

Points: 189
More actions
November 18, 2005 at 8:27 pm

#169757

Good day everyone,
I am a SQL Server newbie and am a Systems Analyst employed with a Bank. We have a CRM application running on a SQL Server 2000 database. The problem I am seeking advise for is at times when we are doing our batch uploads into the database, we are experiencing some table locking which in turns prevents other application from effectively querying the database. This problem results in customers being inconvienced.
Can someone give me some advise on how to trace the source of the locking or can point me to any scripts that will detect a lock and send an email to relevant persons so that the issue can be looked at?
Thanks a lot

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Bowen SSC Enthusiast Points: 189 More actions · Answer 1

Sean Bowen

SSC Enthusiast

Points: 189

November 19, 2005 at 6:50 pm

#604806

No one can help?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Microsoft's PSS usually directs you here:

How to monitor SQL Server 2000 blocking (271509)

There is a new tool, called Sherlock, which takes the information from the sp_blocker_pss80 stored procedure in that KB article and helps analyze it. I'll admit that I haven't had an opportunity to test Sherlock as of yet, but you may find it of use.

You can find the download at SQLTeam.com: http://www.sqlteam.com/downloads/sherlock13.zip

K. Brian Kelley
@kbriankelley

Sean Bowen SSC Enthusiast Points: 189 More actions · Answer 3

Thanks for your help, do you think the procedure can be customized to send a mail notification once a block is detected?

magarity Ten Centuries Points: 1037 More actions · Answer 4

Have you considered locking by page or row instead of table? This needs a lot more overhead (especially by row) so your physical server needs to have plenty of spare capacity depending on the table size. But then only the pages or rows being updated are locked instead of the entire table.

Sean Bowen SSC Enthusiast Points: 189 More actions · Answer 5

You see the application was actually developed by a third party vendor so I dont know for sure what locking mechanism is being used and we no longer do business with the vendor so support is minimal.

But at times when the upload process is underway, no other application seems to be able to get adequately query the tables, this causes problems in our environment.

Any suggestions?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

The application works by putting osql into a loop. If it detects blocking it dumps it to a file... I suppose you could modify it to send an email, but it wasn't intended for that purpose. It was intended to gather information about blocking over a period of time so decisions on indexing, etc. can be made.

K. Brian Kelley
@kbriankelley

magarity kerns SSCrazy Points: 2763 More actions · Answer 7

How about this: Let the application upload to a temporary staging table and when its done, use a stored procedure or DTS package to insert from that table to the real table with row locking. How many rows are we talking about, and are they all inserts of new data or are there any updates of new data? You've referred to 'inserts' but I don't want to assume there aren't any updates in the mix.