0

This simplified query on SQL 2017 takes 40+ sec to complete, I suspect its parameter sniffing issue, but not 100% sure.

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
  • This query returns 15 rows out of 1.7mil rows in dbo.Ticket table
  • Tables State, StateType, Board are really small, 400, 20, 35 rows respectively
  • In the WHERE clause, if I swap the IN filter to use = for T.BoardRecId, it completes in 7 sec
  • Removing the OFFSET FETCH, original query completes in 13 sec, and previous query completes in 1 sec
  • If I set parameter value @BoardRecId='14', duration improves (majority of table contains rows with this value)
  • Tried updating stats with fullscan for the table, no change in performance
  • Tried creating varying indexes, perf did not improve
  • Tried OPTION (RECOMPILE) inside the sp_executesql did not help
  • Did not try rebuilding indexes yet as this has to be done during maintenance hours
  • Tried replacing dbo.Split with a table variable and/or temp table, no improvement

I need ability to support multiple BoardRecIds, which is the reason behind the dbo.Split function, all it does is breaking out a comma separated string to be used inside an IN clause.

The schema is much larger in terms of columns, so trying to simplify it, note that the joined columns all have indexes.

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

Simplified Plan using @BoardRecId IN (SELECT items FROM dbo.Split(...) - 40+ sec
Simplified Plan using @BoardRecId = @BoardRecId - 7 sec
Simplified Plan using @BoardRecId = 17 and dbo.Split(...) - 12 sec

None of these durations are optimal, but the 2nd and 3rd are much better than 40+ sec, so just trying to figure out how to make the best of a bad scenario, and hoping someone can provide a silver bullet here.

0

2 Answers 2

3

If you have a query that doesn't improve with OPTION(RECOMPILE) then you likely have a separate issue from parameter sniffing, that is the root issue. I see some early cases of bad cardinality estimate issues that I think is your root problem.

Specifically I would start looking into your T.BoardRecId IN (...) clause. You'd probably be better off materializing the results of the Split() function into a temp table first, and then INNER JOINing to that temp table in your main query instead, to filter on that predicate. The IN clause is known to cause cardinality estimate issues, as can subqueries and functions, in WHERE clauses.

1
  • 1
    Don't forget a clustered primary key on the temp table. A table valued parameter is another option, especially if using client apps, this can also have indexes. Oct 2, 2022 at 22:43
-2

replace dbo.split finction with STRING_SPLIT...

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.