4

I have the following query:

SELECT T2.Title 
FROM TitleTable T1
    INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1 
    AND T2.FKID2 = @ID_PARAM2 
    AND CONTAINS(T1.Title, '"' + @SINGLE_WORD_PARAM +'"')

Title is of type NVARCHAR(100). All the IDs are INT.

My Execution Plan is such that it is using the Key Lookup (Clustered) operation 80+% of the time:

  • Predicate: TitleTable.FKID2 AS T2.FKID2 = @ID_PARAM2
  • Object: TitleTable T2
  • Output list: TitleTable.Title

Each and every mentioned column is in its own non-clustered index, Title is additionally in the full-text index and there's a clustered index on the primary key as well. Is there a way to include the full-text index in the covering index or something?

EDIT: execution plan

2

Key lookup operations can be avoided by the use of a covering index. Full-text-indexes cannot be "included" in a covering index, however, including the TitleTable in the covering index is still useful since SQL Server can find all the details it needs for the query, aside from the full-text-query results, by seeking the covering index.

I've created a simple test-bed to show this in action.

First, we'll create an empty database for our test, since we cannot create full-text catalogs in tempdb:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'FullTextTest') 
BEGIN
    ALTER DATABASE FullTextTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE FullTextTest;
END
CREATE DATABASE FullTextTest;
ALTER DATABASE FullTextTest SET RECOVERY SIMPLE;
BACKUP DATABASE FullTextTest TO DISK = 'NUL:';
GO

Here, we'll create a mock-up of your table. You say in the question that all mentioned columns have an associated non-clustered index, so we'll define those too:

USE FullTextTest;
GO

CREATE FULLTEXT CATALOG ftc
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION dbo;

CREATE TABLE dbo.TitleTable
(
    PK_ID int NOT NULL
        CONSTRAINT PK_TitleTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FKID1 int NOT NULL
    , FKID2 int NOT NULL
    , Title nvarchar(100) NOT NULL
);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID1
ON dbo.TitleTable (FKID1);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID2
ON dbo.TitleTable (FKID2);

CREATE NONCLUSTERED INDEX IX_TitleTable_Title
ON dbo.TitleTable (Title);

Here's the full-text index:

CREATE FULLTEXT INDEX ON dbo.TitleTable(Title) 
KEY INDEX PK_TitleTable 
ON ftc 
WITH (CHANGE_TRACKING = AUTO, STOPLIST SYSTEM);

I've got a database with around 47,000 words in it which I'll use to fill the dbo.TitleTable table:

INSERT INTO dbo.TitleTable (FKID1, FKID2, Title)
SELECT wl.WordRow, wl.WordRow, wl.Word
FROM WordsDB.dbo.WordList wl;

Here's the query from your question:

DECLARE @ID_PARAM1 int;
DECLARE @ID_PARAM2 int;
DECLARE @SINGLE_WORD_PARAM nvarchar(100);
SET @ID_PARAM1 = 46777;
SET @ID_PARAM2 = 46777
SET @SINGLE_WORD_PARAM = N'"' + 'it' + N'"';
SELECT T2.Title 
FROM TitleTable T1
    INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1 
    AND T2.FKID2 = @ID_PARAM2 
    AND CONTAINS(T1.Title, @SINGLE_WORD_PARAM);

At this point, if we run the query, we see the following plan:

enter image description here

As expected, there is an index seek on the IX_TitleTable_FKID2 non-clustered index, with an associated key-lookup against the table itself for the Title column.

If we add a compound index on both FKID2 and FKID1, we'd expect a different plan, which is what we get:

CREATE INDEX IX_TitleTable_FTS_Cover_NoInclude
ON dbo.TitleTable (FKID2, FKID1);

enter image description here

However, the key lookup for the Title column is still there. What if we add an INCLUDE clause to our index above?

CREATE INDEX IX_TitleTable_FTS_Cover
ON dbo.TitleTable (FKID2, FKID1)
INCLUDE (Title);

enter image description here

Success! No key-lookup operation required. The cost of the query has also dropped from 0.016 to 0.013, so that's a win.

|improve this answer|||||

Your Answer

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

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