June 19, 2006 at 2:02 pm
I'm not sure if the table variable is the cause, but I have about 9 update and delete statements that run against a table variable (the table variable contains less than 500 rows)and when I run ostress against this sproc i find alot of tempdb locks that is being recorder by another script that caputers the tempdb lock.
How can I reduce the number of tempdb locks? Let me know appreciate it.
June 19, 2006 at 2:23 pm
Locks are a necessary part of any update. Are there blocking / timeout issues?
June 19, 2006 at 2:56 pm
No i don't think so. I would have to ask my senior dba as I just started this new job. I am in the process of analyzing and trying to decrease the temp db locks.
So are you saying their isn't a way to reduce it?
June 19, 2006 at 3:14 pm
After over 12 years as a professional developer, I am very much of the opinion: "If it ain't broke, don't fix it."
Sure, there are measures one can take to reduce locking, but as I said, locks are a natural and necessary part of updating data.
It's not locks in and of themselves that need reducing, it's locking / timeout / contention / performance issues that should be your concern.
June 20, 2006 at 5:08 am
I agree with Pam that it may not need fixing. We do have some (rare) occaisions where the tempdb locks are causing memory issues, when this does happen (and only if) we add table lock hints (i.e. with tablockx). We are able to do this because we are running overnight updates in a data staging area so this does not affect users, we only do this where we need to as there is a threshold below which the change is detrimental to the process.
June 20, 2006 at 6:53 am
if your table variable is working in tempdb then you should replace it with a temporary table which has far superior data handling. Check out ms kb articles for differences with table variables and temp tables - I'm sure I saw this post already under another thread?????
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply