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

Advertisements

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

A developer was looking at some reports to try and improve performance and they were advised by Microsoft to use a trick which I had never heard off setting SQL Server trace flag 8780

I know what you are thinking

“Hosk, you didn’t even know about turning on SQL Server trace flag 8780, amateur”

Everyday is a school, so prepare yourself for today’s lesson

What difference did turning on the flag

The performance was dramatically improved, changing the report speed from 14 seconds down to 1 second!

BOOM

I’m interested tell me more

The report was an SQL report and to turn on the flag you simply put the this line at the end of the SQL statement, after the where clause.

option (querytraceon 8780)

e.g.

select name, accountid, telephone1

from account

where name = ‘hosk’

option (querytraceon 8780)

The good thing to note is you can turn this flag on for individual queries.

What does the flag do

The developer called it the “make it faster” setting on SQL. The query in the report was joining three different tables and one of them was contact.

warning – the description below is my understanding, it could well be wrong but it will give you an idea of what I think is happening, more research by yourself would need to be done if you are thinking of using it.

When SQL queries run on SQL Server the SQL server creates an execution plan. SQL Server calculates the most efficient way to retrieve the data you want. When the query is compiled if the execution plan can’t find a quick it will time out and then go with the best one it had found so far.

There could be other more effective execution plans for your SQL statement and if you gave the execution plan a bit more time it could find them.

When I mention time, I’m talking milliseconds (which is a long time for SQL Server)

In the report we were joining the contact table but using a contact guid but the execution plan was timing out because the execution plan was doing a full table scan of the contact table (2 million rows).

Whilst it was trying to scan the whole contact table it timed out and gave up (who can blame it)

SQL Server trace flag – 8780 to the rescue. This flag gives the sql optimizer more time to find the most effective (e.g. quickest) way to retrieve the data.

You set the flag (which can be set server side or for each individual query) this increases the time limit before the optimizer timeout occurs.

The result in our reports meant the execution plan found a better way to optimize the query and the reports ran a lot of faster.

What are the downsides

Traceflags in SQL Server are undocumented and unsupported by Microsoft (unless they advise you to use it 🙂

I view traceflags as developer tools written by developers but not fully tested in all scenarios. This means Microsoft doesn’t know how the queries will react in all scenarios.

The obvious danger by turning off the time limit for optimizing a query I guess is it could keep on optimizing for a long time. The problem is no one really knows what it’s going to do

When to use it

If you have some reports which are taking a long time to run and you have been asked to optimize them, turning on the this flag for individual queries (e.g. reports) could save you a lot of time.

It’s unsupported so I would suggest you only use it when you really need to

Further reading

I couldn’t find a lot of information on this mysterious trace flag but these three articles will help you learn more

Optimizer Timeout or Optimizer memory abort

 Is it possible to give the optimizer more or all the time it needs

SL Server Trace flag – 8780

CRM 2011 – How to find the location of the SQL Server for your CRM Server

I was trying to import a solution but it was throwing an SQL Server timeout error

I was looking at CRM instance, I had remote desktop rights to the CRM server.

I was getting some SQL problems and I could see the SQL server wasn’t on the CRM Server but where was it.

Unfortunatly my user couldn’t access the Deployment Manager and I didn’t know or have access to a user who did.

So I was in an odd situation of wanting to find the SQL Server for CRM Instance and not knowing what server it was on.

So how could I check where the SQL Server was?

 

Investigating why my solution was timing out

I searched for CRM 2011 solution import timeout and came right back to my own blog

CRM 2011 – A time-out occurs when you import a solution in Microsoft Dynamics CRM 2011

The blog post above is quite a popular one and it has had about 5000 views and still gets 6 a month.

 

There are a couple of time out settings, the SQL timeout setting which is held in OLEDBTimeout, I had to have a peek in the Registry and the settings in MSCRM

 

configdb

 

I noticed there was a configdb value and before I go any further DO NOT EDIT THIS VALUE, imagine it’s read only and for information purposes only.

 

Data Source=COM-UKG-SQL\HOSKORG;Initial Catalog=MSCRM_CONFIG;Integrated Security=True;Connect Timeout=60

 

Frustratingly my user didn’t have access to the server but it was a way to find out the server name, in times of need.  There probably is an easier way but this has happened to me a few times where I have been trying to work out the location of the SQL Server, even if it’s to point IT support in the right direction

 

Looking at the Registry fields reminds me of my CRM 4 development days

 

 

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

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/

CRM 2011 – an error occurred while connecting to the MSCRM_CONFIG database

I got this error today when suddenly and for no reason I could work out their CRM 2011 stopped working (this has happened twice in one week with two different customers).

I have blogged about this error before and if you have got this error then check out the blog below

https://crmbusiness.wordpress.com/2011/09/02/crm-2011-mscrm_config-database-is-unavailable/

This time it was the same except the previous solution wasn’t working, although I thought it should

When I was trying to diagnose the problem, I noticed the MSCRM_CONFIG database wasn’t attched but  also it wasn’t detached.

I also noticed there was another database created with a guid type name, when I opened the database I noticed it was basically a copy of the MSCRM_CONFIG database (in terms of tables).

I’m not sure how this happened but it looked like some kind of backup or transaction had caused a copy of the MSCRM_CONFIG database.  I looked at the physical files of the guid database and it had the physical name of MSCRM_CONFIG.mdf.

I think maybe the server lost power and when the server came back up the guid database opened it locked the MSCRM_CONFIG.mdf file so when the real MSCRM_CONFIG database tried to lock it couldn’t.

CRM cannot function if the MSCRM_CONFIG database is down because this is basically a systems setting type database with information for all the organisations on the server.

To resolve the problem I had to unattach the guid database and then attach the MSCRM_CONFIG database and it came up and everything was working again.

here is a screenshot of the dodgy guid database

How to convert an SQL statement into a XML file

Today I found some functionality which I thought was amazing and will save me loads of time creating some sample XML files to test my webservice with.

I had some sample data in database which I wanted to convert into an XML file.

My original idea was to use excel to concatenate bits of it but this turned out to be to slow and difficult.  I then found out that SQL Server has in built functionality to convert sql statements into XML files.

This article goes through some of the functionality and I would also read this article if you want more detail than my example below

below is my select statement, the important part is at the end
FOR XML AUTO,ELEMENTS
this converts the sql statement into XML elements
  
SELECT
      [Code]"Code"
      ,[Name]"Name"
      ,[Commission %]"Commission"
      ,[E-Mail]"Email"
      ,[Phone No_]"Phone"
      ,[Job Title]"JobTitle"
  FROM [database].[dbo].[Salesperson_Purchaser]salesperson
  FOR XML AUTO,ELEMENTS

this is the result, amazing.  I can't believe I have never heard of this fantastic 
feature before

<salesperson>
  <Code>MT</Code>
  <Name>Metaphorix</Name>
  <Commission>0.00000000000000000000</Commission>
  <Email>metaphorix@metaphorix.co.uk</Email>
  <Phone></Phone>
  <JobTitle></JobTitle>
</salesperson>
<salesperson>
  <Code>TS</Code>
  <Name>tom scott</Name>
  <Commission>0.00000000000000000000</Commission>
  <Email>tom.email.co.uk</Email>
  <Phone></Phone>
  <JobTitle></JobTitle>
</salesperson>
<salesperson>
  <Code>DR</Code>
  <Name>Jerry time</Name>
  <Commission>0.00000000000000000000</Commission>
  <Email></Email>
  <Phone></Phone>
  <JobTitle></JobTitle>
</salesperson>