SQL Query Execution takes long long time just to kickstart execution of the query

[EswarSaiTR]

Type: Performance Issue

Run any query even a simple select query

Azure Data Studio version: azuredatastudio 1.47.1 (b6f7beb01f92adaa4b79b6b6f3ac704e95cafe6e, 2024-01-10T02:15:02.628Z) OS version: Windows_NT x64 10.0.19045 Restricted Mode: No Preview Features: Enabled Modes:

System Info

|Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i7-10850H CPU @ 2.70GHz (12 x 2712)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: enabled_on
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)|31.61GB (12.90GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|

Process Info

``` CPU % Mem MB PID Process 0 99 35852 azuredatastudio main 0 101 16456 window [3] (Issue Reporter) 0 144 29352 gpu-process 0 224 30676 window [1] (● SQLQuery_1 - QA - Azure Data Studio) 0 45 33020 utility-network-service 0 103 38228 extensionHost [1] 0 163 28584 "c:\Program Files\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\4.10.2.1\MicrosoftSqlToolsServiceLayer.exe" --application-name azuredatastudio --data-path C:\Users\6112132\AppData\Roaming --locale en --log-file c:\Users\6112132\AppData\Roaming\azuredatastudio\logs\20240227T152306\window1\exthost\Microsoft.mssql\sqltools.log --tracing-level Critical --autoflush-log --parallel-message-processing --parallel-message-processing-limit 100 --enable-sql-authentication-provider --enable-connection-pooling 0 11 38296 C:\Windows\system32\conhost.exe 0x4 0 44 34824 "c:\Program Files\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\4.10.2.1\SqlToolsResourceProviderService.exe" --application-name azuredatastudio --data-path C:\Users\6112132\AppData\Roaming --locale en --log-file c:\Users\6112132\AppData\Roaming\azuredatastudio\logs\20240227T152306\window1\exthost\Microsoft.mssql\resourceprovider.log --tracing-level Critical --autoflush-log 0 11 38048 C:\Windows\system32\conhost.exe 0x4 0 43 38840 "c:\Program Files\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\4.10.2.1\MicrosoftSqlToolsCredentials.exe" --application-name azuredatastudio --data-path C:\Users\6112132\AppData\Roaming --locale en --log-file c:\Users\6112132\AppData\Roaming\azuredatastudio\logs\20240227T152306\window1\exthost\Microsoft.mssql\credentialstore.log --tracing-level Critical --autoflush-log 0 11 16124 C:\Windows\system32\conhost.exe 0x4 0 88 38424 shared-process 0 78 38656 fileWatcher [1] ```

Workspace Info

``` ; ```

Extensions (3)

Extension|Author (truncated)|Version ---|---|--- ssmskeymap|kev|1.1.0 import|Mic|1.6.0 azdata-sanddance|msr|4.1.1

[cheenamalhotra]

@EswarSaiTR

Are you connecting to a remote SQL instance where DNS could be responsible for slow connection time? https://github.com/microsoft/azuredatastudio/issues/18524#issuecomment-1852564787 for reference.

[EswarSaiTR]

@cheenamalhotra

Thats not the issue, SSMS is working very fast on the same instance.

[github-actions[bot]]

We need more info to debug your particular issue. If you could attach your logs to the issue (ensure no private data is in them), it would help us fix the issue much faster.

1. First open the Settings page, find the Mssql: Tracing Level setting and change that to All then restart Azure Data Studio.
2. Open command palette (Click View -> Command Palette) and run the command Developer: Set Log Level... and set the level to Trace.
3. Run the repro scenarios.

4. Collect the following logs:

   Console Logs

   • Open Developer Tools (Help -> Toggle Developer Tools)
   • Click the Console tab
   • Click in the log area and select all text (CTRL+A)
   • Save this text into a file named console.log and attach it to this issue.
   • Developer Tools can be closed via Help -> Toggle Developer Tools

   Application Logs

   • Open command palette (Click View -> Command Palette)
   • Run the command: Developer: Open Logs Folder
   • This will open the log folder locally. Please zip up this folder and attach it to the issue.
5. Now you can revert the Log/Tracing levels changes made in step 1 and step 2.