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%.
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.
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
