iFTS High CPU

Question

 

Im checking out CTP6 and noticed that the CPU on the Box was high and a high number of logical reads going on.

The box was idle besides transactional replication trafic.

 

I tracked it down to the FTS change tracking.

 

when i set

ALTER FULLTEXT INDEX ON <name> SET CHANGE_TRACKING MANUAL

the box calms down to normal.

 

every time i run

ALTER FULLTEXT INDEX ON <name> START UPDATE POPULATION

there is a 100% CPU spike and there is only around 100 pending changes.

 

Any ideas?

 

Carl

 

Answer 1

This is a normal behavior. FTS population is very resource intensive in order to have the data available for search as soon as possible. When you create a FTIndex for the first time, a full population kicks in automatically (unless specified otherwise in the DDL). By default, this FTIndex has automatic change tracking, which propagates any change in the base table to the FTIndex, so your search data is as accurate as possible. When there are many updates going on, the automatic change tracking might use quite a lot of CPU depending on other factors. I dont' think this is your case anyway.

I would recommend letting it finish during the night or when you can afford having CPU busy, and once the population is completed, CPU usage will go back to normal and your other task will not suffer. If your update/inserts rates are very high (100s records/seconds) then let me know as there are other techniques to mitigate possible blocking.

 

Thanks,

 

 

 

 

 

 

 

 

Answer 2

This is not normal behavior. We have 10 searvers doing FTS currently and none of them show this behavour at all.

For example there is another server (SQL2005) that isnt taking any live load, only replication traffic and FTS and the cpu sits at 2-5% 5 minute average, the 2008 server is 40% 5min average.

 

There is deffently something still not right with iFTS.

 

We have been running FTS with automatic updates for the last 4 years with no problems, our catalog's have to be 100% upto date all the time.

Answer 3

I see. I understood the problem differently (I thought your crawl was unfinished yet).

 

Please, can you provide more details on the problem? It would be great to have a list of actions that repro this high CPU usage. Once we know the exact steps you have done, we can then come with more details on what you are observing.

 

Why did you change to manual change tracking? For how long the CPU gets to 100% when you UPDATE POPULATION?..etc.. What is the load of updates/inserts you are getting per minute or second?

 

Thanks!

 

Answer 4

 

Hi,

 

I turned off change tracking when i was tracking down the CPU spikes.

They only last 2-3 seconds but occure often.

 

I just ran a trace and we are doing around 1000 updates a minute (this is a quiet time of the day)

 

Cheers

 

Carl

 

 

Answer 5

Ok,

 

If you see 100% spikes for only short intervals 1 second or less, it might be 'expected' in iFTS due our changes. It is true that we suffer more with high number of upates in comparison with FTS 2005, but 1000/minute should not be an issue (we observe perf issues around >100 updates/second). I understand that with auto change tracking, we are keeping up with the load, but the CPU is 40% when before was 5%? is this correct?  When when you are in manual change tracking...CPU goes down, but when you update population, the CPU again goes to 100%? if so, for how long?

Do you see any performance issues at query or update/insert time?

 

Please, do the following when possible:

 

-enable the following trace flags first:

trace Flags: 7601,7603, 7604, 7605

-repro the problem again (when you see  high CPU when not expected),

-and share the error log please (send it to [e-mail])

 

Thanks for your help!

 

ps: I am OOF from tomorrow on for 7 days, please contact [e-mail] as well in case we don't get to the bottom of this issue. It is important we understand and eventually solve the possible problem.

 

 

Answer 6

Carl,

 

This blog post has some details about how to improve iFTS performance with CTP6

 

http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!875.entry