Query Hangs

[mackenziepsilb]

Issue Type: Bug

Sometime when running queries it just sits there saying executing query and doesn't finish. I have to quit Azure Data Studio, reconnect to the SQL Server and then run the query and it works.

Azure Data Studio version: Azure Data Studio 1.11.0 (460c739a8d90bdbbde39e01abfb8ecdc278ea787, 2019-09-06T17:14:33.682Z) OS version: Darwin x64 18.7.0

System Info

Item	Value
CPUs	Intel(R) Core(TM) i7-6820HQ CPU @ 2.70GHz (8 x 2700)
GPU Status	2d_canvas: enabled flash_3d: enabled flash_stage3d: enabled flash_stage3d_baseline: enabled gpu_compositing: enabled multiple_raster_threads: enabled_on native_gpu_memory_buffers: enabled oop_rasterization: disabled_off protected_video_decode: unavailable_off rasterization: enabled skia_deferred_display_list: disabled_off skia_renderer: disabled_off surface_synchronization: enabled_on video_decode: enabled viz_display_compositor: disabled_off webgl: enabled webgl2: enabled
Load (avg)	2, 2, 2
Memory (System)	16.00GB (0.47GB free)
Process Argv
Screen Reader	no
VM	0%

Extensions: none

[dylan-azucena]

This happens to me all the time (daily). Especially right after connecting to SQL Server and attempting to switch databases. Only thing that works is restarting ADS.

I'm on ADS version: 1.12.0 Darwin x64 18.7.0 (macOS Mojave 10.14.6)

[dylan-azucena]

I want to add a little more information to my particular case. If I open a separate window and connect to the same server where my query is hanging, running sp_whoisactive reveals a session with program name azdata-languageService (and login is my db login) that is running this query:

