SQL Server CPU running at 100% during a full or incremental crawl

We were researching which SQL versions were ok while troubleshooting a problem we had with the SQL Server CPU running at 100% during a full or incremental crawl.

After verifying there were no bottlenecks elsewhere in the farm we turned to SQL and CU version.

Despite there being no errors on the SQL server the SQL guy noticed that some of the SQL queries were not being handled well. 

We were running SQL 2008 R2 Service Pack 1 with Cumulative Update 3 so re-check for recommended SQL versions.

SharePoint 2010 and SQL Hotfix Dependencies

SharePoint Joel has a great article on SQL versions and hotfix dependencies

Check this page : http://www.sharepointjoel.com/Lists/Posts/Post.aspx?List=0cd1a63d-183c-4fc2-8320-ba5369008acb&ID=377

The above article showed that we should be ok but a short Google later turned up these chaps with the same issue with SQL CPU running at 100%.

http://spcrew.com/blogs/lists/posts/post.aspx?id=30

An upgrade to SQL 2008 R2 SP1 CU 7 fixed the problem and we were able to do a Search Content source crawl without crippling our SQL server. 

This SQL article has more info on the issue.

http://support.microsoft.com/kb/2658214

Article ID: 2658214 - Last Review: April 12, 2012 - Revision: 5.0

FIX: Poor performance when you run a query that contains correlated AND predicates in SQL Server 2008 or in SQL Server 2008 R2 or in SQL Server 2012

To resolve this issue, apply the following cumulative update. After you apply the update, you must enable trace flag 4137 to add the new logic for the cardinality estimation of AND predicates.

After patching it is necessary to add the switch /4137 (or -4137) as a startup parameter for the patched SQL instance in order to activate the remedy for this particular problem