CODEWITHSUNDEEP
sql-serverperformance
CMS
CMS

Reputation: 61

MAXDOP and Interesting Performance Experiences

I would like some advice on the following topic.

With one of our products from our company we "connect" to an existing database on the customer's side and import orders into our own SQL Server to let our desktop product plan and optimize them. They are for different lorries that will take different tours to deliver the goods from A to B.

During that import we experienced some performance problems that could have been solved the following way:

We had set up several maintenance plans (from what I've learned is not always the best approach, we are now following Ola Hallengren's Maintenance Solution) to rebuild indexes and update statistics.

But the performance problems are still there.

The former DBA tried to diagnose what was going on with the SQL Server and he used this script:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT 'Identify what is causing the waits.' AS [Step01];
SELECT TOP 10
        [Wait type] = wait_type,
        [Wait time (s)] = wait_time_ms / 1000,
        [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms)     OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;

He found out that CXPACKET had been relatively high.

He googled a bit and found out that the MAXDOP parameter has got something to do with it. So they went to the SSMS and changed the MAXDOP from the one value "0" to the other value "1" or vice versa.

As soon as the parameter was changed (regardless of which direction) the performance immediately got better. Before switching the parameter the CPU was very high (working with the product was almost not possible) after the switch it immediately went to minimal CPU-Usage. It looked as if the SQL Server was bored to death.

If our guys tried to switch the MAXDOP to the other value per script:

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

It surprisingly did not show any effect at all. The CPU-Usage stayed the same high as before executing the script.

My question to you guys is: Do you have a clue why switching the MAXDOP in SSMS brings CPU-Usage down to normal values?

I know that I cannot expect a full satisfying answer as my details of that story are not detailed enough. But I would appreciate if you could just point me into the right direction like

Hey, have a look at I/O , set up your PerfMon like this , etc, etc.

Upvotes: 0

Views: 1581

Answers (2)

Vali Alexandrescu
Vali Alexandrescu

Reputation: 176

I see no answer has been marked as the best one, so let me try and help.

While searching the web to find some help with diagnosing the cause of high CXPACKET values on our side, I hit the good article that shows some different aspects and causes of high CXPACKET values that helped us: http://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server/

Here are the recommendations I found within the article:

  • Do not set MAXDOP to 1 (this is never the solution). To learn more, navigate to this page

  • Investigate your query and CXPACKET history to understand and determine whether it is something that occurred just once or twice, as it could be just the exception in the system that is usually working correctly

  • Check the indexes and statistics on tables used by the query and make sure they are up to date

  • Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system

  • Check whether the CXPACKET is accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the case than the MAXDOP value should be lowered to fit your hardware

  • Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is the case, then parallelism is not the bottleneck. Troubleshoot those wait stats to find the root cause of the problem and solution

Upvotes: 2

John Alan
John Alan

Reputation: 140

Since there were lots of CXPacket waits before, the DOP value must have been 0 (default) and was later set to 1 (disabling parallelism).

There is a lot of controversy around this subject. It may be OK for a highly-optimised purely OLTP load, but most loads are mixed and tend to contain 'heavy' queries that would normally benefit from parallelism.

I would not be happy with MAXDOP set to 1 in general - if you must reduce it, best to try setting it to 2 or 4 and see how it goes. Also worth raising the 'cost threshold of Parallelism' to something higher than the default 5 (try 25 for starters). This will reduce the number of queries considered for parallel plans.

Upvotes: 0

Related Questions