Essa consulta simplificada no SQL 2017 leva mais de 40 segundos para ser concluída, suspeito que seja um problema de detecção de parâmetros, mas não tenho 100% de certeza.
exec sp_executesql N'
SELECT
T.[TicketRecId]
, T.[Title]
FROM dbo.Ticket T
INNER JOIN [dbo].[State] S
ON S.[StateRecId] = T.[StateRecId]
INNER JOIN [dbo].[StateType] ST
ON S.[StateTypeRecId] = ST.StateTypeRecId
INNER JOIN [dbo].[Board] B
ON B.[BoardRecId] = T.[BoardRecId]
WHERE 1=1
AND ST.[Name] NOT IN (''Closed'',''Canceled'')
AND T.BoardRecId IN (SELECT items FROM dbo.Split(@BoardRecId, '',''))
--AND T.BoardRecId = @BoardRecId2
--AND T.BoardRecId = 17
ORDER BY T.[TicketRecId] ASC
OFFSET (@PageNo - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
',N'@PageNo int,@PageSize int,@TeamRecId int,@BoardRecId VARCHAR(2),@BoardRecId2 INT'
,@PageNo=1,@PageSize=50,@TeamRecId=4,@BoardRecId ='17',@BoardRecId2=17
- Esta consulta retorna 15 linhas de 1,7 mil linhas na
dbo.Ticket
tabela - Tabelas
State
,StateType
,Board
são muito pequenas, 400, 20, 35 linhas respectivamente - Na
WHERE
cláusula, se eu trocar oIN
filtro a ser usado=
porT.BoardRecId
, ele completa em 7 segundos - A remoção da
OFFSET FETCH
consulta original é concluída em 13 segundos e a consulta anterior é concluída em 1 segundo - Se eu definir o valor do parâmetro
@BoardRecId='14'
, a duração melhora (a maioria da tabela contém linhas com esse valor) - Tentei atualizar as estatísticas com fullscan para a tabela, sem alteração no desempenho
- Tentei criar índices variados, perf não melhorou
- Tentei
OPTION (RECOMPILE)
dentro dosp_executesql
não ajudou - Ainda não tentei reconstruir os índices, pois isso deve ser feito durante as horas de manutenção
- Tentei substituir
dbo.Split
por uma variável de tabela e/ou tabela temporária, sem melhora
Eu preciso de capacidade para suportar multiple BoardRecIds
, que é a razão por trás da dbo.Split
função, tudo o que ela faz é quebrar uma string separada por vírgula para ser usada dentro de uma IN
cláusula.
O esquema é muito maior em termos de colunas, portanto, tentando simplificá-lo, observe que todas as colunas unidas têm índices.
CREATE TABLE [Support].[Ticket] (
[TicketRecId] BIGINT IDENTITY (1, 1) NOT NULL,
[BoardRecId] INT NOT NULL,
[StateRecId] INT NOT NULL,
[Title] VARCHAR (250) NOT NULL,
[IsDeleted] BIT NOT NULL,
[IsTemplate] BIT NOT NULL,
CONSTRAINT [PK_Ticket_TicketRecId] PRIMARY KEY CLUSTERED ([TicketRecId] ASC),
CONSTRAINT [FK_Ticket_BoardRecId] FOREIGN KEY ([BoardRecId]) REFERENCES [Support].[Board] ([BoardRecId]),
);
GO
CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecIdStateRecIdIsDeletedIsTemplate_Include]
ON [Support].[Ticket] ([BoardRecId],[StateRecId],[IsDeleted],[IsTemplate],)
GO
CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecId_IsDeleted_IsTemplate_Includes]
ON [Support].[Ticket] ([BoardRecId], [IsDeleted], [IsTemplate], [ContactRecId], [ContactSourceRecId])
INCLUDE ([StateRecId]);
GO
CREATE TABLE [Support].[Ticket] (
[TicketRecId] BIGINT IDENTITY (1, 1) NOT NULL,
[BoardRecId] INT NOT NULL,
[StateRecId] INT NOT NULL,
[Title] VARCHAR (250) NOT NULL,
[IsDeleted] BIT NOT NULL,
[IsTemplate] BIT NOT NULL,
CONSTRAINT [PK_Ticket_TicketRecId] PRIMARY KEY CLUSTERED ([TicketRecId] ASC),
CONSTRAINT [FK_Ticket_BoardRecId] FOREIGN KEY ([BoardRecId]) REFERENCES [Support].[Board] ([BoardRecId]),
);
GO
CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecIdStateRecIdIsDeletedIsTemplate_Include]
ON [Support].[Ticket] ([BoardRecId],[StateRecId],[IsDeleted],[IsTemplate],)
GO
CREATE NONCLUSTERED INDEX [nc_Ticket_BoardRecId_IsDeleted_IsTemplate_Includes]
ON [Support].[Ticket] ([BoardRecId], [IsDeleted], [IsTemplate], [ContactRecId], [ContactSourceRecId])
INCLUDE ([StateRecId]);
GO
CREATE TABLE [dbo].[State] (
[StateRecId] INT IDENTITY (1, 1) NOT NULL,
[BoardRecId] INT NOT NULL,
[StateTypeRecId] INT NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[SortOrder] SMALLINT NOT NULL,
[IsDefault] BIT NOT NULL,
[IsDeleted] BIT DEFAULT ((0)) NOT NULL,
CONSTRAINT [PK_State_StateRecId] PRIMARY KEY CLUSTERED ([StateRecId] ASC),
CONSTRAINT [FK_State_BoardRecId] FOREIGN KEY ([BoardRecId]) REFERENCES [dbo].[Board] ([BoardRecId]),
CONSTRAINT [FK_State_StateTypeRecId] FOREIGN KEY ([StateTypeRecId]) REFERENCES [dbo].[StateType] ([StateTypeRecId]),
);
GO
CREATE TABLE [dbo].[StateType] (
[StateTypeRecId] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[IsDeleted] BIT DEFAULT ((0)) NOT NULL,
CONSTRAINT [PK_StateType_StateTypeRecId] PRIMARY KEY CLUSTERED ([StateTypeRecId] ASC),
);
GO
CREATE TABLE [dbo].[Board] (
[BoardRecId] INT IDENTITY (1, 1) NOT NULL,
[TeamRecId] INT NOT NULL,
[Name] VARCHAR (50) NOT NULL,
[IsExternal] BIT DEFAULT ((0)) NOT NULL,
[IsDefault] BIT DEFAULT ((0)) NOT NULL,
[IsDeleted] BIT DEFAULT ((0)) NOT NULL,
[UpdatedDateUTC] DATETIMEOFFSET (0) DEFAULT (SYSUTCDATETIME()) NOT NULL,
CONSTRAINT [PK_Board_BoardRecId] PRIMARY KEY CLUSTERED ([BoardRecId] ASC),
CONSTRAINT [FK_Board_TeamRecId] FOREIGN KEY ([TeamRecId]) REFERENCES [dbo].[Team] ([TeamRecId]),
);
GO
CREATE NONCLUSTERED INDEX [nc_TicketType_BoardRecId]
ON [dbo].[Board]([BoardRecId] ASC);
GO
Plano Simplificado usando @BoardRecId IN (SELECT items FROM dbo.Split(...) - 40+ seg
Plano Simplificado usando @BoardRecId = @BoardRecId - 7 seg
Plano Simplificado usando @BoardRecId = 17 e dbo.Split(...) - 12 segundos
Nenhuma dessas durações é ideal, mas o 2º e o 3º são muito melhores do que 40+ segundos, então apenas tentando descobrir como tirar o melhor de um cenário ruim e esperando que alguém possa fornecer uma bala de prata aqui.
Se você tiver uma consulta que não melhora
OPTION(RECOMPILE)
, provavelmente terá um problema separado da detecção de parâmetros, esse é o problema raiz. Eu vejo alguns casos iniciais de problemas de estimativa de cardinalidade ruim que eu acho que é o seu problema raiz.Especificamente, eu começaria a analisar sua
T.BoardRecId IN (...)
cláusula. Você provavelmente seria melhor materializar os resultados daSplit()
função em uma tabela temporária primeiro e, em seguida,INNER JOIN
ir para essa tabela temporária em sua consulta principal, para filtrar esse predicado. AIN
cláusula é conhecida por causar problemas de estimativa de cardinalidade, assim como subconsultas e funções nasWHERE
cláusulas.