none
High CPU utilization during full-text population in SQL 2005

    Question

  • Hi,

    In SQL 2005 a full-text population of a text field in a table containing 1.2 million records utilizes very high CPU resources. A dual Xeon 3Ghz, 1GB memory was running at 95% during the population of the full text catalog. There was no other activity on this server besides the full-text population. It performed the population in approximately 28 minutes, which is allot faster than SQL 2000, however, the high CPU utilization would be of great concern in a production environment.

    I have not tested incremental or change tracking as yet but i plan to look at this very soon. Is this unusual behaviour or does the SQL full-text service naturally consume allot of CPU resources to perform a full population.

    I am testing this on SQL 2005 Sept CTP (Enterprise). Any feedback is greatly appreciated.

    Cheers,
    Priyanga



    Thursday, September 22, 2005 11:12 AM

Answers

  • Hi Priyanga,
    Just curious, do you also see the high CPU usage when your are NOT running a Full or Incremental Population or do you see this high CPU usage only during FT populations? If the latter, you can to set the CPU affinty of the msftesql service via task mgr /interactive and the AT command, for example:

    at <current_time+1min> /interactive taskmgr.exe

    Then set the processor affinity for the msftesql service and use sp_configure to set SQL Server to use the other available CPU's to avoid CPU conflicts. Note, only sysadmins's have access to the AT command, so this is a secured method.

    If you are seeing high CPU usage by the msftesql service during non-FT population periods, i.e., just running FT Search quieres (contains or freetext), with the Sept CTP of SQL Server 2005, could you let us know that?

    Finally, a certain amount of high CPU & memory usage is normal and expected during Full-text Indexing, but the high usage should not remain for long periods.

    Thanks,
    John
    SQL Full Text Search Blog
    http://spaces.msn.com/members/jtkane/
    Thursday, September 22, 2005 2:36 PM

