2

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?

2 Answers 2

2

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.

2
  • 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. Jan 9, 2011 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, 2011 at 10:29
0

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!

2
  • have you experienced any times it was consuming a steady 30% cpu usage and massive amounts of read? Jan 9, 2011 at 6:40
  • Probably, but since in my experience the overhead was not impacting the rest of SQL I did not care... Jan 18, 2011 at 6:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .