title: SQL Server 2014 Full Text Search Slow compared to SQL Server 2005
body: <p>We are in the process of upgrading from SQL Server 2005 to SQL Server 2014. We rely heavily on Full Text Searching. We are experiencing significantly slower performance with Full Text searches in SQL 2014 that use more complex text searches (Multiple ORs, and NOT, etc.). This will be a showstopper for us being able to upgrade to 2014.</p>
  
  <p>We have partitioned our data by year so have a full text table/catalog for each year. We see the slower performance when searching in one year's data but most definitely see the slower performance when searching across all time. </p>
  
  <p>This is an example of that query:</p>
  
  <pre><code>SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2003, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2004, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2005, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *" ) ) ')
  UNION ALL
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2006, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2007, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2008, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2009, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2010, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL   
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2011, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL   
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2012, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL
  SELECT [KEY]
  FROM CONTAINSTABLE(DATA_MSFT.dbo.YEAR_2013, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  UNION ALL
  SELECT [KEY]
  FROM CONTAINSTABLE(_DATA_MSFT.dbo.YEAR_2014, FT_TEXT, ' ( (" tin metal *" OR " unwrought tin *" OR " tin shot *") ) ')
  </code></pre>
  
  <p>The original query in SQL 2005 takes 19 seconds. The same query in SQL 2014 is taking over 3 1/2 minutes. </p>
  
  <p>After migrating to SQL 2014, we deleted the catalogs and repopulated the indexes. We also updated the stats. Adding OPTION (MAXDOP 1) to the end of the query, reduced the time to 1 1/2 minutes but no where near SQL 2005 performance.</p>
  
  <p>We've also tried using TraceOn 9481 to use the older cost estimator. This had no affect.</p>
  
  <p>We've compared the Execution Plans. In SQL 2005 - Remote Scan: cost 100%. In SQL 2014, Table Value Function: cost 100%. So are not seeing any where to improve.</p>
  
  <p>We've read that SQL 2008 Full Text Searching had some performance issues but from what we read it seemed these issues were fixed so assuming they are also fixed (and improved??) in SQL 2014.</p>
  
  <p>Any other suggestions would be greatly appreciated!</p>