Connection to SQL DB is very slow and none stable

awbmansour commented 1 year ago

Issue Type: Performance Issue

Trying to connect to SQL DB whether it is on local machine or remotely is very slow. When one has a Tab with an active connection, the expected behavior is that if one uses ctl-N to open a new Tab is that the connection will be carried over to the next window, it does not happen consistently. The connection is not stable either. Leaving it for several minutes unattended can get the connection lost.

Many times the connection eventually happnes but the Drop Down list shoing the list of DB's does not display anything except Master, and one has to make a new connection.

Azure Data Studio version: azuredatastudio 1.40.2 (661384637db384fe5d4e6224069adbe708580b16, 2022-12-22T22:17:02.658Z) OS version: Windows_NT x64 10.0.22621 Restricted Mode: No Preview Features: Disabled

System Info

|Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i7-8550U CPU @ 1.80GHz (8 x 1992)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: disabled_off
direct_rendering_display_compositor: disabled_off_ok
gpu_compositing: enabled
multiple_raster_threads: enabled_on
oop_rasterization: enabled
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
skia_renderer: enabled_on
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled| |Load (avg)|undefined| |Memory (System)|15.88GB (1.68GB free)| |Process Argv|--enable-proposed-api ms-toolsai.jupyter| |Screen Reader|no| |VM|0%|

Process Info

``` CPU % Mem MB PID Process 0 75 61836 azuredatastudio main 0 39 1320 utility-network-service 0 191 14228 window (● SQLQuery_1 - SIMPLERAPPS8.i2cdborgbt (Integrated) - workspace (Workspace) - Azure Data Studio) 0 76 17576 shared-process 0 68 14236 ptyHost 0 82 38760 fileWatcher 0 123 44664 extensionHost 0 54 4300 dotnet tool run dotnet-interactive -- notebook-parser 0 27 32412 "C:\Program Files\dotnet\dotnet.exe" C:\Users\admin\.nuget\packages\microsoft.dotnet-interactive\1.0.350406\tools/net6.0/any/Microsoft.DotNet.Interactive.App.dll notebook-parser 0 6 65964 console-window-host (Windows internal process) 0 71 23812 "C:\Program Files\Azure Data Studio\azuredatastudio.exe" "c:\Program Files\Azure Data Studio\resources\app\extensions\json-language-features\server\dist\node\jsonServerMain" --node-ipc --clientProcessId=44664 0 32 27408 "c:\Program Files\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\4.3.1.2\MicrosoftSqlToolsCredentials.exe" --locale en --log-file c:\Users\admin\AppData\Roaming\azuredatastudio\logs\20230103T160115\exthost1\Microsoft.mssql\credentialstore.log --tracing-level Critical --autoflush-log 0 6 48576 console-window-host (Windows internal process) 0 34 40228 "c:\Program Files\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\4.3.1.2\SqlToolsResourceProviderService.exe" --locale en --log-file c:\Users\admin\AppData\Roaming\azuredatastudio\logs\20230103T160115\exthost1\Microsoft.mssql\resourceprovider.log --tracing-level Critical --autoflush-log 0 6 22068 console-window-host (Windows internal process) 0 104 47812 "c:\Program Files\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\4.3.1.2\MicrosoftSqlToolsServiceLayer.exe" --locale en --log-file c:\Users\admin\AppData\Roaming\azuredatastudio\logs\20230103T160115\exthost1\Microsoft.mssql\sqltools.log --tracing-level Critical --autoflush-log 0 6 28144 console-window-host (Windows internal process) 0 107 58240 gpu-process 0 88 65788 issue-reporter ```

Workspace Info

Extensions (18)

Extension|Author (truncated)|Version ---|---|--- admin-pack|Mic|0.0.2 admin-tool-ext-win|Mic|0.0.4 agent|Mic|0.49.0 azcli|Mic|1.7.0 cms|Mic|0.9.2 dacpac|Mic|1.11.0 import|Mic|1.5.4 machine-learning|Mic|0.12.0 powershell|ms-|2022.7.2 profiler|Mic|0.11.0 query-history|Mic|0.5.3 schema-compare|Mic|1.17.0 sql-assessment|Mic|0.6.3 sql-database-projects|Mic|0.20.0 dotnet-interactive-vscode|ms-|1.0.3504060 jupyter|ms-|2022.4.1021342353 azdata-sanddance|msr|4.0.1 extra-sql-script-as|pac|0.5.0

cheenamalhotra commented 1 year ago

Hi @awbmansour

Could you try the latest insiders build and let us know if things are looking better? We've made many improvements in the area and expect performance improvements in connection and query execution.

Thanks!

awbmansour commented 1 year ago

Dear Cheena,

Many Thanks for the follow-up. I appreciate this.

I did a quick test on local databases that used to exhibit poor performance, there is an outstanding improvement in performance, and responsiveness across the board. I also noticed stability improvement in the query list scrolling. So far, Very Well Done.

The Query speed has been tremendously improved, and the DDL DB migration scripts execution performance is substantially improved (these are Entity Framework migration scripts, and each script is no less than 512KB in size). I have tested a sequence of 15 scripts that used to cause lots of delays, none stalled, and all are fine.

Thanks for all your team efforts to reach this result. Hopefully, I will get a chance to test on Azure Remote Databases and get back to you soon.

Best Regards

Adel

On Sat, Aug 26, 2023 at 4:11 AM Cheena Malhotra @.***> wrote:

Hi @awbmansour https://github.com/awbmansour

Could you try the latest insiders build https://github.com/microsoft/azuredatastudio#try-out-the-latest-insiders-build-from-main-branch and let us know if things are looking better? We've made many improvements in the area and expect performance improvements in connection and query execution.

Thanks!

— Reply to this email directly, view it on GitHub https://github.com/microsoft/azuredatastudio/issues/21494#issuecomment-1694096646, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABMRZNRYE7TPKR2KQQAXVADXXFEM3ANCNFSM6AAAAAATPY3W4E . You are receiving this because you were mentioned.Message ID: @.***>

--

Thanks and Best Regards

Adel Mansour Director

simplerApps Software Solutions Ltd. www.simplerapps.com @.***

mobile | viber | whatsapp : +20 122 3987214 skype: awbmansour

cheenamalhotra commented 10 months ago

Hi @awbmansour

It's been a while, we're hoping this to be applicable generically for all databases, since we haven't heard back, I will close this issue as resolved.

microsoft / azuredatastudio

Connection to SQL DB is very slow and none stable #21494