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…
9 thoughts on “Auto-shrink – turn it OFF!”
The article is very useful and clearly advise shrinking databases importancy. Many Thanks.
Hi, Its a good article. While reading I have a question, will dbcc shrinkfile(logfile) too will cause fragmentation. Please clarify.
Hi Peruma – no, only shrinking data files causes index fragmentation.
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?
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.
this information is really very helpful.
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. :)
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!
great post this can save a lot of time and frustration ;] many thankssss