none
Impact Analysis with FTS implementation on large volume database

    Вопрос

  • 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

    27 марта 2012 г. 3:51

Ответы

  • 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

    • Помечено в качестве ответа Stephanie Lv 3 апреля 2012 г. 7:06
    29 марта 2012 г. 5:39

Все ответы

  • 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

    • Помечено в качестве ответа Stephanie Lv 3 апреля 2012 г. 7:06
    29 марта 2012 г. 5:39
  • Your table structure and query that your are firing does matters when you are talking about the preformance.
    29 марта 2012 г. 15:09