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:

 

AUTO_SHRINK Before
AUTO_SHRINK Before
AUTO_SHRINK after
AUTO_SHRINK after

 

 

 

 

 

 

 

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:

AUTO_SHRINK no MAX pressure

 

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

Related

4 Replies to “Myth Busters :: Who is scared of AUTO_SHRINK?”

  • Totally makes sense! I would just say that when you turn on auto shrink in Azure SQL DB, you should make sure you’re NOT defragmenting your indexes based on fragmentation levels. (That’s the whole point of the point of the article you quoted in bold, heh.)

  • Can you show a screenshot with just %’s? When the y-axis is data size it compresses the % to a line.
    I’d like to see a more detail CPU/IO/etc usage % and get a feel for how much resources it use.
    Also, please share the DTU or vCore setting if possible. Thanks.

  • Heh… shrinks take a fair bit of system resources (think $$$ in the cloud). Instead of shrinking all the time, you know what’s causing the growth. Why not just fix that so that you can stop wasting time and money?

    And that’s NOT to say that this wasn’t a good article. Nice to see people thinking about things.

Leave a Reply

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