Query hangs with Azure SQL Database

[jonmozley]

Type: Bug As mentioned in #7440, I seem to have the issue of queries hanging against Azure SQL Databases. No pattern to what type of query causes it. Sometimes happens just when trying to expand Object Explorer.

Cancelling the query or disconnecting the server have no effect. Restarting ADS works, or disconnecting the WiFi which seems to make it realise the connection has failed. If left for an arbitrary amount of time, the query will eventually complete (sometimes 2 minutes, today 16 minutes). No issues when connecting to on prem SQL Server as far as I can recall.

FWIW, I don't remember this being an issue until the last couple of months when our client laptop changed the VPN provider.

I was capturing verbose MSSQL logs during this most recent instance of the issue, see mssql logs.zip.

• Connected to an Azure SQL Database since this morning
• Trivial query of small table A started around 14:28 was hanging
  • ADS remained responsive for this Azure SQL Database while waiting for query
  • Object explorer working fine as of 14:33
  • Another trivial query of table B completed instantly around 14:34
  • No blocking showing when running sp_who2 (and a colleague could query table A without any issues)
• Query completed after approximately 16 mins.
  • I can see this first appear in the logs at 14:44 ("select * from UN_UNIT") so does that mean the query wasn't even sent until that point?
  • Just before this, I can see a "connection broken" in the log, so presumably whatever my query was waiting on finally failed
• Running the same trivial query of table A at 14:45 completed instantly

I will attempt to get the latest Azure Data Studio shortly although ironically this is more difficult since the VPN change that I mentioned!

Azure Data Studio version: azuredatastudio 1.46.1 (ba29842b81dec01177415e53948ca2168e69c3f8, 2023-10-02T18:14:22.887Z) OS version: Windows_NT x64 10.0.19045 Restricted Mode: No Preview Features: Enabled Modes:

System Info

|Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i7-8650U CPU @ 1.90GHz (8 x 2112)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: disabled_off
direct_rendering_display_compositor: disabled_off_ok
gpu_compositing: enabled
multiple_raster_threads: enabled_on
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled
webgpu: enabled| |Load (avg)|undefined| |Memory (System)|15.88GB (2.16GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|

Extensions: none

[cheenamalhotra]

Thanks for the logs! I see a few instances of below error:

SqlError.ctor | ERR | Info Number 10054, Error State 0, Error Class 20, Error Message 'A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)', Procedure 'None', Line Number 0

Why it happens is another thing, but even if it does, either the query execution should fail with that error, or we could retry making connection.

[jonmozley]

Thanks for the prompt response! Confirming the same issue exists after upgrading to 1.47.

Similar results today (hung for about 16 mins). I can't see the exact message about logins that you highlighted yesterday but still seeing An existing connection was forcibly closed by the remote host just before my query actually starts.

sqltools2.zip

Let me know if you need any more info.

[jkmuka]

I have had this issue for a while not and has become worse with 1.47.0. Simple queries will hang for a while and while waiting I can literally launch a new instance of SSMS, connect to the target server, and run the same SQL and it will complete execution all before the data studio command has even responded. Unfortunately, I will need to switch back to SSMS due to the amount of time this issue is has added to my workflow.

[GridTrekkor]

Confirming that the problem remains in 1.47 -- I've had to close and reopen about a dozen times today.

[aborremans]

I am also reproducing this problem in 1.47

[jonmozley]

New related error spotted today: I started a query, tried to cancel it but failed to cancel as previously seen, closed the tab then opened another tab, got this:

Execution failed due to an unexpected error:

            A query is already in progress for this editor session. Please cancel this query or wait for its completion.

[jonmozley]

Further update: I've switched back to SSMS (19.3) for now, but even that is encountering similar issues. Rather than a query timer ticking up, the whole window freezes, and has a similar timeout in giving up on the query.

I appreciate SSMS and Data Studio are very different beasts but thought it was worth highlighting.

@jkmuka, have you noticed similar?

[PrejM]

I have the same issue with both SSMS and ADS. Is there a temp fix, other than downgrading the apps? A simple query such as select getdate() is freezing up. I'm using SSMS v19.0.2 and ADS v1.47.1 on Windows Version 10.0.19045.3930

[chicaneau]

This issue plagues my life, has anyone discovered a workaround? does disabling a particular feature help? EDIT: I have disabled Intellisense, which was suggested on another thread, and I haven't had a query hang in a few days! I have a feeling ADS hammers Azure Synapse with too many queries for intellisense and it eventually blocks that session. Just a theory.