PERFXPERT. New ability to analyze the impact of CXPACKET expectations on performance

PERFXPERT. New ability to analyze the impact of CXPACKET expectations on performance

Hello everybody!

We want to share good news.

In PerfExpert monitoring, starting with version 4.0.8.81 it is possible to collect information on expectations about the parallelism of any SQL query from the READS and DURATION traces, which can lead to deadlocks.

So, if you have a level of parallelism different from 1 on SQL Server, then in any case you have parallelism expectations, the level of which and the impact on a specific database is good to know.

The figure below shows a screen shot from the Duration trace, where some requests in the CXPACKET column show how long the request has been waiting for in parallelism.

This provides an excellent opportunity to evaluate the impact of parallelism on the duration of heavy queries.

If you group all the requests in the received trace by their type (according to MD5), you can see those groups of requests that most often “suffer” from parallelism – the “% Share of CXPACKET” column is sorted in descending order:

This list contains all kinds of requests that are worth paying attention to in terms of slowing them down due to parallelism settings.

For example, let’s take the MD5 request group 13324255069401524566 (the second line on the previous screen). There were a total of 19 such requests. And almost all of them had an extremely negative effect of parallelism – the duration of CXPACKET’s waits was compared to the duration of the request – see figure below, columns Duration and CXPACKET.

Thus, if during the analysis it is possible to identify groups of heavy requests, in which the total waiting time of CXPACKET is significant, comparable to the duration of the request or even more, then parallelism is recommended to be disabled for them.

For more information on the influence of parallelism on the operation of the 1C system, its settings and fine-tuning of the MAXDOP parameter for individual groups of requests, see the analytical article Notes of the 1C optimizer (part 4). Parallelism in 1C, settings, waiting for CXPACKET.

Related posts