Parallelism – Set it to 1 or increase your page load speeds by 4-5 seconds

SQL Parallelism is one of those settings that Microsoft recommend you implement. To be honest, up until this point I have always asked for it, but never really known what the impact was of not setting this property… until today.

I’m working with a client at the moment that outsources the management of their SQL servers to a third party (which means we don’t have easy access to all the normal tools you use to troubleshoot SQL speed & performance issues, like Query analyser or Performance Monitor, etc.). I’ve worked with too many in-house IT teams (who are normally under enormous time constraints) so when I asked for something to be changed, I ask them to send a screenshot back to me with the changed settings (This allows me to verify they’ve done it, and the image goes into the “Build and Config” docs that we give clients).

Anyway, new site soft-launched on Wednesday, but as we were doing final UAT we noticed that page loads were anywhere between 6 seconds and 10 seconds. We tried a few things – looked for query timeouts in NetMon, Had a look at the page loads in Fiddler to see what was slow, used ProcMon to see if access to a non-critical file was being rejected, etc. When we activated the Dev Dashboard in SharePoint, we noticed that the main SQL queries were returning in 700-1100 milliseconds – and most pages had between 4 and 8 queries on them (Standard SharePoint pages). This was far larger than what you’d normally expect, so we asked the guys in IT to follow it up with the external vendor. When they asked the third party to investigate the SQL performance issues we were seeing, they eventually came back and said that they forgot to set up MAXDOP to 1. Once they had done so, we saw heavy page loads drop from 10 seconds to 1.1 seconds, with most pages coming back in the sub-second timeframes.

The symptoms we were seeing were:

  • It took forever to create a new site collection (2-3 minutes)
  • Pages seemed to “Hang” when a link was initially clicked, and then they’d suddenly come back to life
  • Fiddler showed the delay was in the initial page request – after that, the assets and external files loaded at normal speed.
  • Starting up the site after an IISRESET took about 2 minutes (which we originally thought was the CRL issue, where Signed ASP.NET code goes to crl.microsoft.com and checks for revoked certificates on initial compile)

Credit goes to Michael Carey from Di Data for spotting the slow SQL load times in the Dev Dashboard (I got him to show me how to switch it on for next time – not being a dev, it was not a tool I’d used before).

Using STSADM…

Switch it on :
STSADM –o setproperty –pn developer-dashboard –pv On
Switch it off:
STSADM –o setproperty –pn developer-dashboard –pv Off
Enable and Hide it, so that it only appears when you click the icon (top right, near the user’s name):
STSADM –o setproperty –pn developer-dashboard –pv OnDemand
Display the dashboard when an expensive operation occurs
STSADM –o setproperty –pn developer-dashboard –pv expensiveoperationsonly

More good info about the Dev dashboard (and how to secure it using Powershell for use by people with Site Collection Admin permissions, for example) can be found here: http://blogs.msdn.com/b/russmax/archive/2010/02/10/sharepoint-2010-logging-improvements-part-2-introducing-developer-dashboard.aspx

Pros: It only adds about 10ms to the load time for a heavy page – and most of this time is spent writing extra ULS log entries, more than anything… So there’s little impact in switching it on (particularly using the OnDemand switch).

Cons: It cannot be used to check Sandboxed solutions (Really, if you wanted to know the “cost” of a feature it would be for the sandbox)… and it is a Farm-wide setting (so you can’t activate it on a poorly performing site, for example – it’ll appear on the entire farm)

B

Advertisements

About Brad Saide

I'm a SharePoint consultant. I'm also slowly going bald, seem to have a permanent spare tyre around my waist and enjoy socialising with friends over a beer or 10. The last 2 may possibly be related. Started working with SharePoint when the first version was in limited beta release (participated in the Technology Adoption Program while at Woolworths) and have been committed to the adoption of the technology as a business enabler ever since.
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to Parallelism – Set it to 1 or increase your page load speeds by 4-5 seconds

  1. Eric Xue says:

    Hey Brad,

    Thanks for sharing and your stuff is top-notch as always.

    A quick question here though. In terms of ‘set up MAXDOP to 1, does it mean that it is only supported by one processor? Based upon my experience, it should set MAXDOP value up to how many processors available on that SQL server box, in order to maximize the performance.

    Therefore, if it’s ‘1’, it only leverages one process, so not quite sure about whether it’s being paralleled or not.

    P.S. this is a KB article about MAXDOP setting http://support.microsoft.com/kb/329204

    I might be totally wrong, but just keen to find out about it. 😉

    All best

    Eric

    • Brad Saide says:

      Thanks for the reminder Eric – I forgot to put the link in from Microsoft with the guidance – http://technet.microsoft.com/en-us/library/cc298801.aspx#Section6 – I think the reasoning behind this is that every page load results in a number of small quick queries against the database server – as such, the server actually acquires an overhead (and a fairly significant one) when it tries to “divide and conquer” the queries and is much more efficient handling them on one CPU.
      For Long-running queries it makes a lot more sense to spread them over multiple CPUs to get more value from them – this is another reason why SharePoint should always be on its own instance, so the settings most suitable for SharePoint do not affect other applications.

  2. Eric says:

    Nice reference link, Brad.

    To ensure optimal performance, we strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances that host SharePoint Server 2010 databases. For more information about how to set max degree of parallelism, see max degree of parallelism Option (http://go.microsoft.com/fwlink/?LinkId=189030).

    This is the perfect answer to what I was looking for!

    Btw, for some reason, I don’t receive any comment follow-up email even though I intentionally tick that option below.

    Anyway, thanks for sharing all that and your blog is my KB repository now!

    all best

    Eric

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s