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.

Test Scripts

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 1^st 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 = "https://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

do {

   $newItem = $list.Items.Add()

   $newItem["Title"] = "AutoItem " + $I

   $newItem.Update()

   $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

$list= $web.Lists["List"]

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.

SQL Server

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

SharePoint Server

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

The Results

All results are in seconds elapsed taken from the PowerShell output.

Test	Standalone	AlwaysOn
Create site collection	30.9	52.3
Insert items	28	56
Read items	0.84	0.85

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.

Performance Conclusions

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.

Cheers,

Sam

Comments

• Anonymous
  August 20, 2014
  Have you measured performance using asynch on the content databases, instead of synch?

• Anonymous
  August 20, 2014
  Any chance you can re-run with the content databases in async mode?  I'm curious what the results would be.

• Anonymous
  August 20, 2014
  Yeah, I'll be doing that soon. Keep checking back :)

• Anonymous
  August 21, 2014
  Here it is - blogs.msdn.com/.../sql-server-alwayson-asynchronous-vs-synchronous-performance-for-sharepoint.aspx

• Anonymous
  August 31, 2014
  Great post! Was wondering if the full script (including time measurement steps) could be made available?

• Anonymous
  September 01, 2014
  Yep, it's pretty easy: $sw = [system.diagnostics.stopwatch]::startNew()

Do stuff

$sw.Stop() $sw.Elapsed #Shows all sorts of good time elapsed info

• Anonymous
  September 07, 2014
  Thank you!

• Anonymous
  September 08, 2014
  The comment has been removed