ajustando a operação de exclusão enorme na tabela do sql server

Testando com base em seus comentários

Testado no SQL Server 2014 SP3

stg_table e stg_table_1 não possuem índices em service_date.

ambas as tabelas são carregadas com milhões de linhas de dados e a operação de exclusão está demorando muito.

DDL

CREATE TABLE dbo.st_table_1( stg_table_1_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                             service_date datetime2,
                            val  int)
CREATE TABLE dbo.stg_table (stg_table_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                            service_date datetime2,
                            val  int)

PK's + Índices agrupados em campos de identidade.

DML

INSERT INTO dbo.stg_table WITH(TABLOCK)
(
service_date,val) 
SELECT -- 1M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

INSERT INTO dbo.st_table_1 WITH(TABLOCK)
(
service_date,val) 
SELECT -- 2.5M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(2500000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

INSERT INTO dbo.stg_table WITH(TABLOCK)
(
service_date,val) 
SELECT -- 4M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(4000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

2,5 milhões de linhas dbo.st_table_1e 5 milhões de linhas em dbo.stg_table (quase) todas essas 2,5 milhões de linhas serão excluídas pela consulta, que é mais de 10 vezes menor que a sua.

Executando sua consulta

O plano de execução real para sua instrução de exclusão básica

Como esperado, dbo.stg_tableé acessado duas vezes para obter os valores máximo e mínimo com uma agregação de fluxo. O tempo de CPU e decorrido / tempo de execução:

  CPU time = 4906 ms,  elapsed time = 4919 ms.

Uma dica de índice ausente é adicionada ao plano de execução:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[st_table_1] ([service_date])
INCLUDE ([stg_table_1_ID])

No entanto , quando adicionamos o índice, uma classificação extra aparece para excluir as linhas desse índice recém-adicionado:

O plano

E o tempo de CPU / tempo decorrido aumenta:

   CPU time = 11156 ms,  elapsed time = 11332 ms.

YMMV , mas no meu exemplo, com base em seus comentários sobre os dados, não melhorou a consulta.

Criando um índice em [dbo].[stg_table]

CREATE NONCLUSTERED INDEX IX_service_date
ON [dbo].[stg_table] ([service_date]);

Como resultado, o MAX()and MIN()pode aproveitar o índice recém-criado para retornar apenas uma linha em vez de uma verificação completa do índice clusterizado:

Com o tempo de execução melhorado:

 SQL Server Execution Times:
   CPU time = 2609 ms,  elapsed time = 4028 ms.

E o plano de execução

Mas isso é baseado apenas na indexação e no meu próprio exemplo. Prossiga por sua conta e risco.

Notas Extras

Você deve dividir essa exclusão em lotes separados para que ela não preencha o arquivo de log e não tenha um grande bloco de exclusão com falha / sucesso.

Você também pode considerar usar (TABLOCK)para que toda a tabela esteja bloqueada desde o início.

SET STATISTICS IO, TIME ON;
delete dbo.st_table_1 WITH(TABLOCK)
where service_date between(select min(service_date) from stg_table)
                   and (select max(service_date) from stg_table);

Atualização: SELECT INTO+sp_rename

select * into db.temp_stg_table_1
from db.stg_table_1
where service_date not between( select min(service_date) from db.stg_table)
                             and (select max(service_date) from db.stg_table);

exec sp_rename 'stg_table_1' , 'stg_table_1_old'

exec sp_rename 'temp_stg_table_1' , 'test_table_1'

drop table stg_table_1_old

que tal se ir com a lógica acima para excluir os milhões de registros. quaisquer vantagens e desvantagens com isso.

Além do desempenho, sp_renameprecisa de um Sch-Mbloqueio para ser concluído, o que significa que ele precisa esperar que todas as outras sessões liberem seus bloqueios na tabela antes de poder ser modificado. Quaisquer índices/restrições na tabela original desaparecerão e você terá que recriá-los.

Quando executo a consulta em meus próprios dados:

select * into dbo.temp_stg_table_1
from dbo.st_table_1
where service_date not between( select min(service_date) from dbo.stg_table)
                             and (select max(service_date) from dbo.stg_table);

Isso não representa seus dados, lembre-se disso.

Ele está lendo todas as linhas para retornar 0, o que não é o ideal.

Com um alto tempo de execução:

 SQL Server Execution Times:
   CPU time = 27717 ms,  elapsed time = 10657 ms.

Mas isso não é realmente significativo sem mais informações sobre seus dados. Um plano de consulta seria necessário para fornecer conselhos mais corretos.

Eu simplesmente nunca excluiria 37 milhões de linhas em uma instrução . Não se trata do plano de execução que você obtém - a sobrecarga de encontrar linhas para excluir (se você tem sniffing de parâmetro afetando a localização dessas linhas ou não) é muito menor do que a sobrecarga de realmente excluí-las e registrar essas exclusões. Se você dividir isso em partes, poderá amortizar esse custo ao longo do tempo e processar as exclusões em uma programação que seja adequada à sua fantasia, em vez de tudo de uma vez.

-- you can play with these parameters to see what offers the best trade-off
DECLARE @BatchSize int = 10000, @TransactionInterval tinyint = 5;

DECLARE @s datetime, @e datetime, @r int = 1;

SELECT @s = MIN(service_date), @e = MAX(service_date) FROM dbo.stg_table;

BEGIN TRANSACTION;

WHILE (@r > 0)
BEGIN
  IF @r % @TransactionInterval = 1
  BEGIN
    COMMIT TRANSACTION;
    BEGIN TRANSACTION;
  END

  DELETE TOP (@BatchSize) FROM db.st_table_1
    WHERE service_date >= @s AND service_date <= @e;

  SET @r = @@ROWCOUNT;
END

IF @@TRANCOUNT > 0
BEGIN
  COMMIT TRANSACTION;
END

Você também pode considerar a durabilidade atrasada se estiver em uma versão moderna o suficiente do SQL Server (consulte esta resposta e esta postagem de blog ).

A consulta acima pode funcionar bem devido ao índice ausente, mas a consulta ainda está errada.

Declare @Fromdate DateTime
Declare @Todate DateTime

select @Fromdate=min(service_date),@Todate=max(service_date) 
from dbo.stg_table

SET STATISTICS IO, TIME ON;
delete dbo.st_table_1 WITH(TABLOCK)
where service_date >=@Fromdate
                  and service_date <=@Todate

Peguei o exemplo acima e executei sem Index, demorou 18 segundos para excluir 410792 linhas.

Se eu criar o Index como acima, sem dúvidas, ele terá o melhor desempenho.

1. Portanto, não Sub Queryem Wherecondições, pode dar High Cardianility Estimateem consulta complexa.
2. Dê mais importância por escrito Optimize querydo que index.Ambos são importantes.

Observação :

Se o desempenho for ruim ou pior por causa Parameter Sniffingdisso, você deve encontrar uma maneira adequada de evitar Parameter sniffing, caso contrário, você deve IGNORÁ-lo.

Afinal nem tudo Store Procedureé escrito com OPTION RECOMPILE.

Pelo que entendi, no meu script @FromDatee @Todatenão são parâmetros proc, são variáveis ​​locais, então não há dúvida de Parameter Sniffing.