All replies

  • Hi Priyanga,
    Just curious, do you also see the high CPU usage when your are NOT running a Full or Incremental Population or do you see this high CPU usage only during FT populations? If the latter, you can to set the CPU affinty of the msftesql service via task mgr /interactive and the AT command, for example:

    at <current_time+1min> /interactive taskmgr.exe

    Then set the processor affinity for the msftesql service and use sp_configure to set SQL Server to use the other available CPU's to avoid CPU conflicts. Note, only sysadmins's have access to the AT command, so this is a secured method.

    If you are seeing high CPU usage by the msftesql service during non-FT population periods, i.e., just running FT Search quieres (contains or freetext), with the Sept CTP of SQL Server 2005, could you let us know that?

    Finally, a certain amount of high CPU & memory usage is normal and expected during Full-text Indexing, but the high usage should not remain for long periods.

    Thanks,
    John
    SQL Full Text Search Blog
    http://spaces.msn.com/members/jtkane/
    Thursday, September 22, 2005 2:36 PM
  • Hi John,

    We moved to a 4 CPU Itanium2 1.10Ghz, 16GB RAM and SQL 2005 RC1 Enterprise.  On this server I do not see high CPU usage when i am not running a full or incremental population.

    During the full population i saw msftefd.exe and msftesql.exe processes consuming the highest CPU usage other than msqlservr.exe. I have found that setting the affinity on msftesql is not enough to limit the CPU usage to a specific processor. msftefd also needs to have the affinity set to limit the full-text population restricted to one or more CPUs.

    For example, when i set the instance to use all the four CPUs  and the msftefd.exe & msftesql.exe processes to use only CPU0, CPU0 was 100% utilised while the other three CPUs were about 4%, 10% and 31% respectively. 

    Cheers,
    Priyanga
    • Proposed as answer by Mustafa Fazlyi Friday, March 18, 2011 9:35 AM
    • Unproposed as answer by Mustafa Fazlyi Monday, March 21, 2011 10:35 AM
    Tuesday, October 04, 2005 6:00 AM
  • Hi Priyanga,
    Great news! So, you were able to successfully separate the SQL Server 2005 FTS service (msftesql.exe) from SQL Server 2005, such that the high cpu usage does not directly affect SQL Server 2005 processing! Note, this process can be made to survive a re-boot of the server via setting up a "driver" service. I intend to blog about this in the near future, so feel free to post a comment or two!

    Yup, I forgot to mention msftefd.exe. Note, that the SQL Server 2005 msftefd.exe (PKM executable) is the equalivant of the SQL Server 2000 mssdmn.exe (Microsoft PKM Search Filter Daemon). The SQL 2005 msftefd daemon is launched by the msftesql service when it is necessary to read or "crack" specific file types via IFilters files for example: MS Word (.doc), Adobe PDF (.pdf), etc.

    Thanks,
    John
    SQL Full Text Search Blog
    http://spaces.msn.com/members/jtkane/
    • Proposed as answer by Mustafa Fazlyi Friday, March 18, 2011 9:35 AM
    • Unproposed as answer by Mustafa Fazlyi Monday, March 21, 2011 10:35 AM
    Tuesday, October 04, 2005 2:53 PM
  • Hi Priyanga,
    Just curious, do you also see the high CPU usage when your are NOT running a Full or Incremental Population or do you see this high CPU usage only during FT populations? If the latter, you can to set the CPU affinty of the msftesql service via task mgr /interactive and the AT command, for example:

    at <current_time+1min> /interactive taskmgr.exe

    Then set the processor affinity for the msftesql service and use sp_configure to set SQL Server to use the other available CPU's to avoid CPU conflicts. Note, only sysadmins's have access to the AT command, so this is a secured method.

    If you are seeing high CPU usage by the msftesql service during non-FT population periods, i.e., just running FT Search quieres (contains or freetext), with the Sept CTP of SQL Server 2005, could you let us know that?

    Finally, a certain amount of high CPU & memory usage is normal and expected during Full-text Indexing, but the high usage should not remain for long periods.

    Thanks,
    John
    SQL Full Text Search Blog
    http://spaces.msn.com/members/jtkane/


    Mustafa Fazlyi Aprilgatan 9 G 54330. Tibro 46000, SWEDEN E mail.farri_88@msn.com
    Friday, March 18, 2011 9:54 AM
  • Hi Priyanga,
    Just curious, do you also see the high CPU usage when your are NOT running a Full or Incremental Population or do you see this high CPU usage only during FT populations? If the latter, you can to set the CPU affinty of the msftesql service via task mgr /interactive and the AT command, for example:

    at <current_time+1min> /interactive taskmgr.exe

    Then set the processor affinity for the msftesql service and use sp_configure to set SQL Server to use the other available CPU's to avoid CPU conflicts. Note, only sysadmins's have access to the AT command, so this is a secured method.

    If you are seeing high CPU usage by the msftesql service during non-FT population periods, i.e., just running FT Search quieres (contains or freetext), with the Sept CTP of SQL Server 2005, could you let us know that?

    Finally, a certain amount of high CPU & memory usage is normal and expected during Full-text Indexing, but the high usage should not remain for long periods.

    Thanks,
    John
    SQL Full Text Search Blog
    http://spaces.msn.com/members/jtkane/


    Mustafa Fazlyi Aprilgatan 9 G 54330. Tibro 46000, SWEDEN E mail.farri_88@msn.com
    Friday, March 18, 2011 9:55 AM
  • Hi Priyanga,
    Just curious, do you also see the high CPU usage when your are NOT running a Full or Incremental Population or do you see this high CPU usage only during FT populations? If the latter, you can to set the CPU affinty of the msftesql service via task mgr /interactive and the AT command, for example:

    at <current_time+1min> /interactive taskmgr.exe

    Then set the processor affinity for the msftesql service and use sp_configure to set SQL Server to use the other available CPU's to avoid CPU conflicts. Note, only sysadmins's have access to the AT command, so this is a secured method.

    If you are seeing high CPU usage by the msftesql service during non-FT population periods, i.e., just running FT Search quieres (contains or freetext), with the Sept CTP of SQL Server 2005, could you let us know that?

    Finally, a certain amount of high CPU & memory usage is normal and expected during Full-text Indexing, but the high usage should not remain for long periods.

    Thanks,
    John
    SQL Full Text Search Blog
    http://spaces.msn.com/members/jtkane/


    Mustafa Fazlyi Aprilgatan 9 G 54330. Tibro 46000, SWEDEN E mail.farri_88@msn.com
    Friday, March 18, 2011 9:55 AM
  • Hi Priyanga,
    Great news! So, you were able to successfully separate the SQL Server 2005 FTS service (msftesql.exe) from SQL Server 2005, such that the high cpu usage does not directly affect SQL Server 2005 processing! Note, this process can be made to survive a re-boot of the server via setting up a "driver" service. I intend to blog about this in the near future, so feel free to post a comment or two!

    Yup, I forgot to mention msftefd.exe. Note, that the SQL Server 2005 msftefd.exe (PKM executable) is the equalivant of the SQL Server 2000 mssdmn.exe (Microsoft PKM Search Filter Daemon). The SQL 2005 msftefd daemon is launched by the msftesql service when it is necessary to read or "crack" specific file types via IFilters files for example: MS Word (.doc), Adobe PDF (.pdf), etc.

    Thanks,
    John
    SQL Full Text Search Blog
    http://spaces.msn.com/members/jtkane/


    Mustafa Fazlyi Aprilgatan 9 G 54330. Tibro 46000, SWEDEN E mail.farri_88@msn.com
    Monday, March 21, 2011 10:36 AM