1

I've got a really strange issue with a Query plan generated for a very simple SQL query. The query is searching a full text index, and returning the count of records.

For some reason, this SQL query is producing a Non Clustered Scan on an index, which I don't believe it is optimal to do. I believe that for the count, as the Primary Key is in the full text index, a clustered seek would be all that is required.

Would anyone have any suggestions on why such a query plan is being used?

Odd thing is, with slight different variants of the SQL, sometimes it uses the Clustered Index (which is really fast), sometimes it uses the Non Clustered Seek.

Here's the query:

EXEC sp_executesql N'SELECT count(T.[ID])
FROM [dbo].[Item] AS T
WHERE CONTAINS (
        (
            T.[Description]
            )
        ,@P0M
        )
'
    ,N'@P0M nvarchar(4000)'
    ,@P0M = N'"mouse*"'

Here's the Query Plan: https://i.stack.imgur.com/1XJcf.png As you can see, the Non Clustered Seek costs 51%, plus 8% Parallelism and 15% Hash match on the Bitmap.

The table has a lot of data. Over 3 million records.

Many thanks

Edit: Here's the plan: https://www.brentozar.com/pastetheplan/?id=HyiABrg1K

Here's the table definition:

CREATE TABLE [dbo].[Item](
    [ID] [uniqueidentifier] NOT NULL,
    [Description] [nvarchar](500) NOT NULL,
    [Manufacturer] [uniqueidentifier] NOT NULL,
    [Manufacturer Name] [nvarchar](100) NULL,
    [Manufacturer Item No.] [nvarchar](50) NOT NULL,
    [BC Item No.] [varchar](20) NULL,
    [CRM Item No.] [varchar](50) NULL,
    [Category] [uniqueidentifier] NULL,
    [Version No.] [varchar](50) NULL,
    [Blocked] [bit] NULL,
    [UNSPSC Code] [int] NULL,
    [Barcode] [char](13) NULL,
    [Last Update Date Time] [datetime] NULL,
    [Weight (kg)] [decimal](18, 3) NULL,
    [RRP] [decimal](18, 2) NULL,
    [RRP Currency] [uniqueidentifier] NULL,
    [timestamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

There's also lots of foreign keys.

Here's the only other index:

CREATE UNIQUE NONCLUSTERED INDEX [Manufacturer Part No] ON [dbo].[Item]
(
    [Manufacturer] ASC,
    [Manufacturer Item No.] ASC
)
INCLUDE([ID]) 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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
JBird
  • 13
  • 4
  • 1
    If you want to supply a query plan, use [Paste the Plan](https://www.brentozar.com/pastetheplan/), not an image; we can inspect the query plan then. – Thom A Jul 29 '21 at 14:25
  • 1
    https://www.brentozar.com/archive/2020/11/why-full-texts-contains-queries-are-so-slow/ – Mitch Wheat Jul 29 '21 at 14:26
  • Dumping clustering keys into a temp table and joining again might be more performant. We can't test this on your data, you are going to have to do that. But please share the query plans as mentioned, and add your complete table and index definitions to the question – Charlieface Jul 29 '21 at 15:07
  • Thanks for comments. I've edited question to include the plan, plus table definitions. Thanks! – JBird Jul 29 '21 at 15:21
  • 1
    @MitchWheat thanks for this link. Interesting and makes sense. In the post's situation for the full text index, it looks like it's using the full text index to get the full result set, then filtering further based on the date. From what I understanding with full text index, this makes sense - it's searched the Binary Tree for all results, which has resulted in a list of primary keys, then it filters these primary keys to get the records required. For the scenario with my query, there's no additional filtering being done. – JBird Jul 30 '21 at 09:25

1 Answers1

0

After lots of digging, I found this Query Optimizer Gone Wild - Full-Text Search Query Plans.

Looks like it is by design. A ContainsTable query doesnt need to join in order to get the row count.

the CONTAINS function must also scan a index on the source table to get the count

JBird
  • 13
  • 4