August 31, 2015 at 11:26 am
We have an application that collects real-time data from our production database servers. Recently, we've upgraded to SQL Server 2014, setup an AlwaysOn group and using a listener read-only connections are routed to the secondary. The code uses an ID-based iteration. It grabs the TOP(n) records where the ID is greater than the last max ID (of the previous fetch). It's not ideal code, I know and I didn't write it, so keep your big boy underoos from getting in a wad. I'm not in a position to change the production server to be serializable at this time, implement hekaton or replace major chunks of the code. I need ideas to help identify the following issue:
The problem is some rows take longer to insert and they don't always commit in a sequential order. This causes the code that iterates to "skip" records (using read committed isolation). We need all records or else data won't be aggregated correctly/accurately. We did not see this in production until recently (see recent changes above) and the logic to fetch data hasn't changed in years. We thought it might be related to latency with the secondary, so we pointed the data consumer at the primary. The results were the same, however a lot less frequent.
What I'm trying to determine is the cause. Is it some kind of wait? I have a hunch it's related to Network I/O, but how can I be sure?
Thanks in advance,
SQLStacey
There is no spoon. And there is no default sort order in SQL either.
August 31, 2015 at 11:39 am
Your transaction isolation level might be the issue here. When reading from an Availability Group secondary, you will be using SNAPSHOT isolation. Nothing you do can change this fact. Query hints, isolation level settings, etc. are overridden by the AG's need to maintain consistency while continuing to process the data replication underneath.
Likely you were using READ COMMITTED or some other isolation level before adding the AG and now see increased issues because of SNAPSHOT.
Derik Hammer
@SQLHammer
www.sqlhammer.com
August 31, 2015 at 11:51 am
Did you mean to say READ UNCOMMITTED? Read Committed would have the same issue as Snapshot.
August 31, 2015 at 12:57 pm
No, read committed. We did not see this problem or saw it very rarely before recent changes.
There is no spoon. And there is no default sort order in SQL either.
August 31, 2015 at 1:53 pm
Transactions commit on the secondary in the exact same order as the primary. If you were using read committed isolation, it would still suffer from gaps due to transactions completing in different lengths of time. The gaps may be more pronounced on the secondary due to latency, but definitely not due to the transaction isolation level being snapshot. That would only make sense if they were using nolock/read uncommitted on the primary.
The problem would exist on the primary as well in that case. It just wouldn't be as noticeable.
September 3, 2015 at 3:55 pm
SOLUTION: So without making major changes to the application or framework, we were able to tweak the query that fetches the data to filter out any records created in the last 5 seconds. Not an ideal fix (my vote was hekaton), but it's the one that worked for our needs right now. The amount of seconds can be tweaked again if network latency increases/decreases. If using a stored procedure, the second value could be parameterized so the schema doesn't have to change when latency changes.
SELECT TOP(n)
FROM dbo.bla
WHERE CreateDt <= DATEADD(SECOND, -5, GETUTCDATE())
There is no spoon. And there is no default sort order in SQL either.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply