Small table escalating to table locks
SQL Server 2000:
We have a combination of about 15 stored procs which all accessed the same set of temp tables (created in the main proc).
When we upgraded to 2000, the usage of temp tables was killing us on performance.
We made physical tables to get away from the temp tables.
These physical "staging" tables are only in use for as long as the procs need them. Each table has a SPID column that represents the session number (and the SPID is part of the primary clustered index).
Every update/insert/delete on the table references that SPID.
At any one time, there would only be 500 rows, maximum, in this table. And that only would be the case if multiple users were calling the procs at the same time.
The problem: Deadlocks.
Every once in a while, we deadlock.
In the procs, every select statement or join statement on these tables uses NOLOCK. Every insert, delete or update uses ROWLOCK.
We shouldn't be deadlocking unless things are escalating to page or table locks.
I suspect that since our updates, inserts and deletes actually affect a large percentage of data, that it may be escalating the locks.
Does anyone have suggestions? We can't go back to using temp tables. That was causing things to slow down from 4 seconds to 45 seconds, in some cases.
We have a combination of about 15 stored procs which all accessed the same set of temp tables (created in the main proc).
When we upgraded to 2000, the usage of temp tables was killing us on performance.
We made physical tables to get away from the temp tables.
These physical "staging" tables are only in use for as long as the procs need them. Each table has a SPID column that represents the session number (and the SPID is part of the primary clustered index).
Every update/insert/delete on the table references that SPID.
At any one time, there would only be 500 rows, maximum, in this table. And that only would be the case if multiple users were calling the procs at the same time.
The problem: Deadlocks.
Every once in a while, we deadlock.
In the procs, every select statement or join statement on these tables uses NOLOCK. Every insert, delete or update uses ROWLOCK.
We shouldn't be deadlocking unless things are escalating to page or table locks.
I suspect that since our updates, inserts and deletes actually affect a large percentage of data, that it may be escalating the locks.
Does anyone have suggestions? We can't go back to using temp tables. That was causing things to slow down from 4 seconds to 45 seconds, in some cases.
ASKER
I had considered that. However, if it was about to escalate to a page lock, then it would be escalated to a table lock.
Nothing in the BOL indicated that it would force it to remain at row level locking.
Nothing in the BOL indicated that it would force it to remain at row level locking.
KLRDER,
what is your transaction isolation level ?
Do you work with COM+ (if so the default transation isolation level is serializable - that can explain why you have table lock).
Do indexes exist on the table ? ( I've seen that SQL does table lock even in read-commited while there is no index on the table).
Why "the usage of temp tables was killing us on performance" ?
Uri
what is your transaction isolation level ?
Do you work with COM+ (if so the default transation isolation level is serializable - that can explain why you have table lock).
Do indexes exist on the table ? ( I've seen that SQL does table lock even in read-commited while there is no index on the table).
Why "the usage of temp tables was killing us on performance" ?
Uri
ASKER
We are using com+. The open method of the Recordset explicitely uses adLockReadOnly as the lock type.
Indexes do exist on the table. In fact we always access the table with the primary clustered index.
I have no idea why it changed from SQL Server 7 to 2000. I just know that when we converted, the performance of temp tables immediately degraded significantly.
When we performed tests on procs that utilized temp tables, the performance was significantly impacted when more than one user called the same proc at the same time. In fact, if we had several users calling the same proc at the same time, response would degrade to over four times as slow. This was not the case when we changed those same procs to use table variables.
Indexes do exist on the table. In fact we always access the table with the primary clustered index.
I have no idea why it changed from SQL Server 7 to 2000. I just know that when we converted, the performance of temp tables immediately degraded significantly.
When we performed tests on procs that utilized temp tables, the performance was significantly impacted when more than one user called the same proc at the same time. In fact, if we had several users calling the same proc at the same time, response would degrade to over four times as slow. This was not the case when we changed those same procs to use table variables.
If you can use table variable, why don't use it instead of physical table ?
When you inserting / deleting rows from the table, what is the lock type ? (com+ default ?)
I agree with your essumption that not allowed page lock won't solve the problem, that's why I think the problem happen while one user insert / update and the other try to select.
adLockReadOnly doesn't mean to skip locked records, just no to lock the records been read.
You can give a hint on the query to skip locked records.
Also, is the cursor is dynamic or static and forward-only ?
Uri
When you inserting / deleting rows from the table, what is the lock type ? (com+ default ?)
I agree with your essumption that not allowed page lock won't solve the problem, that's why I think the problem happen while one user insert / update and the other try to select.
adLockReadOnly doesn't mean to skip locked records, just no to lock the records been read.
You can give a hint on the query to skip locked records.
Also, is the cursor is dynamic or static and forward-only ?
Uri
ASKER
We can't use table variables because the tables in question have to be updated/read from all procs called by the main proc. You can't do that with table variables.
All of our select queries on those tables use WITH (NOLOCK) and all of our updates/inserts/deletes use WITH (ROWLOCK).
The cursor is forward only.
All of our select queries on those tables use WITH (NOLOCK) and all of our updates/inserts/deletes use WITH (ROWLOCK).
The cursor is forward only.
Have you considered pinning the tables with:
DBCC PINTABLE ( database_id , table_id )
You could write a sql agent job executes whenver SQLAgent Starts that will pin the tables for you. This should drastically reduce the I/O wait and thus keep deadlocking from occuring.
DBCC PINTABLE ( database_id , table_id )
You could write a sql agent job executes whenver SQLAgent Starts that will pin the tables for you. This should drastically reduce the I/O wait and thus keep deadlocking from occuring.
ASKER
Since that would require a restart to the server if the table ever grew (through a bad query or whatever) beyond the cache, we would not consider that option.
It is a very good suggestion, however.
It is a very good suggestion, however.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
KLRDER:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
ASKER
Hellooooooo Mr CleanupPing. Points were already refunded and question closed.
Just curious KLRDER. How did you eventually solve the problem?
ASKER
It took some thinking because we haven't had that problem in a long time.
But...
Ok, the problem stemmed from the component calling the procs. We found it when a trace was performed in production and there were no table lock escalations. Therefore, it only stood to reason that it was a transaction lock.
We researched the component calling the procs and found that it had been written to issue an MTS transaction prior to calling the proc (the proc was only meant to return data). Once that transaction was removed from the component, we've had not deadlocking issues since.
But...
Ok, the problem stemmed from the component calling the procs. We found it when a trace was performed in production and there were no table lock escalations. Therefore, it only stood to reason that it was a transaction lock.
We researched the component calling the procs and found that it had been written to issue an MTS transaction prior to calling the proc (the proc was only meant to return data). Once that transaction was removed from the component, we've had not deadlocking issues since.
Yeah that was over a year ago. Interesting . . seems that we were all misguided by the assumption that it was in fact a table lock that was causing the problem, and the challange of keeping that from happening. Anyway, sounds like some pretty nifty detective work . . . don't you love this job?
ASKER
Hee hee. Yep.
exec SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
Unfortunately, SQL server doesn't allow this for Table locks. But if it's page locks that are giving you the trouble, this may fix it.