What is max degree of parallelism option setting in SQL Server?

A ticket opened recently with Microsoft support involved changing a the  max degree of parallelism on the SQL server.

I had not used or heard of this setting before, which isn’t a surprise I tend to leave SQL Server settings to the people who know more about the consequences.

Early in the year I did look at effects of changing the SQL Server trace flag 8780 which resulted in reports running faster but is unsupported unless Microsoft support teams advices you to do it!

CRM SQL Query go faster trick – SQL Server trace flag 8780

My goal in this blog is to give an overview about max degree of parallelism Option and some links to more information.

Remember to always consider the effects of changing flags and options, they can change results/efficiency in other SQL queries

What is max degree of parallelism Option 

The place to start is the MSDN article

max degree of parallelism Option

When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. The default value of 0 uses all available processors up to 64 CPUs

You can learn more about Degree of Parallelism in this specific article

This setting is sometimes known as MAXDOP (max degree of parallelism)

Hosk understanding

This max degree of parallelism controls how many processors in the SQL server can be used on any one single query.

The default value for Max degree of parallelism is set to 0, this means there is no limit to the number of processors.

If you change the option to a number e.g. 1, you are then limiting the SQL server to use one processor for each query.

This setting seems to be changed when users want to limit the number of processors used in single queries to stop SQL server taking all the processors for single queries.

The setting is also used to increase the number to improve performance in single queries.

This is a server side setting so you need to consider how changing this setting could effect the performance of all SQL queries on the SQL server (and maybe not just CRM)

More information

This article is very useful because it mentions limitations and restrictions and recommedations

Configure the max degree of parallelism Server Configuration Option

This recommendations I found interesting

  • This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
  • To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.
  • You can override the max degree of parallelism value in queries by specifying the MAXDOP query hint in the query statement. For more information, see Query Hints (Transact-SQL).

It seems to suggest you can set it on an individual query basis.

Here is another article

Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server

How to change the Max degree of parallelism Option

It’s an SQL option so set the value using an sql statement.

I took the code from this page

https://msdn.microsoft.com/en-us/library/dd979074(v=crm.6).aspx


exec sp_configure 'show adv', 1;

RECONFIGURE WITH OVERRIDE;

exec sp_configure 'max degree', 1

RECONFIGURE WITH OVERRIDE;

Exec sp_configure

This is a server wide setting, so it would effect all the CRM organisations you have on the SQL Server and other databases.

How does is this setting used with CRM

Take into account I have not had any personal experience changing this setting, I saw it was mentioned as a possible resolution, I decided to investigate.  

This setting usually effects CRM if you have multiple long running queries.  It can cause the CRM performance to slow.

This article talks about CRM 4

Microsoft Dynamics CRM Server performance is less than expected

In the example above it seems the SQL server was using all the processors on one long running query which caused performance of CRM in general to slow down.  It limits sets the MAXDOP to 1 to stop this.

This article is very good and the blog looks into the setting in detail

Max degree of Parallelism in SQL Server

The article concludes it’s probably best to tinker with this setting only if your having slow performance from your SQL server/CRM.

Other articles

Case Study: Part 1: CXPACKET Wait Stats & ‘max degree of parallelism’ Option: Introduction to Using Wait Stats to Identify & Remediate Query Parallelism Bottlenecks

Case Study: Part 2: CXPACKET Wait Stats & ‘max degree of parallelism’ Option: Suppressing Query Parallelism Eliminated CXPACKET Waits & Liberated 30% of CPU

Interesting forum discussions

Max degree of parallelism setting

How to find the number of users connected to a SQL Server database

I was having some intermittent problems with the connector.  When I turned on the connector it would soon be unable to connect to the SQL database.

I was thinking it maybe be had something to do with the SQL server limiting connections.

The code below will show you the amount of connections and how many on each database.  I found it in this forum post

http://stackoverflow.com/questions/216007/how-to-determine-total-number-of-open-active-connections-in-ms-sql-server-2005

SELECT

    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

And this gives the total:

SELECT 
    COUNT(dbid) as TotalConnections
FROM
    sys.sysprocesses
WHERE 
    dbid > 0

CRM 2011 installation error – SQL Server: The instance name must be the same as computer name

I got this error on a recent CRM Install.

The reason for this error was because the name of the SQL Server machine had been changed after SQL Server 2012 had been installed.

So what I think basically happens when you install CRM it creates the default instance with the same name as the computer – server name.

So I believe the CRM 2011 installation was looking for the service name which is the same name as the server and couldn’t find one.

luckily there is a couple of good blogs written on this

http://community.dynamics.com/product/crm/crmtechnical/b/zhongchenzhoustipstricksandportaldevelopment/archive/2012/04/28/dynamics-crm-2011-installation-error-the-instance-name-must-be-the-same-as-computer-name.aspx

