0

i would like to receive an advice on the following topic.

With one of our products from our company we "connect" to an existing database on customers side and import orders into our own SQL Server to let our desktop product plan and optimize them all 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:

Besides i have to say that i took over responsibility from the other guy that worked in this company before me as the Accidental DBA, but now it's my job :-)

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 MaintenanceSolution) to rebuild indexes and update statistics. But the performance problems were still there.

The former DBA tried to diagnose what was going on on 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 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.

Thank you in advance, Christoph

3 Answers 3

2

A few things happened when that change was made.

When this change was made SQL Server expired all the execution plans from the plan cache. When this happened every query that was being run SQL Server had to generate new plans.

When you change the MAXDOP setting from 0 to 1 this simply prevents SQL Server from being able to use parallel execution plans. Parallel plans mean that SQL Server can use all the processors for a single query. When you set this setting to 1 it means that each query can use no more than a single thread.

The CX_PACKET wait type just means that one thread of a parallel query is waiting for another thread of that parallel query to complete. CX_PACKET is a symptom not a cause.

To identify the root cause you need to look at the execution plans for the problem queries and see what the problems are. You could have missing indexes, you could have out of date statistics, you could need more RAM, you could have slow hard drives, etc.

3
  • Thanks for your answer, i will follow your advice as soon as the customers system shows the slower performance again, but how come that there is a difference in changing the value using mouse-clicks in SSMS or scripting it?
    – CMS
    Apr 15, 2013 at 23:40
  • It was just dumb luck. Because it did that it tells me that there was a bad plan in the cache, probably due to parameter sniffing. The bad plan was flushed when the setting was changed and a good plan was created after the setting was changed.
    – mrdenny
    Apr 16, 2013 at 1:47
  • As always with SQL Server the answer is "it depends", but the hint from mrdenny pointed me into the right direction. There is no "do this" or "you must not do this", but this answer came very close.
    – CMS
    Jan 16, 2014 at 8:22
0

Based on my experience, the CXPACKET was always the tricky wait type for younger DBAs that often caused some predictably wrong reactions. I've tried in Troubleshooting the CXPACKET wait type in SQL Server article to present most of the reasons for high CXPACKET but also to explain the CXPACKET background

So for those who don't want to get in details, I will post the summary of the article here:

  • Do not set MAXDOP to 1, as this is never the solution

  • Investigate the 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 normally
    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

0

When the MAXDOP is set to 0, the SQL server will use all the available processors to execute the query. When you change the MAXDOP value to 1, You have restricted the SQL Server to use only 1 process/thread for a single query.

Mostly, you will see the CX_PACKET wait type when MAXDOP is set to 0 (all available processors) or set to greater than 1 (e.g, 4). In this case, a query will execute in parallel using 4 processes and when one process/thread is waiting for another process/thread to finish, you will observe the CX_PACKET wait type.

To understand more about MAXDOP, read this article explaining the MAXDOP.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .