azdata-LanguageService is causing huge delays in query execution

[romanovmv]

I'm connecting to my SQL Server located in the UK from Australia (ping around 300ms). When I'm running any query (even very simple one) I sometimes need to wait for up to a minute to see the result.
Steps:

1. Hit Run

2. Messages window is emty (no "Started executing..." message yet). But I can see "Executing query" in the footer)

3. In SQL Server profiler I can see the following queries from azdata-LanguageService app:

exec sp_executesql N'select c.definition,convert(bit,OBJECTPROPERTY(c.object_id,N''ExecIsQuotedIdentOn'')) from [UK001P].sys.sql_modules c where c.object_id = @_msparam_0',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'296296761'

or

exec sp_executesql N'SELECT
CAST(ISNULL(ic.seed_value,0) AS bigint) AS [IdentitySeed]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
WHERE
(clmns.name=@_msparam_0)and((tbl.name=@_msparam_1 and SCHEMA_NAME(tbl.schema_id)=@_msparam_2))
OPTION (FORCE ORDER)
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'Parameter_Id',@_msparam_1=N'bit_CubeGetParameter_SSAS',@_msparam_2=N'dbo'

and others

4. Only after all those queries have been run I can see "Started executing ..." in the messages window and get the output dataset shortly.

It seems to me that Intellisense is just getting the list of all DB object it can find in my query editor and fetches for metadata. And because the server is far it's actually a gap of 2 seconds between the calls.

[adsbot]

Thanks for submitting this issue. Please also check if it is already covered by an existing one, like:

• Cannot execute a query (#3868)

[romanovmv]

Oh, it seems that I've already submitted the same issue a year ago...
Sorry, you can close this one.

[kburtram]

@romanovmv thanks for providing the profiling details. The language service queries should be executed asynchronously with the SQL Script queries. We'll double-check to make sure there isn't some unexpected blocking between these two components.

[romanovmv]

Just in case it helps... Had to wait for around 1 minute to get the result, so that part is not included into GIF.
I've also checked on my Windows VM, the same problem.

[romanovmv]

Had to roll back to the ADS version from February 2018, that one works much faster...

[anthonydresser]

Looks like this may be the same problem #7440

[bara666]

My team and I have been suffering from these problems since March, when we started telecommuting. At that point we started to connect through the VPN and the situation has gotten quite complicated at some point. When we go to the office and we are within the corporate network, we have no problems or are mitigated by the low latency of the network.

We have reviewed the VPN outages and it is totally stable, the pings are within normal metrics. My whole team is working on Ubuntu machines, but other Windows machines using SQL Manager do not suffer from these problems in the same situations.

I need to know what information could be contributed to the thread to be able to advance this topic (logs, traces, configurations, ...). I believe that quite a few of us are affected and that together we can eradicate this problem that affects us all.

[Smoovsky]

My team and I have been suffering from these problems since March, when we started telecommuting. At that point we started to connect through the VPN and the situation has gotten quite complicated at some point. When we go to the office and we are within the corporate network, we have no problems or are mitigated by the low latency of the network.

We have reviewed the VPN outages and it is totally stable, the pings are within normal metrics. My whole team is working on Ubuntu machines, but other Windows machines using SQL Manager do not suffer from these problems in the same situations.

I need to know what information could be contributed to the thread to be able to advance this topic (logs, traces, configurations, ...). I believe that quite a few of us are affected and that together we can eradicate this problem that affects us all.

Same here, we can confirm that the performance is extremely bad when latency between client and server goes above 100ms. It is particularly slow(though it's so slow already) when your query yields an error result, typically takes more than a minute to return.

[cheenamalhotra]

Maybe related with #22970 ?

[bara666]

Wonderful, I've been using the insiders version 1.45.0 all day today and the improvement detected is impressive, I'll keep testing

[bara666]

I have been using the stable version 1.44.1 and the Bug has been fixed, wonderful

[cheenamalhotra]

Thanks for confirming!
Closing as resolved.

[romanovmv]

Hi @cheenamalhotra ,
I don't believe this has been fixed. I'm on 1.44.1 and experiencing the same issues as I did when created this ticket. In order to reproduce you can just go F1 -> Refresh Intellisense Cache and then try and execute any query while the refresh happens.

[cheenamalhotra]

Hi @romanovmv

The queries being executed are not changed, they continue to execute as such. However, previously ADS was creating a new connection for every query, which caused significant lag, and now we've enabled connection pooling.

But I do see some lag here even with this improvement, which as you mentioned relates with the amount of queries executed, and is ~20 seconds on an Azure SQL DB.

I'm attaching logs here for investigation:
QueryHang_RebuildIntellisense.txt
(Total time taken for textDocument/rebuildIntelliSense is ~20 seconds, obstructing query execution in the editor)

[cheenamalhotra]

This issue has been fixed in the latest insiders build and fix will be included in the upcoming release.
Closing as resolved :)