January 2020 – Run SQL Server BLOG

17th January 20201st February 2020

Test your MAXDOP – SQL Server max degree of parallelism

The default value of your SQL server is MAXDOP=0. It means there is no limit for parallelism. Your query can use all of your CPU core.

Scenario:
If your SQL Server is not used, than your CPU utilization is low,
your query can use all of the server’s resources.
I run a cross join query in a table which has 40.000 rows.
The server has 8 core.
I set hint for the query to simulate the MAXDOP server setting.

The query and the result:

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 8) 
--  result 33520 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 6)  
-- result 43461 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 4) 
-- result 59837 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 3) 
-- result 80219 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 2) 
-- result 116805 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 1)  
-- result 232825

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 8)

-- result 33520 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 6)

-- result 43461 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 4)

-- result 59837 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 3)

-- result 80219 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 2)

-- result 116805 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )

FROM szamolotabla o1

CROSS JOIN szamolotabla o2 option (maxdop 1)

-- result 232825

So if your query use only 1 thread it run longer ( in the example 232 seconds), if it use 8 thread it run much more quicker ( 33 seconds).
So 232/33 ~= 7 . Ok the speed is not 8x , but almost 8. The operating system and other processes use system resources.

Should we use the maxdop value without limit? In the real life a lot of queries run at the same time, so if your query get all of the resource the other waiting the resources. So you try to figure out what is ideal value?
If you have no idea, or don’t have experience with the new databases , you can set the recommended value:
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

Server Propertis - MAXDOP — Server Propertis – MAXDOP

You can reduce the parallelism in your server with increasing the Cost Threshold for Parallelims.

You can set it for individually for every database:

Database settings

Max DOP for you database and Max DOP For Secondary for your Always ON AG secondary replicas.

13th January 202013th January 2020

How to Check Index Fragmentation and Partitioned Table Index Fragmentation

I use two script. The firs one is for general purpose and run faster, the second one is good for partitioned table and get many details about partitioned index and table.

SELECT OBJECT_NAME(ips.OBJECT_ID)
 ,i.NAME
 ,ips.index_id
 ,ips.partition_number
 ,index_type_desc
 ,avg_fragmentation_in_percent
 ,avg_page_space_used_in_percent
 ,page_count
 ,ips.record_count
 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
-- where OBJECT_NAME(ips.OBJECT_ID) ='table_name'
ORDER BY index_id,ips.partition_number,avg_fragmentation_in_percent DESC

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SELECT OBJECT_NAME(ips.OBJECT_ID)

,i.NAME

,ips.index_id

,ips.partition_number

,index_type_desc

,avg_fragmentation_in_percent

,avg_page_space_used_in_percent

,page_count

,ips.record_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)

AND (ips.index_id = i.index_id)

-- where OBJECT_NAME(ips.OBJECT_ID) ='table_name'

ORDER BY index_id,ips.partition_number,avg_fragmentation_in_percent DESC

Detailed query for partitioned table:

/*
Script  : Index Fragmentation Status (includes Partitioned Tables/Indexes)
Version : 1.0 (April 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/
- Hide quoted text -
SELECT
             SCHEMA_NAME(o.schema_id) AS SchemaName               
            ,OBJECT_NAME(o.object_id) AS TableName
            ,i.name  AS IndexName
            ,i.type_desc AS IndexType
            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
            ,COALESCE(fg.name ,fgp.name) AS FileGroupName
            ,p.partition_number AS PartitionNumber
            ,p.rows AS PartitionRows
            ,dmv.Avg_Fragmentation_In_Percent
            ,dmv.Fragment_Count
            ,dmv.Avg_Fragment_Size_In_Pages
            ,dmv.Page_Count 
            ,prv_left.value  AS PartitionLowerBoundaryValue
            ,prv_right.value AS PartitionUpperBoundaryValue
            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
            ,pf.name        AS PartitionFunction
            ,ds.name AS PartitionScheme
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
            ON i.object_id = p.object_id
            AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
            ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv
            ON dmv.OBJECT_ID = i.object_id
            AND dmv.index_id = i.index_id
            AND dmv.partition_number  = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
      ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
      ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)
      ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)
      ON dds.partition_scheme_id = ps.data_space_id
      AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
      ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)
      ON fgp.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
ORDER BY
     SchemaName
    ,TableName
    ,IndexName
    ,PartitionNumber

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

/*

Script : Index Fragmentation Status (includes Partitioned Tables/Indexes)

Version : 1.0 (April 2010)

Author : Richard Doering

Web : http://sqlsolace.blogspot.com

*/

- Hide quoted text -

SELECT

SCHEMA_NAME(o.schema_id) AS SchemaName

,OBJECT_NAME(o.object_id) AS TableName

,i.name AS IndexName

,i.type_desc AS IndexType

,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned

,COALESCE(fg.name ,fgp.name) AS FileGroupName

,p.partition_number AS PartitionNumber

,p.rows AS PartitionRows

,dmv.Avg_Fragmentation_In_Percent

,dmv.Fragment_Count

,dmv.Avg_Fragment_Size_In_Pages

,dmv.Page_Count

,prv_left.value AS PartitionLowerBoundaryValue

,prv_right.value AS PartitionUpperBoundaryValue

,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange

,pf.name AS PartitionFunction

,ds.name AS PartitionScheme

FROM sys.partitions AS p WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.objects AS o WITH (NOLOCK)

ON o.object_id = i.object_id

INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv

ON dmv.OBJECT_ID = i.object_id

AND dmv.index_id = i.index_id

AND dmv.partition_number = p.partition_number

LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)

ON ds.data_space_id = i.data_space_id

LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)

ON ps.data_space_id = ds.data_space_id

LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)

ON pf.function_id = ps.function_id

LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)

ON dds.partition_scheme_id = ps.data_space_id

AND dds.destination_id = p.partition_number

LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)

ON fg.data_space_id = i.data_space_id

LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)

ON fgp.data_space_id = dds.data_space_id

LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)

ON ps.function_id = prv_left.function_id

AND prv_left.boundary_id = p.partition_number - 1

LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)

ON ps.function_id = prv_right.function_id

AND prv_right.boundary_id = p.partition_number

WHERE

OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0

ORDER BY

SchemaName

,TableName

,IndexName

,PartitionNumber