Hi Friends and Geeks, I have few issues with table locks while executing queries and stored procedures. Though the queiries have just select statements with nolock option and stored procedures with "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" option still I have serious problems with table locks. Specially stored procedures are awaiting long time to recompile while tables are locked and that caused time outs of my queries which is 30 seconds in my application. I would really appreciate if anyone can share your knowledge or insight on this topic. If you can send me any documentation or links about "stop table locks" while executing select queries and "stop recompilation of stored procedures". Thanks, Bhushan Bhushan Kalla
Check: http://www.sql-server-performance.com/at_sql_locking.asp http://www.sql-server-performance.com/reducing_locks_hints.asp Luis Martin Moderator SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason. Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte All postings are provided “AS IS†with no warranties for accuracy.
Locking is a natural element of RDBMS and needs to happen. If you experience really serious problems, we need more informations in order to help you. -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Heute schon gebloggt?http://www.insidesql.de/blogs
Since you claim that all selects use dirthy read, the reason might be that you have one or more insert/update/delete operations accessing majority of rows in the table so sql server escalates locks and locks the table eventually. Another reason may be lack of proper index(es) so even if small % of rows is affected, there is no other choice then to scan entire table in order to find rows specified by the where clause. Also make sure where statetments use sargable conditions, otherwise table scan may be necessary even though indexes are in place. As Frank mentioned, for more specific recommendation we need more info.
Also determine SQL memory settings and any other applications sharing the server resources. 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.
quote:Originally posted by iambhushan Specially stored procedures are awaiting long time to recompile while tables are locked and that caused time outs of my queries which is 30 seconds in my application.I've missed that point. If I got the explanation right, that usually happens when you create temporary table with "select ... into" statetment. During the period data are populated, tempdb remains locked. If that's the case use "create table" then "insert into".
