Fulltext Index Search Has Large Number Of Page Reads

I have a full-text index on a column in a table that contains data like this: searchColumn 90210 Brooks Diana Miami FL diana.brooks@email.com 5612233395 The column is an aggregate

Solution 1:

I have a couple of thoughts on this. First the Select * will generate a great number of page reads because it has to pull all columns which may or may not be indexed. When you pull every column it most likely will not make use of the best Index plan out there.

As to your Where clauses, when using the @searchTerm and the value of "FL" AND "90210*" AND "Diana*" AND "Brooks*" it has to check the datapages multiple times each time it is run. Think of how you would look up this information if you had to do it. You look at a piece of paper with the info on it and see if the search column contains FL. Now does it contain FL and 90210*. Now does it contain both of those plus Diana...etc.

You can see why it would keep having to go back to the page to read over and over again. The second query only has to look at 2 columns narrowly defined.

If you want more information on this, I would suggest a class by Brent Ozar that is free right now. How to think like the SQL Server Engine

I hope that helps.