Trace Lock: Timeout 0 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Trace Lock: Timeout 0


Hi, I have captured the sqlserver activities in a trace file. On examining the trace file, I found that 1000s of entries
with LOCK: TIMEOUT with 0 duration. This happens when I
execute an Update statement which don’t have any LOCK hint. I have changed my query to use lock hint (TABLOCK) and running again. But, would like to know if Lock:Timeout 0 will cause a performance problem Thanks
Not a all.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
This even is that a request for a lock on a resource, such as a page, has timed out because another transaction is holding a blocking lock on the required resource. http://www.sql-server-performance.com/blocking.asp for your information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
hi i have some doubt… Is it good to set query timeout and lock timeout values using sp_configure
statemnt? krishna chaitanya.s
project engineer-SQLDBA "To be confident of what u r accepting",I would say,"U have got to understand what u r rejecting."

Hi,<br />its relavent to your application’s demand please search for for this and read relavent article on Vyas’s site ,<a target="_blank" href=http://www.vyaskn.tripod.com/watch_your_timeout.htm>http://www.vyaskn.tripod.com/watch_your_timeout.htm</a> to clear your doubts.<br /><br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami<br />
——————————————————————–
This even is that a request for a lock on a resource, such as a page, has timed out because another transaction is holding a blocking lock on the required resource
Satya SKJ
Contributing Editor & Forums Moderator ——————————————————————— Yes I agree with you sathya. I was running an update statement without giving
any locking hints.
This query created 9 Threads and I see huge occurrence of LOCK TIME OUT 0
I added lock hint (TABLOCK) and run the query….to my surprice…it is not there anymore and I see slight performance gain. Many a Thanks

http://www.sql-server-performance.com/rd_table_hints.asp fyi too, you might be risking the performance loss without mentioning any hints for an update query. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>