We use SQL elastic pools at work. I needed to learn more about their nuances and help you with some of their challenges.
Setting Up Elastic Pools
Here‘s some general info about elastic pools and setting them up.
Main points about elastic pools
- Elastic pools enable you to purchase resources for a pool shared by multiple databases to accommodate unpredictable usage periods by individual databases.
- You can create multiple pools on a server, but you can’t add databases from different servers into the same pool.
- Pools are well-suited for many databases with specific utilization patterns. These patterns are characterized by low average utilization with infrequent utilization spikes for a given database. Conversely, multiple databases with persistent medium-high utilization shouldn’t be placed in the same elastic pool.
All the databases in the elastic pool share the DTU and storage. This is very apparent when multiple persistent medium-high utilization databases are in the same pool, which is why Microsoft advises against it.
Elastic Pool Storage
Also, an issue came up around storage and what was being allocated to a database. Some thought the database was running out of storage, but the pool still had plenty of free space. The column on the left starting with D is the database name. You can see it seems deceptive, as if your database may be running out of room.
If you go check the database itself, you will see in the following image that the allocated space is 15.09 GB. The used space is 15.07 GB, but the remaining space is 234.93. This is because the elastic pool has a maximum of 250 GB shared by any of the databases.
The following image shows the pool storage with only 37.5 GB in use and 45 GB allocated to the pool currently. Only 15% of the maximum pool storage is in use.
Allocated vs. used vs. remaining amounts may be confusing if you aren’t familiar with elastic pools. It appears Microsoft allocates very little storage above what you need in a database, leaving room for the other databases to grow.
Elastic Pool DTU
We put these highly active databases in the same pool, and it causes a fair amount of DTU consumption problems.
There are a few ways you can try to mitigate this:
- Either put them in separate pools – the best option, according to Microsoft, if you have persistent medium-high utilization databases.
- Tune all the queries to use less DTU – this is a good option if you have a lot of naughty queries – and most of us have at least some of those. Check out my post on sp_QuickieStore to figure those queries out.
- Try to set the min/max DTU – this seems fraught to me, and here’s more info from Microsoft on it – and there is no guarantee they won’t have the same DTU contention issues.
Putting Multiple Pools on the Same Server
Let’s test this out with four existing databases that I have on the same server. They aren’t in a pool already.
I need to create the pools to add them to a pool first.
I will configure the pool in the same resource group as my server. It won’t save me money when I have only four basic databases. This is an example to show you the process. The use case is more for maybe S3 and higher DTU dbs.
I created two of those pools so you can see how to add the four databases to two different pools.
You have to go into the respective pools to add the databases. I will add two databases to each, but you can add however many you want to each – within limits.
You need to click Add Databases.
I will add these two to this pool
Then save that, and we will see two databases in that pool now.
I was going to add the two other databases to the other pool. But then, I decided to test what happens if I tried to add a database in another pool already. It moves the database to the other pool. So, now I have one db in one pool and three dbs in the other.
If I go look at another server, we see no pool available because a pool isn’t shareable across multiple servers.
Setting Min/Max in a Pool for Database DTU
I can’t do much with the basic pool to show you the min/max, so it’s not a great example for setting it.
I will change the pool settings to standard so we can see more of the per database settings with DTU.
No setting max to zero — seems logical because why even have a database if it can’t consume anything?
In the case below, each database’s max is 50, and the min is 0.
However, per the Microsoft documentation, there are no guarantees that any database will not exceed the max. “Max DTUs per database is not a resource guarantee for a database. If the workload in each database does not need all available pool resources to perform adequately, consider setting max DTUs per database to prevent a single database from monopolizing pool resources. Some degree of over-committing is expected since the pool generally assumes hot and cold usage patterns for databases, where all databases are not simultaneously peaking.”
Setting Storage Max Per DB
This is set on each DB itself, unlike DTU, where you must set it the same across all the databases. You must go into each database’s storage and set the max it can use. Per Microsoft, “You can also set maximum storage per database, for example to prevent a database from consuming all pool storage. This setting can be configured independently for each database.”
Now, I go into the specific database to limit storage. I set this one to use only 1 GB vs. the 2 GB max. It’s in the basic elastic pool and has very limited storage to begin with.
I moved a database into my elastic pool configured with the standard service tier. This way, you can see the storage max there. This pool has a maximum storage of 50 GB. If I modify a database’s storage max, I can set it to 30 GB, which means it won’t use any more storage than that.
Note: It will let you set the max for the database storage way above the pool max. Here, it’s set to 250, but the pool only allows 50 GB max. Be careful tinkering with these settings.
Pools can save you money depending on your use case, but they add some level of complexity. This can get complicated quickly if you want to constrain certain DBs to only use a certain amount of DTU or storage. Make sure to carefully analyze your database workloads and their spikes to ensure that databases in the same pool won’t cause a lot of contention sharing DTU with each other.
The post Using SQL Elastic Pools appeared first on sqlkitty.