Sql-server – Key Lookup isn’t applied by default

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:

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);

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);

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.

Only rows in 7 partitions qualify the predicate(s) so there is no need to ever lookup rows in the other 3 partitions.

The query plan may choose to scan on B and probe (lookup) on A.

A Key Lookup is always a seek into the clustered index. There is no partition elimination in your original example by the way - the query processor is just reporting that it touched 7 distinct partitions (3..9) while executing the query. None of the lookups accessed other partitions.