Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '13 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 '13 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 at 8:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.