Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Locks on tempdb objects Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 23, 2013 6:21 AM
Points: 4, Visits: 43
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.
Post #1445650
Posted Tuesday, April 23, 2013 1:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1445656
Posted Tuesday, April 23, 2013 2:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 23, 2013 6:21 AM
Points: 4, Visits: 43
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).
Post #1445675
Posted Tuesday, April 23, 2013 2:24 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1445679
Posted Tuesday, April 23, 2013 2:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 23, 2013 6:21 AM
Points: 4, Visits: 43
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.
Post #1445688
Posted Tuesday, April 23, 2013 3:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:48 AM
Points: 23,400, Visits: 32,257
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1445694
Posted Tuesday, April 23, 2013 3:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1445704
Posted Wednesday, April 24, 2013 12:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 23, 2013 6:21 AM
Points: 4, Visits: 43
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).
Post #1445784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse