April 23, 2013 at 12:59 pm
Hi all.
I'm calling for your help please. I've a problem in production:
there's a transaction which is opened from the business logic (TransactionScope) and in some cases perfoms a lot of actions such as insert/update/delete by one row in some quantity (about 10) of tables in the DB1.
There's a block of methods which perfomes selects on DB2 (which tables aren't blocked by this transaction) in a middle of transaction before block of another few DML methods (so we can't complete tran).
So the problem is that these selects perfom very slowly (0.5 sec), meanwile the same without the scope of transaction and with the same plan perfoms very fast (30ms).
I think the key is in the large quantity of key locks (about 1.5million in sys.dm_tran_locks for specific session). Escalation can't be thrown because of key operations (but not about 5000 at time). So I'm trying to solve this situation by blocking a whole table before DML operation for that case so reducing amount of key-locks.
But the problem is that I can't (at this moment) eliminate a lot of key, page, object locks on tempdb. But according to the situation above I suggest that reducing locks in DB1 and the existence them in tempdb won't solve the problem.
And how do you think?
What should I do? Only to find the procedures with temp tables and change them to variables?
Thanks.
April 23, 2013 at 1:10 pm
I think a lot more information is needed to say anything useful. Oh, except for the fact that changing temp tables to table variables will do nothing for locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2013 at 2:14 pm
GilaMonster (4/23/2013)
I think a lot more information is needed to say anything useful. Oh, except for the fact that changing temp tables to table variables will do nothing for locks.
GilaMonster, thanks for answer. Which additional information shoud I provide to get help?
Nevertheless what about experience or some axioms: when you have a lot of fine-grained locks in transaction you'll get a problem with accessing data? 🙂
As for tables vs variables - why will that do nothing? sys.dm_trans_locks doesn't show as many locks as temp tables.
For example in my environment:
create table #tmp
insert into #tmp values()
produces 29 locks (excluding DATABASE level). At the same time table variables produces no locks at all (excluding DATABASE level).
April 23, 2013 at 2:24 pm
Eugene Davydov (4/23/2013)
GilaMonster (4/23/2013)
I think a lot more information is needed to say anything useful. Oh, except for the fact that changing temp tables to table variables will do nothing for locks.GilaMonster, thanks for answer. Which additional information shoud I provide to get help?
As much as possible.
Imagine someone was asking for your help in this but not allowing you to access the server.
Code if possible. A clearer explanation of what exactly the problem is. Any error messages, relevant wait types, etc, etc, etc
when you have a lot of fine-grained locks in transaction you'll get a problem with accessing data? 🙂
No. If there are too many locks, SQL will just escalate to a table lock unless escalation is disabled. Lots of locks can cause blocking, but that's about it.
As for tables vs variables - why will that do nothing? sys.dm_trans_locks doesn't show as many locks as temp tables.
Both temp tables and table variables are specific to the session that created them (ignoring global temp tables), hence any locks taken are fairly irrelevant as they will never block other users.
Table variables however have that whole 'no stats' problem, which could negatively impact query performance unless the table variables are very small.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2013 at 2:59 pm
GilaMonster (4/23/2013)
when you have a lot of fine-grained locks in transaction you'll get a problem with accessing data? 🙂
No. If there are too many locks, SQL will just escalate to a table lock unless escalation is disabled. Lots of locks can cause blocking, but that's about it.
I said that information: c# code calls in loop some simple procedures which perfoms only atomic insert/update/delete (by one row). There's no reason for escalation. More loop amount, more amount of locks in session (and - again - no escalation will trigger).
GilaMonster (4/23/2013)
Both temp tables and table variables are specific to the session that created them (ignoring global temp tables), hence any locks taken are fairly irrelevant as they will never block other users.
Table variables however have that whole 'no stats' problem, which could negatively impact query performance unless the table variables are very small.
I didn't speak about blocks. Only about locks placed by SQL Server and which I saw in sys.dm_tran_locks for specific session.
And because there're huge amount of fine-grained lock I see perfomance degradation in that session.
April 23, 2013 at 3:18 pm
Eugene Davydov (4/23/2013)
GilaMonster (4/23/2013)
when you have a lot of fine-grained locks in transaction you'll get a problem with accessing data? 🙂
No. If there are too many locks, SQL will just escalate to a table lock unless escalation is disabled. Lots of locks can cause blocking, but that's about it.
I said that information: c# code calls in loop some simple procedures which perfoms only atomic insert/update/delete (by one row). There's no reason for escalation. More loop amount, more amount of locks in session (and - again - no escalation will trigger).
GilaMonster (4/23/2013)
Both temp tables and table variables are specific to the session that created them (ignoring global temp tables), hence any locks taken are fairly irrelevant as they will never block other users.
Table variables however have that whole 'no stats' problem, which could negatively impact query performance unless the table variables are very small.
I didn't speak about blocks. Only about locks placed by SQL Server and which I saw in sys.dm_tran_locks for specific session.
And because there're huge amount of fine-grained lock I see perfomance degradation in that session.
And the locks are held for the duration of the transaction. If the application controls the transaction, and a single transaction consists of multiple loops calling the stored procedures, then each lock taken will be held until the applicatioin commits or rollsback the transaction. This also means that your transactioin time includes the time it takes to make each round trip to the server until all inserts/updates/deletes are completed.
April 23, 2013 at 3:37 pm
Eugene Davydov (4/23/2013)
I said that information: c# code calls in loop some simple procedures which perfoms only atomic insert/update/delete (by one row). There's no reason for escalation. More loop amount, more amount of locks in session (and - again - no escalation will trigger).
If they aren't running in a transaction, those locks will be released as soon as the insert/update/delete completes, so there won't be more and more locks. If they are in a transaction, then they're eligible for lock escalation as soon as you have a certain number of locks on the same table unless lock escalation has been disabled.
And because there're huge amount of fine-grained lock I see perfomance degradation in that session.
Lots of locks, by themselves should not cause performance degradation.
Are you seeing high waits? If so, what type?
Are you seeing high CPU time?
Are you seeing high signal wait times?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2013 at 12:47 am
GilaMonster (4/23/2013)
If they aren't running in a transaction, those locks will be released as soon as the insert/update/delete completes, so there won't be more and more locks. If they are in a transaction, then they're eligible for lock escalation as soon as you have a certain number of locks on the same table unless lock escalation has been disabled.
Make a simple test in Management Studio with loop of 10000 times and any insert or update of on one row and you will see that there's no escalation occurs - because of no threshold (5000 rows) in one statement crosses.
GilaMonster (4/23/2013)
And because there're huge amount of fine-grained lock I see perfomance degradation in that session.
Lots of locks, by themselves should not cause performance degradation.
Are you seeing high waits? If so, what type?
Are you seeing high CPU time?
Are you seeing high signal wait times?
High CPU time and the same duration in simple statement which without of transaction perfoms considerably faster (about 10 times) with the same plan (plan is as simple as possible).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply