SE_REPL_SLOW_SECONDARY_THROTTLE Wait Type

Question

Hi,

We're using Entity Framework to talk to SQL Azure and we're running into this wait type which eventually causes our commands to timeout. Is there a way to get round it, or are we going to have to break down our transactions into smaller groups?

Regards,
Matthew

Answer 1

Hi Matthew,

Please have a look at this blog to address this type of issue: SQL Azure and Entity Framework Connection Fault Handling.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

SE_REPL* wait types is similar in concept to the IO wait types in that you writes (and possibly reads depending on your locking pattern) are being blocked because one of your secondary replicas has fallen too far behind so we need to slow things down to allow it to catch up.  Typically, SE_REPL should be fairly transient, but if you are seeing it regularly there may either be something wrong on one of your secondaries (bad drive, overloaded, etc.) that would monitoring has not detected yet.  In that scenario, you should open up a support case.

Although rarer, another scenario is if you are doing a lot of very small transactions (think inserting a single row per transaction a couple thousand times regularly), it can backup up our replication process.  We have fixed several performance bottlenecks in this specific area and are fixing even more in upcoming service updates.

Evan

Answer 3

Hi Evan,

We're writing a lot of records (over 300,000) in one massive transaction which could be the cause of our problem. We're working on getting Entity Framework to split it up into smaller groups but haven't yet been able to do so.

The transaction has usually been open for over 10 minutes before this wait type kicks in and closes our connection. I know we're not using it for how it's designed but I wondered if there was a different workaround for it.

Thanks for your answer.

Matthew

Answer 4

That wait type won't trigger the termination of the query or connection.  Are you sure your query is not timing out due to the command timeout?

Answer 5

Hi Evan,

Yes it's timing out because of the Command Timeout. We've got that set to a fairly high amount (I think) at 120 seconds. If I set that to a higher value do you think the job would eventually succeed or are we likely to get our connection "throttled" by Azure for consuming too many resources?

We're currently rewriting our import routine to make use of SqlBulkCopy so hopefully won't have this problem for long!

Regards,
Matthew

Answer 6

Be careful here - the term "throttling" explicitly refers to the termination of queries that are using too many resources.  In your case, your query fails because it hits your query timeout.  I find it a bit suprising that your query always times out due to a wait type of SE_REPL.  That wait type should be fairly transitory and generally not reproducible.  If you are indeed finding that the same query always times out due to the same wait type then something is wrong somewhere and I would recommend you open up a support case.

https://support.microsoft.com/oas/default.aspx?gprid=14919&st=1&wfxredirect=1&sd=gn

Evan

Answer 7

The the following links for details on why this is happening:

http://stackoverflow.com/questions/15788798/sql-azure-one-session-locking-entire-db-for-update-and-insert/15809731?noredirect=1#comment22495222_15809731

http://social.technet.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c3003a28-8beb-4860-85b2-03cf6d0312a8