0

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 Query Plan Full text Search Enger Spool

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?

12
  • Hi, Naveed Ahmed. Please, we'll be able to better help you if your performance question contained the execution plan and query being executed, like this.
    – Ronaldo
    May 12 '20 at 11:35
  • Thank you so much Ronaldo for your reply, I have added the query plan to the question, can you please take a look. May 12 '20 at 16:10
  • Is there a performance problem or you just want to avoid a worktable in your exec plan? May 12 '20 at 16:35
  • The query performance is poor (runs slow) when I apply ORDER BY LastUpdated DESC. And I want the results to be sorted by last modified date. If I make ORDER BY EntityId the results are returned very quickly, I want the same performance but with LastUpdated DESC.Is it possible? I think the performance problem is due to the worktable/ Index Spool(Eager) May 12 '20 at 16:58
  • Could you also run the query without the ORDER BY using the same parameters and add that query plan so that we can compare them?
    – Ronaldo
    May 13 '20 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.