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