Skip to content

Commit e4a0870

Browse files
AlexTitenkoAlexTitenko
committed
Add MAXDOP recommendation script and DATEDIFF function
1 parent 8c2a41c commit e4a0870

2 files changed

Lines changed: 136 additions & 0 deletions

File tree

‎Scripts/MAXDOP_recommendation.sql‎

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
/* Use coreinfo (utility by sysinternals) as this will give you
2+
a. Logical to Physical Processor Map
3+
b. Logical Processor to Socket Map
4+
c. Logical Processor to NUMA Node Map as below :
5+
Logical to Physical Processor Map:
6+
**---------------------- Physical Processor 0 (Hyperthreaded)
7+
--**-------------------- Physical Processor 1 (Hyperthreaded)
8+
----**------------------ Physical Processor 2 (Hyperthreaded)
9+
------**---------------- Physical Processor 3 (Hyperthreaded)
10+
--------**-------------- Physical Processor 4 (Hyperthreaded)
11+
----------**------------ Physical Processor 5 (Hyperthreaded)
12+
------------**---------- Physical Processor 6 (Hyperthreaded)
13+
--------------**-------- Physical Processor 7 (Hyperthreaded)
14+
----------------**------ Physical Processor 8 (Hyperthreaded)
15+
------------------**---- Physical Processor 9 (Hyperthreaded)
16+
--------------------**-- Physical Processor 10 (Hyperthreaded)
17+
----------------------** Physical Processor 11 (Hyperthreaded)
18+
Logical Processor to Socket Map:
19+
************------------ Socket 0
20+
------------************ Socket 1
21+
Logical Processor to NUMA Node Map:
22+
************------------ NUMA Node 0
23+
------------************ NUMA Node 1
24+
Now, based on the above info, the Ideal MaxDop setting should be calculated as
25+
a. It has 12 CPU’s which are hyper threaded giving us 24 CPUs.
26+
b. It has 2 NUMA node [Node 0 and 1] each having 12 CPU’s with Hyperthreading ON.
27+
c. Number of sockets are 2 [socket 0 and 1] which are housing 12 CPU’s each.
28+
Considering all above factors, the max degree of Parallelism should be set to 6 which is ideal value for server with above configuration.
29+
So the answer is -- "it depends" on your processor footprint and the NUMA configuration and below table will summarize what I explained above:
30+
8 or less processors ===> 0 to N (where N= no. of processors)
31+
More than 8 processors ===> 8
32+
NUMA configured ===> MAXDOP should not exceed no of CPU’s assigned to each
33+
NUMA node with max value capped to 8
34+
Hyper threading Enabled ===> Should not exceed the number of physical processors.
35+
Below is a quick and dirty TSQL script to generate Recommendation for MAXDOP setting
36+
37+
Author : Kin Shah
38+
Purpose : Recommend MaxDop settings for the server instance
39+
Tested RDBMS : SQL Server 2008R2 */
40+
41+
declare @hyperthreadingRatio bit
42+
declare @logicalCPUs int
43+
declare @HTEnabled int
44+
declare @physicalCPU int
45+
declare @SOCKET int
46+
declare @logicalCPUPerNuma int
47+
declare @NoOfNUMA int
48+
49+
select @logicalCPUs = cpu_count -- [Logical CPU Count]
50+
,@hyperthreadingRatio = hyperthread_ratio -- [Hyperthread Ratio]
51+
,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
52+
,@HTEnabled = case
53+
when cpu_count > hyperthread_ratio
54+
then 1
55+
else 0
56+
end -- HTEnabled
57+
from sys.dm_os_sys_info
58+
option (recompile);
59+
60+
select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
61+
from sys.dm_os_schedulers
62+
where [status] = 'VISIBLE ONLINE'
63+
and parent_node_id < 64
64+
group by parent_node_id
65+
option (recompile);
66+
67+
select @NoOfNUMA = count(distinct parent_node_id)
68+
from sys.dm_os_schedulers -- find NO OF NUMA Nodes
69+
where [status] = 'VISIBLE ONLINE'
70+
and parent_node_id < 64
71+
72+
-- Report the recommendations ....
73+
select
74+
--- 8 or less processors and NO HT enabled
75+
case
76+
when @logicalCPUs < 8
77+
and @HTEnabled = 0
78+
then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
79+
--- 8 or more processors and NO HT enabled
80+
when @logicalCPUs >= 8
81+
and @HTEnabled = 0
82+
then 'MAXDOP setting should be : 8'
83+
--- 8 or more processors and HT enabled and NO NUMA
84+
when @logicalCPUs >= 8
85+
and @HTEnabled = 1
86+
and @NoofNUMA = 1
87+
then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
88+
--- 8 or more processors and HT enabled and NUMA
89+
when @logicalCPUs >= 8
90+
and @HTEnabled = 1
91+
and @NoofNUMA > 1
92+
then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
93+
else ''
94+
end as Recommendations

