Something about SQL Server Isolation Level

  As we all know Oracle's default transaction isolation level is Read Committed. That means you can read data from changes made by other session as long as it's committed. Readers are not blocking writers and vice versa.

   The default isolation level of MS SQL Server is also Read Committed, however, from time to time our users saw their select blocked by other sessions and after they added NOWAIT hint, you get these errors
  Lock request time out period exceeded

  I did a quick testing and confirmed the observation. A select statement will be blocked by uncommitted transaction on underlying table and if put NOWAIT hint, the query will exit will the error. 

  

  There's another hint NOLOCK in SQL Server, however, that's definitely not preferable option, because the behavior of NOLOCK is same as setting transaction isolation level to READ UNCOMMITTED. Which mean you could read dirty data that changed by other session but not committed yet.

  

  After a little digging, I found SQL Server documented this behavior. Basically you need to set database option READ_COMMITTED_SNAPSHOT to ON, by default it was set to OFF.

  select * from sys.databases

  The SNAPSHOT serving the similar function as Oracle Rollback segment, holding pre-image of changes made by transaction. I am surprised SQL Server by default setting it to 0 (OFF) which essentially disabled default isolation level READ Committed. Means all read will require shared lock on table and if there's open transaction, the read is blocked.

 http://technet.microsoft.com/en-us/library/ms173763.aspx

  Of course the quick fix is set to READ_COMMITTED_SNAPSHOT to ON which can be done at database level of SQL Server.
  ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON

Quick follow up on this, as expected there's performance penalty by turning on Read Committed Snapshot. A blog talked about this a while ago. Apparently, SQL Server does not improve much in the last few years. 
Clearly SQL Server is not suitable for high volume read/write load.  Small Biz and office use please.

Performance Impact: The Potential Cost of Read_Committed_Snapshot