Post reply

Fulltext search slow sql server 2005

  • April 19, 2010 at 7:56 pm

    #386740

    Hi,

    We had a table presently around 12000 rows.It has got upto 10 indexes.It also had a fulltext index on a Title column.It used to work normally.We had a small relase in database yesterday.Suddenly,i observed, that full text search drastically slowed down.In the relase,we haven't altered any thing for that table.

    After struggling sometime,we tried to remove 2 indexes,and saw it was again faster.

    Now my question is,With those indexes prior,fulltext search is working fine.Can anybody guess what could be wrong?Also i had a question,i know more indexes consume more table space.So,removing 2 indexes may free some space and that helped fulltext search faster?or could be anything else.

    Please help me in ressolving this problem.

  • April 20, 2010 at 3:23 am

    #1154412

    Have you updatedd statistics ?

    Did you see the execution plan for that poor queries?

    If possible post them .then only i can suggest something

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • April 21, 2010 at 11:47 pm

    #1155858

    Hi,thanks for replying.I found the problem.But still i didn't find the solution.The problem is not the autual table where i do the fulltext search.In the stored prcodeure,which i am using for fulltextsearch,i have joined that table with a view(which is a join of few tables)

    I found that the problem is in a table which is used in the view formation.

    In that table,if there are about 1000 records the query was faster.But,if i increase the rows to 1200 or more,the query is taking long and long and long time.

    You have asked me to to give the executionplan.

    Please find it attached.

    Please let me kno if u r not clear or u need anything else.

    Thanks again.

  • April 22, 2010 at 12:16 am

    #1155868

    i also found same type of problem with heavy views.

    Have you checked that join have proper indexes?. Give some time with "ImpressionTitle" and "PreTMSTitile" table , i found heavy index scan on that.

    Also try to tune that view.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • April 22, 2010 at 5:40 am

    #1155995

    There are also a missing index on the ImpressionTitle table.

    Check for the "MissingIndexes" tag in the xml plan.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply