none
Replication latency due to full text internal object RRS feed

  • Question

  • Hi All,

    Issue:

    Replication getting delayed due to blocking on subscriber server, when looked into details was able to see the FTGatherer process background process getting blocked by an object of type IT which is the internal table.

    One thing which I have noticed is full text is enabled at server and database on which the process that is running and resulting in blocking of the system spid. Blockings are occuring when an update query is being run on a table that is added to full text catalog which is blocking the SQL internal process.

    Please let me know if anyone has come across this sort of issue and your suggestions would be helpful in troubleshooting this issue.

    Thanks in Advance!

    Regards,

    Kranthi

    Tuesday, January 17, 2017 6:23 PM

Answers

  • Last year in a customer environment I had a nasty experience with Fulltext. It was a SQL Server 2012 Standard Edition and the archiving process was consisting of inserting the rows to be archived to the archive database and deleting the copied rows from the source and as you can guess there was a fulltext index on the source table. It took days for the fulltext index to be updated and during that time CPU usage was above 90%. As it was an internal process, there was nothing to do but to wait for it to complete. When it kicks in, it just works and you never know when it's gonna finish, as least I couldn't find a way then. After the completion of the population, birds were singing and the sun was shining again. Sigh...

    By the way, to mitigate the side effect I asked for a bit more CPU resource from the support team.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 19, 2017 2:39 PM
    Answerer

All replies

  • Are you running

    DBCC TRACEON (7646, -1)

    Tuesday, January 17, 2017 6:32 PM
    Moderator
  • No, the trace flag is not enabled on server.

    Regards,

    Kranthi

    Wednesday, January 18, 2017 4:21 AM
  • Last year in a customer environment I had a nasty experience with Fulltext. It was a SQL Server 2012 Standard Edition and the archiving process was consisting of inserting the rows to be archived to the archive database and deleting the copied rows from the source and as you can guess there was a fulltext index on the source table. It took days for the fulltext index to be updated and during that time CPU usage was above 90%. As it was an internal process, there was nothing to do but to wait for it to complete. When it kicks in, it just works and you never know when it's gonna finish, as least I couldn't find a way then. After the completion of the population, birds were singing and the sun was shining again. Sigh...

    By the way, to mitigate the side effect I asked for a bit more CPU resource from the support team.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Thursday, January 19, 2017 2:39 PM
    Answerer