As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits.
I know the knee-jerk solution is to turn off all parallelism by setting MAXDOP to 1 - sounds like a bad idea. But another idea is to increase the cost threshold before parallelism kicks in. The default of 5 for the cost of an execution plan is fairly low.
So I was wondering if there's a query out there already written that would find me the queries with the highest execution plan cost (I know you can find those with the highest duration of execution and so on - but is the execution plan cost retrievable somewhere, too?) and that would also tell me if such a query has been executed in parallel.
Does anyone have such a script at hand, or can point me in the direction of the relevant DMV, DMF or other system catalog views to find this out?