Full text index causing recompilation issues - a weird one perhaps?

• InvisibleCat

  Default port

  Points: 1496

  More actions

  July 31, 2013 at 5:57 am

  #281156

  Hi,

  This morning I pushed a number of new full text catalogs, indexes and procedures to a SQL 2005 (SP2) server, everything deployed correctly. I did some tests on the procedures and they were returning data as expected.

  Shortly after we had reports of issues, so I begin to investigate and it seems that a large number of requests were coming in from another database server (SQL 2008 R2 SP1).

  I had a look at the log file on the SQL 2008 R2 server and the log contained a large number of entries for;

  "A possible infinite recompile was detected for SQLHANDLE 0x0200000037A942212AB60B7FABB0F89E216D7428A74C62F3, PlanHandle 0x0600050037A94221408114F8000000000000000000000000, starting offset 0, ending offset -1. The last recompile reason was 6."

  I then as a safety measure disabled the full text catalogs, this made no difference, so then I fully rolled back the changes (dropped the new objects etc). This also made no difference, in the end the only thing that seemed to stop it was a restart of the SQL instances, this immediately resolved the issue.

  All has been fine since then, I did some digging and found comments where someone else had suffered similar problems (http://blog.netnerds.net/2012/05/a-possible-infinite-recompile-was-detected-sql-server-bug-with-linked-servers/). However in their set up they were using synonyms, in ours we are not, we only views across the linked server.

  My question is, does anyone know why the FTS would have done this? The "last recompile reason was 6" indicates that the "Remote rowset changed" - http://technet.microsoft.com/en-us/library/dd353307(v=sql.90).aspx

  Non of the above behaviour was evident in development or testing and I've never seen FTS do anything similar to this, so any help you can provide would be good. I'm fairly sure that this was only triggered by one catalog (due to the linked server) but I can't be 100% of that.

  Any info that anyone has would be great.

  Thanks,

  Nic