Query Hangs - Githubissues

mackenziepsilb commented 5 years ago

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

Ben-CA commented 1 year ago

I know it doesn't help the diagnostics, but I'm still on 1.39.1, and keep declining updates until I hear whether it's been fixed yet. It still hangs for several seconds sometimes (maybe up to 30 seconds) a few times a day for no apparent reason (perhaps a brief network blip in my VPN connection and it doesn't try again frequently enough?), but I haven't had to close the program down entirely and re-open it like I did with the most problematic versions. Since I use it basically every day fairly regularly, that was a real pain.

erinstellato-ms commented 1 year ago

@erinstellato-ms Yes, I just upgraded to 1.41.2 and it's no different than the previous version.

@anjode As noted, there are fixes from a recent issue (https://github.com/microsoft/azuredatastudio/issues/22044) that are available in the Insider build. If you are able to try that to see if it resolves your issue, that would be great. Otherwise, the changes will be available in the next stable release (1.42, expected this month). Thanks!

erinstellato-ms commented 1 year ago

Hi @Ben-CA - we have made changes that we expect will address your issue, and they are available in the 1.42 Insider build. You can install the Insider build, side by side with the 1.39.1 release, to test and see if resolves your issue.

anjode commented 1 year ago

@erinstellato-ms Yes, I just upgraded to 1.41.2 and it's no different than the previous version.

@anjode As noted, there are fixes from a recent issue (#22044) that are available in the Insider build. If you are able to try that to see if it resolves your issue, that would be great. Otherwise, the changes will be available in the next stable release (1.42, expected this month). Thanks!

@erinstellato-ms Great news! I will download the Insider Build. Thank you!

alanrenmsft commented 1 year ago

@erinstellato-ms this is actually not related, the issue you mentioned is an in-cycle regression, and this issue is still being worked on.

@anjode I am still investigating this issue.

@erinstellato-ms Yes, I just upgraded to 1.41.2 and it's no different than the previous version.

@anjode As noted, there are fixes from a recent issue (#22044) that are available in the Insider build. If you are able to try that to see if it resolves your issue, that would be great. Otherwise, the changes will be available in the next stable release (1.42, expected this month). Thanks!

anjode commented 1 year ago

@alanrenmsft Thanks for the clarification. Still seeing some query hanging in the insiders build but not to the same severity as previous versions.

Ben-CA commented 1 year ago

Hi @Ben-CA - we have made changes that we expect will address your issue, and they are available in the 1.42 Insider build. You can install the Insider build, side by side with the 1.39.1 release, to test and see if resolves your issue.

@erinstellato-ms Thanks - I've downloaded and installed the latest 1.43.0 Insiders (I presume that would include the changes made in 1.42?) and I'll see if that works better.

myoder020 commented 1 year ago

I'm on the newest Insider build. When ADS is connecting to my server it sometimes hangs. When looking at the query that is currently running this was what I saw.

(@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000))
SELECT
clmns.name AS [Name],
clmns.column_id AS [ID],
clmns.is_nullable AS [Nullable],
clmns.is_computed AS [Computed],
CAST(ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey],
clmns.is_ansi_padded AS [AnsiPaddingStatus],
CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
CAST(ISNULL(cc.is_persisted, 0) AS bit) AS [IsPersisted],
ISNULL(clmns.collation_name, N'') AS [Collation],
CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
clmns.is_identity AS [Identity],
CAST(ISNULL(ic.seed_value,0) AS numeric(38)) AS [IdentitySeedAsDecimal],
CAST(ISNULL(ic.increment_value,0) AS numeric(38)) AS [IdentityIncrementAsDecimal],
(case when clmns.default_object_id = 0 then N'' when d.parent_object_id > 0 then N'' else d.name end) AS [Default],
(case when clmns.default_object_id = 0 then N'' when d.parent_object_id > 0 then N'' else schema_name(d.schema_id) end) AS [DefaultSchema],
ISNULL(dc.Name, N'') AS [DefaultConstraintName],
(case when clmns.rule_object_id = 0 then N'' else r.name end) AS [Rule],
(case when clmns.rule_object_id = 0 then N'' else schema_name(r.schema_id) end) AS [RuleSchema],
CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsDeterministic'),0) AS bit) AS [IsDeterministic],
CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsPrecise'),0) AS bit) AS [IsPrecise],
ISNULL(ic.is_not_for_replication, 0) AS [NotForReplication],
CAST(COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsFulltextIndexed') AS bit) AS [IsFullTextIndexed],
CAST(COLUMNPROPERTY(clmns.object_id, clmns.name, N'StatisticalSemantics') AS int) AS [StatisticalSemantics],
CAST(clmns.encryption_type AS int) AS [EncryptionType],
clmns.encryption_algorithm_name AS [EncryptionAlgorithm],
clmns.column_encryption_key_id AS [ColumnEncryptionKeyID],
ceks.name AS [ColumnEncryptionKeyName],
CAST(clmns.is_filestream AS bit) AS [IsFileStream],
CAST(clmns.is_sparse AS bit) AS [IsSparse],
CAST(clmns.is_column_set AS bit) AS [IsColumnSet],
usrt.name AS [DataType],
s1clmns.name AS [DataTypeSchema],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision],
CAST(clmns.scale AS int) AS [NumericScale],
ISNULL(xscclmns.name, N'') AS [XmlSchemaNamespace],
ISNULL(s2clmns.name, N'') AS [XmlSchemaNamespaceSchema],
ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
CASE WHEN usrt.is_table_type = 1 THEN N'structured' ELSE N'' END AS [UserType],
clmns.generated_always_type AS [GeneratedAlwaysType],
CAST(clmns.is_hidden AS bit) AS [IsHidden],
CAST(0 AS bit) AS [IsDroppedLedgerColumn],
CAST(clmns.is_masked AS bit) AS [IsMasked],
CAST(
        CASE
        WHEN ep1.value IS NOT NULL or ep2.value IS NOT NULL or ep3.value IS NOT NULL or ep4.value IS NOT NULL THEN 1
        ELSE 0
        END
       AS bit) AS [IsClassified],
ISNULL(clmns.graph_type, 0) AS [GraphType]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key
LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and 0 = cik.is_included_column
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.default_constraints as dc ON clmns.default_object_id = dc.object_id
LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id
LEFT OUTER JOIN sys.column_encryption_keys AS ceks ON (ceks.column_encryption_key_id = clmns.column_encryption_key_id)
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.schemas AS s1clmns ON s1clmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
LEFT OUTER JOIN sys.tables t ON t.object_id = clmns.object_id
LEFT OUTER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
LEFT OUTER JOIN sys.extended_properties AS ep1 ON clmns.object_id = ep1.major_id AND clmns.column_id = ep1.minor_id and ep1.name = 'sys_sensitivity_label_name'
LEFT OUTER JOIN sys.extended_properties AS ep2 ON clmns.object_id = ep2.major_id AND clmns.column_id = ep2.minor_id and ep2.name = 'sys_sensitivity_label_id'
LEFT OUTER JOIN sys.extended_properties AS ep3 ON clmns.object_id = ep3.major_id AND clmns.column_id = ep3.minor_id and ep3.name = 'sys_information_type_name'
LEFT OUTER JOIN sys.extended_properties AS ep4 ON clmns.object_id = ep4.major_id AND clmns.column_id = ep4.minor_id and ep4.name = 'sys_information_type_id'
WHERE
(clmns.name=@_msparam_0)and((tbl.name=@_msparam_1 and SCHEMA_NAME(tbl.schema_id)=@_msparam_2))

This query had a wait type of MEMORY_ALLOCATION_EXT

romanovmv commented 1 year ago

I'm on the 1.43.0-insider build and the issue described in https://github.com/microsoft/azuredatastudio/issues/7440#issuecomment-1109174035 is back.

Ben-CA commented 1 year ago

On the 1.43.0-insider build, and just had a query totally hung up for a couple minutes. Pressing cancel did nothing for maybe 2 minutes:

Yet the query said Executing, and 00:00:00

Finally it cancelled. Then I was able to retry the query and it took less than a second.

If cancel "took effect" immediately, this would be less of an issue.

erinstellato-ms commented 1 year ago

@myoder020 @romanovmv @Ben-CA Thanks for trying and providing feedback. As @alanrenmsft noted, I was confusing issues and this is obviously still occurring. We are investigating and working to get it addressed and we may request additional information to try and troubleshoot. Appreciate your help and feedback here, we are committed to getting many of these long-standing (and frustrating) issues resolved.

JayCal commented 1 year ago

If it helps anyone, after thinking it was network-related, I've concluded my (1.41.2, Windows) issues arose when there are "Problems" detected in the workspace (bottom left).

I often do data analysis across different database servers and "Select-to-Run" queries. 10-12 queries later, I "Change Connection". When new connection is up, Intellisense (I assume) runs and finds errors because tables/schemas etc. are not found in the new connection.

Most of the time, I can still Select-to-Run a query w/o errors and get a result. Eventually, I'll get the 0:00 Executing query, and the only way to get results back is to shut down and re-open, or wait for the Intellisense to finish. Once ADS is done doing what it needs to do, the query comes back immediately. While inconvenient, my workaround has been to either make sure I comment everything out before switching connections, or keep my scratch query files database specific. I've never had the "Executing Query" issue when my Errors and Warnings are both 0.

nihirisuto commented 1 year ago

^ Just tested this on my end and this seems to be the root of the issue I'm experiencing as well. I'm often using multiple connections between tabs.

Unchecking all intellisense settings for both user and workspace. Initial tests seem to show this is fixing it? Thought it was going to hang at 00:00:00 again but it started countin right when I started staring at the timer. Just switched between 4-5 different tabs all with different connections and I'm able to go back to each one and run a query. Going to keep testing here, but if this really just fixed it you just made my day @JayCal

Edit: I still haven't seen the issue come back. Been running queries left and right here. It almost feels as if there was some sort of regression in intelli sense speed or maybe a bigger overarching piece of azure data studio that powers intelli sense that slowed things down. I did notice that if I got to the 00:00:00 issue earlier this morning, if I just let the query hang went to do something else, I'd come back to it finished... it was just incredibly slow. 1-5+ minutes before a simple query executed. I would imagine this wait time varies based on database size & statistics for tables, some databases are big and maybe intelli sense is taking a long time to index everything so it can quickly display it in popups. Beats me I won't feign that I know how any of this works but I'm at least incredibly ecstatic that I can query reliably again.

edit 4/13/2023: unfortunately I was a little premature on thinking this fixed it, seeing that this issue seems to persist. triple checked all my intellisense settings and they're all disabled and I'm still getting query hangs.

botknoa commented 1 year ago

I am experiencing this bug a hundred times a day. This makes using ADS absolutely agonizing all the time. Help. I'm suffering.

Version: 1.44.1 Commit: 8f53a316fa00a98264f1ab119641cd540b5af25c Date: 2023-06-01T02:13:21.084Z VS Code: 1.70.0 Electron: 19.1.8 Chromium: 102.0.5005.167 Node.js: 16.14.2 V8: 10.2.154.15-electron.0 OS: Darwin x64 22.4.0

erinstellato-ms commented 1 year ago

@botknoa This happens 100 times a day? Where you try to execute a query and then it simply hangs up, with no notification and no error? To what version of SQL are you connecting?

jtsom commented 1 year ago

This hang / freeze / extreme slow down happens all the time for me also. It mostly manifests when I am connected to our company VPN (Using the Azure VPN) - the connection will either just timeout, can't even connect to the database, or when it does, any query takes minutes just to do a simple query.

It also seems to be when connecting to the database using a FQDN. If I use the direct IP address, the connection usually (not always) is quicker, and queries respond quicker.

Using SSMS, with the same VPN connection works just fine.

Also, connecting and querying a local database (SQLExpress), usually works well, with some occasional slowness.

drethedevjs commented 1 year ago

I'm on version 1.39.0. It does it for me when I switch to different servers. If I stay on the same server all day, it works fine but that's not realistic for my job. We have a sharded db so I'm switching all the time. I'm also using a VPN.

MikePittAge commented 1 year ago

I have this same issue also - I've never really noticed if it only happens when switching servers, I'll keep an eye on it. I have this issue connection to SQL Server 2019 but previously had it when we ran SQL Server 2012.

erinstellato-ms commented 1 year ago

@jtsom @drethedevjs @MikePittAge Can you confirm/test with the latest release, 1.44.1? We have made a lot of improvements related to connection over the last several releases and it's best for us to troubleshoot with the latest version.

jtsom commented 1 year ago

For me, it's always the latest Insider version - 1.45.0-insider (420a622d)

drethedevjs commented 1 year ago

@erinstellato-ms I'm running a slow query at the moment. Once it's finished, I'll update and test on v1.44.1.

erinstellato-ms commented 1 year ago

For me, it's always the latest Insider version - 1.45.0-insider (420a622d)

I love that you're using the Insider version! Now...am I correct in guessing that you're connecting to an Azure database, not an on-prem DB?

jtsom commented 1 year ago

For me, it's always the latest Insider version - 1.45.0-insider (420a622d)

I love that you're using the Insider version! Now...am I correct in guessing that you're connecting to an Azure database, not an on-prem DB?

Yes, Azure database.

erinstellato-ms commented 1 year ago

FYI, I've looped in engineering and will reach out if need more info from anyone. Thank you for continuing to provide feedback and information - we appreciate it!

MikePittAge commented 1 year ago

@jtsom @drethedevjs @MikePittAge Can you confirm/test with the latest release, 1.44.1? We have made a lot of improvements related to connection over the last several releases and it's best for us to troubleshoot with the latest version.

I've updated to 1.44.1 and get the same issue - it happens so frequently that it takes no time at all to reproduce the issue. I didn't change connection and I had no error/warning outstanding - it just hangs when you run the query. The execution time doesn't start moving either - it's like it's doing some kind of pre-execution check and gets stuck. What's interesting is I can just repeatedly run the same piece of SQL in the same connection and get the issue every few times.

erinstellato-ms commented 1 year ago

Thanks @MikePittAge - we may follow up to see if you can collect logs/diagnostics if we need them. Thank you for trying in the latest release (feel free to check out the new stuff while you're there!)

botknoa commented 1 year ago

@erinstellato-ms I am connecting to Synapse Dedicated SQL Pools. The primary behavior is an indefinite hang with no error or result, requiring restarting ADS. Secondary behavior includes hangs that do recover after a 10-30 second wait.

Ben-CA commented 1 year ago

I've stayed on v. 1.39.1, where there's the occasional inexplicable 10+ second wait (accessing an older SQL Server 2008 via VPN) - after encountering the full hang up (requiring application restart) too many times on later versions. I also have Insider installed, but have resorted back to sticking with an older version until this gets cleared up. (Otherwise, as clunky as it is, I'd be better off using MS SQL SMS.)

drethedevjs commented 1 year ago

@Ben-CA and I are on the same page. I stuck with 1.39.1 for the same reason. It appears that later versions have regressed.

cheenamalhotra commented 1 year ago

Thanks for providing info that it's related with Synapse Dedicated Pool. I was able to capture logs and reproduce the delay.. Here is a log: QueryHang_dedicatedPool.txt

@alanrenmsft could you investigate what might have changed after 1.39.1 that caused this behavior? It may be related with SMO update is my assumption.. but let's take a look at solving this asap.

Opened https://github.com/microsoft/azuredatastudio/issues/23400 to track this for July release.

drethedevjs commented 1 year ago

@cheenamalhotra To be clear, it's better in v. 1.39.1 but it's not fixed. We still experience hangs in a more predictable manner.

cheenamalhotra commented 1 year ago

Sure, that's most likely a different cause and this thread stands true as-is. But will dig into it as well - I'm just getting started.

nihirisuto commented 1 year ago

@erinstellato-ms I've.. lived with this for quite a few versions now. I know it's an ongoing issue that seems to be a cat and mouse chase to fix. There was some thought that it might be the intellisense stuff causing hangs at one point, but I think even disabling all intellisense features didn't resolve for me, but it very well could be something related to connecting up to sql server instances that have lots of database objects. I've pretty much always updated to the latest insiders on MacOS with the hope that it's fixed. I primarily see this when running my work VPN, it doesn't seem to bug me when I'm on premise in the office, but other users have stated months back in this github issue that it wasn't a VPN issue for them.

This is a little unorthodox as far as solutioning goes haha... but I'm up at a Lynnwood office. If you guys absolutely can't reproduce and want me to come visit the Microsoft campus so any seattle-based devs can take a peek at what's going on on my instance of azure data studio I'd be happy to do that. Or we can do some calls or something and I can work through with an engineer. I'm an azure data studio evangelist in my office and I shoehorn this tool into every conversation I have at the watercooler :P always tuned into this tools success because when its working well its my favorite place to be by miles.

other details that might be of interest:

our enterprise uses a citrix vpn (hate it)
sql server connections include 2008r2, 2014, 2016
only have a couple azure connections
extensions:
theme i pulled over from a vscode vsix (one of the many dracula themes)
sp_whoisactive
SQL Server Import
simple alignment
sometimes after restart when things hang ill need to modify a stored procedure and opening that can be incredibly slow as well but this probably falls under the same bucket of getting a transaction of the ddl back from sql server
a lot of times it takes forever once ive connected to a sql server to get the full database object/schemas to load in the object explorer. my connection throughput over vpn when im working remote is certainly slower than being on premise at my office, so maybe there is still some reason to believe the intellisense or similar processes trying to catalog things or fetch statistics on azure data studio might be chained to connection speed & could be a culprit, and marginally slower connections might compound into major slow downs when querying data in the tool. again when im in office where im ethernet plugged into our network and things are instant, this is never an issue. like others have said, sometimes ill hit run then ill just swipe away to a different workspace and come back to it and my query finally executes. A near instant query on a 5 record 5 column table may take 30 seconds to a minute though.

winzig commented 1 year ago

I've been monitoring this issue for years and it seems to get better and then get worse over time. In my case, I'm connecting to 2 different AWS RDS SQL servers, via an OpenVPN client connection to an OpenVPN server that operates within our AWS VPC. I'm connecting using the FQDN, e.g. xxxxxxxxx.yyyyyyyyy.us-east-1.rds.amazonaws.com.

I am running ADS from macOS Ventura 13.4.

The behavior I see is that I'm often waiting for a query to complete, but usually if I wait long enough it will complete. Other times the only way to get going again is to kill ADS and restart.

bhargobjs commented 1 year ago

I too am facing similar issue with ADS in my Mac. To fix it have to kill ADS every time.

chitrankv27 commented 1 year ago

Its been going on for a while and recently it has become very difficult to work on.

DTronD commented 1 year ago

Having been in the office for the last two weeks and had zero "chilledToGo" (i.e. query timer sticks at 00:00 without counting but eventually starts counting and results return) or frozen/hung queries, I'm convinced this is HIGHLY correlated with VPN usage.

From home, I'm using the Azure VPN Client to connect to our hybrid domain Azure tenancy where our SQL Servers (and other fun stuff) are located. When in the office it's a plain connection to the WLAN which is in turn using a site-2-site Azure connection to allow office workers to reach cloud-goodies. Home suffers from problem, in-office has been perfect for two weeks. Is this some evil plot to force me back into the office. EeeeeeGHADS!

nihirisuto commented 1 year ago

Home suffers from problem, in-office has been perfect for two weeks. Is this some evil plot to force me back into the office. EeeeeeGHADS!

💀 😂

formula349 commented 1 year ago

I love that this issue is seeing activity. I've lived with this freezing/delays probably since ADS was released. I also connect to an Azure DB (managed instance) via P2S VPN.

angel-torres commented 1 year ago

Yes. I also have the same issue. I connect to a Meraki VPN and it freezes when connected to SQL Server. Restarting the app helps for a bit but it does it again pretty quickly. I have no problems when connected to SQL Server on a local network.

raeokay commented 1 year ago

Just back from a vacation and updated to 1.44.1. Where this used to hang for random amounts of time and eventually resolve, now it's hanging in perpetuity and requires a full restart of ADS. It's really making this app unusable.

I've never noticed any pattern with database switching or anything of the sort. I can be working happily in a window and then it'll just happen. I do also suspect it has something to do with Intellisense, but then when I turn Intellisense off, I see no improvement.

Am connecting to a company on-prem server from home via VPN (Forticlient) as well.

erinstellato-ms commented 1 year ago

Hi all!! Not tagging everyone :) but I wanted to say thanks for continuing to provide feedback and share your experience here. It is extremely beneficial as it helps us identify patterns (e.g. VPN use is a definite theme). Even though we are not providing daily updates, please know that this is an issue we are actively working on. We will share an update when we can (e.g. a change in an Insider build that folks could try). Thank you.

jtsom commented 1 year ago

I'm on the latest Insiders, and am trying to just do a "show top 1000" on a table.

I am connected to one of our corporate databases through Azure VPN, and clicking the twisty arrow to open the database to see the tables is basically not working. It is spinning forever.

FWIW... doing the same thing to my local SQLEXPRESS database gives an all but instant response.

Also, I found that if I connect to the Azure SQL database via it's IP address, through the VPN, it's usually MUCH quicker. Using the FQDN is when it's slow - dead.

AlexanderChaker commented 1 year ago

Been following this issue for a while, and it seems with yesterday's release v1.45 it's been fixed, or at least significantly better. It could have something to do with the support for connection pooling (which you have to enable) Let me know if I'm mistaken, but my setup has not changed at all (remote work using VPN), and I do feel a difference.

erinstellato-ms commented 1 year ago

@AlexanderChaker We do expect connection pooling to provide a benefit, yes, but there is other work we would still like to complete to fully address this issue. I'm very happy to hear that performance is better for you with this release.

MikePittAge commented 1 year ago

@AlexanderChaker We do expect connection pooling to provide a benefit, yes, but there is other work we would still like to complete to fully address this issue. I'm very happy to hear that performance is better for you with this release.

I have to agree that 1.45 has made a huge difference. I’ve been using the Insider build and the amount of times I’ve had the issue has dramatically reduced. Thank you! 😊

cheenamalhotra commented 1 year ago

Another improvement is coming for large data users: https://github.com/microsoft/sqltoolsservice/pull/2161 (fasten your seat belts!)

erinstellato-ms commented 1 year ago

@MikePittAge @AlexanderChaker If you ever try out the Insider builds, you can find this most recent change that @cheenamalhotra referenced in today's Insider build. It's related to returning large result sets so maybe not applicable for you, but tell your friends! :)

cheenamalhotra commented 1 year ago

Hi Everyone!

Would request you to try out the latest insiders build and let us know how it goes! The issue with blocking metadata collection has been fixed and we're hopeful this would resolve majority of query hang problems.

Version: 1.46.0-insider (user setup) Commit: https://github.com/microsoft/azuredatastudio/commit/8c988623431f875fb4ae0049d72c94faf8eb7123 Date: 2023-08-24T17:23:12.287Z VS Code: 1.79.2 Electron: 22.3.14 Chromium: 108.0.5359.215 Node.js: 16.17.1 V8: 10.8.168.25-electron.0 OS: Windows_NT x64 10.0.22621

Thanks! :)

DeanBecker commented 1 year ago

@cheenamalhotra

Anecdotally, miles better. Gave the 1.46.0-insider build a go this morning, flicking between small queries that would have undoubtedly caused the hangs previously, and apart from one hang, it was massively more responsive than it has been in a long time.

About the one hang that did happen: it was after a cancelled query in the same tab; cancelled a large query in the middle of row retrieval (which didn't happen instantly), and then executed a small query in the same tab after it had successfully cancelled. Then experienced the same hang as prior; elapsed timer counter stuck on zero, but the query eventually comes to life and executes (in this case, instantly when it does start running, as it was very small).

Other than that one, early days but massive improvement!

Version: 1.46.0-insider Commit: 8c988623431f875fb4ae0049d72c94faf8eb7123 Date: 2023-08-24T17:21:53.679Z VS Code: 1.79.2 Electron: 22.3.14 Chromium: 108.0.5359.215 Node.js: 16.17.1 V8: 10.8.168.25-electron.0 OS: Darwin arm64 22.5.0

microsoft / azuredatastudio

Query Hangs #7440