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 ID
s 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:
Best Answer
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:
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:
Here's the full-text index:
I've got a database with around 47,000 words in it which I'll use to fill the
dbo.TitleTable
table:Here's the query from your question:
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 theTitle
column.If we add a compound index on both
FKID2
andFKID1
, we'd expect a different plan, which is what we get:However, the key lookup for the
Title
column is still there. What if we add anINCLUDE
clause to our index above?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.