Eric:

Our application is a web based. We have quite a few tables in our database. My concern is with the two tables namely Response and Load which causes blocking.

Response -------- ResId int (Identity column) SurId int SerId int Rescontact varchar(255) ResPassword varchar(10) ResGlobalId decimal(18) ResFlag int ResLoadDate datetime

Load ---- SurveyCode varchar(8) GlobalId decimal(18) Contact varchar(255) MailingId int DispatcherId int Password varchar(10)

Millions of rows are loaded (using insert and select) from the table Load to Response by calling a Stored Procedure. What it does is selecting SurveyCode, GlobalId, Contact and MailingId and inserting into Response table. Then it updates Load table setting DispatcherId = ResId and Password = ResPassword. While the process is going on, end users would keep inserting and updating the Response table. As a result load process blocks the end users. However we have minimized the blocking, I am seriously considering of change in design. Even Microsoft has confirmed that the maximum lock threshold is 765.

I am seriously thinking of redesigning. Any help coming up with better design would be highly appreciated.

Thanks in advance,

Kothan.

-- Anonymous, January 21, 2000

Answers

Kothan,

I don't think you have been advised correctly by Microsoft. I think you can set the Lock Escalation (LE) threshold as you desire. Check out the SQL Server Books Online article, "locks Option". It states:

"locks is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change locks only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server."

You would then want to consider changing the lock options, LE threshold maximum, LE threshold minimum, and LE threshold percent.

If changing the lock options does not work for you, rewrite your Stored Procedure to process a small number of rows, say 500, at a time.

Good Luck,

Eric

-- Anonymous, January 27, 2000