Hi,

Thank for the advise. However, I am lost at where to go about looking at each area to tune my SP. Any recommendation or advise for me on this area. Thank you

>>Hi,
>>
>>From the BOL, if set ansi_defaults is on, then SET IMPLICIT_TRANSACTIONS
>>is ON. However, it also mentioned that MS Ole DB provider for SQL server & ODBC will turn it off when connected.
>>
>>Therefore, when I am using Query analyzer to run my SP, is SET IMPLICIIT_TRANSACTIONS ON ?
>>
>>On the other hand, using 'DBCC useroptions' in QA, show that SET IMPLICIT_TRANSACTIONS is ON.
>>
>>How about my web and windows application when connecting to SQL2k SP4 ?
>>
>>It is because some of my SP show lot of tables locking in the enterprise manager -> management -> Currenty Activity -> blocking/object.
>>
>>Initially, my SP running fine and fast like less than a minute. However,
>>sequence running again, my SP will take 2-3 minutes.
>>Is above command causing my SP slowness. Please advise and comment. thank you
>
>V,
>
>There is a new 'feature' in SP4 concerning blocking. SP4 will show PAGELATCH blocking where before it would not be shown. It is not blocking more, only displaying what has always happened more accurately.
>
>I would look in other areas to improve the performance first.