SELECT
u.name AS [Name],
u.principal_id AS [ID],
CAST(CASE WHEN u.principal_id < 5 OR u.principal_id = 16382 OR u.principal_id = 16383 THEN 1 ELSE 0 END AS bit) AS [IsSystemObject],
CASE WHEN N'U' = u.type THEN 0 WHEN N'G' = u.type THEN 1 WHEN N'S' = u.type THEN 2 WHEN N'C' = u.type THEN 3 WHEN N'K' = u.type THEN 4 WHEN N'E' = u.type THEN 5 WHEN N'X' = u.type THEN 6 END AS [LoginType],
CAST(CASE dp.state WHEN N'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess],
u.sid AS [Sid],
u.create_date AS [CreateDate],
u.modify_date AS [DateLastModified],
ISNULL(u.default_schema_name,N'') AS [DefaultSchema],
ISNULL(ak.name,N'') AS [AsymmetricKey],
ISNULL(cert.name,N'') AS [Certificate],
ISNULL(suser_sname(u.sid),N'') AS [Login],
ISNULL(u.default_language_lcid, -1) AS [DefaultLanguageLcid],
ISNULL(u.default_language_name,N'') AS [DefaultLanguageName],
u.authentication_type AS [AuthenticationType],

        CASE
        WHEN N'C' = u.type THEN 1
        WHEN N'K' = u.type THEN 2        
        WHEN N'S' = u.type AND SUSER_SNAME(u.sid) is null AND u.authentication_type != 2 THEN 3
        ELSE 0 END
       AS [UserType]
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id and dp.type = @_msparam_0
LEFT OUTER JOIN sys.asymmetric_keys AS ak ON ak.sid = u.sid
LEFT OUTER JOIN sys.certificates AS cert ON cert.sid = u.sid
WHERE
(u.type in ('U', 'S', 'G', 'C', 'K' ,'E', 'X'))and(u.name=@_msparam_1)

If I killl that session, my original query in the other window starts running right away.

[Charles-Gagnon]

@dylan-azucena If you run that query yourself does it hang? (you'll need to replace the @ params with appropriate values)

That query shouldn't be blocking for any noticeable amount of time - although if something else is holding a lock one one of those tables that explain it. Do you see any other queries running that might be doing that?

[dylan-azucena]

@Charles-Gagnon I took a look at the query plan for that query to get the param values. The query runs very quickly for me from a separate window with the same params. It seems to hang even when there are no other queries running on the instance, so nothing else could be holding locks. I also checked for blocking info on the session, and there is none.

What's odd is that, when my original window is hanging, the above query appears to run over and over with the same exact parameters.

I also see a few errors in the console in Dev Tools:

Unhandled Promise rejection: Canceled ; Zone: <root> ; Task: Promise.then ; Value: Canceled: Canceled
    at Object.t.canceled (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:127:183)
    at cancel (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:236:737)
    at t.Client.requestEvent (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:1407:542)
    at Object.listen (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:1406:856)
    at Object.listen (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:522:290)
    at t.SearchChannelClient.fileSearch (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:4822:579)
    at file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:6869:51
    at ZoneDelegate.invoke (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:388:26)
    at Zone.run (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:138:43)
    at /Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:872:34
    at ZoneDelegate.invokeTask (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:421:31)
    at Zone.runTask (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:188:47)
    at drainMicroTaskQueue (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:595:35) Canceled: Canceled
    at Object.t.canceled (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:127:183)
    at cancel (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:236:737)
    at t.Client.requestEvent (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:1407:542)
    at Object.listen (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:1406:856)
    at Object.listen (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:522:290)
    at t.SearchChannelClient.fileSearch (file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:4822:579)
    at file:///Applications/Azure Data Studio.app/Contents/Resources/app/out/vs/workbench/workbench.desktop.main.js:6869:51
    at ZoneDelegate.invoke (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:388:26)
    at Zone.run (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:138:43)
    at /Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:872:34
    at ZoneDelegate.invokeTask (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:421:31)
    at Zone.runTask (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:188:47)
    at drainMicroTaskQueue (/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:595:35)
console.<computed> @ /Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:2280

and

/Applications/Azure Data Studio.app/Contents/Resources/app/node_modules.asar/zone.js/dist/zone-node.js:2280   ERR No application in the Launch Services database matches the input criteria.: Error: No application in the Launch Services database matches the input criteria.

But I'm not sure whether those errors are related to this behavior.

[lberrocal]

I seem to be having a similar issue. Seemingly at random whenever I execute a query it would get stuck at "Executing query..." and the timer at 00:00:00.

The editor is still responsive while this is happening and It doesn't seem to be related to the type of query since I've run into scenarios where I run the query fine on the first time, try to run it again and get the same issue. When I try to execute the query in another editor (such as SQL Server Management Studio) this issue does not happen.

[Kaleb8812]

I have the same issue. It's extremely frustrating needing to restart the entire Data Studio sometimes hourly, PLEASE HELP!

Other info:

• The cancel button on the specific query window is active to click, but nothing happens when I click it; and it stays active
• Also, I've left the the whole program running on a VM, and the "Executing query..." time will run for longer than 99 hours (longer than the timer can count up to)

[sqlruss]

I'm encountering the issue as well. If I execute sp_whoisactive from SSMS, I see my Azure Data Studio session hung with "azdata-languageservice" as the program_name, LCK_M_S as the Wait, and another user's SSMS query session as the blocking SPID. Not sure that it is relevant, but the other user is executing an OPENQUERY statement.

[jakubsemerak]

This is extremely annoying bug. I have started using Management Studio again, because azure data studio is simply unusable.

[lucaslucenagithub]

April 30 and here I am with the same problem, I noticed that happens more often when I leave ads idle for considered period of time. If i execute query after other it works fine... I recently migrate from Windows to Linux, if anyone can suggest me a better way of management sql server, I would really apreciate the help!

[dylan-azucena]

Still happening for me too. Sometimes I will run a query and ADS will hang for minutes before actually starting the query, which then might only take a few seconds or less to run. Sometimes restarting ADS works, other times it doesn't.

[haydnlj]

Having the same issue - frequently ADS seems to hang for minutes, with the elapsed time sitting at 00:00:00. Have not been able to capture this via profiler to confirm if the same azdata-languageService is causing the block. I can confirm that I am accessing the database over WAN via VPN if that adds any context.

[pnhughes]

I'm experiencing this problem as well. Seems to occur if I run a query that raises an error. After correcting the code and rerunning the segment, I get the Executing hang immediately. My elapsed time keeps counting. Disconnecting from the database causes the elapsed time to stop but if I reconnect to the same database, the elapsed time resumes counting from where it left off. Clicking the Cancel button does nothing, only restarting ADS resolves it.

I have checked connection activity when this issue occurs to determine whether or not my query is somehow still actually processing. I have observed that all of my ADS connections are sleeping when checking with sp_who2 even though ADS reports that the query is still executing.

This is ADS 1.19.0, Windows 10 64-bit running against a local instance of SQL Server 2019 Express. The query in question is one which drops and creates a stored procedure, nothing major.

[ipatalas]

I'm experiencing the same issue almost daily. I really love ADS but still have to fall back to SSMS. The only thing that helps is to restart ADS :( Funny thing is that even a query as simple as that fails: Left it running for few days...

My specs: Version: 1.19.0 (user setup) Commit: 4095037f2578c23033867e611e82c13de114ca5a Date: 2020-06-11T22:06:05.429Z VS Code: 1.46.0 Electron: 7.2.4 Chrome: 78.0.3904.130 Node.js: 12.8.1 V8: 7.8.279.23-electron.0 OS: Windows_NT x64 10.0.17763

I can help troubleshooting that but need advice on what to check when it happens.

[molegris]

Hi, new to github. Can we vote for this bug to be fixed? Cause I have to same issue, happens 1-2 per day. And it's very annoying.

As @ipatalas said, once it starts doing that even a Select 1 will hang.
If I may add to the use case, once the problem started to appear I'm still able to run queries ONLY IF I do it File>NewQuery. Then whatever query is in there will run perfectly the first time but will automatically hang on the second attempt.

I don't know if this is actually related to issue #9170 (languageService and intellisense related issue) but the behavior is really similar to what @romanovmv describe is this post: https://github.com/microsoft/azuredatastudio/issues/9170#issuecomment-593084461

[aharpervc]

I was disappointed to see that this wasn't addressed in this month's release 😢

e: vvv this also happens frequently on Windows 😢

[winzig]

I've been tracking this issue here as it's referenced in many other tickets as well: https://github.com/microsoft/azuredatastudio/issues/8867

The underlying issue, according to MSFT folks, is that the macOS version of the SQL Client driver does not have connection resiliency. Or at least, it does not work properly. The last comment on my ticket provides a bit more detail referencing where the problem might lie if anyone has time to dig into it:

https://github.com/microsoft/azuredatastudio/issues/8867#issuecomment-658936982

So far, I have not had time.

The OP is running on macOS, so this is likely their problem. I see a few comments here from people on Windows, thus they are probably experiencing a different issue (or their is a resiliency problem in both platforms).

[lberrocal]

The OP is running on macOS, so this is likely their problem. I see a few comments here from people on Windows, thus they are probably experiencing a different issue (or their is a resiliency problem in both platforms).

I'd just like to point out that this happens to me frequently on Windows and If memory serves right, a couple other issues that were closed as a duplicate of this one had Windows information as well. Same behavior described in here.

[romanovmv]

I have the same problem, raised a ticket #9170 but it's still unresolved as well. Happens on my macOS, but I've installed ADS on my virtual machine running Windows and had the same issue.

It's working fine while I'm connecting to local SQL Servers (local I mean ping < 50ms here in Australia). Once I switch to the "remote" US or UK server with ping above 300ms - here the problem occurs. azdata-languageService still seems to be running synchronously, making lost of meta queries similar to there the ones mentioned above in the thread, even though @kburtram mentioned that intellisense should be running asynchronously.

I've also noticed that it happens every time I have an active connection to the "remote" server in a Servers tab.

I haven't found any workaround yet, just trying not to connect to "remote" servers and restarting ADS occasionally.

[grdinic-ua]

Same issue here, though in my specific case I have several "Edit Data" windows open in a grid. Seemingly at random they'll flip to a "frozen active query state", as in the stop button shows but clicking it doesn't do anything. To reset I have to close that tab and re-open the table for editing. In my case the tables and queries are really simple, just a few rows of basic text data.

[daniel0x00]

Reporting hereby the same problem. It happens randomly and cannot reproduce so much. It seems it happens when you move across query windows. Very annoying, it makes it not usable. Only solution is to restart.

Environment:

• OS: Windows 10 20H2.
• ADS: 1.25.3 - most up to date version as of now.
• MSSQL: local server: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: )

Version: 1.25.3 (system setup)
Commit: b277eafb8f5328ea26c6edfddd1999292ac27937
Date: 2021-02-09T23:10:35.151Z
VS Code: 1.48.0
Electron: 9.4.3
Chrome: 83.0.4103.122
Node.js: 12.14.1
V8: 8.3.110.13-electron.0
OS: Windows_NT x64 10.0.19042

[jtsom]

Happens for me almost for every new query. Even a simple query such as select * from settings where id = 1 will hang forever.

This is running under Windows connecting to mssqllocaldb.

Version: 1.28.0-insider (user setup)
Commit: 780ca84f9a5104591fbae4231c9323f14f6aab28
Date: 2021-03-23T05:44:51.441Z
VS Code: 1.51.0
Electron: 9.4.3
Chrome: 83.0.4103.122
Node.js: 12.14.1
V8: 8.3.110.13-electron.0
OS: Windows_NT x64 10.0.19042

[gsimardnet]

I have that kind of problems as well. This issue should be the top priority. There is no point in adding any other features to the product if it's unstable.

Notify when it's solved. Until then I'll have to go back to SSMS.

[raeokay]

This is definitely still an issue, regardless of query complexity. I've been trying to move my team over to ADS and this has been a showstopper.

I can't reliably reproduce it. I haven't noticed a pattern. It can happen in a new window, or within an existing one. It's been occurring since I started using ADS last winter, but current install is:

Version: 1.29.0 (system setup) Commit: 0f5cfdc2c8c2433028842e16dc5ac1a65da24292 Date: 2021-05-15T01:12:11.112Z VS Code: 1.53.0 Electron: 11.2.2 Chrome: 87.0.4280.141 Node.js: 12.18.3 V8: 8.7.220.31-electron.0 OS: Windows_NT x64 10.0.19041

[phastafrican]

I've been facing same issue for a few months now. Queries randomly hang. Extremely frustrating.

Version: 1.31.1 (user setup) Commit: 65fb22cc7c36db9c53af1ed2fdbdf48f66c682be Date: 2021-07-28T21:03:21.018Z VS Code: 1.56.0 Electron: 12.0.7 Chrome: 89.0.4389.128 Node.js: 14.16.0 V8: 8.9.255.25-electron.0 OS: Windows_NT x64 10.0.18363

[cientovolando2021]

It seems like it's gonna be a bug without solution. Same issue for me on Windows 10. Returning to DBeaver.

[drethedevjs]

I also have this issue on a Windows laptop. When I switch to a new DB or initially connect the first query I run hangs. The execution time just stays at zero. After a few minutes or so, it does something.

[anjode]

Still seeing this as an issue. Restarting doesn't seem to help. I hit run on my query and the query timer says 00:00 and stalls for a while. Sometimes several minutes before it finally runs. I hope this gets some visibility.

EDIT: Adding more info, I notice that if I run a different query in a different tab it also hangs so it's not tab specific. I can run the same query in SSMS and it runs. Meanwhile, ADS is still hung but finally breaks free and runs the query.

[winzig]

Experiencing this issue once again after it seemed to be better for quite a while. Only change is I'm on a new machine and OS (upgraded from previous Intel Mac laptop to M1 Apple Silicon running Monterey 12.0.1). NOTE: I've had the laptop for a few weeks now, and the problem did not start immediately with the new laptop... it was working fine the first couple weeks on it, so I don't think it's strictly an OS/hardware issue?)

