Maria's journey in the data fields

Did you know, that when you use READ UNCOMMITTED isolation level or WITH (NOLOCK) hint inside the query, it is still places Sch-S (schema stability) locks during compilation and execution and can be blocked by any concurrent trancastion that that holds a Sch-M (schema modification) lock on the table! So, when using READ UNCOMMITTTED isolation level, keep in mind that READUNCOMMITTED and NOLOCK hints apply only to the data locks and there is nothing you can do with Sch-S lock.

If you ever wondedered what king of locks your selct query ever issues and for how long, run it in QA environment with isolation level Repeatable Read, which holds all locks you select issues till the end of transaction. Default isolation level Read Committed keeps query locks only till select query has finished running so in most cases you are not able to catch them in sp_lock if your select works fast enough. So, add"SET TRANSACTION ISOLATION LEVEL REPEATABLE READ" inside transaction, then open another window and check out EXEC sp_lock and in some cases you will be surprized how many locks sql server places and removes during simple queries.