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