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).
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)