July 17, 2007 at 11:53 am
I have a stored procedure that runs about every fifteen minutes that does some updates, inserts and deletes on an order table. About once a day this procedure will cause some blocking issues. I have added the WITH(NOLOCK) query hint but that has not solved the problem.
I was wondering if I should set the isolation level to something like SNAPSHOT. I am not an expert at this subject and do not want to cause different problems with this particular stored procedure.
Any help would be greatly appreciated.
July 17, 2007 at 12:38 pm
Hi There,
I'm not a pro at this either but have come across this problem before myself.
Can you find out if it is the same code that is blocking with this procedure.
You may find that the procedure that is blocking/blocked by the above code maybe the same all the time. In which case you could look to optimise the other procedure, if you can allow dirty reads in the other procedure then with (nolock) in that code may help the above procedure run better.
Another thing to look at is the follow:
Are you update delete and insert statements large(e.g more than 1 row at a time) if so can you not put them in a loop so that they do 1 at a time this locks up the table for smaller sections of time. Also do you have loads of triggers on your tables that can be changed or optimised?
Just a few things to look at and think about.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 18, 2007 at 1:31 pm
I just realized I put this in the wrong section. I will repost this to the appropriate section. Sorry
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply