Search code examples
sql-serverperformancelockingdeadlock

How to change locking strategy in SQL Server?

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?

Solution

  • 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.