1

After realizing a database had the auto shrink option turned on i searched about it and i realized it is a good practice to turn it off (there are many webpages advisign this, like this one), since auto shrink is considered to cause performance issues in general

So i run

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF WITH NO_WAIT'

I realized that after this the RAM usage from SQL Server is much higher and it ends up writing in the swap file by slowing down the whole server.

What causes this memory issue? Isn't it a good practice to turn auto shrink off on a normal db (this is a database of a client server ERP application, so mostly used to read data and with less frequency to write it).

I realize that autoshrink was set when this database was a sql server 2000 database (may be at that time the default value was True), moreover a new databasein sql serveer 2008 R2 has AutoShrink False by default.

Should i set AutoShrink On again? OF course I can do by trial and error, but I am asking to find a technical reason about why auto shrink can cause memory issues.

Community
  • 1
  • 1
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
  • With the AUTO_SHRINK OFF, make sure that you are doing your Maintenance Plan: Check databases, shrink, rebuild indexes, update stats, ect... – Chuck May 09 '16 at 21:22
  • SQL Server by design tries to use as much RAM as possible for caching. Auto Shrink should not have any impact on it. If you want to reduce the size of RAM used, necessary when other applications are running besides SQL Server, investigate setting "max server memory" https://msdn.microsoft.com/en-us/library/ms178067.aspx – Alex May 10 '16 at 01:05

1 Answers1

1

I realized that after this the RAM usage from SQL Server is much higher and it ends up writing in the swap file by slowing down the whole server.

Its very good practice to Turn OFF AUTO SHRINK, regrading memory rise this is just not possible, atleast I have never seen this and nobody has ever reported. On contrary when auto shrink kicks in it might requires lot of I/O to bring pages in memory to perform shrink and memory consumption might rise.

You mentioned about swap file writing what do you mean by that ?

Should i set AutoShrink On again? OF course I can do by trial and error, but I am asking to find a technical reason about why auto shrink can cause memory issues.

NO don't set it ON. There must be other queries running which would have requested lot amount of memory. This is whole different topic what caused memory to rise we need to find that.

What is output of select @@Version ?

Shanky
  • 607
  • 4
  • 23
  • Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) – UnDiUdin May 20 '16 at 15:28
  • You must update SQL Server 2008 r2 to Sp3 ASAP. There are lot of memory related fixes and you would eventually be running supported instance, although extended support is applicable now to SQL Server 2008 R2 – Shanky May 20 '16 at 16:06