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

do {

   $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

$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 Smile.

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.




Create site collection



Insert items



Read items




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.



Comments (8)

  1. good timing, just setting one up now! says:

    Have you measured performance using asynch on the content databases, instead of synch?

  2. GrandmasterPhil says:

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

  3. Yeah, I'll be doing that soon. Keep checking back 🙂

  4. Neville says:

    Great post! Was wondering if the full script (including time measurement steps) could be made available?

  5. Yep, it's pretty easy:

    $sw = [system.diagnostics.stopwatch]::startNew()

    # Do stuff


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

  6. Neville says:

    Thank you!

  7. Neville says:

    My initial testing has also shown such similar overhead for write transactions when synchronous availability group is used. This suggests that the overhead in write time is the trade-off for the automatic failover capability. Given that mirroring is being deprecated there doesn't seem to be any alternative for high availability that does not have an overhead like that of automatic failover.