High parallelism in Query Store.

Question

Greetings. As you see in the screen shot the bulk of my wait time is going to one wait type.

A few fun facts:

1. This is in a reporting type DB with the bulk of activity coming from ETL and Power BI queries, no OLTP type stuff at all.
2. Maxdop on the instance is set to 4 (our of 12 CPU), with Cost Threshold for Parallelism set to 20 (not sure how we wound up on that).
3. Not currently experiencing any sort of emergency, this is more like preemptive performance tuning. That said this question is intended for more academic/ learning that needing to troubleshoot anything.

Most articles on the parallelism topic reference high cxpackets, but Im not currently experiencing that as we're not having any issues.

So my question is should seeing the screen shot be reason for concern, or should it be considered that SQL is doing exactly what it should be? In other words should it be viewed the queries in the last hour have been utilizing parallelism as I'd want them to, or that this is a problem waiting to surface?

Thanks!

Answer 1

Something has to be on top. And with an analytical workload, you probably have queries that benefit from parallelism.

I would have been more concerned if CPU had been on top. Or Network IO.