Strange Query Plan for SQL Query – Clustered Index Seek

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

Answer

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