Azuredatastudio: 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.

[lucasgit2000]

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.