Hi,
Difficult to answer as I don't know enough about the application and it's procedures. Possibly this link will help. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7mur.asp

One of the biggest headaches with any SQL is the design of the procedures for locking, especially under high transactional load.
With bad design the queue becomes full of BLOCKED procedures all waiting on other procedures to complete, it is a constant battle between speed/data integrity and usability.


>Hi,
>
>Thank a lot for your article link.
>BTW, I found that when my SP are running,
>some of the SP are causing lot of table & index locking in my Enterprise manger -> management -> Current Activity -> Lock process/objects
>
>It led me to look at SET ANSI_Defaults command issue.
>Mine DB default to setting the ansi_defaults to on which
>cause SET IMPLICIT_TRANSACTIONS ON. Could it be due to this
>setting ?
>
>I am wondering whether this setting should be ON or OFF ? Any recommend or advise for me. Thank you
>
>
>>Hi,
>>Sorry the link didn't stick, here it is http://support.microsoft.com/kb/224453/en-us
>>
>>>Hi,
>>>
>>>First, thank for your guide but which KB article are you referring to.
>>>Sorry for my ignorance.
>>>
>>>
>>>
>>>>Hi,
>>>>Working thru this KB article and it's links usually will identify the problem.
>>>>
>>>>
>>>>>Hi,
>>>>>
>>>>>Anyone faces this issue, of why the same SP in SQL2k SP4
>>>>>when initially run, is fast and okay.
>>>>>
>>>>>However, when i run it the second time or more, it just
>>>>>keep getting slower from 30seconds to 2minutes.
>>>>>
>>>>>Anyone know who or how it is happening.
>>>>>
>>>>>My SP just using a cursor and even when
>>>>>I change it to using loop, it does not
>>>>>help.
>>>>>
>>>>>Thank you