Rotating Header Image

The Yo-Yo Diet For Your Database

gfgfgd

Everytime I see a database with both AutoGrow and AutoShrink enabled I cringe.  I don’t “snicker” cause I leave any and all snickering to when someone says the words “Access Database”.  See here if you’re confused.

OK so why is having both AutoGrow and AutoShrink enabled on your database such a bad thing… I mean Microsoft wouldn’t give us these options unless there was a good reason to use them right? WRONG!!

I’ll admit that as a safety net, having AutoGrow set on your database is not a bad idea.  The rub here is that with proper capacity planning and Database File Management the “AutoGrow” process should never actually happen.  I don’t know about you but I like to know when, where, and how my database files are growing to minimize the amount of fragmentation and potential performance hit. 

Let’s talk about a world where Capacity Planning and Database File Managment doesn’t exist… a world where AutoGrow and AutoShrink are both enabled… a world where if you database needs more space you let SQL Server AutoGrow and if your database has too much space you let SQL Server AutoShrink.  This is a world I don’t ever want to live in… EVER

Why is this viscious cycle such a bad idea?

  • Resources – If you’re constantly growing and shrinking your database files there is a potentially significant CPU and IO hit (especially with the shrink) and you’re going to end up nickel and diming yourself to death. 
  • Fragmentation – If you’re constantly autogrowing and autoshrinking your database files will end up in bits and pieces all over your disk.  If you’re data is spread out all over the disk think of the additional performance hit in reading that data and combine that with the fact that autoshrinking can cause huge fragmentation in your indexes and that’s quite a double whammy.
  • Control – If you’re relying on AutoGrow and AutoShrink to manage your database files you’re giving away control as to when these operations will execute.  These processes don’t wait or care about scheduled downtime or maintenance windows… if SQL Server determines it needs to autogrow or autoshrink it’s going to do it. 

At the end of the day when it comes to your Database File Management you want to be a control freak.  You don’t want to put your database on a yo-yo diet where it’s constantly growing and shrinking and ending up having a negative impact on it’s “metabolism” (Performance)

There is actually a Microsoft Connect Item by Aaron Bertrand to Deprecate all Shrink Database Options… join the fight and give this item the 5 star rating it deserves!!

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

4 Comments

  1. Chris Wood says:

    You are certainly right with the auto shrink. If you want a well organized databasea and rebuild your indexes then auto shrink will quickly bring back the fragmentation.

    We run with auto grow but set a maximum size and allow a couple of Mb sized grows (never in %) and have alerts set for data file size so that when a database does expand we know about it. This is only for production DB’s. This is mainly because we usually don’t have much idea about the growth of our databases as the analysts design the DB’s. We also split the user data from the system data by defining a DATA filegroup which is the default filegroup.

    Chris

  2. Chris Wood says:

    I forgot to mention that we run DBCC loginfo to see how many VLF’s our logs have. If this number gets too big we will offload the log, shrink it, offload the log and then expand it to reduce those VLF’s.

    Chris

  3. […] the original: The Yo-Yo Diet For Your Database | Benchmark IT Consulting | Colin … By admin | category: it consulting | tags: are-legitimate, business, Consulting?, sales, […]

  4. The website adds that the iPad Pro will be slightly thicker than the iPad Air 2 because of its larger form factor. Reports suggest that it will be 7mm thick compared to 6.1mm for the iPad Air 2.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.