KB4013877 - Atualização permite que o plano de consultas DML analise tabelas de consulta otimizadas para memória em paralelo no SQL Server 2016

Resumo

Antes desta atualização, não há suporte para planos de consulta paralela para operações DML que façam referência a tabelas ou variáveis de tabela com otimização de memória, mesmo que elas não sejam o destino da operação DML. As instruções INSERT, UPDATE, DELETE e MERGE sempre teriam um plano serial se houver qualquer referência a tabelas ou variáveis de tabela com otimização de memória, mesmo para casos em que os planos paralelos são suportados com tabelas e variáveis de tabela tradicionais baseadas em disco.

Esta atualização adiciona suporte para planos paralelos e análise paralela de tabelas com otimização de memória e variáveis de tabela em operações DML que fazem referência a tabelas com otimização de memória ou variáveis de tabela, desde que não sejam o destino da operação DML. As instruções INSERT, UPDATE, DELETE e MERGE que envolvem a modificação de uma tabela ou variável de tabela com otimização de memória continuam a ser seriais.

Seguem-se os três requisitos para ativar planos paralelos para instruções DML que façam referência a tabelas ou variáveis de tabela com otimização de memória:

1. Certifique-se de que a base de dados utiliza o nível de compatibilidade 130. Este é um pré-requisito para planos paralelos com tabelas com memória otimizada.

2. Instale uma atualização de serviço que inclua essa correção. Consulte a secção "Mais informações".

3. Ative esta atualização de uma de duas formas:

   1. Ative o sinalizador de rastreio 9939 para ativar apenas esta atualização. Esta opção é recomendada para todos os clientes que utilizam tabelas ou variáveis de tabela com otimização de memória e que ainda não estão a utilizar a opção 3b abaixo.
   2. Habilite os hotfixes do otimizador de consultas SQL Server. Pode fazê-lo através do sinalizador de rastreio 4199, de uma configuração do âmbito da base de dados ou de uma sugestão de consulta. Para obter mais informações, consulte SQLServer query optimizer hotfix trace flag 4199 servicing model.

Resolução

Este problema foi corrigido nas seguintes atualizações cumulativas para SQL Server:

Atualização cumulativa 5 para SQL Server 2016

Atualização cumulativa 3 para SQL Server 2016 SP1

Acerca das atualizações cumulativas para SQL Server:

Cada nova atualização cumulativa para SQL Server contém todos os hotfixes e todas as correções de segurança que foram incluídas com a atualização cumulativa anterior. Consulte as atualizações cumulativas mais recentes para SQL Server:

Atualização cumulativa mais recente para o SQL Server 2016

Apêndice: Exemplo da correção

O script Transact-SQL a seguir ilustra um plano paralelo para uma operação DML em uma tabela baseada em disco que faz referência a uma tabela com otimização de memória. Com esta atualização aplicada, o script irá devolver dois planos de consulta para a mesma operação. O primeiro é sem rastreamento sinalizador 9939 e será serial. O segundo está com o sinalizador de rastreamento 9939 habilitado e será paralelo.

-- make sure that you connect to a database that has a memory-optimized filegroup with at least one container
-- the following script creates such a filegroup and container if it does not exist:
--   https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/features/in-memory/t-sql-scripts/enable-in-memory-oltp.sql
 
-- make sure DB compat level 130 is used
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130
GO
 
DROP PROCEDURE IF EXISTS dbo.[InsertSalesOrder_Native_Batch]
DROP TABLE IF EXISTS dbo.SalesOrder_MemOpt
GO
CREATE TABLE dbo.SalesOrder_MemOpt (
    order_id     INT      IDENTITY NOT NULL,
    order_date   DATETIME NOT NULL,
    order_status TINYINT  NOT NULL,
    amount       FLOAT    NOT NULL,
    CONSTRAINT PK_SalesOrderID PRIMARY KEY NONCLUSTERED HASH (order_id) WITH (BUCKET_COUNT = 10000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO
 
-- Natively compiled proc
-- Create Natively compiled procedure to speed up inserts.
CREATE PROCEDURE [dbo].[InsertSalesOrder_Native_Batch]
@order_status TINYINT=1, @amount FLOAT=100, @OrderNum INT=100
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
    DECLARE @counter AS INT = 1;
    WHILE @counter <= @OrderNum
        BEGIN
            INSERT  INTO dbo.SalesOrder_MemOpt
            VALUES (getdate(), @order_status, @amount);
            SET @counter = @counter + 1;
        END
END;
GO
-- Insert sample data
EXECUTE dbo.[InsertSalesOrder_Native_Batch] 1, 100, 500000;
GO 10
 
 
SET SHOWPLAN_XML ON
GO
 
-- Insert into a temp table from memory-optimized table
-- Without the trace flag, this is serial. Inspect the query plan to verify it is serial.
SELECT * INTO #temp FROM SalesOrder_MemOpt
GO
SET SHOWPLAN_XML OFF
GO
-- enable parallel plan for DML with references to memory-optimized tables
DBCC TRACEON (9939)
GO
 
SET SHOWPLAN_XML ON
GO
-- Verify the query plan is parallel.
SELECT * INTO #temp FROM SalesOrder_MemOpt
GO
 
SET SHOWPLAN_XML OFF
GO
 
DBCC TRACEOFF (9939)
GO

Referências

Saiba mais sobre a terminologia que a Microsoft utiliza para descrever as atualizações de software.