Skip to main content
Database Administrators

Return to Question

Incorporated comment; edited tags; improved language and formatting
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 423
  • 663

Performance problems after setting ALLOW_SNAPSHOT_ISOLATION ONon

I'm running a 15Gb15GB database on SQL serverServer 12.0.5207.

Server is a virtualized Windows Server 2012 R2, 16Gb ram, 4CPU with 16GB RAM and 4 CPUs.

Yesterday, we set ALLOW_SNAPSHOT_ISOLATION to ONALLOW_SNAPSHOT_ISOLATION to ON, to check if it canwould help prevent some deadlockdeadlocks we have seen.

Performance decreased a LOT!

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

We decidedecided to set ALLOW_SNAPSHOT_ISOLATION back to OFFALLOW_SNAPSHOT_ISOLATION back to OFF.

The problem is that is hasthis had NO EFFECTno effect,; we still have performance decreased performance. We restart

We restarted the SQL serverServer and related serviceservices, but it doesdid not resolve the issue.

  Any suggestionsuggestions?

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

Performance problems after ALLOW_SNAPSHOT_ISOLATION ON

I'm running a 15Gb database on SQL server 12.0.5207.

Server is a virtualized Windows Server 2012 R2, 16Gb ram, 4CPU.

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

Performance decreased a LOT!

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

We decide to set ALLOW_SNAPSHOT_ISOLATION back to OFF.

The problem is that is has NO EFFECT, we still have performance decreased. We restart the SQL server and related service, it does not resolve.

  Any suggestion?

Thank you.

Performance problems after setting ALLOW_SNAPSHOT_ISOLATION on

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;
deleted 403 characters in body
Source Link
Davide Gironi
Davide Gironi
  • 39
  • 3

I'm running a 15Gb database on SQL server 12.0.5207.

Server is a virtualized Windows Server 2012 R2, 16Gb ram, 4CPU.

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

Performance decreased a LOT!

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

We decide to set ALLOW_SNAPSHOT_ISOLATION back to OFF.

The problem is that is has NO EFFECT, we still have performance decreased. We restart the SQL server and related service, it does not resolve.

Any suggestion?

Thank you.

SOLVED

USE DataBaseName
GO
sp_updatestats;

ALLOW_SNAPSHOT_ISOLATION flushes the plan cache for that database, statistics are out of date. Just have to rebuild statistics. Reference post: https://social.msdn.microsoft.com/Forums/SECURITY/en-US/989228a8-e3c3-43d9-a364-f658acead900/performance-decreas-after-allowsnapshotisolation-on?forum=sqldatabaseengine

Hope this helps

I'm running a 15Gb database on SQL server 12.0.5207.

Server is a virtualized Windows Server 2012 R2, 16Gb ram, 4CPU.

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

Performance decreased a LOT!

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

We decide to set ALLOW_SNAPSHOT_ISOLATION back to OFF.

The problem is that is has NO EFFECT, we still have performance decreased. We restart the SQL server and related service, it does not resolve.

Any suggestion?

Thank you.

SOLVED

USE DataBaseName
GO
sp_updatestats;

ALLOW_SNAPSHOT_ISOLATION flushes the plan cache for that database, statistics are out of date. Just have to rebuild statistics. Reference post: https://social.msdn.microsoft.com/Forums/SECURITY/en-US/989228a8-e3c3-43d9-a364-f658acead900/performance-decreas-after-allowsnapshotisolation-on?forum=sqldatabaseengine

Hope this helps

I'm running a 15Gb database on SQL server 12.0.5207.

Server is a virtualized Windows Server 2012 R2, 16Gb ram, 4CPU.

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

Performance decreased a LOT!

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

We decide to set ALLOW_SNAPSHOT_ISOLATION back to OFF.

The problem is that is has NO EFFECT, we still have performance decreased. We restart the SQL server and related service, it does not resolve.

Any suggestion?

Thank you.

added 417 characters in body
Source Link
Davide Gironi
Davide Gironi
  • 39
  • 3

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

Server is a virtualized Windows Server 2012 R2, 16Gb ram, 4CPU. Yesterday

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

Performance decreased a LOT! Lock

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

We decide to set ALLOW_SNAPSHOT_ISOLATION back to OFF. The

The problem is that is has NO EFFECT, we still have performance decreased. We restart the SQL server and related service, it does not resolve. Any

Any suggestion?

Thank you!.

SOLVED

USE DataBaseName
GO
sp_updatestats;

ALLOW_SNAPSHOT_ISOLATION flushes the plan cache for that database, statistics are out of date. Just have to rebuild statistics. Reference post: https://social.msdn.microsoft.com/Forums/SECURITY/en-US/989228a8-e3c3-43d9-a364-f658acead900/performance-decreas-after-allowsnapshotisolation-on?forum=sqldatabaseengine

Hope this helps

I'm running a 15Gb database on SQL server 12.0.5207. Server is a virtualized Windows Server 2012 R2, 16Gb ram, 4CPU. Yesterday we set ALLOW_SNAPSHOT_ISOLATION to ON, to check if it can help prevent some deadloch we have. Performance decreased a LOT! Lock waits per seconds increased, and CPU pass from 25% to 50% usage. We decide to set ALLOW_SNAPSHOT_ISOLATION back to OFF. The problem is that is has NO EFFECT, we still have performance decreased. We restart the SQL server and related service, it does not resolve. Any suggestion?

Thank you!

I'm running a 15Gb database on SQL server 12.0.5207.

Server is a virtualized Windows Server 2012 R2, 16Gb ram, 4CPU.

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

Performance decreased a LOT!

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

We decide to set ALLOW_SNAPSHOT_ISOLATION back to OFF.

The problem is that is has NO EFFECT, we still have performance decreased. We restart the SQL server and related service, it does not resolve.

Any suggestion?

Thank you.

SOLVED

USE DataBaseName
GO
sp_updatestats;

ALLOW_SNAPSHOT_ISOLATION flushes the plan cache for that database, statistics are out of date. Just have to rebuild statistics. Reference post: https://social.msdn.microsoft.com/Forums/SECURITY/en-US/989228a8-e3c3-43d9-a364-f658acead900/performance-decreas-after-allowsnapshotisolation-on?forum=sqldatabaseengine

Hope this helps

Source Link
Davide Gironi
Davide Gironi
  • 39
  • 3