I have the following query:
SELECT TOP 250 doc_Main.Id,doc_Main.Gid,doc_Main.Name,doc_Main.TimeEnter,doc_Main.Prio,doc_Main.Direction,
(doc_Main.Id_RoleSet IN (1,2,....) AND
((CONTAINS((doc_Main.Subject, doc_Main.Body), '"bulk"'))
ORDER BY doc_Main.TimeEnter DESC
doc_main table is a big one with over 6 million records.
I have tried different combinations of clustered indexes and nonclustered indexes after reading on the internet but still i can't find the right combination.
The problem is that if i define some indexes or forced to use some it will work fine if the word searched in the full text catalog is very often present in the table but not when it has few appearances. If i change those indexes or define others it starts to work for few appearances but not for many.
Please help me to find the right approach to find out which indexes and statistics have to be created on this table to work fast in all cases.