Home / server / Questions / 220253
In Process
Sam Saffron
Asked: 2011-01-09 16:41:18 +0800 CST

SQL Server Full Text Search resource consumption

  • 772

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?

sql-server performance sql-server-2008 full-text-search

2 Answers

  1. 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

  2. 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!

    • 0