Sample Header Ad - 728x90

Performance problems after setting ALLOW_SNAPSHOT_ISOLATION on

0 votes
2 answers
2141 views
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;
Asked by Davide Gironi (39 rep)
Sep 6, 2018, 09:23 AM
Last activity: Apr 15, 2025, 01:44 PM