Is there any performance difference on Full Text Search Between SQL Server 2008R2 and SQL Server 2012

Question

Is there any performance difference on Full Text Search Between SQL Server 2008R2 and SQL Server 2012?

We have an existing set of SQL Servers 2008 R2 standard edition (a replicating pair). We're in the process of migrating our databases and replication towards SQL Server 2012. To ensure that our new server will be synched with our live servers, we just made a subscription on SQL2012 pointing to the SQL2008R2. These subscriptions include databases with Full Text Catalogs.

The problem came when we tried testing some Full Text Queries. We isolated few queries that runs very fast on our current production but is very slow on our new server (1sec vs. 1 min).

We have a new and powerful Hardware

We cannot accept this because we designed our brand new server to be a lot more powerful than it's predecessor. Our new server has 128GB memory, dedicated disks per database and logs, 18 core CPUs at the very least and our old server does not measure even close to 1/2 on what we have now.

Database is fully replicated. 

Even the stats and indexes are the same. We even have the same tempdb configuration.

We noticed a difference in actual execution plans

In the old server, the execution plan only contains 2 batches. But in the new SQL2012 server, there are 7 batches of the same replicated stored proc

sql 2008 R2 Execution Plan

sql 2012 EXECUTION PLAN

I guess to cut the story short, how come the execution plans on querying the same stored proc on same databases (replicated) and different servers have different execution plans even though down to the stats level, their all the same?

Answer 1

As I understand it, SQL Server 2012 Full Text Indexing is not too different, but there are component changes.  So you should check the following to see if anything is impacting you:  

Behavior Changes to Full-Text Search http://msdn.microsoft.com/en-us/library/ms143272.aspx

The 2012 version of "Improve the Performance of Full-Text Indexes" has more information than earlier versions.  Have you reviewed this article to see if any of it is helpful? 

http://technet.microsoft.com/en-us/library/ms142560.aspx

RLF

Answer 2

these execution plans are for different tables. The first shows an insert into msrepl_queuedtranfino and the second into tbl_Search_KeywordLogs.

I don't think this is an apples to apples comparison. Also are stats upto date for both tables? Parallelism is used in one but not in the other.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 3

Its for the same tables -- which is what's weirding me out. The one with the short execution plan is from the publisher database and the one in the long one is from the subscriber database but is definitely the same table. We've made sure that all indexes and stats info are the same and updated.

---

MCP, MCSD, MCDBA (ANXA)

Answer 4

Ok, I think I finally found what are the real problems and how to resolve it -- although I do not know how to resolve it universally or if its really a problem with the new SQL or some configuration somewhere.

The real difference between the 2 execution plans from our SQL2008 and SQL2012 is the parallelism and the following warnings:

• TEMPDB warning: Operator used TempDB to spill data during execution with spill level 1
• Parallelism warning: Operator used TempDB to spill data during execution with spill level 2

What is a good temporary solution?

MAXDOP <very low number>

Does it solve everything?

• No it doesn't.
• It does remove the parallelism and puts the query execution time at par with our old server.
• However, the SORT  (#3) still reports the warning of a spill level 1.
• The SELECT (#1) still indicates a granted memory that is not enough which is probably the reason for the SORT warning.

What did we previously do before this?

1. Duplicate all indexes and statistics
2. Update the statistics of all tables with FULLSCAN
3. Everything still ended up with us having a slow stored proc

My confused questions now are:

1. Is this because there is a difference between 2008 and 2012 when it comes to query plan generation for Full Text Catalogs?
2. What are standard configurations we can implement to ensure that the query plan would always be correct especially for Full Text Catalogs?
3. How do we configure Granted Memory on the server to be more than enough without tricking the query itself.

My references that validated this

• http://www.sqlpassion.at/blog/PermaLink,guid,2a24830a-5cf9-4438-96e9-af0a6d7372c3.aspx
• http://blogs.technet.com/b/mat_stephen/archive/2005/02/08/369120.aspx
• http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx
• http://www.sqlworkshops.com/parallelquerymemory.htm
• http://www.sqlworkshops.com/workspacememory.htm

---

MCP, MCSD, MCDBA (ANXA)