Enabling Read Committed Snapshot Isolation in MS SQL server
In MS SQL environments enabling 'Read committed Snapshot isolation (RCSI)' is the first step in handling application performance and crash issues.
What is RCSI?
What is RCSI?
Since databases handle multiple transactions in parallel, there has to be a mechanism to handle concurrent reads and writes in the database. For example, if one transaction is running a update query and other transaction is executing a select query then, whether the select query should be able to see the newly updated value or the old value? Or should the select query transaction wait till the update query transaction completes? These are some of the questions that are answered based on the concurrency model that is being used. There are 2 concurrency models.
1. Optimistic concurrency control
2. Pessimistic concurrency control
In pessimistic model, transactions lock the rows that they are reading or modifying so that other transactions have to wait till this transaction completes to access the rows.
In optimistic model, the rows are not locked. Two transactions can simultaneously read/update a row. Only if there are any conflicting updates identified, then one of the transactions is rolled back. Since there is no locks involved in this model, this performs much better than the former.
Postgres database by default has optimistic model. SQL server on the other hand was originally designed using pessimistic model but due to performance problems in it an option was later provided in SQL server to enable optimistic concurrency control. This is called read committed snapshot isolation.
How to verify / enable RCSI
To check whether RCSI has been enabled:
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE [name] = '<db_name>'
If this returns 1 then RCSI is enabled. If not, follow the below instructions to enable it.
Instructions to enable RCSI:
This process might take considerable amount of time to complete. Please do this on a weekend / during off hours as it requires downtime.
1. Shut down the ServiceDesk Plus service. If any other application (maybe reporting tools) connects to servicedesk database, you need to stop them too.
2. Please login to SQL Management studio and execute the below queries...
i. take the database offline
USE master
GO
ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ii. enable read committed snapshot
ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO
iii. bring database back online
USE master
GO
ALTER DATABASE <db_name> SET ONLINE
GO
3. Please verify whether RCSI is enabled using the query mentioned at the top. You may start ServiceDesk Plus now.
Impact of enabling read committed snapshot isolation
Enabling Read Committed Snapshot Isolation does increase the memory requirements. Unfortunately there is no way to say how much the size would increase other than actually trying it out in the SQL server.
The SQL server documentation only mentions that for every row in the database an additional 14 bytes would be required for maintaining version information.
Other than that, when a transaction modifies some rows in the database, the older versions are stored in temp db till all concurrent transactions at that time complete. i.e. temp db memory requirement would be proportional to the number of transactions and length of the transactions at any point in time. When the concurrent transactions come down, temp db size will also decrease.
Other than that, when a transaction modifies some rows in the database, the older versions are stored in temp db till all concurrent transactions at that time complete. i.e. temp db memory requirement would be proportional to the number of transactions and length of the transactions at any point in time. When the concurrent transactions come down, temp db size will also decrease.
New to ADSelfService Plus?
Related Articles
SQL Server High CPU - To collect queries consuming CPU resources
1. To get an idea of how much CPU the queries are currently using, out of overall CPU capacity, run the following statement: DECLARE @init_sum_cpu_time int, @utilizedCpuCount int --get CPU count used by SQL Server SELECT @utilizedCpuCount = COUNT( * ...Performance debugging from version 9306 onwards
On Performance/Crash Issues, we have one URL to generate all debug details, this is applicable from 9306 onwards. Need to execute the URL in the browser, which has already login session into the application ...Remote read only access to database for Postgres customers
Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow ...Securing PostgreSQL Database Connection with SSL in ServiceDeskPlus
Overview This article serves as a comprehensive guide for customers intending to secure their database connections when using with ServiceDeskPlus Application with PostgreSQL database, similar to securing Microsoft SQL Server (MSSQL) connections. The ...Troubleshooting Oauth configuration for mail server settings
Oauth in ServiceDesk Plus / AssetExplorer for Mail SDP supports Oauth configuration for Office365 and GSuite mailboxes since 11106 build. Office365 mailboxes can be configured with Oauth authentication using EWS (since 11106) and IMAP / SMTP (since ...