http://mscrmuk.blogspot.co.uk/2008/10/sql-server-instance-name-must-be-same.html

The answer to this problem is to rename the instance name with some SQL commands I never come across before.

Open up an SQL Query window and type

sp_helpserver 

This will give you the name of the current server instance and a bunch of other values (but don’t worry about those)

We now need to drop this name by running the SQL command below, You put the value of the incorrect server name in the quotes  (where I have INCORRECT SERVER NAME”

SP_DROPSERVER “INCORRECT SERVER NAME”

now run the command below and the name in the quotes is the name of the server, don’t forget to add the comma local at the end otherwise the sql statement will error.

SP_ADDSERVER “DATABASE-1”, local

After you have done the change you can run

sp_helpserver 

It should return the changed value.

You now need to run restart SQL Server and try the CRM install verification again

Find the version of SQL server you are running

This would seem an easy thing to do but it can be a maze of screens.

You can go to the SQL Server manager and go help –> about

You can also run the SQL query below

SELECT @@VERSION ‘Version’, serverproperty(‘edition’) ‘Server Edition’ , SERVERPROPERTY(‘ProductLevel’) ‘ProductLevel’

which gives information like this

Version

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

Edition

Enterprise Edition (64-bit)

Product

RTM

Whilst searching for a list of build numbers I found this excellent article from Microsoft which contains information about how to find version numbers of different products and lists some of the version numbers of the different releases

How to determine the version and edition of SQL Server and its components

if you want a comprehensive list of SQL Server number then the blog below is excellent

http://sqlserverbuilds.blogspot.co.uk/

Free E-Book – Introducing SQL Server 2012

CRM 2011 rollup 8 is on the way and to get the large benefits of this rollup (cross browser, mobile apps, better reporting) you will need to be running SQL Server 2012.  So the bottom line is we are all going to need to know how SQL Server 2012 works

So to help you get to grips  MS Press are giving away a free SQL Server 2012 ebook, so a big thanks to the writers Ross Mistry and Stacia Misner.

To buy the hardback version of the book it will cost you 15 bucks but you can download the pdf version completely free.

Click here to download the pdf version

download the PDF version of this title here

below is the contents page of the book.

PART I DATABASE ADMINISTRATION

1. SQL Server 2012 Editions and Engine Enhancements

2. High-Availability and Disaster-Recovery Enhancements

3. Performance and Scalability

4. Security Enhancements

5. Programmability and Beyond-Relational Enhancements

PART II BUSINESS INTELLIGENCE DEVELOPMENT (by Stacia Misner)

6. Integration Services

7. Data Quality Services

8. Master Data Services

9. Analysis Services and PowerPivot

10. Reporting Services

CRM 2011 – Now works with SQL Server 2012

Microsoft are gearing up for the big new release later this year by releasing SQL Server 2012 and making it compatible with CRM 2011.  The link below goes into detail about the big release

Q2 2012 Service update

So How did I find out about SQL Server 2012 being compatible with CRM 2011, well a couple of sources.

Jamie Miley sums up the changes and has links to the relevant support pages in his blog

http://mileyja.blogspot.com/2012/03/sql-2012-is-now-officially-supported.html

He has some interesting information on a couple of potential problems with the CRM 2011 and SQL Server marriage.

Just be warned, that it’s not perfect yet and Microsoft is working on a couple of issues, but they will have hot-fixes out for these soon.  The issues below are both noted in the KB articles above.

– One issue in 4.0 can cause reports not to be displayed and there isn’t a workaround, but a hotfix is coming.

– Lastly, in CRM 2011, there is still an issue they are working on that can cause the outlook client to fail when using SQL 2012.  A hotfix is also actively being worked on for this at this time also.

I found some information from the excellent blog Dynamics CRM in the field, this is the blog written by Dynamics CRM service team and has some excellent technical information.  Click the link below to find links to SQL Server 2012

http://blogs.msdn.com/b/crminthefield/archive/2012/03/07/dynamics-crm-amp-sql-2012-compatibility.aspx

This blog has some key information in it.

Firstly you need to be on at least rollup 6.

It also has some links to problems which might occur when upgrading to SQL Server 2012 – http://support.microsoft.com/kb/2686619

if you want to know what benefits you will get by using SQL Server 2012 with CRM 2011 then you can read the Microsoft blog below and it will show you the new reporting and other benefits

http://blogs.msdn.com/b/crm/archive/2012/03/07/microsoft-dynamics-crm-and-sql-server-2012-better-together.aspx

Personally I will be waiting until CRM 2011 and SQL Server 2012 has been tested a bit more thoroughly before I consider getting this up and running but releasing this to the CRM community will certainly speed things along.