Quando uso uma tabela com otimização de memória com um procedimento armazenado que passa a tabela para uma função com valor de tabela embutido, recebo um erro:
Msg 596, Level 21, State 1, Line 0
Não é possível continuar a execução porque a sessão está no estado de interrupção.
Msg 0, Level 20, State 0, Line 0
Ocorreu um erro grave no comando atual. Os resultados, se existirem, deveriam ser descartados.
Estou usando o SQL Server 2016 Developer Edition:
Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) - 13.0.4435.0 (X64)
27 de abril de 2017 17:36:12
Copyright (c) Microsoft Corporation
Developer Edition (64 bits) no Windows 10 Pro 6.3 (Build 14393: ) (Hipervisor)
Eu posso recriar o erro assim:
CREATE TYPE [dbo].[Name] AS TABLE
( [Name] VARCHAR(500) NOT NULL PRIMARY KEY NONCLUSTERED )
WITH (MEMORY_OPTIMIZED = ON)
GO
CREATE TABLE Table1
( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL
, [Data] nvarchar(max) not null
)
INSERT INTO Table1 ([Data])
VALUES (N'blah1'), (N'blah2'), (N'blah3'), (N'blah4')
GO
CREATE OR ALTER FUNCTION dbo.Test
( @table dbo.[Name] READONLY
) RETURNS TABLE AS RETURN
SELECT *
FROM Table1 t
JOIN @table t2
ON t2.[Name] = t.[Data]
GO
CREATE OR ALTER PROCEDURE dbo.Test2
@table dbo.[Name] READONLY
AS
SELECT *
FROM dbo.Test(@table)
GO
DECLARE @memTable dbo.[Name]
INSERT INTO @memTable ([Name])
VALUES ('blah1'), ('blah3')
EXEC dbo.Test2 @memTable
Às vezes isso acontece e às vezes não. Eu tenho outros procedimentos que funcionam de forma semelhante e geralmente funcionam bem.
Eu recebo esse despejo no meu ERRORLOG
(abreviado para abreviar):
2017-07-17 13:14:36.14 spid56 ***Stack Dump sendo enviado para
C:\Program Files\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\SQLDump0058.txt 2017-07-17
13:14:36.14 spid56 SqlDumpExceptionHandler: Processo 56 gerou
exceção fatal c0000005 EXCEPTION_ACCESS_VIOLATION. O SQL Server está
encerrando este processo. 2017-07-17 13:14:36.14 spid56 * ***************************************** **************************************** 2017-07-17 13:14: 36.14 spid56 * 2017-07-17 13:14:36.14 spid56
* BEGIN STACK DUMP: 2017-07-17 13:14:36.14 spid56 * 07/17/17 13:14:36 spid 56 2017-07-17 13: 14:36.14 spid56 * 2017-07-17
13:14:36.14 spid56 * 2017-07-17 13:14:36.14 spid56 *
Endereço de exceção = 00007FF9DBE76564 Módulo (SQLLANG+00000000000C6564)
2017-07-17 13: 14: 36.14 SPID56 * Código de exceção = C0000005 Exception_Access_Violation
2017-07-17 13: 14: 36.14 Spid56 *
Acesso
13:14:36.14 spid56 * Buffer de entrada 292 bytes - 2017-07-17
13:14:36.14 spid56 * DECLARE @memTable dbo.[Nome]
INSERT INTO @memTable ([Nam 2017-07-17 13:14:36.14 spid56 * e]) VALORES ('blah1'), ('blah3') EXEC dbo.Test2 @memTable
2017-07-17 13:14:36.14 spid56 * 2017-07-17 13:14:36.14 spid56
* 2017-07-17 13: 14: 36.14 SPID56 * 2017-07-17 13: 14: 36.14 SPID56 * Tamanho final da base da base do módulo 2017-07-17 13: 14: 36.14 SPID56 *
SQLSERVR 00007FF6C6F30000 00007FF6F6F6F6F97FF97FF6F6F6F30000 00007FD56 13:14:
36.14 spid56 * ntdll 00007FF9FE880000 00007FF9FEA50FFF 001d1000 2017-07-17 13:14:36.14 spid56 * KERNEL32 00007FF9FC7E0000 00007FF9FC88BFFF ...
00019000 2017-07-17 13:14:36.14 spid56 * xtp_v_8_907150277_184387255245303_1 00007FF9E7590000 00007FF9E75A8FFF
00019000 2017-07- 17 13:14:36.14 spid56 * dbghelp
0000000077350000 00000000774E5FFF 00196000 2017-07-17 13:14:36.14
SPID56 * 2017-07-17 13: 14: 36.14 SPID56 * P1HOME:
FFFFFFFFFFFFFE: 2017-07-17 13: 14: 36.14 SPID56 * P2HOME:
000000000000: 2017: 07-17 13: 14: 36.14
SPID56 00007FF9DD881A70 000001CF0CAA8A70 0000000000000000 000001CF31FE5C08 000001CF33A76040 000001CF125481A0
2017-07-17 13:14:36.14 spid56 * P4Home: 0000000000000040:
2017-07-17 13:14:36.14 spid56 * P5Home: 000001CF3FD16080: 00007FF9DB1D8C10 000001CF406CB248 000001CF3FD16CD8 0000000000000003
000001CF40080550 000001CF40080048 2017-07-17 13 :14:36.14 spid56
* P6Home: 00007FF9DB0585C9: EE850FC085ED3345 20356E05F7000382 0E850F0010000000 0268858B48000383 890274C085480000 249C8D4CC68B4818
2017-07-17 13:14:36.14 spid56 * ContextFlags: 000000000010005F:
2017-07-17 13:14:36.14 spid56 * MxCsr: 0000000000001FA8:
...
2017-07- 17 13:14:36.14 spid56 * Rcx: 0000000000000000:
2017-07-17 13:14:36.14 spid56 * Rdx: 0000000000000000:
2017-07-17 13:14:36.14 spid56 * Rbx: 0000007EF8A7AB80: 00007FF9DD881A70 000001CF0CAA8A70 0000000000000000 000001CF31FE5C08
000001CF33A76040 000001CF125481A0 17-07-2017 13:14:36.14 spid56
* Rsp: 0000007EF8A7A570: 000001CF1E6CC370 000001CF35D1D450 000001CF35D1D500 000001CF33A76040 00007FF9DB0BCB10 0000000000000000
2017-07-17 13:14:36.14 spid56 * Rbp: 000001CF35D1D450: 00007FF9DD80C7B0 000001CF00000001 000001CF35D1D500 0000000000000004
000001CF35D1D478 0000000000000000 2017-07-17 13:14:36.14 spid56
* Rsi: 000001CF35D1D500: 00007FF9DD82C638 0000000000000001 000001CF0A59A060 0000000000000006 0000000000000000 000001CF35D1D5C0
2017-07-17 13:14:36.14 spid56 * Rdi: 00007FF9DD86FF28: 00007FF9DC8968A0 00007FF9DC47C240 00007FF9DC47C250 00007FF9DC47C260
00007FF9DC62A170 00007FF9DD3D1D40 2017-07-17 13:14:36.14 spid56
* R8: 0000000000000001: 2017-07-17 13:14:36.14 spid56 * R9: 0000000000000038: 2017-07-17 13:14:36.14 spid56 *
R10: 000001CF33A76040: 00007FF9DB1D8718 000001CF00000001 0000000000002000 000001CF0000006E 000001CF0B9E0100 000001CF00000000
2017-07-17 13: 14:36.14 spid56 * R11: 0000007EF8A7A520: 0000000000000000 00007FF9DBE75499 000001CF33A50260 0000000000000000
000001CF33A50260 000001CF0CA8B480 2017-07-17 13:14:36.14 spid56
* R12: 000001CF20580E30: 00007FF9DD80C7B0 0000000000000001 000001CF20580EE0 0000000000000002 000001CF20580E58 000001CF0CA83910
2017-07-17 13:14:36.14 spid56 * R13 : 000001CF0CA8B480: 00007FF9DD827FA8 000001CF00000001 000001CF0A59A060 000001CF00000008
0000000000000000 000001CF0A59A060 2017-07-17 13:14:36.14 spid56
* R14: 0000000000000000: 2017-07-17 13:14:36.14 spid56 * R15: 000001CF35D1D5C0: 00007FF9DD86F068 000001CF0A59A060 000001CF00000000 0000000000000000 000001CF0CA82490 0000000000000000
2017-07-17 13:14:36.14 SPID56*RIP: 00007FF9DBE76564: 8B486850FF018B48 896052FFC88B4810 8B490000D02484 [ SPSTIONHATHATHATHATHATHATHATHATHATHATHATHATHOTHATHATHATHATHOTHATHATHATHATHOTHATHATHATHATHATHOMTIONSTONHATHATHATHOTHATHATHATHATHATHATHATHATHATHATHTONHATHATHATHOMATHTOMATHATHATHATHATHTONHATHATHATHATHATHATHAIRO
.
**************************************************** *** 2017-07-17 13:14:36.14 spid56 * ----------------------------------- -------------------------------------------- 2017-07-17 13 :14:36.14 spid56 * Short Stack Dump 2017-07-17
13: 14: 36.14 SPID56 00007FF9DBE76564
...
2017-07-17 13: 14: 36.16 SPID56 00007FF9FE8E70D1
Módulo (NTDLL+00000000000670D1) 2017-07-17 13: 14: 36.16
SPID56 17
13:14:36.87 spid56 Código de retorno do processo de despejo externo 0x20000001.
O processo de despejo externo não retornou erros. 17/07/2017 13:14:36.87
Erro do servidor: 17310, gravidade: 20, estado: 1. 17/07/2017
13:14:36.87 Servidor Uma solicitação de usuário da sessão com SPID 56
gerou uma exceção fatal. O SQL Server está encerrando esta sessão.
Entre em contato com os Serviços de Suporte ao Produto com o dump produzido no
diretório de log.
Este é um bug: Conectar Envio de Bug
Uma solução alternativa é não passar a tabela com otimização de memória para o procedimento armazenado. Você pode usá-lo depois disso, no entanto.
Corrigido no SQL Server 2017 CU1:
Correção: Erro quando um procedimento armazenado passa uma tabela com otimização de memória para uma função com valor de tabela embutida no SQL Server 2017