Sql-server – High CPU in SQL Server supporting SharePoint databases while running FAST search crawl

As this is SharePoint your hands are tied. You can't create indexes or statistics without loosing support from Microsoft.

CXPACKET just means that one thread of a parallized query is waiting for another thread to finish waiting for something else. SOS_SCHEDULER_YIELD can mean a few things, typically it means that you need to fix your indexes are you have queries that aren't tuned correctly so the SQL Server is pausing those queries (yielding them) so that it can work on other stuff (as this is SharePoint you can't really do anything about this). OLEDB means that the SQL Server is waiting on the OLEDB driver so probably there are linked server queries or something along those lines that are calling out to another database server. There's nothing you can do about that either.

From what it sounds like there's about nothing you can do without throwing more hardware at the problem. Use Profiler, Server Side Tracing or Extended Events and find the queries which are taking to long to run and check their execution plans and see how bad that are. Odds are those search queries are doing large scan operations.

How big are the content databases? Microsoft recommends keeping the content databases under 100 Gigs so that scanning is kept to a minimum.

I have had similar issues with SQL Server, it is possible that your server is not optimally configured. Newer Xeons come with TurboBoost, HT, etc. that can affect server performance significantly.

For example, we have had success with; Low Latency Configuration for Dell servers

The settings will be applicable to non-Dell servers, they just might have different names.

We also improved performance by setting the windows power management profile to high performance, from Balanced. A final piece is that it is recommended to reserve up to 8GB of memory for the OS on x64 servers, the default SQL install takes all memory. You might want to try 4/8GB reservation by setting your max SQL Server memory configuration to 4/8GB less than total memory.

My recommendation would be to revert to the old server if possible. If you do not have regression/automation/load scripts available, then the best you can do is to record your system activity for 1-4 hours during a high activity period. Then setup a web server the same as production, and a client machine to run the script. Run the same activity against the new server, make the configuration change and run the same activity again. Really you would want to do much more, but it doesn't appear that it would be viable and is outside the scope of this question.