SQL Server Performance Forum – Threads Archive
Locking/Isolation Level
Hello, Our analysts run stored procedures that update their own tables in their work database but reference the production schema as part of the update qualification. The production schema is a data mart and so is updated during non working hours. UPDATE ANALYST_TABLESET COLUMN_A = ‘Y’
FROM ANALYST_TABLE A,
PROD_DB..PROD_TABLE1 B,
PROD_DB..PROD_TABLE2 C
WHERE A.ID = B.ID
AND B.ID = C.ID
AND C.TYPE = ‘A’ We are seeing a lot of locking going on against the production tables. The analysts cannot manipulate the production data, only select. The production schema is a data mart and so is updated during non working hours. We are thinking about having the analysts set any production tables to NOLOCK, or change their isolation level to something less restrictive. Any thoughts?
yep using NOLOCK will reduce the locking in the production tables… is there an index on prod_table2( type, id )? How many rows match that criteria type=A? Cheers
Twan
And how about DBCC checks against the database? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Yes you can use NOLOCK, but be aware that you may read some dirty data (data that has not been committed into production tables yet).
And what kind of locking are you observing on the production tables ? If all you are doing is reads from production tables, then all you should be seeing is shared locking and shared locks won’t block each other.
Agree with rest of the suggestions. Do you have appropriate indexes on production tables PROD_DB..PROD_TABLE1 and PROD_DB..PROD_TABLE2 on ID column? Creating an indexed view on the required data from the two tables may also help. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
]]>