SQL Server Full-Text Search Order by another Column Performance

Question

I have two tables, Entity and EntityDetails.

Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime).

EntityDetails table has columns EntityId, Details1, Details2.

Where Col3 in Entity table and Details1 and Details2 in EntiryDetails table are full text indexed.

I also have a NONCLUSTERED INDEX on Entity table.

CREATE NONCLUSTERED INDEX [IX_LastUpdated] ON [dbo].[Entity]
(
    [LastUpdated] DESC,
    [Col2] ASC
)
INCLUDE(EntityId, Col1, Col3) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Now, when I perform full text search on full text indexed columns using CONTAINS(EntityDetails.[Details1],EntityDetails.[Details2],EntityDetails.[Details3,@Keywords]), everything works fine if the results are served as it is. But when I add Order By LastUpdated to the query I see 'Worktable' with a lot of Scans and thousands of logical reads in query statistics and Index Spool (Eager).

I thought its probably because the results returned by the fulltext search are in default order of the key column.

So I added a NONCLUSTERED INDEX on EntityDetails.

CREATE UNIQUE NONCLUSTERED INDEX [IX_EntityDetails] ON [dbo].[EntityDetails]
(
    [EntityId] DESC
)
INCLUDE([Details1],[Details2],[Details3]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

And tried ORDER BY EntityId DESC, but still same.

Can anyone please help?

Update

Here's the query plan

URL to Query Plan

https://www.brentozar.com/pastetheplan/?id=HyF0nB_9U

Full Text Indexes

CREATE FULLTEXT INDEX ON EntityDetails(Details1,Details2,Details3) KEY INDEX PK_EntityDetails;

CREATE FULLTEXT INDEX ON Entity(Col3) KEY INDEX PK_Entity;

NONCLUSTERED Index on Entity Table

CREATE NONCLUSTERED INDEX [IX_EntityLastUpdated] ON [dbo].[Entity]
(
    [LastUpdated] DESC,
    [Col4] ASC,
    [Col5] ASC,
    [Col6] ASC
)
INCLUDE([EntityId],[Col1],[Col2],[Col3]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Update 2

Here's the updated plan with INDEX(PK_Entity) and ORDER BY EntityId DESC

https://www.brentozar.com/pastetheplan/?id=SknlXxj9I

and with INDEX(PK_Entity) and ORDER BY LastUpdated DESC

https://www.brentozar.com/pastetheplan/?id=HkjV7gs98

Update 3

After trying the Answer:

When using

DECLARE @Keywords varchar(255) = 'keyword',
@Col4 tinyint = 162,
@Col3 varchar(255) = 'value';

;WITH Entity_CTE (EntityId, Col1, Col2, Col3, Col4,LastUpdated, Details2, Details1)  
AS  
(  
    SELECT 
        Entity.[EntityId], 
        Entity.[Col1],
        Entity.[Col2], 
        Entity.[Col3], 
        Entity.[Col4],
        Entity.[LastUpdated],
        EntityDetails.[Details2],
        EntityDetails.[Details1]
    FROM [Entity] WITH (NOLOCK) 
        INNER JOIN [EntityDetails] WITH (NOLOCK) on Entity.[EntityId] = EntityDetails.[EntityId] 
    WHERE 
        CONTAINS((EntityDetails.[Details1],EntityDetails.[Details2]), @Keywords) 
        AND 
        Entity.[Col4]=@Col4 
        AND CONTAINS((Entity.[Col3]), @Col3)  
)  
SELECT EntityId, Col1, Col2, Col3, Col4, Details2, Details1  
FROM Entity_CTE
ORDER BY [LastUpdated] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

I have below IO statistics:

Table 'EntityDetails'. Scan count 0, logical reads 4299, physical reads 0, page server reads 0, read-ahead reads 22, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Entity'. Scan count 1, logical reads 22, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

When using above query inside a stored procedure:

CREATE PROCEDURE usp_TEMP
@Keywords varchar(255),
@Col4 tinyint = 162,
@Col3 varchar(255)

AS

;WITH Entity_CTE (EntityId, Col1, Col2, Col3, Col4,LastUpdated, Details2, Details1)  
AS  
(  
    SELECT 
        Entity.[EntityId], 
        Entity.[Col1],
        Entity.[Col2], 
        Entity.[Col3], 
        Entity.[Col4],
        Entity.[LastUpdated],
        EntityDetails.[Details2],
        EntityDetails.[Details1]
    FROM [Entity] WITH (NOLOCK) 
        INNER JOIN [EntityDetails] WITH (NOLOCK) on Entity.[EntityId] = EntityDetails.[EntityId] 
    WHERE 
        CONTAINS((EntityDetails.[Details1],EntityDetails.[Details2]), @Keywords) 
        AND 
        Entity.[Col4]=@Col4 
        AND CONTAINS((Entity.[Col3]), @Col3)  
)  
SELECT EntityId, Col1, Col2, Col3, Col4, Details2, Details1  
FROM Entity_CTE
ORDER BY [LastUpdated] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

I see below IO statistics:

Table 'EntityDetails'. Scan count 0, logical reads 180, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 1401, logical reads 65571, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Entity'. Scan count 1, logical reads 22, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Update 4

It seems that using OPTION(QUERYTRACEON 4136) fixes the issue, but I am not sure about possible implications, does it has any negative impact?

SQL Server Full-Text Search Order by another Column Performance

0 Answers0