2

I am doing performance experiments regarding the fulltext search feature of Microsoft SQL Server using the stackoverflow database.

I have a (seemingly simple) query that results in different execution plans (and vastly different run times) depending simply on the specified offset value.

This version of the query (offset 0) is slow:

SELECT Comments.Id, Comments.PostId, Comments.Score, Comments.Text, Comments.CreationDate, Comments.UserId
FROM Comments   
WHERE contains(Text,'SQL')
AND contains(Text,'Server')
AND contains(Text,'2016')
AND Score IS NOT NULL
ORDER BY Score DESC
OFFSET 0 rows
FETCH NEXT 100 ROWS ONLY
OPTION (RECOMPILE);
(100 rows affected)
Table 'Worktable'. Scan count 2159279, logical reads 4325811, 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 'Comments'. Scan count 1, logical reads 12299107, physical reads 1661, page server reads 0, read-ahead reads 2466352, 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 0, logical reads 0, 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.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 43515 ms,  elapsed time = 23474 ms.

Execution plan: enter image description here

This version of the query (offset 2000), however, completes within fractions of a second:

SELECT Comments.Id, Comments.PostId, Comments.Score, Comments.Text, Comments.CreationDate, Comments.UserId
FROM Comments   
WHERE contains(Text,'SQL')
AND contains(Text,'Server')
AND contains(Text,'2016')
AND Score IS NOT NULL
ORDER BY Score DESC
OFFSET 2000 rows
FETCH NEXT 100 ROWS ONLY
OPTION (RECOMPILE);
(100 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, 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 'Comments'. Scan count 0, logical reads 11853, physical reads 0, page server reads 0, read-ahead reads 30, 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 0, logical reads 0, 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.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 20 ms.

Execution plan: enter image description here

If, in the first query, I replace the ORDER BY / OFFSET 0 / FETCH NEXT 100 by a simple TOP 100, but of course, this is not a viable solution in practice, because I want that specific (and deterministic) order:

(100 rows affected)
Table 'Comments'. Scan count 0, logical reads 821, physical reads 0, page server reads 0, read-ahead reads 107, 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.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 6 ms.

enter image description here

More info about the data and schema:

  • There are 74,428,966 rows in the table Comments.
  • Here is the table definition:
CREATE TABLE [dbo].[Comments](
    [Id] [int] NOT NULL,
    [PostId] [int] NULL,
    [Score] [int] NULL,
    [Text] [nvarchar](max) NULL,
    [CreationDate] [datetime] NULL,
    [UserId] [int] NULL,
 CONSTRAINT [PK_Comments] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  • There is a full text search index on the Text column
  • Here are the other indexes:
CREATE NONCLUSTERED INDEX [Comments_CreationDate] ON [dbo].[Comments]
(
    [CreationDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Comments_PostId] ON [dbo].[Comments]
(
    [PostId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Comments_Score] ON [dbo].[Comments]
(
    [Score] ASC,
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Comments_UserId] ON [dbo].[Comments]
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

My question is: Why are the execution plans different and what would be the recommended way to improve the situation, such that both queries use the fast execution plan?

Addendum:

Based on one of Nikitas suggestions, I tried an altered version of the two queries.

Slow:

WITH CTE AS (SELECT Id   
FROM Comments   
WHERE contains(Text,'SQL')
AND contains(Text,'Server')
AND contains(Text,'2016')
AND Score IS NOT NULL
ORDER BY Score DESC
OFFSET 0 rows
FETCH NEXT 100 ROWS ONLY
)
SELECT Comments.Id, Comments.PostId, Comments.Score, Comments.Text, Comments.CreationDate, Comments.UserId
FROM Comments
JOIN CTE on Comments.Id = CTE.Id
ORDER BY Comments.score DESC
OPTION (RECOMPILE);
(100 rows affected)
Table 'Worktable'. Scan count 2009360, logical reads 4025794, 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 'Comments'. Scan count 1, logical reads 3915, 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.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 2125 ms,  elapsed time = 2117 ms.

Execution plan

Fast (same query, except OFFSET=2000):

(100 rows affected)
Table 'Comments'. Scan count 9, logical reads 131034, physical reads 0, page server reads 0, read-ahead reads 1304, 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 0, logical reads 0, 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 'Workfile'. Scan count 0, logical reads 0, 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 0, logical reads 0, 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.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 1548 ms,  elapsed time = 217 ms.

Execution plan

4
  • Does the problem still exist? Obviously, the first query was run on outdated statistics (look at estimated/actual rows on the first screen). Feb 27, 2020 at 15:35
  • I have rerun the queries and updated the query plans in the question. Although the query with offset 0 is faster than before, it is still significantly slower than the other one; also, the estimates are also still way off. I executed both queries immediately after each other. There are no other queries (or inserts, deletes, updates...) going on.
    – Hi4s
    Feb 27, 2020 at 16:03
  • 1
    could you please fix the question description? you use the same offset for both cases and I don't see the first execution plan Feb 27, 2020 at 16:33
  • You can't remove the ORDER BY without also removing the OFFSET. So can you show the query / plan without the ORDER BY and the OFFSET? Also to avoid things like key lookups, see this post, which describes why you shouldn't just use SELECT * ... OFFSET. sqlperformance.com/2015/01/t-sql-queries/… Feb 27, 2020 at 16:52

1 Answer 1

1

Different offset means different number of rows should be matched - 100 in the first case and 2100 in the second one. In the second case SQL Server may choose more expensive parallel execution plan but it may work faster by different reasons. For example, because of statistics misestimation the first query may work much slower than expected and it would be better to choose the second plan for it from the very beginning. To do this you could parametrize your query and use hint OPTIMIZE FOR @offset = 2000.
Also with proper indexing it may has a sense to use a subquery to select required row ids with offset first, and after that join other fields to these 100 ids rather than SELECT * with offset 2000 (this way SQL Server may need to sort a lot of large rows before it be able to omit first 2000 of them).
Please check the following query (for both offsets and with statistics time, io on enabled):

WITH CTE AS (SELECT id   
FROM Comments   
WHERE contains(Text,'SQL')
AND contains(Text,'Server')
AND contains(Text,'2016')
AND Score IS NOT NULL
ORDER BY Score DESC
OFFSET 0 rows
FETCH NEXT 100 ROWS ONLY
)
SELECT Comments.* 
FROM Comments
JOIN CTE on Comments.id = CTE.id
ORDER BY Comments.score DESC
OPTION (RECOMPILE)
;
8
  • Thanks for your ideas, @Nikita. Making the offset a variable and using the "optimize for" does indeed result in the quicker execution plan, but I am not sure if this solution will be applicable in the context of an application later on. It seems quite fragile and difficult to determine the right offset value to optimize for (here, it seems to be values >= 63). The CTE also made the slower query (offset 0, not optimized for larger offset) quicker, but it is still significantly slower than the one with the higher offset (or optimization).
    – Hi4s
    Feb 28, 2020 at 9:09
  • this CTE should make the second query faster too Feb 28, 2020 at 9:23
  • also you can try to save execution plan and use "USE PLAN N'your_xml_plan'" hint (or do the same using plan guide) but it may be not good idea too because this way you force SQL Server to use the same plan for any parameters Feb 28, 2020 at 9:39
  • I added the query plans and statistics output for your CTE suggestions. Regarding USE PLAN, it seems that this hint is not compatible with full-text search: Cannot execute query because USE PLAN hint conflicts with use of distributed query or full-text operations. Consider removing USE PLAN hint.
    – Hi4s
    Feb 28, 2020 at 10:32
  • @Hi4s, yes. Attempt failed:) sorry, I forgot about fill-text. Feb 28, 2020 at 10:46

Your Answer

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

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