Sql-server – SQL Server Full Text Search resource consumption

full-text-searchperformancesql serversql-server-2008

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?

Best Answer

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.

Related Solutions

Sql-server – How to detect if full text search is installed in SQL Server

I like SELECT over PRINT

So here is my take on this

SELECT 
    CASE 
         WHEN 
             FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1 
         THEN 
              'INSTALLED' 
         ELSE 
              'NOT INSTALLED' 
    END IsFullTextInstalled

Sql-server – What can cause long query durations without high resource usage

As you are running SQL 2005 you can take your SQL Profiler data and compare it against the Perfmon data to see if you can see a correlation. This is done by saving your trace data and perfmon data to files using the normal techniques. Then open the SQL Profiler trace within profiler, and then one of the options in the file menu will be Import Performance Data. This will let you select a query and see what the counters were doing at that time (or close to it depending on your perfmon collection interval).

Disk queue spikes are never good. Especially that high. What's the IO that you are pushing to the disk when the queue gets that high? Basically you don't want a disk queue any higher than (2*n) where n in the number of disks in the array. Since you are using a 2 disk RAID 1 n=1 in your case (since you only get the speed of a single disk).

In perfmon there's a counter which is the seconds per read and seconds per write. What do these counters look like when the queries start taking a long time to run. What about normally? (Anything over .02 seconds is bad.) What's the estimated page life expectancy? (Anything under 300 seconds is usually bad, but this can vary.) What's the SQL Server cache hit ratio? (Anything below ~97% is usually bad. I like mine above 99.9%.)

Best Answer

Related Solutions

Sql-server – How to detect if full text search is installed in SQL Server

Sql-server – What can cause long query durations without high resource usage

Related Topic