Myth Busters :: Who is scared of AUTO_SHRINK?

I’ve start using AUTO_SHRINK on Azure SQL Databases around 8 months ago. Then 1 month ago we adopted it on a larger scale. It’s time to divide myth from reality.

Are there any myth around this pretty unused feature?

A lot. As it happens most of the times someone writes an blog post and the dogma is reproduced on other blogs, and in the end everyone is repeating the same holy words without even going to check.

Plus, most of these preachers are really known in the SQL Server cult and going against them would be like vomiting on the Pope. I’m talking about Paul Randal or Brent Ozar. People that I really venerate too.

So the evangelists are wrong now?

I don’t want to say that.

I’ll limit myself saying that most of the blog posts around AUTO_SHRINK they have something in common:

• They are outdated (blog posts were published more than 10 years ago)
• They mention on-prem but they never approach Azure SQL Database scenarios
• They never mention SQL  Elastic Pool and the necessity to fit SQL Databases into it.

This is gonna be heavy… let me grab some popcorn.

Let’s start with a “why”

Why shrinking a database?

Everyone will tell you that shrinking a database is a useless operation: the database will increase in size again!

Exactly, there is no point to do it, right?

Probably there is no point to do it on-prem but what if you need to make databases fit into a SQL Elastic Pool? If the databases don’t fit you have to move to a bigger Elastic Pool but in in that case you don’t brake even.

The cost of the SQL Elastic Pool doesn’t justify moving to it. It’s too expensive! Better staying out.

But thanks to AUTO_SHRINK you can fit more databases into the Elastic Pool. This is the Elastic Pool before and after the AUTO_SHRINK:

 

 

 

 

 

 

 

 

Now you can really make the most out of a SQL Elastic Pool.

Yeah but Paul Randal says that “[…] 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.” (written in 2007)

Paul is one of the most amazing guys that the SQL Server community has. (He is the guy behind CHECKDB).

I’ve nothing about that blog post, I just wanted to add that his article dates back 2007:

• There was no cloud at that time and the blog post is mainly mentioning on-prem scenarios .
• There was no FinOps because there were no size/cost problems.

Are you saying that on-prem SQL Servers and Azure SQL Databases behave differently?

Precisely, while on one hand it’s true that you cannot control when AUTO_SHRINK kicks in… I haven’t seen any pressure on the DB. This is a an example:

 

In this case we were able to pass form a 1Tb SQL Database (S4 = 339.78 USD/month) to a 100Gb SQL Database (S1 = 21.49 USD/month).

And note that I’m measuring:

• CPU percentage (Max)
• Log IO percentage (Max)
• Data IO percentage (Max)
• DTU percentage (Max)

I’m taking the MAX not the AVG. This metrics would show spikes if there were. But there were none!

No pressure at all, flat line for CPU, Log IO, Data IO and DTU.

What about the Index/Page Fragmentation?

Brent Ozar says: “[…] this is often combined with a nightly index rebuild process that puts indexes back in order again – but leaves free space behind. The very next time shrink runs, it re-fragments everything, and your storage is stuck on the Hamster Wheel of Death.” (written in 2012)

Giving the time at which this article was written I believe Brent is referring to on-prem.

In fact is the same Brent Ozar that wrote a notorious post named Stop Worrying About SQL Server Fragmentation. Fragmentation might be a pain if you are using HDD: If magnetic hard drives have to jump around to different areas of the drive, their performance suffers. But this doesn’t happen if you are using SSD storage.

If you are using SQL Elastic Pool vCores all disks are using SSD and you don’t have to worry about Index fragmentation or Page fragmentation.

What about alternatives like DBCC SHRINKDATABASE or SHRINKFILE commands?

Both DBCC SHRINKFILE (log, 0) and DBCC SHRINKDATABASE (N'db1') are simply not working in an Azure SQL Database scenario.

From my experience they can take days to complete even on small size databases. (Not on vanilla test databases like AdventureWorks)

And while AUTO_SHRINK doesn’t show any sign of CPU, Log I/O, Data I/O pressure you can rest assured that you will see your CPU hitting the 100% for the whole amount of the operation.

Conclusion

There is a place where you can use AUTO_SHRINK and that is Azure.

There is a scenario in which it’s worth to use AUTO_SHRINK and that is when you are trying to fit multiple databases into a SQL Elastic Pool.

And this because:

• Even if it might cause Index/Page fragmentation that is not a problem if you are on an SSD
• Is not true that AUTO_SHRINK takes up a lot of resources. (Tested with Max CPU, Log I/O, Data I/O both on DTU and vCores.)
• DBCC SHRINKDATABASE or SHRINKFILE commands are not an alternative
• Is a good way to minimize the costs of SQL Elastic Pool and Azure SQL Databases

** UPDATE**

I received a few comments and a lot of direct messages on LinkedIn.

You mainly ask me to dig deeper into the metrics of the Azure SQL Database. Let’s dive in to the metrics.

The AUTO_SHRINK kicks in at 4:00am and at 10:52am.

And you were right: as you can see from the .gif the percentages are not really flat.

At 4:00am they are still very low and I cannot see any consequence

At 10:52am, yes the CPU is under pressure… (around 50%) but there are also sessions and workers. This because there was something going on. I mean, it makes sense to have a database in use at that time of the day. I cannot really link this pressure to the AUTO_SHRINK.

Especially if at 4:00am the pressure was flat.

If I look close at what happened at 4:00am the data allocation shrink of 400Gb in 1 minute. And this happens effortless. In 1 single minute.

 

 

 

How can an SQL Database shrink 400Gb so fast without computational drawbacks?

This is why I wrote this blog post.

Next test:

I will now take an AdventureWork database and inflate it, decrease the size, and see if I can reproduce this on a vanilla database.

Please comment if you have any suggestion or if you think I’m missing something.

Stay tuned.