Performance problems after setting ALLOW_SNAPSHOT_ISOLATION on

Question

I'm running a 15GB database on SQL Server 12.0.5207.

Server is a virtualized Windows Server 2012 R2 with 16GB RAM and 4 CPUs.

Yesterday, we set ALLOW_SNAPSHOT_ISOLATION to ON, to check if it would help prevent some deadlocks we have seen.

Performance decreased a LOT!

Lock waits per seconds increased, and CPU went from 25% to 50% usage.

We decided to set ALLOW_SNAPSHOT_ISOLATION back to OFF.

The problem is this had no effect; we still have decreased performance.

We restarted the SQL Server and related services, but it did not resolve the issue. Any suggestions?

Indexes have been rebuilt using Ola Hallengren's MaintenanceSolution.sql script we run weekly. This is the execution command:

EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30;

Answer 1

Yesterday we set ALLOW_SNAPSHOT_ISOLATION to ON, to check if it can help prevent some deadloch we have.

First of all this cannot help you with deadlocks at all.

Enabling ALLOW_SNAPSHOT_ISOLATION just activates the mechanism for storing row versions in tempdb.

So after you've enabled it, your server started to produce row versions on every data modification (UPDATE, DELETE) and this could only take server recourses, nothing gain only slowlyness.

If you wanted to prevent blocking on data modifications without rewriting your code you should enable RCSI on your database:

ALTER DATABASE MyDatabase  
SET READ_COMMITTED_SNAPSHOT ON

Unless you do it, you database still operates under Read Committed.

Or you could rewrite your code and explicitly set the Snapshot Isolation level for each session in order to access versioned rows.

---

We decide to set ALLOW_SNAPSHOT_ISOLATION back to OFF. The problem is that is has NO EFFECT, we still have performance decreased.

Did you control if it still enabled?

select snapshot_isolation_state_desc 
from sys.databases 
where name = yourDBname;

If it's really is not enable anymore, the cause of your performance decrease can be index fragmentation that was created while row versions were used. Row versioning is not free and all your rows that were modified got 14-bytes tags, this means that the same row doesn't fit anymore the same page where it resided before it was update, even if you was updating fixed length column. So you could have many page splits and even could double the space the index occupied, if every page was splitted. In this case index rebuild can help.

UPDATE

As Paul White noticed in his comment, if all you need is to change the default RC to RCSI, there is no need at all to enable ALLOW_SNAPSHOT_ISOLATION on your database. It's used to allow SNAPSHOT isolation, i.e. your code should be changed and you should open your transaction explicitely using SI. For just using RCSI, setting READ_COMMITTED_SNAPSHOT to ON is sufficient.

Answer 2

I solved this myself with:

USE DataBaseName
GO
sp_updatestats;

ALLOW_SNAPSHOT_ISOLATION flushes the plan cache for that database. Statistics were out of date. Just have to rebuild statistics.

Reference post on MSDN.