I created an index on the ManagedEntityProperty table for the SCOM DW database ( It is a primary xml index so we can query the PropertyXML column efficiently.

Now, the query uses the index properly, but it still takes 85% of time on parallelism. Is there a way to fix that?

tmp7F5E.pesession (645.8 kB)
avatar image By Dale 3 asked Apr 05 at 08:09 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Apr 11 at 08:29 PM

If you disable parallelism (`OPTION (MAXDOP 1)`), does the query then run faster, or slower? Something is always going to take time and accumulate wait stats, and not all CXPACKET wait stats are "bad" - usually this is just indicating that parallelism is working correctly. From my view (looking at the work distribution across threads on the Plan Tree tab), it looks like parallelism is helping this query, not hurting it.

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Apr 05 at 08:09 PM

Seen: 128 times

Last Updated: Apr 11 at 08:29 PM