Timeline for SqlConnection.Open vs SqlConnection.OpenAsync - what's different between the two beyond the obvious?
Current License: CC BY-SA 3.0
35 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 11, 2023 at 9:10 | answer | added | Alex from Jitbit | timeline score: 0 | |
| Feb 22, 2023 at 17:48 | comment | added | Triynko | Bad info here. Even in .NET 4.7.2, the underlying OpenAsync implementation is synchronous and thread blocking; I've looked at the decompiled code. This can be demonstrated by attempting to open 100+ simultaneous connections and watching them unable to get a pool thread to complete on, because it's blocking the ones it has. Setting MinThreadCount to something like 200 solves the problem and allows 100+ connections to be established instantly, whereas without setting MInThreadCount, they all start to fail with timeouts. For example: stackoverflow.com/a/57337499/88409 | |
| Jan 24, 2022 at 6:04 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Dec 23, 2021 at 11:33 | answer | added | developer65 | timeline score: 4 | |
| Nov 17, 2016 at 23:06 | comment | added | ccook | @usr thanks again. So I set the TP min and max to 1000 and tested to see where the timeouts occurred. With Open() it's at the max parallelism of 100 where, with OpenAsync() its over 200. I suspect that's due to nuances of tasks being handled? As far as performance, i think we are just seeing the improvement in using async on the connection so that it's no longer blocking. | |
| Nov 17, 2016 at 22:38 | comment | added | usr |
Parallel is dumb, it does not ramp up. The TP ramps up. You must limit Parallel to far below the TP limits. Otherwise, internal ADO.NET TP usage might be crowded out. We want to eliminate TP overloading effects. Also, you probably should set the TP min value so that there is no ramp up. It just creates threads immediately up the the min setting. Let's take the ramp out of this investigation. Also note, that the Task.Run wrapping you are doing is doubling the number of threads required. That's why I suggested 500/5000 limit values.
|
|
| Nov 17, 2016 at 22:18 | comment | added | ccook | @usr it seemed at first that setting the max/min value on the thread pool does make quite a difference, but I think it's just in the ramp up behavior of Parallel.ForEach. Oddly, OpenAsync is still able to handle more concurrent threads before timing out. With a connection pool of 100, I can only finish with a max degree of parallelism of 100 safely with Open(), with OpenAsync() I can push it into 200 without issue. | |
| Nov 17, 2016 at 21:33 | comment | added | ccook | @Igor, ill try his suggestion now. But that's exactly why I was confused, the documentation describes it as a simple wrapper, so I expect the same behavior. If it should be different in any way, I wouldnt have worried about it. | |
| Nov 17, 2016 at 21:25 | comment | added | Igor |
Very odd, I can also duplicate it. The code for OpenAsync calls through to Open so should not be a difference. Have you tried what usr suggested?
|
|
| Nov 17, 2016 at 20:40 | history | edited | ccook | CC BY-SA 3.0 |
added 175 characters in body
|
| Nov 17, 2016 at 20:38 | comment | added | ccook | @Igor, I am only changing from Open() to await OpenAsync(), nothing else. Note the code is always sync, just comparing that one change Open() vs OpenAsync() | |
| Nov 17, 2016 at 20:37 | comment | added | Igor |
In your sync version are you also changing ExecuteReaderAsync() to ExecuteReader() and ReadAsync to Read() ?
|
|
| Nov 17, 2016 at 20:32 | comment | added | ccook | @Igor, just added a second edit which reproduces the same difference with no external packages. | |
| Nov 17, 2016 at 20:31 | history | edited | ccook | CC BY-SA 3.0 |
added 2487 characters in body
|
| Nov 17, 2016 at 20:31 | comment | added | Igor |
Actually not necessary. I can fit it in the comment: conn.Open(); using (var command = new SqlCommand("waitfor delay '00:00:05'", conn)) command.ExecuteNonQuery(); And removed the entire Task.Run(... wrapper
|
|
| Nov 17, 2016 at 20:25 | comment | added | ccook | @Igor, definitely possible, trying an ADO.NET test without Dapper. | |
| Nov 17, 2016 at 20:14 | comment | added | Igor |
Is it possible there is something in Dapper that would cause this that it does with the SqlConnection instance? I changed it a little and used ado.net directly (no dapper) and got up to 250 concurrent connections with Open spinning the default 10,000 number of times. Is there anything else special you are doing for the Open version or just replaced the 2 Async calls with the synchronous calls?
|
|
| Nov 17, 2016 at 20:02 | comment | added | ccook | @usr, i will give that a shot. It's worth noting though, when using OpenAsync instead of Open in our applications ORM, our load tests go from meager to being bottlenecked by the SQL server's available resources. So, whatever it is, it affects ASP.NET MVC as well, not just console applications. | |
| Nov 17, 2016 at 19:51 | comment | added | usr | The case interests me now. I do not see a good reason there should be any difference. An idea: If Open blocks internally and the thread pool is totally overloaded (here it clearly is) then blocking might take a long time because completing the task being blocked on might require TP resources for IO completion processing. Set the TP to 5000 min/max threads and set MaxDOP for Parallel to 500. The difference should disappear. | |
| Nov 17, 2016 at 19:48 | history | edited | ccook | CC BY-SA 3.0 |
added 38 characters in body
|
| Nov 17, 2016 at 19:37 | comment | added | ccook | @usr, reproduced and included in an edit | |
| Nov 17, 2016 at 19:37 | history | edited | ccook | CC BY-SA 3.0 |
added 2397 characters in body
|
| Nov 17, 2016 at 19:12 | comment | added | ccook | Sure, give me a few minutes then :) | |
| Nov 17, 2016 at 19:11 | comment | added | usr |
You could just execute waitfor delay 1 second or select * from master..spt_values. A repro of this should fit into 10-30 lines if freshly written. My guess: You won't be able to repro and discover your bug in the process.
|
|
| Nov 17, 2016 at 19:09 | comment | added | ccook | I agree, and that would be good. To share I'll need to set it up with just the nuget packages and against something like northwind - which should be just find as the test was already very minimal but dubiously proprietary. Might need a couple of hours though to sneak that in. | |
| Nov 17, 2016 at 19:06 | comment | added | usr | What you describe is very unexpected. The conn pool has little magic. Very straight forward. Likely, the benchmark was flawed. Care to post code? I have seen a lot of different issues over the years. Could be many things. | |
| Nov 17, 2016 at 19:05 | comment | added | ccook | Concrete issue, no, the issue is that we are having failures opening connections in production when under load which lead to the local benchmark and load testing. Note when I'm comparing the two mentioned above it's concurrent and asynchronous. | |
| Nov 17, 2016 at 19:02 | comment | added | ccook | Exactly what I did actually (the micro benchmark). I found that I ran out of (timeout on opening) available connections in the application pool (default size of 100) when I had about 35 concurrent async commands executing on connections executing on connections opened with Open. I would have expected it to be at 100. Changing it to use OpenAsync, nothing else, and it would maintain about 300 concurrent commands before failing to open connections. It seems like the connections themselves are async with OpenAsync, not just the opening of them. | |
| Nov 17, 2016 at 19:01 | comment | added | usr | Is there a concrete issue you are having in your application? If not that's fine but if yes that might be easier to answer. | |
| Nov 17, 2016 at 19:00 | comment | added | usr | I believe not but you should test it in a micro benchmark. Spin up some workload, pause the debugger a few times and look at the Parallel Stacks window. It is very enlightening to watch internal call stacks of libraries to understand what code has impact on throughput. Starting with .NET 4.5 you should see all threads blocked on a task/event for sync APIs and almost no threads active in the async case. | |
| Nov 17, 2016 at 18:58 | comment | added | ccook | I agree, I didnt expect any difference. Asking it a different way, would sql server and the thread pool handling the two connections differently? | |
| Nov 17, 2016 at 18:57 | comment | added | usr |
running the connection pool dry early it should not behave differently. Whether the thread blocks on IO or on an event makes little difference. executing asynchronous commands ... on those connections behaves differently This might be the case, I don't know. Asynchronous Processing=true must have had some effect in previous .NET versions. I think the wrong question is to look at Open(Async) and the right one is to look at what Asynchronous Processing=true did. But according to this it should at most have had a small CPU cost impact: stackoverflow.com/a/7852617/122718
|
|
| Nov 17, 2016 at 18:53 | comment | added | ccook | So, is the unmanaged sql connection fundamentally different? edit - i realize you are saying it's the same. | |
| Nov 17, 2016 at 18:52 | comment | added | usr | Starting with .NET 4.5 internally it's always async IO. The sync version just blocks... | |
| Nov 17, 2016 at 18:49 | history | asked | ccook | CC BY-SA 3.0 |