insert on table being unnaturally slow

August 16, 2021 Andy DBA Stack Exchange 0

clip art of a double-quote character

Question

I have an "Basic" pricing tier Azure SQL database with a table of 7 columns, an ID column of int that is the clustered index and primary key, one datetime2(0) column, 3 varchar(100) columns and 2 varchar(MAX) columns, all nullable.

The table has no triggers, constraints or foreign keys.

Now I’m inserting a large amount of test data, I’m doing an

INSERT INTO table_name (<all columns, except the ID one>)
values (<just some values, the ones for varchar(MAX) being 221 characters long>)`
GO 680000

However the query has been running for 5 hours and only 290000 rows have been inserted.

I’m trying to find out why.

asked 2021-08-16 by Tessaract

Answer

You’ll need to look at the waits for the session doing the insert to figure out what the bottleneck is. Given that you are on a "Basic" tier, your insert is probably being artificially throttled based on the service tier.

If you run a query like this…

SELECT *
FROM sys.dm_exec_session_wait_stats
WHERE session_id = <session doing the insert>
ORDER BY wait_time_ms DESC

…I suspect you’ll see that the top wait is probably something like LOG_RATE_GOVERNOR or HADR_THROTTLE_LOG_RATE_GOVERNOR. These wait types are caused specifically due to the artificial limits put on the rate at which you can write to the transaction log in Azure SQL DB, and is a common bottleneck on large inserts when using the Basic Tier. The Basic Tier is extremely limited on available system resources. Note: It’s possible to hit the limit on log rate without hitting the service tier’s DTU limit.

One solution is to simply use a higher service tier, which will allow you to have more DTUs (and thus more overall system resources) to use for your large insert. After your load is complete, you can then switch back to a lower service tier. I’ve written more about DTUs, and attempted to correlate DTUs back to traditional on-prem hardware you may be more familiar with –you can read that here.

There may be more options for improving throughput on a lower service tier, but to do so, you’ll need to look in detail at exactly what you’re doing, and what your resource bottleneck is.

answered 2021-08-16 by Andy Mallon