Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

When SQL Server builds a fulltext index computer resources are consumed (IO/Memory/CPU)

Similarly when you perform full text searches, resources are consumed.

How can I get a gauge over a 24 hour period of the exact amount of CPU and IO(reads/writes) that fulltext is responsible for, in relation to global SQL Server resource usage.

Are there any perfmon counters, DMVs or profiler traces I can use to help answer this question?

share|improve this question
add comment

2 Answers

When working in SQL Server 2008 and up, the full text searching is actually done by the SQL Server engine. The full text search service is still used, but only to do word breaking on the search query which is then passed right back to the database to do the actual searching.

If you put the full text indexes on a different drive you can monitor the IO for those drives easily enough. Or you can just monitor the physical file IO for each file in perfmon. There won't be any way to monitor the CPU load being used just by full text, unless you can find a full text specific perfmon counter that gives you that info.

share|improve this answer
    
Yeah I even searched through SQL Profiler and could not find anything. It's so hard to make a case that something is a problem unless you can measure it. All I know is that a bunch of queries that involve FTS are responsible for a large amount of load. –  Sam Saffron Jan 9 '11 at 10:26
    
Disable the full text index and see how much load goes down. That'll be the only way to tell. Simply searching the indexes shouldn't be all the much load. –  mrdenny Jan 9 '11 at 10:29
add comment

In my experience, there is no direct way to measure the resources used by the indexing engine.

However, the indexing engine has always been well behaved, and seems to run at a lower priority than SQL itself and other server processes. It seems to yield cpu and memory nicely and to play nice.

I have used SQL full text indexing and Windows full text indexing since the late 90's. SQL 2005 is really quite well behaved.

HTH!

share|improve this answer
    
have you experienced any times it was consuming a steady 30% cpu usage and massive amounts of read? –  Sam Saffron Jan 9 '11 at 6:40
    
Probably, but since in my experience the overhead was not impacting the rest of SQL I did not care... –  samsmith Jan 18 '11 at 6:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.