Minha versão do SQL Server é SQL Server 2019 (RTM-CU18). O código de reprodução a seguir requer que um grupo de arquivos na memória seja criado. Para quem está acompanhando, lembre-se de que um grupo de arquivos na memória não pode ser descartado de um banco de dados depois de criado.
Eu tenho uma tabela simples na memória na qual insiro números inteiros de 1 a 1200:
DROP TABLE IF EXISTS [dbo].[InMem];
CREATE TABLE [dbo].[InMem] (
i [int] NOT NULL,
CONSTRAINT [PK_InMem] PRIMARY KEY NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY );
INSERT INTO [dbo].[InMem]
SELECT TOP (1200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;
Eu também tenho o seguinte procedimento armazenado compilado nativamente:
GO
CREATE OR ALTER PROCEDURE p1
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
SELECT c1.i, c2.i, c3.i
FROM dbo.[InMem] c1
CROSS JOIN dbo.[InMem] c2
CROSS JOIN dbo.[InMem] c3
WHERE c1.i + c2.i + c3.i = 3600;
END;
GO
O procedimento retorna uma linha quando executado. Na minha máquina, leva cerca de 32 segundos para ser concluído. Não consigo observar nenhum comportamento incomum em termos de uso de memória durante a execução.
Posso criar um tipo de tabela semelhante:
CREATE TYPE [dbo].[InMemType] AS TABLE(
i [int] NOT NULL,
INDEX [ix_WordBitMap] NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON );
bem como o mesmo procedimento armazenado, mas usando o tipo de tabela:
GO
CREATE OR ALTER PROCEDURE p2 (@t dbo.[InMemType] READONLY)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
SELECT c1.i, c2.i, c3.i
FROM @t c1
CROSS JOIN @t c2
CROSS JOIN @t c3
WHERE c1.i + c2.i + c3.i = 3600;
END;
GO
O novo procedimento armazenado gera um erro após cerca de um minuto:
Msg 701, Nível 17, Estado 154, Procedimento p2, Linha 6 [Batch Start Line 57] Não há memória de sistema suficiente no pool de recursos 'padrão' para executar esta consulta.
Enquanto o procedimento é executado, posso ver a quantidade de memória usada pelo secretário de memória MEMORYCLERK_XTP aumentar para cerca de 2800 MB para o banco de dados consultando o sys.dm_os_memory_clerksdmv. De acordo com o sys.dm_db_xtp_memory_consumersDMV, quase todo o uso de memória parece ser do consumidor "pool de páginas de 64K":
Para referência, aqui está como executei o novo procedimento armazenado. Ele usa as mesmas 1200 linhas da tabela:
DECLARE @t dbo.[InMemType];
INSERT INTO @t (i)
SELECT i
from [dbo].[InMem];
EXEC p2 @t;
O plano de consulta resultante é um plano de loop aninhado simples sem operadores de bloqueio. Por solicitação, aqui está um plano de consulta estimado para o segundo procedimento armazenado.
Não entendo por que o uso de memória aumenta para mais de 2 GB para essa consulta quando uso um parâmetro com valor de tabela. Eu li vários pedaços de documentação e white papers OLTP na memória e não consigo encontrar nenhuma referência a esse comportamento.
Usando o rastreamento ETW, posso ver que o primeiro procedimento gasta a maior parte do tempo da CPU chamando hkengine!HkCursorHeapGetNexte o segundo procedimento gasta a maior parte do tempo da CPU chamando hkengine!HkCursorRangeGetNext. Também posso obter o código-fonte C para ambos os procedimentos. O primeiro procedimento está aqui e o segundo procedimento, com o problema de memória, está aqui . No entanto, não sei ler o código C, então não sei como investigar mais.
Por que um procedimento armazenado simples compilado nativamente usa mais de 2 GB de memória ao executar loops aninhados em um parâmetro com valor de tabela? O problema também ocorre quando executo a consulta fora de um procedimento armazenado.