‎User_Defined_Function/udf_2DateDiff.sql‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
/*Author: Itzik Ben-Gan
2+
Description: Compute Date and Time Difference in Parts
3+
Solution to computing the difference between two date and time values in a combination of parts ranging from the year down to the nanosecond.*/
4+
5+
IF OBJECT_ID(N'dbo.DATEDIFFPARTS', N'IF') IS NOT NULL
6+
DROP FUNCTION dbo.DATEDIFFPARTS;
7+
GO
8+
CREATE FUNCTION dbo.DATEDIFFPARTS(@dt1 AS DATETIME2, @dt2 AS DATETIME2)
9+
RETURNS TABLE
10+
AS
11+
RETURN
12+
SELECT
13+
sgn,
14+
yydiff - subyy AS yy,
15+
(mmdiff - submm) % 12 AS mm,
16+
DATEDIFF(day, DATEADD(mm, mmdiff - submm, dt1), dt2) - subdd AS dd,
17+
nsdiff / CAST(3600000000000 AS BIGINT) AS hh,
18+
nsdiff / CAST(60000000000 AS BIGINT) % 60 AS mi,
19+
nsdiff / 1000000000 % 60 AS ss,
20+
nsdiff % 1000000000 AS ns
21+
FROM ( VALUES( CASE WHEN @dt1 > @dt2 THEN @dt2 ELSE @dt1 END,
22+
CASE WHEN @dt1 > @dt2 THEN @dt1 ELSE @dt2 END,
23+
CASE WHEN @dt1 < @dt2 THEN 1
24+
WHEN @dt1 = @dt2 THEN 0
25+
WHEN @dt1 > @dt2 THEN -1 END ) ) AS D(dt1, dt2, sgn)
26+
CROSS APPLY ( VALUES( CAST(dt1 AS TIME), CAST(dt2 AS TIME),
27+
DATEDIFF(yy, dt1, dt2),
28+
DATEDIFF(mm, dt1, dt2),
29+
DATEDIFF(dd, dt1, dt2) ) )
30+
AS A1(t1, t2, yydiff, mmdiff, dddiff)
31+
CROSS APPLY ( VALUES
32+
( CASE WHEN DATEADD(yy, yydiff, dt1) > dt2 THEN 1 ELSE 0 END,
33+
CASE WHEN DATEADD(mm, mmdiff, dt1) > dt2 THEN 1 ELSE 0 END,
34+
CASE WHEN DATEADD(dd, dddiff, dt1) > dt2 THEN 1 ELSE 0 END ) )
35+
AS A2(subyy, submm, subdd)
36+
CROSS APPLY ( VALUES( CAST(86400000000000 AS BIGINT) * subdd
37+
+ (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t2)
38+
+ DATEPART(ns, t2))
39+
- (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t1)
40+
+ DATEPART(ns, t1)) ) )
41+
AS A3(nsdiff);
42+
GO

0 commit comments

Comments
 (0)