Impact Analysis with FTS implementation on large volume database

Question

Hello SQL community,

We have implemented FTS on a large volume database.Now I would like to demonstrate the impact of changes done to tables and views for enabling FTS .

Below are the objectives of this excercise.

1.Impact on Memory (disk space)

2.Comparison of search performance with FTS and without FTS.

3.Impact on insert/modify operations

Can somebody suggest what type of tests I need to perform to arrive the appropriate metrics for each objctive.

Appreciate your inputs

Thanks

Samba

Answer 1

Hi Samba,

According to your description, it is hard to measure how the Full Text Search impact on the performance and resource.

When we look into the performance (time and memory consumed, I/O reads), it is about a query statement based on table structures, not dedicated to the process on full text search within the query. Moreover, if the requirement cannot be fulfilled other than the FTS, it is not able to compare the performance with FTS and without FTS. For the first two questions, you may monitor the overall performance of an instance with FTS enabled by Performance Monitor or DMV’s. Please see: The Most Important Performance Monitor Counters for SQL Server. Assuming there are two queries using FTS and LIKE, which are used to return same data from the same table, you can comparing the two query plans to find the difference on performance. As for disk consumed by FTS, please pay attention to ‘IndexSize’ and ‘LogSize’ on the full-text catalog properties: FULLTEXTCATALOGPROPERTY.

For the third question, it is related to the Full-Text Indexes population after data updated. Also, please make use of the Performance Monitor Counters to monitor the performance during the population. For more information: Full-Text Index Population.

---

Stephanie Lv

TechNet Community Support

Answer 2

Your table structure and query that your are firing does matters when you are talking about the preformance.