mardi 27 mars 2012 03:51
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
Toutes les réponses
jeudi 29 mars 2012 05:39Modérateur
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.
TechNet Community Support
- Marqué comme réponse Stephanie LvModerator mardi 3 avril 2012 07:06
jeudi 29 mars 2012 15:09Your table structure and query that your are firing does matters when you are talking about the preformance.