Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning blocking LCK_M_SCH_S, LCK_M_SCH_M RE: blocking LCK_M_SCH_S, LCK_M_SCH_M

  • January 9, 2013 at 1:55 pm

    #1575434

    GilaMonster (1/9/2013)

    opc.three (1/9/2013)

    The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.

    Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.

    That's right. I was thinking of trying to explicitly set READ COMMITTED, which is not honored when READ_COMMITTED_SNAPSHOT is ON (i.e. DBCC USEROPTIONS still shows read committed snapshot). Setting READ UNCOMMITTED (and NOLOCK) would still be honored.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato