DB Options :- Enabling Optimistic Concurrency in SQL Server
It’s very common for databases to reach its limit over time even with good indexing and hardware. At some point the database administrator is going to have to consider if using Optimistic Concurrency. Optimistic Concurrency is a feature that allows the database to reduce the overlap of concurrent read and write operations on the same data page. This is achieved by creating a copy of the page and storing it as a version store in the tempdb. This way each task gets access to its own version of the page as it was when the task (transaction) started.
Optimistic Concurrency does have some disadvantages such as the excessive load it can put on the tempdb. With sufficient effort put into optimizing the temp databases DBAs can achieved significant concurrency improvements despite this. This has been a solution of choice for a number of legacy systems where the cost of upgrading the hardware etc. is not worth it. In this video we cover the default behavior of SQL Server and why it has an impact on the performance. We then move on to enable Optimistic Concurrency by enabling ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT. Followed by how to identify the impact of using this feature on the temdb by querying the views dm_tran_version_store_space_usage and dm_db_file_space_usage. This is a valuable tool and every DBAs Arsenal and definitely worth exploring.
Need more information? Visit https://www.enabledbusinesssolutions.com
To know more about Jayanth Kurup visit http://www.jayanthkurup.com
Jayanth is a Microsoft SQL Server trainer / consultant with over 15 years’ experience. He has worked with companies like Microsoft, IBM, HCL, Wipro, Well Fargo, Zeomega and many more. He is based out of Bangalore and a very active member of the SQL Server Community.
In this channel he covers complex topics related MS SQL Server , PowerBI , Azure , AWS , Powershell, SSIS , SSAS , SSRS , Machine Learning , AI , etc
You can also reach Jayanth via his LinkedIn profile below / jayanth-k. .
music from HAAWK