Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have been unable to find out if Sharepoint 2010 or 2013 (or even 2007) are able to take advantage of this MSSQL feature to improve the performance of the database operations invoked by Sharepoint and reduce the potential performace impact of locking when working with about 5000 rows (or more)

To minimize database contention, SQL Server often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it's more efficient for SQL Server to temporarily escalate the lock to the entire table until the database operation is completed. Note that the actual number is not always 5,000, and can vary depending on your site, the amount of activity in the database, and your site's configuration.

The performance impact of this locking should be minimized if the database is configured to use row versioning, also known as READ_COMMITTED_SNAPSHOT isolation:

Read committed isolation using row versioning is enabled by setting the READ_COMMITTED_SNAPSHOT database option ON. Snapshot isolation is enabled by setting the ALLOW_SNAPSHOT_ISOLATION database option on. When either option is enabled for a database, the Database Engine maintains versions of each row that is modified. Whenever a transaction modifies a row, image of the row before modification is copied into a page in the version store. The version store is a collection of data pages in tempdb. If multiple transactions modify a row, multiple versions of the row are linked in a version chain. Read operations using row versioning retrieve the last version of each row that had been committed when the transaction or statement started.

Do you know of any documentation, article or example that clarifies if read commited snapshot isolation is supported in Sharepoint?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

READ COMMITTED SNAPSHOT ISOLATION is not supported by SharePoint. Review the presentation below by Lisa Gardner from the PASS Summit back in 2012 for more details.

http://blogs.msdn.com/cfs-filesystemfile.ashx/__key/communityserver-components-postattachments/00-10-36-86-42/SQLNSharePoint_5F00_Frienemies.pptx

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.