Blog

In theory, you can add indexes online with SQL Server Enterprise Edition.

In theory, with AlwaysOn Availability Groups, you can add and drop indexes on the primary replica whenever you want.

In theory, you can perform read-only queries on the secondaries whenever you want, and nobody gets blocked.

In practice, these things don’t always add up to the same answer.

I’ve got an AlwaysOn Availability Group demo lab with SQL Server 2014 hosting the AdventureWorks2012 database. I’ll start on my secondary server, and I’ll get all of the suffixes from the Person.Person table:

Clustered index scan on Person.Person

Clustered index scan on Person.Person

The query results aren’t important – instead, I’m showing the actual execution plan, which is very important. By default, there’s no index on the Suffix field, so I get a clustered index scan.

Say that for some bizarre reason, this is the type of query my end users constantly run, so I decide to switch over to the primary to add an index on Suffix. (As a reminder, you have to add indexes on the primary, not the secondaries.) After adding the index, I run the query again:

ag-demo-step-4

Index scan on our newly created index

Woohoo! We’ve now got an index scan on my new index. (Yeah, it’s not a seek, but this is a crappy query, and a scan is the best I’m going to get.) So far, so good.

But now let’s mix things up a little – let’s run that same query, but start a transaction with it:

Starting a transaction and running a SELECT

Starting a transaction and running a SELECT

Now I have an open transaction, which really shouldn’t mean anything because you can’t do updates on the secondary. However, switch over to the primary, and drop that newly created index. Back over on the secondary, where we’ve got that open transaction, run the exact same query again:

Boom

Boom

Our transaction has been knocked out and failed. It’s funny to think of a read-only transaction as “failing”, but if you have a stored procedure that starts things off with BEGIN TRAN and then does a lot of reads, thinking it’s going to get a complete point-in-time picture of the data, that’s not going to work.

I know what you’re thinking: “But Brent, I don’t even use snapshot isolation, because I read Kendra’s post about it, and I know we’re not ready to test our app with it yet.” Thing is, SQL Server uses RCSI behind the scenes on all AlwaysOn Availability Group replicas – and it’s lying to you about whether or not the feature is enabled. Here’s the sys.databases view for my secondary replica:

Trust no one.

Trust no one.

SQL Server claims that for the AdventureWorks2012 database, both snapshot isolation and read committed snapshot are off – but it’s lying to you. Read Books Online about querying the replicas and note:

Read-only workloads use row versioning to remove blocking contention on the secondary databases. All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.

They’re not kidding – even if I specify dirty reads, I can run into the same problem. Here, I start a transaction, but I’ve asked for the dirtiest, no-lockin-est reads I can get:

Riding dirty

Riding dirty

But when I add or drop a related index on the primary, even WITH (NOLOCK) transactions are affected:

Wow, Microsoft's getting much more casual with the error messages lately.

Wow, Microsoft’s getting much more casual with the error messages lately.

This starts to point to the challenge of running complex reporting jobs off the secondary replicas. SQL Server is constantly changing the data underneath your queries, and most of the time, it cooperates beautifully. However, if you have a long-running stored procedure (like minutes or hours long), and you want a point-in-time picture of the underlying data while you generate reports on the replica, you don’t want to play around with transactions. Instead, a database snapshot might be a better fit.

If you’d like to run these demos on your own lab, download the scripts here.

↑ Back to top
  1. This actually makes me happy for a different reason. Coming into an environment where NOLOCK was declared as standard and required on *everything*, and recently migrating it to AGs, it’s comforting to know that AGs are completely ignoring them the way I thought they were but hadn’t had a chance to prove to myself whether NOLOCK is ignored in a snapshot isolation setting.

  2. Great post – I did the test today in our lab environment as we hit this very issue in production last week whereby an analyst reported this error while running a SELECT statement on an AlwaysOn Secondary.

    The table he was selecting from was having an UPDATE STATISTICS run on the Primary but I cannot see any record of Indexes being updated .

    Would an UPDATE STATISTICS on an Index or Column that was being referenced from the secondary also cause this error?

    Thanks for your help!

    • Jack – if you’ve got the AlwaysOn environment up and running already, why not run a test to find out? Use the same repro code shown here, but do it with an update statistics instead. It would take less time than writing this comment. ;-)

      • Ha – yes you are correct – of course I thought of this after I wrote it – if there was a recall button I would have pressed it :)

        So I did the test and was not able to recreate the issue when replacing the DROP INDEX with UPDATE STATS.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php