时间戳

首页 > 解决方案 > SQL SERVER 2016 慢插入

sql - SQL SERVER 2016 慢插入

问题描述

当插入一个块时，插入开始非常快（1 秒内 3000 条），但随着时间的推移，对于总共 800 万条记录中的每 3000 条，插入需要 60-70 秒。

有人知道我可以回顾一下：到目前为止，我们已经验证了自动增长它在磁盘级别检查内存并处理任何增加以提醒某些事情。它是一个 VM Ware 虚拟服务器，Sql server 2016 Enterprise。

你能帮我吗，但我可以检查 BDD、存储和服务器的所有配置。

table_data_tmp 800 万表标识（用于检索）并包含索引 data_table_tmp1 800 万 table_data_tmp1_2 3 百万条记录 tabla_datos_fin 1900 万 30GB - 在此处插入时插入时间很慢

附加脚本仅作为参考，因为已经确定在插入到最终表中是每次通过插入所需的位置

IF EXISTS (SELECT 1 FROM SYS.INDEXES  WHERE OBJECT_ID = OBJECT_ID( 'tabla_datos_fin') AND NAME = 'IX_tabla_datos_fin_TIP_IDE_1' AND IS_DISABLED =0)
 ALTER INDEX IX_tabla_datos_fin_TIP_IDE_1 ON tabla_datos_fin DISABLE;
GO

TRUNCATE TABLE tabla_datos_tmp


IF EXISTS (SELECT 1 FROM SYS.INDEXES  WHERE OBJECT_ID = OBJECT_ID( 'IDX_tabla_datos_tmp') AND NAME = 'tabla_datos_tmp')
DROP INDEX tabla_datos_tmp.IDX_tabla_datos_tmp
GO

INSERT INTO tabla_datos_tmp (campos)
        SELECT *
        FROM [tabla_datos_tmp1] 

CREATE INDEX IDX_tabla_datos_tmp ON tabla_datos_tmp (CONTROL_INT) INCLUDE (all campos)


DECLARE
    @batchsize INT,
    @ctr INT,
    @rc INT,
@val_ini int,
@val_fin int
SELECT
    @batchsize = 3000,
    @ctr = 0;

WHILE 1 = 1
BEGIN

set @val_ini = @batchsize * @ctr 
set @val_fin =  @batchsize * (@ctr + 1)

truncate table tabla_datos_tmp1_2


INSERT INTO tabla_datos_tmp1_2
(all campos)
SELECT*
FROM tabla_datos_tmp WITH (INDEX =IDX_tabla_datos_tmp )
WHERE CONTROL_INT > @VAL_INI
AND CONTROL_INT <= @VAL_FIN 

UPDATE tabla_datos_tmp1_2 SET FECHA_ULTIMA_ACTUALIZACION= GETDATE ()



/* INSERTION IS DELAYED HERE */
INSERT INTO tabla_datos_fin (al campos)
SELECT *
FROM tabla_datos_tmp1_2 
/**********************/

DELETE  A
FROM tabla_datos_tmp A   WITH (INDEX =IDX_tabla_datos_tmp )
WHERE CONTROL_INT > @VAL_INI AND CONTROL_INT <= @VAL_FIN

IF @@ROWCOUNT = 0
BREAK;

SET @ctr = @ctr + 1;
PRINT @ctr
END

IF EXISTS (SELECT 1 FROM SYS.INDEXES  WHERE OBJECT_ID = OBJECT_ID( 'tabla_datos_fin') AND NAME = 'IX_tabla_datos_fin' AND IS_DISABLED =1)
 ALTER INDEX IX_tabla_datos_fin ON tabla_datos_fin REBUILD;  
GO
IF EXISTS (SELECT 1 FROM SYS.INDEXES  WHERE OBJECT_ID = OBJECT_ID( 'tabla_datos_fin') AND NAME = 'IX_tabla_datos_fin_TIP_IDE_1' AND IS_DISABLED =1)
ALTER INDEX IX_tabla_datos_fin_TIP_IDE_1 ON tabla_datos_fin REBUILD;  
GO

标签： sqlsql-servertsql

解决方案

使用 sql server wait stats 查看 sql 正在等待什么。找到之后，深入研究其他性能计数器，直到找到问题所在。我的猜测是你的磁盘跟不上。

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

推荐阅读