Azure SQL Database Performance comparison part 1 of 9: Basic

Welcome to my blog series on Azure SQL DB performance. In this blog I’ll write about my findings with the Azure SQL DB Basic tier.

Click here for information about the set-up
Click here for the scripts I’ve used

Intro

This tier is the cheapest one around, it’ll cost you about 5 euro’s per month. This should already set some expectations. For this you will get 5 DTU’s max, 2 GB of storage, 1 TempDB file and a max of 300 concurrent sessions.

Limits

When you look at the documentation, the basic tier has low CPU (at max less than one), 1 to 4 IOPS per DTU (translating to 5-20 IOPS in total), a latency of 5ms read and 10 ms write and a maximum of 7 days backup retention. Even though it’s advertised as a production database, I wouldn’t store essential data there. However, I can use it for metadata storage in a Data Factory or Synapse Analytics environment.

About the IOPS, one IOP is a read of a disk cluster, usually 4 Kb. In this case, this means that the database is capable of reading 20 to 80 Kb of data per second.

If you’ve looked at the code I’ve been using, you’ll see a comment in there. A comment for the insert script that for basic tier, don’t use the 50 threads but 25. Why? Well, basic only has 30 threads for you, so when you exceed that number of threads you’ll get all kinds of errors. So make sure you’re not using temp tables because they need a thread to stay alive and the basic tier will kill off stagnant threads in favour of active ones.

Resources provided

When the database was deployed, I got a server with 167 GB of memory available, 1 whole GB of that was mine to use. I got 1 CPU core with 12 workers, 1 scheduler of the 12 in total. These results came from the first query in the script file, where I’m checking the hardware.

Running query’s

We’re not using a database to sit around idle, we need to insert, read, delete and update data (in whatever order you fancy). When you look into the code post, you’ll find the scripts I’ve used to do just that. Insert a million rows, try and read a lot of stuff, delete 300.000 records and update 300.000. The reads, updates and deletes are randomized. Because of the large number, I think the results are useable.

Insert 1 million records

The insert took around 15 hours to complete. The CPU needed about 5 milliseconds for each iteration, the actual seconds per iteration came out on 1,342. Each iteration had a little over 12 logical reads. Most prevalent and relevant wait types:

• PAGEIOLATCH_EX
• IO_QUEUE_LIMIT
• XE_LIVE_TARGET_TVF
• PWAIT_EXTENSIBILITY_CLEANUP_TASK
• PVS_PREALLOCATE
• HADR_FABRIC_CALLBACK
• WRITELOG

To summarize these waits, the database had a hard time reading from disk, ran into it’s IO limit, had to support extended events, had to wait on background tasks, replicate data in the availability group and had to wait on a log block to be written to disk.

My read latency ended up around 1,3 seconds, the write latency stuck around 2 milliseconds.

One other thing I found out while testing is that growing out the data file takes a lot of time. The first testrun I did took about 12 hours. Some things went wrong so I gave the database a second chance and it completed much faster. The only thing that changed was that the file had grown from the default size to the size needed to contain all the crappy data. Keep that in your mind when using this tier.

Select data

After inserting data, it was time to select some. The script that I’ve used is intentionally horrible to make the worst of the performance. I’m sure your scripts are much better. But in this case, the select took 6 minutes and 39 seconds to complete. Just one. The database had to do 59.082 reads for this and took 0,3 seconds on the CPU. If I were to wait until the 30.000 records were selected, I’d have to wait 5 months, 18 days and 13 hours. Before you run away, when running less parallel query’s and simpler one, the performance is fine.
The wait types are a bit weird.

• HADR_FABRIC_CALLBACK
• PWAIT_EXTENSIBILITY_CLEANUP_TASK
• VDI_CLIENT_OTHER
• XE_LIVE_TARGET_TVF
• IO_QUEUE_LIMIT
• PAGEIOLATCH_SH
• RESOURCE_GOVERNOR_IDLE
• SOS_SCHEDULER_YIELD
• BACKUPTHREAD
• BACKUPIO

Besides being very heavily throttled on execution, VDI wait seems to tell me there was movement going on in the background. The database was being moved to another host. The read latency was about 1,2 seconds, the write latency again just 2 milliseconds.

Delete data

After selecting data I decided I had enough of the large amount and had to trim some of. So I deleted 50.000 records. This took a bit over 30 minutes with about 5 logical reads, 4 milliseconds on the CPU and the actual iteration time came out on 2,3 seconds. The waits are comparable with the select

• HADR_FABRIC_CALLBACK
• PWAIT_EXTENSIBILITY_CLEANUP_TASK
• XE_LIVE_TARGET_TVF
• VDI_CLIENT_OTHER
• PVS_PREALLOCATE
• PAGEIOLATCH_EX
• IO_QUEUE_LIMIT
• PAGEIOLATCH_SH
• RESOURCE_GOVERNOR_IDLE
• WRITELOG
• LCK_M_X

Again the database is waiting on availability groups, some background task, throttling, moving between hosts and right at the bottom it’s waiting for an exclusive lock to really delete a row. This wait registered a total of 76 seconds during the execution of the job.

The disk latency was identical to the previous one.

Update data

The last job I let rip on the database was an update task. I ran a script to update 300.000 records. This took 27 minutes, had 3 logical reads, 0 CPU seconds per iteration and 0.01 seconds per iteration. This went fairly quick. The waits:

• RESOURCE_GOVERNOR_IDLE
• PWAIT_EXTENSIBILITY_CLEANUP_TASK
• XE_LIVE_TARGET_TVF
• HADR_FABRIC_CALLBACK
• PVS_PREALLOCATE
• PAGELATCH_EX

Most of these waits are already mentioned, the pagelatch_ex registered 203 seconds during the execution and tells us the database is waiting to access the file page in memory. The disk latency was much lower this time, 179 milliseconds read and 4 milliseconds write.

Finally

Was this a fair test on this database? Not really, it never stood a single chance. Hammering it with resource intensive query’s isn’t what this database is built for. It’s a cheap way of storing non-critical data that isn’t heavily accessed. Use it for that purpose and you’ll be fine.