Sql-server – Find Queries that Are Causing Waits

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.

CXPACKET is never a cause; it gets all the blame, but it's always a symptom of something else. You need to catch these queries in the act and figure out what "something else" is. It might be different from query to query, and turning off parallelism altogether is - as you've suggested - unnecessary overkill in most cases. But it is often the least amount of work, which is why it is such a prevalent "fix."

If you can get an actual plan for a query that seems to be responsible for high CXPACKET waits, load it into SentryOne Plan Explorer. There's usually a reason behind this; we show which parallel operations led to thread skew, and you can easily correlate that to estimates that are off (we highlight operations with estimates that are off by a at least certain threshold). Usually the underlying problem is really bad/out-of-date (or unavailable) statistics.

Unfortunately what you'll find in sys.dm_exec_cached_plans are estimated plans. They won't tell you whether the plan went parallel when it was actually used, because the actual plan is not what's cached. In some cases you expect to see both a serial and parallel plan for the same query; this is not how SQL Server deals with the situation for parallel plans that might be parallel at runtime. (Lots of information about that here.)