Notebooks hang/crash

[ingebeumer]

Type: Performance Issue

Hi,

Azure Data Studio uses many resources when running Notebooks with many t-SQL queries on large databases that generate many results, often it hangs / crashes ADS. My machine may not be one with the largest specs but also on machines with more resources it happens.

Example is working with SQL Server 2019 Diagnostic Information Queries by Glenn Berry. I sometimes use this query set with customers while working on performance optimization. Love the Notebooks that I can send to them, which they can run and return including query results for analysis and recommendations. When I execute the t-SQL script in ADS or SSMS it runs fast, when I copy the queries to Notebooks Code cells and Run all they take much longer. I have already split Glenn Berry's script into 2 notebooks (see SQL Server 2019 Diagnostic Information Queries.zip) but still on large databases generating many results it hangs / crashes ADS.

When opening Notebooks that include query results you easily run into a not responding message. Especially the 2nd Notebook (part 2_database specific queries) can get large when executed on large databases with many objects). When patient and selecting Keep Waiting it will often open. I cannot always relate duration / hangs / crashes to resource usage I see in my machine. Customers have also run into ADS crashing with code -36861 while running all cells in the Notebook. Happy that ADS remembers and includes results from before the crash so you don't have to start over entirely, but still the crashing experience is not optimal.

Can you please review where Notebooks performance and resource usage is eligible for improvement? Regards, inge

===== Azure Data Studio version: azuredatastudio 1.45.0 (8eff468100f02157f2219335f7a566d14451ca49, 2023-07-24T18:22:12.106Z) OS version: Windows_NT x64 10.0.22631 Restricted Mode: No Preview Features: Enabled Modes:

System Info

|Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i7-1065G7 CPU @ 1.30GHz (8 x 1498)| |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
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled
webgpu: enabled| |Load (avg)|undefined| |Memory (System)|15.60GB (2.04GB free)| |Process Argv|| |Screen Reader|yes| |VM|0%|

Process Info

``` CPU % Mem MB PID Process 0 244 30956 azuredatastudio main 0 83 556 ptyHost 0 7 2920 console-window-host (Windows internal process) 0 103 41344 "C:\Program Files\PowerShell\7\pwsh.exe" -noexit -command "try { . \"c:\Users\invand\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\contrib\terminal\browser\media\shellIntegration.ps1\" } catch {}" 0 98 5904 shared-process 0 932 6464 window [1] (● SQL Server 2019 Diagnostic Information Queries_part 1_instance level queries.ipynb - Untitled (Workspace) - Azure Data Studio) 0 94 18964 window [2] (Issue Reporter) 0 182 19008 extensionHost [1] 0 46 7732 c:\Users\invand\.azuredatastudio\extensions\microsoft.kusto-0.5.8\sqltoolsservice\Windows\3.0.0-release.133\MicrosoftKustoServiceLayer.exe --locale en --log-file c:\Users\invand\AppData\Roaming\azuredatastudio\logs\20230801T114525\window1\exthost\Microsoft.kusto\kustoService.log --tracing-level Critical 0 11 9612 console-window-host (Windows internal process) 0 43 7868 "c:\Users\invand\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\4.8.1.5\MicrosoftSqlToolsCredentials.exe" --application-name azuredatastudio --data-path C:\Users\invand\AppData\Roaming --locale en --log-file c:\Users\invand\AppData\Roaming\azuredatastudio\logs\20230801T114525\window1\exthost\Microsoft.mssql\credentialstore.log --tracing-level Critical --autoflush-log 0 11 20508 console-window-host (Windows internal process) 0 45 11336 "c:\Users\invand\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\4.8.1.5\SqlToolsResourceProviderService.exe" --application-name azuredatastudio --data-path C:\Users\invand\AppData\Roaming --locale en --log-file c:\Users\invand\AppData\Roaming\azuredatastudio\logs\20230801T114525\window1\exthost\Microsoft.mssql\resourceprovider.log --tracing-level Critical --autoflush-log 0 11 40832 console-window-host (Windows internal process) 0 82 19568 "C:\Users\invand\AppData\Local\Programs\Azure Data Studio\azuredatastudio.exe" "c:\Users\invand\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\json-language-features\server\dist\node\jsonServerMain" --node-ipc --clientProcessId=19008 0 198 34028 "c:\Users\invand\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\4.8.1.5\MicrosoftSqlToolsServiceLayer.exe" --application-name azuredatastudio --data-path C:\Users\invand\AppData\Roaming --locale en --log-file c:\Users\invand\AppData\Roaming\azuredatastudio\logs\20230801T114525\window1\exthost\Microsoft.mssql\sqltools.log --tracing-level Critical --autoflush-log --enable-sql-authentication-provider 0 11 30520 console-window-host (Windows internal process) 0 46 39804 c:\Users\invand\.azuredatastudio\extensions\microsoft.azuremonitor-0.1.9\sqltoolsservice\Windows\3.0.0-release.133\MicrosoftKustoServiceLayer.exe --locale en --log-file c:\Users\invand\AppData\Roaming\azuredatastudio\logs\20230801T114525\window1\exthost\Microsoft.azuremonitor\azuremonitorService.log --service-name AzureMonitor 0 11 40892 console-window-host (Windows internal process) 0 171 19248 gpu-process 0 43 21240 utility-network-service 0 76 36684 fileWatcher [1] ```

Workspace Info

``` | Window (● SQL Server 2019 Diagnostic Information Queries_part 1_instance level queries.ipynb - Untitled (Workspace) - Azure Data Studio) | Folder (_prepare to share): 706 files | File types: sql(221) sqlplan(177) csv(83) pbix(37) xel(34) xlsx(21) | txt(15) ipynb(14) ps1(13) json(12) | Conf files:; ```

Extensions (23)

Extension|Author (truncated)|Version ---|---|--- firstresponderkit|dre|0.6.0 qpi|joc|0.1.6 admin-pack|Mic|0.0.2 admin-tool-ext-win|Mic|0.1.3 agent|Mic|0.49.0 arc|Mic|1.8.0 azcli|Mic|1.8.0 azuremonitor|Mic|0.1.9 cms|Mic|0.9.3 dacpac|Mic|1.14.0 import|Mic|1.5.5 kusto|Mic|0.5.8 managed-instance-dashboard|Mic|0.4.2 powershell|ms-|2022.7.2 profiler|Mic|0.14.0 schema-compare|Mic|1.20.0 sql-assessment|Mic|0.6.4 sql-database-projects|Mic|1.2.0 sql-migration|Mic|1.4.9 sql-dw|Mic|0.0.1 azdata-sanddance|msr|4.0.1 schema-visualization|R0t|0.8.2 poor-sql-formatter|WSR|0.1.0

We have written the needed data into your clipboard because it was too large to send. Please paste.

[Charles-Gagnon]

@ingebeumer While we investigate this, do you know if this is a recent regression or have you observed this behavior in the past?

[Charles-Gagnon]

One thing I noticed while looking into this is that we don't seem to be properly cleaning up resources when an editor is closed - with the result that closing and reopening a large Notebook (such as one of the ones attached) ends up constantly increasing memory usage.

This is happening in 1.44.1 though, so isn't a regression with 1.45 at least.

[ingebeumer]

Thank you for reviewing @Charles-Gagnon. It is not a recent regression; I have observed this behavior before working with multiple customers.

[Charles-Gagnon]

Thank you for confirming! We'll be taking a look at perf-related issues such as this going forward and will respond with any updates here.