March 16, 2010 at 7:41 am
We are experiencing problems with transaction timeouts in our application.
After running n number of alter/insert/update commands within a transaction, we start experiencing select timeouts where n is some moderately large number and the selection criteria is very simple.
Is there any setting on SQL Server side that would limit the number of queries that can be run as part of a Transaction. Any setting in SQL Server that would increase the performance in this case?
(We are aware of the fact that limiting the scope of the transaction would solve this, but would like to check the alternate way)
We are using SQL Server standard on Win 2K3.
Thanks for your Time.
-RK
March 16, 2010 at 8:41 am
When you run an alter database, it needs to apply an exclusive schema lock for that table, that causes selects in other SPIDs to wait until the transaction commits, if besides alters you run updates you are adding more locks to this transaction, therefore affecting more SPIDs.
The easy solution is making sure that you commit every transaction as fast as you can, if you alter a table within a transaction, once it finishes commit it, run an insert/update/delete, make sure it did what it was intended to, and then commit or rollback in case you need to, but not leave transactions open for long periods.
If you are able to make this, the performance of your SQL Server will increase.
Frank.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply