SCOM DW XML Query

Dale 2017-04-05 20:09:13

I created an index on the ManagedEntityProperty table for the SCOM DW database (https://technet.microsoft.com/en-us/library/gg508713.aspx). 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?

Aaron Bertrand 2017-04-11 20:29:48
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.