SharePoint & SQL Server AlwaysOn vs Standalone Performance
How to setup SharePoint with SQL Server AlwaysOn has been covered nicely now, but I’ve not covered the performance hit setting up such a system will incur. The short version is: updates are about x2 slower than standalone for a x2 node AlwaysOn cluster; reading data is about the same performance (which would make sense).
Update: a comparison on synchronous/asynchronous commit modes is available here. In short, async is nearly as fast as standalone if used, although there are downsides.
For now we’ll benchmark just synchronous-commit AlwaysOn as that’s the safest yet slowest way of operating a SQL Server AlwaysOn cluster for SharePoint, even though some (most) databases support asynchronous commits.
This isn’t going to be the be-all-and-end-all of experiments, just to give an idea of the performance gap when implementing AlwaysOn with SharePoint. Each test is measured with a System.Diagnostics.Stopwatch and were run several times to get an average, discounting the 1st run each time to make sure caches were warmed up etc. Here are said tests + scripts:
Create team-site site-collection
Simple new site-collection + feature activation.
$siteURL = "http://sp15/sites/perftest"
$template = Get-SPWebTemplate "STS#0"
New-SPSite -Url $siteURL -OwnerAlias "sfb-testnet\root" -Template $template
Create custom list and insert 1000 items
While loop to insert one-by-one a bunch of simple items. 1000 is enough to highlight the performance difference.
$web = Get-SPWeb $siteURL
$listTemplate = $web.ListTemplates["Custom List"]
$list = $web.Lists.Add("List", "Test list", $listTemplate)
$i = 1
$newItem = $list.Items.Add()
$newItem["Title"] = "AutoItem " + $I
while ($i -le 1000)
Read 4,999 items
It’s 4,999 because that’s one less than the maximum that the query throttle will allow (by default).
$web = Get-SPWeb $siteURL
Write-Host ($list.GetItems()).Count "items read from list."
Test Server Hardware & Setup
Nothing special really. Hosted all on the same Hyper-V machine with 24 cores so plenty of CPU muscle to handle any background noise. All virtual machines use real, non-shared nor dynamic memory.
- 4 CPUs, 4GB RAM. AlwaysOn cluster of x2 machines on the same subnet; single instance on its own, on the same subnet.
- Nothing fancy about the disk setup in either the standalone or AlwaysOn servers – data on OS disk to make it equally terrible a setup in both instances .
- 4 CPUs, 8GB RAM. Also on the same subnet as the SQL boxes for lowest latency.
- Just with the WFE roles installed – no search, UPA, AppFabric or anything else on each farm to avoid extra SQL traffic that’s not related to our PowerShell scripts.
All results are in seconds elapsed taken from the PowerShell output.
Create site collection
Reading item performance is pretty much identical on both setups. Here’s that data in graphical format:
The slowdowns pretty much only happen for write operations.
It’s pretty clear from this that writing suffers a lot more of a performance hit with AlwaysOn than reading. That makes sense given there’s no synchronous blocking for read – it’ll come from a SQL node without bothering the others.
Writing data on the other hand shows a near 100% performance decrease with synchronous AlwaysOn writes enabled. This should improve with asynchronous writes of course but that it for another day.