May 22, 2006 at 12:14 am
I've got some stored procedures that are responsible for inserting and updating data in my server. They get used by BizTalk so there is a potential for many instances of these stored procs to be running in a very small timeframe.
I am looking at ways to control the volume of information from BizTalk but would also like to make sure I have provided the best possible service from my stored procs.
Are there any best practices I should adhere to or at least try to follow.
What about some general Dos and Don'ts?
I am sure there are some cunning tricks out there that I am not aware of.
Cheers
Steve
May 23, 2006 at 1:34 pm
If you google SQL deadlock, you'll find a list of things to watch out for.
Top of the list:
1) Keep transactions as short as possible
2) Always access tables in same order, as much as possible.
3) If you're doing deletes as well, deadlocks will be unavoidable, especially if 1) and 2) are not adhered to.
4) Make sure your statistics are up to date.
5) Make sure you have appropriate indexes. The maintenance on these will cost a little bit, in terms of perf (SQL Server should do this automatically). The payback is normally huge.
HTH,
Paul
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply