.::: Performance Compare 4 CPU vs 1 CPU In SQL Server Using Execution Plan (Setting MaxDop Max Degree Of Parallelism) :::.

 
1. Download & restore AdventureWorks2019
2. Open New Query and run Query below & Execute
set Statistics io, time on
use AdventureWorks2019

-- Query running on 4 logical processors

 

select * from
(select *,
row_number() over (order by SalesOrderDetailID ASC) TopRow,
row_number() over (order by SalesOrderDetailID DESC) BottomRow
From Sales.SalesOrderDetail
) t
WHERE TopRow = 1 or BottomRow = 1
OPTION (MAXDOP 4)
GO


-- Query running on 1 logical processors

select * from
(select *,
row_number() over (order by SalesOrderDetailID ASC) TopRow,
row_number() over (order by SalesOrderDetailID DESC) BottomRow
From Sales.SalesOrderDetail
) t
WHERE TopRow = 1 or BottomRow = 1
OPTION (MAXDOP 1)
GO

 
3.  Result from Execute

 

4. Select Query below & Run "Display Estimated Execution Plan (Ctrl+L)
-- Query running on 4 logical processors

select * from
(select *,
row_number() over (order by SalesOrderDetailID ASC) TopRow,
row_number() over (order by SalesOrderDetailID DESC) BottomRow
From Sales.SalesOrderDetail
) t
WHERE TopRow = 1 or BottomRow = 1
OPTION (MAXDOP 4)
GO


-- Query running on 1 logical processors

select * from
(select *,
row_number() over (order by SalesOrderDetailID ASC) TopRow,
row_number() over (order by SalesOrderDetailID DESC) BottomRow
From Sales.SalesOrderDetail
) t
WHERE TopRow = 1 or BottomRow = 1
OPTION (MAXDOP 1)
GO

5. See compare "Execution Plan" Display