Auto-shrink – turn it OFF!

I’m in the middle of a flight from Washington D.C. to Zurich on the way to Barcelona for TechEd IT Forum and I can’t sleep – Kimberly’s out like a light so what else is there to do except write another blog post? :-)OK – actually posting this from Barcelona on Tuesday before our first of 12 sessions here!

In the Database Maintenance workshop we did at SQL Connections last week I promised to blog a script I used to show how data file shrink operations cause massive fragmentation of indexes. The reason is that data file shrink starts at the end of the data file and moves a single page at a time to a free space below the shrink threshold. In the process of doing so, it perfectly reverses the physical order of the pages comprising the leaf level of an index – thus perfectly fragmenting it!!

Let’s try out my simple script that demonstrates this. First thing I’m going to do is create a new database and create a 10MB ‘filler’ table, which I’m going to then drop later to create a space that shrink can use.

USE MASTER;
GO
 
IF DATABASEPROPERTYEX ('shrinktest', 'Version') > 0
      DROP DATABASE shrinktest;
 
CREATE DATABASE shrinktest;
GO
USE shrinktest;
GO
 
SET NOCOUNT ON;
GO
 
-- Create and fill the filler table
CREATE TABLE filler (c1 INT IDENTITY, c2 VARCHAR(8000))
GO
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 1280) -- insert 10MB
BEGIN
      INSERT INTO filler VALUES (REPLICATE ('a', 5000));
      SELECT @a = @a + 1;
END;
GO

Next I’ll create the ‘production’ table that we’d really like to keep in optimal shape for performance:

-- Create and fill the production table
CREATE TABLE production (c1 INT IDENTITY, c2 VARCHAR (8000));
CREATE CLUSTERED INDEX prod_cl ON production (c1);
GO
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 1280) -- insert 10MB
BEGIN
      INSERT INTO production VALUES (REPLICATE ('a', 5000));
      SELECT @a = @a + 1;
END;
GO 

Now I’ll use the sys.dm_db_index_physical_stats DMV to check the fragmentation of the production table’s clustered index – it should be almost zero:

-- check the fragmentation of the production table
SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (
      DB_ID ('shrinktest'), OBJECT_ID ('production'), 1, NULL, 'LIMITED');
GO
avg_fragmentation_in_percent
----------------------------
0.390930414386239

This is what I expected. Now I’m going to drop the filler table, run a shrink operation (auto-shrink, DBCC SHRINKDATABASE and DBCC SHRINKFILE all use the exact same code under the covers) and then check the fragmentation again:

-- drop the filler table and shrink the database
DROP TABLE filler;
GO
 
-- shrink the database
DBCC SHRINKDATABASE (shrinktest);
GO
 
-- check the index fragmentation again
SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (
      DB_ID ('shrinktest'), OBJECT_ID ('production'), 1, NULL, 'LIMITED');
GO
avg_fragmentation_in_percent

----------------------------
99.7654417513683

Wow! The index went from almost 0% fragmented to almost 100% fragmented – the shrink operation totally reversed the physical ordering of the leaf level of the clustered index – nasty.

One of the common maintenance operations I see at customer sites is to run a database shrink at some interval, and I always advise against it – now you can see why. Running a regular shrink operation can cause horrible fragmentation problems. The worst problems I see are those customers with maintenance plans that rebuild all indexes and then run a shrink to remove the extra space necessary for the index rebuilds – completely undoing the effects of the index rebuild!

One other common thing I see is to have auto-shrink set on for one or databases. This is bad for several reasons:

  • Shrink causes index fragmentation, as I’ve just demonstrated above.
  • You can’t control when it kicks in. Although it doesn’t have any effect like long-term blocking, it does take up a lot of resources, both IO and CPU. It also moves a lot of data through the buffer pool and so can cause hot pages to be pushed out to disk, slowing things down further. If the server is already pushing the limits of the IO subsystem, running shrink may push it over, causing long disk queue lengths and possibly IO timeouts.
  • You’re likely to get into a death-spiral of auto-grow then auto-shrink then auto-grow then auto-shrink… (in my experience, if someone is using auto-shrink, they’re most likely using and relying on auto-grow too). An active database usually requires free space for normal operations – so if you take that free space away then the database just has to grow again. This is bad for several reasons:
    • Repeatedly shrinking and growing the data files will cause file-system level fragmentation, which can slow down performance
    • It wastes a huge amount of resources, basically running the shrink algorithm for no reason
    • Auto-grow itself can be bad, especially if you don’t have Instant File Initialization turned on – see this post from Kimberly’s blog, where all allocations to the file being grown are blocked while the new portion of the file is being zero-initialized.

Bottom-line: auto-shrink should *NEVER* be turned on…

11 thoughts on “Auto-shrink – turn it OFF!

  1. Hi, Its a good article. While reading I have a question, will dbcc shrinkfile(logfile) too will cause fragmentation. Please clarify.

    Thanks
    -Perumal

  2. I just recently started working at a place that uses shrink a lot to address space issues. How would go about rectifying the situation for prod databases in the range of 60 – 100 gig, in use on a 24/7 schedule where drive space is at a premium?

  3. If the database isn’t going to grow again then you could shrink and remove fragmentation with ALTER INDEX REORGANIZE. If the database is going to continually shrink/grow/shrink/grow, turn shrink off and let the database remain at one size to avoid performance issues. Shrinking regularly really is one of the worst things you can do.

  4. Very userful information and put across clearly too.
    However this raises the question – What can we do to reduce the database size on the disk space. Can we not reorganize the Indexes each time? Why is this not optimized by sql server? You can choose not to answer the last one. Not that you do not have a choice on all of the questons. :)
    Thanks,
    Gurjit

  5. Hi Gurjit – many times it’s best to just leave the database size as is and don’t remove extra space. If you absolutely must, one way to do it is to create an extra filegroup and then CREATE INDEX … WITH DROP_EXISTING the indexes into the new filegroup, and drop the old one. SQL Server doesn’t optimize shrink because after it was written there was never any engineering time given to it. I proposed a more fragmentation-aware rewrite of it when I owned that portion of the Storage Engine but there wasn’t time. Thanks!

  6. Thank you Paul. But here is my problem: I have to fit 20 databases into an Elastic Pool.
    It’s a mere question of money: if I don’t shrink them I don’t brake even because the Elastic Pool is going to cost more than keeping the DBs outside of the Elastic Pool.

    In this scenario is AUTO_SHRINK worth using?
    Because if I use DBCC SHRINKDATABASE on Azure SQL Databases:
    1) it takes ages (more than 24h)
    2) after I run DBCC SHRINKDATABASE little has changed
    3) I need something that keeps shrinking them otherwise the Elastic Pool cannot contain them.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.