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

A question about the “max degree of parallelism

Advertisement

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>

CRM 2011 – Will Denali, the new version of SQL Server help ease the pain of creating reports in CRM 2011

This week I have been writing some reports using SSIS and have been tearing my hair out at the slow and painful process using SSIS can be.  Writing reports in CRM 2011 is one area Microsoft really need to improve.

I have read on a few blog posts that in the next release of SQL Server which is codenamed Denali might improve this so I tried to find some information on it and I have found a bit of information but nothing to get my hopes up.  The good news is Denali is meant to be released in 2011 and you can see from the table below, which I found here

Sql Server historical releases

In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.

• 1993 – SQL Server 4.21 for Windows NT
• 1995 – SQL Server 6.0, codenamed SQL95
• 1996 – SQL Server 6.5, codenamed Hydra
• 1999 – SQL Server 7.0, codenamed Sphinx
• 1999 – SQL Server 7.0 OLAP, codenamed Plato
• 2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
• 2003 – SQL Server 2000 64-bit, codenamed Liberty
• 2005 – SQL Server 2005, codenamed Yukon (version 9.0)
• 2008 – SQL Server 2008, codenamed Katmai (version 10.0)
• 2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
• Next – SQL Server 2011, Codenamed Denali

I did finally find a blog with top 9 features in Denali, you can read the whole blog post here but these features did give me some hope

3. Juneau

Juneau is the code name for the new SQL Server Development Tools. Its purpose is to provide a single development environment for all database related project types including bringing BIDS (Business Intelligence Development Studio) and SSMS (SQL Server Management Studio) into the same IDE.

4. Apollo

Related Articles

• Microsoft Previews ‘Denali’ SQL Server Product
• Microsoft Releases SQL Server ‘Denali’ Preview

Apollo is the codename for Column-based Query Accelerator. I have to admit that this is the feature I’m most excited about. I watched some impressive demos showing how much performance gain is realized on very large result set queries. Microsoft claims an approximate 10 times increase in query performance.

Columnstore Indexes are what is set up to provide for this increase in query performance. Essentially, instead of storing rows on pages, columns are stored individually on a set of pages. Algorithms can then be used to compress the redundancy out of the column. Thewhitepaper on the new Columnstore Index points out the following benefits:

• only the columns needed to solve a query are fetched from disk (this is often fewer than 15% of the columns in a typical fact table),
• it’s easier to compress the data due to the redundancy of data within a column, and
• buffer hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, while infrequently used parts are paged out.

5. Crescent

This cool new tool aimed at the business user is actually still yet to come. Project “Crescent” furthers Business Intelligence for everyone by providing a powerful and speedy data visualization tool in the browser. It has a feature called storyboarding which allows the user to create multiple snapshots of the data in order to tell a story about that data. Once the snapshots are created, the storyboard can be exported to PowerPoint where the data is displayed live inside the PowerPoint slides. The demos of this product are a must-see. Below are two links. The last is the keynote from Day 1 of the PASS Summit (by Ted Kummert, Microsoft Sr. Vice President, Business Platform Division) and contains a lot more than just Project Crescent.

Data Visualization Done Right: Project Crescent

PASS Summit 2010 Day One Live Streaming Keynote

so at the end of the article I am starting to feel a bit more hopeful but because I haven’t heard anything about the release of a new version of SQL server then it does dampen my enthusiasm a bit.  Please Microsoft help ease the pain of many CRM developers by improving the reporting in CRM 2011.

CRM 2011 – How to get the Last Login Date and Time of a CRM Instance

today I found myself wondering if I check when a CRM instance was last used.  We have a bunch of CRM instances and I was wondering when or if they were being used.

I was a bit dissappointed not to find anything within CRM 2011 but then I found this article which had a piece of SQL code which could do the trick.  I guess this would probably work for CRM 4.

You need to run the query against the MSCRM_CONFIG database.   When I ran the query it returned null for the user name but it did return the time .  You will have to join a few more tables to get the user name.

In Microsoft Dynamics CRM 2011 you can get the last login time of a CRM user by using the below query:

SELECT

AuthInfo, O.FriendlyName, SUO.LastAccessTime

FROM

SystemUserOrganizations SUO

LEFT JOIN SystemUserAuthentication SUA ON SUO.UserId = SUA.UserId AND LEFT(AuthInfo, 1)=’C’

LEFT JOIN Organization O ON SUO.OrganizationId=O.Id

WHERE

LastAccessTime IS NOT NULL

ORDER BY SUO.LastAccessTime DESC

The query outputs the systemuser name, the organisation name and the date and time of the last login:

username@exaple.com

OrganisationName

2011-03-23 16:36:13.457

You can finetune the query to your own wishes.

How to Copy SQL Server Agent Jobs between Servers

Today I have to copy some SQL Server Agent Jobs from the Dev database to the test database.  Hmmm, I have got a lot of experience with SQL server agent jobs

I right clicked on the jobs and saw they were treated like sql stored procedure.  When you right clicked the job you can then choose Script Job as and then save CREATE To and then you can choose to either save it to a File and or New Query Editor Window.

So basically I saved it to a new query window, I then opened a query window on the Test database, ran the query and whammo, I had copied the job across.

I would like to add a word of warning, in my instance a lot of variables were still pointing at the old server, file directories and connections.  So you will have to be careful here, especially if you are doing this from test to live but of course you lto know that already.

if you want step by step instructions then I found this good article below which you can find here.  Although the article uses SQL Server 2005, it’s the same process I used on SQL server 2008

How To Copy SQL Server 2005 Jobs From One Server To Another

This example shows how to copy a scheduled backup job from one instance of SQL server 2005 to another.

In Microsoft SQL Server Management Studio, expand the SQL Server Agent tree.

Expand the jobs tree.

Here you will see a list of all the jobs currently stored on the server.

Right-click on the job that you want to copy over to a second server.
Select Script Job as –> Create To –> New Query Editor Window.

This will open a new window in the management studio containing auto-generated code.

Connect to the Second server, which will be the destination for the copied job.

Open up a new tab in the management studio – Copy the entire auto-generated code from the previous step and paste it into the new tab.

Below is the auto-generated code ready to execute on the new server.
Execute the whole code to continue.

Refresh and expand the Jobs tab on the second server, all being well the copied job will be displayed in the job list.

Right-click the job and select Properties to view the details.

You will see that the steps, schedules, alerts, notifications etc. will have been copied across. The job will now run on server 2 in the same way as it ran on server 1.