I'm on ADS 1.33.1

I'm connected via OpenVPN client to an OpenVPN server also running at AWS.

I'm running the queries against MSSQL running at AWS RDS. When I hit the Run button, it will sit there with "Executing query..." at the bottom, and the timer NOT ticking. Quite a while passed (30-60 seconds), and then the simple query finished executing and the timer indicated only 2 seconds had passed.

Is there a way to get additional debugging info on the query execution when this happens? Might provide a clue.

(Note this happens regularly even after restarting ADS.)

[haydnlj]

In case it isn't clear to the devs, this problem that has persisted for several years now essentially makes the ADS tool unusable a significant percentage of the time. Simple queries can take from 1 to 3 minutes to begin executing (i.e. after initiating execute with either mouse or keyboard shortcuts, the time counter stays at 00:00:00 for several minutes before starting). Literally greater than 50% of the queries I execute have this issue - and although a restart usually fixes the next query, the problem returns shortly thereafter. This has been on every version for the last 2+ years. I am using this over a WAN connected via VPN (Cisco Anyconnect) - I personally believe there is a connection persistence issue at play. I experience this same issue on all different clients I have used this on (Windows & Linux), connected to different SQL servers. Aside from my corporate network and VPN infrastructure, the client is the only constant here that I can see. I have removed all extensions in case there was some interference there to no avail.

I notice a lot of new features being added at each release - am surprised that a show stopper bug. It is a real shame, because the tool is otherwise perfect and great to see ongoing feature improvements.

[romanovmv]

@haydnlj I’m with you here. Every time you start running queries again the remote server where the ping is high you actually spend more time waiting on the query to start executing rather than doing useful work.

The cause of the issue is described here https://github.com/microsoft/azuredatastudio/issues/7440#issuecomment-580369675 and it’s obvious that intellisense is blocking the execution, but for some reason it hasn’t been fixed yet….