主页 / dba / 问题 / 298118
Accepted
Tessaract
Asked: 2021-08-17 08:45:42 +0800 CST

在桌子上插入异常缓慢

  • 772

我有一个“基本”定价层 Azure SQL 数据库，其中包含一个 7 列表、一个作为聚集索引和主键的 int ID 列、一个 datetime2(0) 列、3 个 varchar(100) 列和 2 个 varchar(MAX ) 列，都可以为空。

该表没有触发器、约束或外键。

现在我正在插入大量的测试数据，我正在做一个

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

但是查询已经运行了 5 个小时，只插入了 290000 行。

我试图找出原因。

performance azure-sql-database

2 个回答

  1. Best Answer

    您需要查看执行插入的会话的等待情况，以确定瓶颈是什么。鉴于您处于“基本”层，您的插入可能会根据服务层被人为地限制。

    如果您运行这样的查询...

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

    ...我怀疑您会看到顶部等待可能类似于LOG_RATE_GOVERNORor HADR_THROTTLE_LOG_RATE_GOVERNOR。这些等待类型是由于人为限制写入 Azure SQL DB 中的事务日志的速率而引起的，并且是使用基本层时大型插入的常见瓶颈。基本层的可用系统资源极为有限。注意：有可能在不达到服务层的 DTU 限制的情况下达到日志速率限制。

    一种解决方案是简单地使用更高的服务层，这将允许您有更多的 DTU（因此更多的整体系统资源）用于您的大型插入。加载完成后，您可以切换回较低的服务层。我已经写了更多关于 DTU 的文章，并尝试将 DTU 与您可能更熟悉的传统本地硬件相关联——您可以在此处阅读。

    可能有更多选项可以提高较低服务层的吞吐量，但要做到这一点，您需要详细了解您正在做什么，以及您的资源瓶颈是什么。

    • 3

  2. 单行插入（尤其是在隐式提交之后）将生成比批量插入更多的事务日志数据。

    使用事务日志备份作为写入多少事务日志数据的粗略且现成的示例：

    CREATE TABLE new_employees  
(  
 id_num int IDENTITY(1,1),  
 fname varchar (20),  
 minit char(1),  
 lname varchar(30),
 lob_col varchar(max)
);  

set nocount on 
BACKUP LOG [demo_db] TO  DISK = N'V:\SQL\Backups\demo_db_log_clear.bak' WITH NOFORMAT, NOINIT,  NAME = N'demo_db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
select getdate()
go
insert into new_employees  
(  
 fname 
,minit 
,lname 
,lob_col 
)
values
('Andrew'
,'J'
,'Sayer'
,replicate('X',221)
);
go 100000
select getdate()
BACKUP LOG [demo_db] TO  DISK = N'V:\SQL\Backups\demo_db_log_single.bak' WITH NOFORMAT, NOINIT,  NAME = N'demo_db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
select getdate()
go
insert into new_employees  
(  
 fname 
,minit 
,lname 
,lob_col 
)
select top 100000
 'Andrew'
,'J'
,'Sayer'
,replicate('X',221)
FROM sys.all_columns ac
cross join sys.all_columns ac2
go
select getdate()
go
BACKUP LOG [demo_db] TO  DISK = N'V:\SQL\Backups\demo_db_log_bulk.bak' WITH NOFORMAT, NOINIT,  NAME = N'demo_db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

    （我正在使用 100,000 行，因为我不耐烦地等待单个值插入完成您的计数）。

    我家用机器上的结果：

    Single row insert
Time taken: 2021-08-16 20:49:41.510 to 2021-08-16 20:50:04.477 = 23 seconds
Transaction log backup size: 50010 pages

Bulk row insert
Time taken: 2021-08-16 20:50:04.787 to 2021-08-16 20:50:05.177 = 0.4 seconds
Transaction log backup size: 4601 pages

    所以它大约快了 50 倍，并且生成了十分之一的事务日志数据。

    唯一要确保的是行生成源可以生成足够的行，我只是sys.all_columns与自身交叉连接，这在我相当空的数据库中产生了很多。

    • 1

相关问题