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
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
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;