How to change locking strategy in SQL Server?

Question

I've read articles like these: http://www.codinghorror.com/blog/archives/001166.html http://www.databasejournal.com/features/mssql/article.php/3566746/Controlling-Transactions-and-Locks-Part-5-SQL-2005-Snapshots.htm And from what I understand, SQL Server has a very pessimistic locking strategy. And to improve performance, I should change the locking Read Committed Snapshot. But I can't find where to do this. Where do I change the locking strategy?

Accepted Answer

You can read up on Using Row Versioning-based Isolation Levels with examples on how to set them using the ALTER command.

It is set at the database level as follows:

```
ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;
```

A better starting point is the parent of the above documentation, which covers related topics: Row Versioning-based Isolation Levels in the Database Engine.

EDIT: added links mentioned in my comments below.

- DBCC USEROPTIONS - determine current isolation level

- Controlling Transactions and Locks Part 5: SQL 2005 Snapshots - explains read committed snapshot and the isolation level.

How to change locking strategy in SQL Server?

Solution

Problem