Query Window "Hang"

[zengshengliu]

Issue Type: Bug

Sometimes the query windows will just "hang" there. Clicking on "Run" does nothing, and there is no error or warning. It just wouldn't execute the query.

Azure Data Studio version: Azure Data Studio 1.2.4 (1ebfd02, 2018-11-02T21:36:55.143Z)
OS version: Darwin x64 18.2.0

System Info

Item	Value
CPUs	Intel(R) Core(TM) i7-8750H CPU @ 2.20GHz (12 x 2200)
GPU Status	2d_canvas: enabled checker_imaging: disabled_off flash_3d: enabled flash_stage3d: enabled flash_stage3d_baseline: enabled gpu_compositing: enabled multiple_raster_threads: enabled_on native_gpu_memory_buffers: enabled rasterization: enabled video_decode: enabled video_encode: enabled webgl: enabled webgl2: enabled
Load (avg)	2, 2, 2
Memory (System)	16.00GB (0.17GB free)
Process Argv	/Applications/Azure Data Studio.app/Contents/MacOS/Electron
Screen Reader	no
VM	0%

Extensions (5)

Extension	Author (truncated)	Version
agent	Mic	0.35.0
profiler	Mic	0.3.0
server-report	Mic	0.1.3
whoisactive	Mic	0.1.1
sql-search	Red	0.2.1

[zengshengliu]

After this happen, if I closed the query window and open a new one, a "progress bar" will show on top of the "Search server names" box in Server window. So it looks like there is something running in the background that cause the query window to become unresponsive.

[zengshengliu]

I look into it more, it seems like its the intellisense service that is running behind the scene, when it is being blocked on the database side, it render the Azure Data Studio inoperational.

[kburtram]

@zengshengliu it would be unexpected that the intellisense SQL queries would cause UI delays since they run async in a child process and the UI doesn't block rendering waiting for these tasks (it waits on callback notifications). I'll try to repro the issue.

[zengshengliu]

@kburtram Sorry I might have stated it wrong. The UI itself is fine, but no query will execute when its in that state.
What happen is while typing the query, when get to certain part (like the table name), interllisense tries to get the table definition like column names and got blocked, but the user (me) continue the query. After we finish typing and try to execute, nothing will happen. The query doesn't execute, nor does it contains any error message.
There are multiple developers within the company that encounters this issue.

Another issue (maybe) is that the query intellisense uses (one shown on the screenshot), it doesn't get kill when we exit Asure Data Studio. I usually notice a few blocked queries from the activity monitor even after I close (quit) the tool. Exploring the running process does shows that its still running in background.

[joshbooker]

related:
#2694

[GrzegorzLyp]

The query editor still hangs on executing some queries. This is terrible.

[dparsons86]

Confirmed that this is still a think with the March 2021 release.

[toasteur]

it started to happen regularly again since a few days.
restarting the application fixes the problem for a time

[gdwallace]

I just recently started using Azure Data Studio, I'm on version 1.32.0 and can confirm that this is still an issue.

I am working from home and have to connect to a VPN to access the servers that house our DBs. I have no connection issues with the VPN. However; Azure Data Studio will stop responding for no reason. Even if I don't leave it idle for any amount of time. I can be working in it for 20 - 30 minutes (sometimes longer / sometimes shorter) and the next query I try to execute will just hang. Clicking Cancel does nothing, and the counter showing how long it takes to execute the query remains 0

[GrzegorzLyp]

After some investigation on my DBs, it turned out that while I am waiting the query to be executed, ADS execute tons of technical queries on my DB and that seems to be the reason.

[joshbooker]

@GrzegorzLyp, when it hangs, try closing the script file and reopening from disk in a new tab then execute again.

Do you get the results from the first execution in the results pane of the second?

If so, related: #5627

[gdwallace]

I just had this happen to me.
I closed the tab that froze up and opened a new tab. I lost connection to the database.

[joshbooker]

reconnect and execute again in new the tab. look closely at results. are the results from both executions displayed in the results pane of the second execution?

[gdwallace]

No, once I close the frozen tab, I have to reconnect to the DB, the only results I see in the results pane are from the query I just executed.

[joshbooker]

thanks for trying. the only way I've been able to repro #5627 is to immediately reopen the file from disk in a new tab, reconnect and execute -immediately after the hang. Would love some more folks to repro and attention paid to this issue.

[GrzegorzLyp]

@GrzegorzLyp, when it hangs, try closing the script file and reopening from disk in a new tab then execute again.

Do you get the results from the first execution in the results pane of the second?

If so, related: #5627

In this situation none of the tabs work.

[joshbooker]

correct. did you try closing the tabs and immediately reopen the file from disk in a new tab, reconnect and execute -immediately after the hang?

[GrzegorzLyp]

Just written "select"

And 643 events in profiler mosty asking for something about logins (probably it takes longer as you have more logins)

[GrzegorzLyp]

And the last killer query is below. It takes minutes to execute

exec sp_executesql N'SELECT
SCHEMA_NAME(sp.schema_id) AS [Schema],
sp.name AS [Name],
sp.object_id AS [ID],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
AS bit) AS [IsSystemObject],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)
ORDER BY
[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC'

[Charles-Gagnon]

@shueybubbles Looks like stuff coming from SMO causing slowdowns here. Have you seen anything like this?

@GrzegorzLyp If you run the query locally (you'll need to fiddle around with the params) do you see the same slowness?

[shueybubbles]

@Charles-Gagnon the queries can be slow for lots of reasons. The UI shouldn't be blocking on completions at all.

[kburtram]

Closing as a duplicate of #7440. Using that as the primary issue for this since it has more recent comments and activity.