Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Database Folder not Expanding #6450

Closed
joelvega opened this issue Jul 22, 2019 · 41 comments
Closed

Database Folder not Expanding #6450

joelvega opened this issue Jul 22, 2019 · 41 comments
Assignees
Labels
Area - Object Explorer Bug Impact: Performance Triage: Done Triage: Postponed Items that were planned for a release but didn't make it for one reason or another.

Comments

@joelvega
Copy link

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

Issue Type: Performance Issue

With connecting to an Azure SQL Database, the Database folder will not expand. It will always timeout.

Azure Data Studio version: azuredatastudio 1.9.0 (78a42e1, 2019-07-10T04:31:36.998Z)
OS version: Windows_NT x64 10.0.17763

System Info
Item Value
CPUs Intel(R) Core(TM) i9-9900K CPU @ 3.60GHz (16 x 3600)
GPU Status 2d_canvas: enabled
checker_imaging: disabled_off
flash_3d: enabled
flash_stage3d: enabled
flash_stage3d_baseline: enabled
gpu_compositing: enabled
multiple_raster_threads: enabled_on
native_gpu_memory_buffers: disabled_software
rasterization: enabled
surface_synchronization: enabled_on
video_decode: enabled
webgl: enabled
webgl2: enabled
Load (avg) undefined
Memory (System) 31.92GB (16.32GB free)
Process Argv
Screen Reader no
VM 0%
Process Info
CPU %	Mem MB	   PID	Process
    1	    67	 67428	azuredatastudio main
    1	    76	  2236	   window (Issue Reporter)
    1	   230	 29960	   window (Azure Data Studio)
    0	   109	 68796	     extensionHost
    0	    69	 22236	       "c:\Users\JVega\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\1.5.0-alpha.105\MicrosoftSqlToolsServiceLayer.exe" --log-file C:\Users\JVega\AppData\Roaming\azuredatastudio\mssql\sqltools_68796.log --tracing-level Critical
    0	     7	 26060	         console-window-host (Windows internal process)
    0	    15	 60868	       "c:\Users\JVega\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\1.5.0-alpha.105\SqlToolsResourceProviderService.exe" --log-file C:\Users\JVega\AppData\Roaming\azuredatastudio\mssql\resourceprovider_68796.log --tracing-level Critical
    0	     7	  3852	         console-window-host (Windows internal process)
    0	    18	 66156	       "C:\Users\JVega\AppData\Local\Programs\Azure Data Studio\azuredatastudio.exe" "c:\Users\JVega\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\json-language-features\server\dist\jsonServerMain" --node-ipc --clientProcessId=68796
    0	    15	 69092	       "c:\Users\JVega\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\sqltoolsservice\Windows\1.5.0-alpha.105\MicrosoftSqlToolsCredentials.exe" --log-file C:\Users\JVega\AppData\Roaming\azuredatastudio\mssql\credentialstore_68796.log --tracing-level Critical
    0	     7	 35800	         console-window-host (Windows internal process)
    0	    47	 35700	   shared-process
    0	   103	 63632	   gpu-process
Workspace Info
;
Extensions: none
@adsbot
Copy link

adsbot bot commented Jul 22, 2019

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

@Charles-Gagnon
Copy link
Contributor

Could you please paste the contents of the original report? Does this happen consistently and is there any error displayed when you try to expand or does it just sit there spinning forever?

@joelvega
Copy link
Author

Could you please paste the contents of the original report? Does this happen consistently and is there any error displayed when you try to expand or does it just sit there spinning forever?

I copied and pasted the entire error message and results in the original post. This problem does happen consistently since I have applied an update from Azure Data Studio. After spinning from some time, it gives the error message "Object Explorer task didn't complete within 45 seconds."

I am able to right-click on the database and select Manage and then I instantly get a list of the table and I can do my work from there. This issue seems to be isolated to opening the database folder in the object explorer.

@MaddyDev MaddyDev added the Bug label Jul 23, 2019
@juniordeveloperbootcamp

I have a simlar issue after update to version 1.9.0. Please take a look at the screenshots provided
Screen Shot 2019-07-25 at 10 50 07 AM

if I right click database folder and choose to manage, then it opens database tables and SPs in the dashboard view

Screen Shot 2019-07-25 at 10 50 54 AM

@chaibloom
Copy link

chaibloom commented Nov 7, 2019

It would be nice if this bug got more love. We have Azure servers hosting 5000 DBs, and they always time out when trying to open ServerName/Databases in the connections pane. While it can take a bit to populate that data in SSMS, it does eventually come up, and typically in less than 45 seconds.

@aaomidi
Copy link
Contributor

aaomidi commented Nov 11, 2019

@chaibloom which version of ADS are you using?

@chaibloom
Copy link

@aaomidi - 1.13.0

@fluggo
Copy link

fluggo commented Nov 26, 2019

I have this same issue in 1.13.1. Like juniordeveloperbootcamp, if I choose to "Manage" and then refresh the Object Explorer view, I am able to see the tables and views. Otherwise, it times out.

Server I'm connecting to is version 14.0.3045.24.

@nvonbenken
Copy link

I get this issue as well from time to time on 1.15.0-insider

@bew4lsh
Copy link

bew4lsh commented Jan 29, 2020

Experiencing this issue at version 1.14.1. Would love to use this product in place of ssms, but this basic feature's incompatibility with azure SQL strikes me as disqualifying. 'Azure' data studio, huh.

@luislozanodev
Copy link

same issue here. connecting to SQL 2014 and can't expand the database folders since I install Azure data studio a week ago. another issue, I deleted the Server Dashboard Search tables widget and i can't get it back, any ideas how to? I only have the tasks widget. see below. I appreciate any help.

Capture

@Charles-Gagnon
Copy link
Contributor

@xspdr1800 - You can reset that by opening the command pallet and running the Preferences: Open Settings (JSON) command. Then find the "dashboard.server.widgets" value and delete it and everything in the square brackets after it. This will be something like this :

"dashboard.server.widgets": [
    {
        "name": "Tasks",
        "widget": {
            "tasks-widget": [
                "newQuery",
                "mssqlCluster.task.newNotebook",
                {
                    "name": "restore",
                    "when": "!mssql:iscloud && mssql:engineedition != 11"
                },
                "configureDashboard"
            ]
        },
        "gridItemConfig": {
            "sizex": 1,
            "sizey": 1
        }
    },
    {
        "widget": {
            "backup-history-server-insight": null
        }
    },
    {
        "widget": {
            "all-database-size-server-insight": null
        }
    }
]

Then re-open the dashboard and the widget should appear again.

@alanrenmsft @kisantia This is pretty annoying that there doesn't seem to be an easy way to reset the default dashboard widgets - let's make sure we have that be easy with the new Dashboard refactor.

@cairneym
Copy link

cairneym commented Mar 25, 2020

Also experiencing the expansion timeout with 500 databases on Azure SQL DB. Connection to the server is fine and the dashboard shows the full list of databases alsmost instantaneously.

However trying to expand the databases node on Object Explorer results in the 45 seconds timeout.

Is the timeout value configurable in the settings anywhere?

Version 1.15.1

@alanrenmsft
Copy link
Contributor

@xspdr1800 - You can reset that by opening the command pallet and running the Preferences: Open Settings (JSON) command. Then find the "dashboard.server.widgets" value and delete it and everything in the square brackets after it. This will be something like this :

"dashboard.server.widgets": [
    {
        "name": "Tasks",
        "widget": {
            "tasks-widget": [
                "newQuery",
                "mssqlCluster.task.newNotebook",
                {
                    "name": "restore",
                    "when": "!mssql:iscloud && mssql:engineedition != 11"
                },
                "configureDashboard"
            ]
        },
        "gridItemConfig": {
            "sizex": 1,
            "sizey": 1
        }
    },
    {
        "widget": {
            "backup-history-server-insight": null
        }
    },
    {
        "widget": {
            "all-database-size-server-insight": null
        }
    }
]

Then re-open the dashboard and the widget should appear again.

@alanrenmsft @kisantia This is pretty annoying that there doesn't seem to be an easy way to reset the default dashboard widgets - let's make sure we have that be easy with the new Dashboard refactor.

@Charles-Gagnon I could find the original comment provided by @xspdr1800, but I think we can do it by adding a task 'Restore settings' to server and database dashboards, the workflow would be:

  1. click the 'Restore settings' task button in the tasks widget
  2. prompt the user with impact of the action and ask for confirmation (using standard vscode popup notification)
  3. remove the customization done by user

I will run this by the team in our next dashboard improvement meeting this week.

@tbolon
Copy link

tbolon commented Apr 29, 2020

This error still occurs on latest version (1.17). Was already submitted in #3270.

As suggested in the other issue, the database listing is instant when using the server details, but fails when using the left pane browser. For just displaying the database nodes, the query you execute could be optimized.

It's not really fun to exclude all people using Azure SQL Database with more than a few hundreds database from using your tool.

Perhaps at least you could provide a simple way (raw setting) to increase the timeout for this query ?

@perrydyball
Copy link

Still an issue in 1.19

Changing to using full fat SQL SSMS

@tbolon
Copy link

tbolon commented Jul 16, 2020

Still occurring on latest version with less than 50 databases on an Azure Sql Database server. Same error: the server browser tries to connect to all databases one after another with a global timeout of 45 seconds. Unsurprisingly, it does not scale well when you have any serious amount of databases. And it works perfectly well when you use the Manage Server pane to list all databases (certainly only using master db to populate this list and restraining itself from doing any operation on each database while enumerating).

I am surprised that this issue is not given any attention, since it completely ruins basic Azure Data Studio usage when connecting to Azure Sql Database Server with more than any trivial amount of databases.

@alanrenmsft
Copy link
Contributor

@abist could you please take a look?

@abist
Copy link
Contributor

abist commented Jul 16, 2020

@alanrenmsft I don't think I will be able to work on this for August release, this is a perf issue and a non-trivial task

@hfleitas
Copy link

From terminal, click output, Log (Window)

authenticationType:SqlLogin|database:|server:SQLSVR2019|user:hiram|group:37244e90-6961-4ab4-b7d2-dfaa19ef5e44
[2020-10-23 15:46:08.088] [renderer1] [info] Adding connection with DB name connection:providerName:MSSQL|applicationName:azdata|authenticationType:SqlLogin|database:hiramdb|server:SQLSVR2019|user:hiram|group:37244e90-6961-4ab4-b7d2-dfaa19ef5e44
[2020-10-23 15:46:08.169] [renderer1] [error] An unknown error occurred. Please consult the log for more details.
[2020-10-23 15:46:08.170] [renderer1] [error] Cannot read property 'isCloud' of null: TypeError: Cannot read property 'isCloud' of null
	at t.MssqlIconProvider.getConnectionIconId (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\mssql\dist\main.js:411:37875)
	at d.$getConnectionIconId (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:697:248)
	at g._doInvokeHandler (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:969:707)
	at g._invokeHandler (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:969:399)
	at g._receiveRequest (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:968:59)
	at g._receiveOneMessage (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:966:886)
	at c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:965:34
	at l.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:48:475)
	at v.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:259:381)
	at c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:1149:963
	at l.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:48:475)
	at v.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:259:381)
	at t.PersistentProtocol._receiveMessage (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:264:451)
	at c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:261:489
	at l.fire (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:48:475)
	at p.acceptChunk (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:256:851)
	at c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:256:203
	at Socket.t (c:\Users\hfleitas\AppData\Local\Programs\Azure Data Studio\resources\app\out\vs\workbench\services\extensions\node\extensionHostProcess.js:266:54)
	at Socket.emit (events.js:223:5)
	at addChunk (_stream_readable.js:309:12)
	at readableAddChunk (_stream_readable.js:290:11)
	at Socket.Readable.push (_stream_readable.js:224:10)
	at Pipe.onStreamRead (internal/stream_base_commons.js:181:23)
[2020-10-23 15:46:08.574] [renderer1] [error] Failed to create Object Explorer session

@Charles-Gagnon
Copy link
Contributor

@hfleitas I think your issue is more along the lines of #12992

Has this also recently just started happening for you? (as in with the latest release)

I'm going to leave instructions for some further logs to provide in the linked issue - the issue seems to be in a separate part of the application. So if you could get me those that would be helpful.

@hfleitas
Copy link

@Charles-Gagnon yes, this is recently occurring for me and my team. I'll send the logs in the linked issue. Thx

@hastiAu
Copy link

hastiAu commented Jan 9, 2021

hi @Charles-Gagnon
I find this "dashboard.server.widgets":

but I cant delete it
how can I delete?

@silverspringhusker
Copy link

I am experiencing the same thing as referenced in 13836

I have no problem in SSMS expanding Databases and within a database, Tables. But in both ADS 1.27.0 for Mac and Windows 10, clicking on Databases doesn't show anything. However, in both Windows and Mac, in the Connection Details pane, I can choose a database from the Database drop-down. But once I establish a connection to a particular database, I can't see any properties or anything else for that database. On this server, unlike others where I have not experienced this issue, I don't have sysadmin privileges, just public, with db_owner privileges on the individual databases. (These are all on-premise SQL Servers, using integrated authentication from both Mac and Windows)

One possible additional clue. I just tried establishing a connection in IntelliJ IDEA to the problematic database server. I am using the Microsoft JDBC Driver 9.2 for SQL Server with integrated authentication. If I specify the database, I can browse only the tables in the dbo. schema (including sysdiagrams), none of the tables in other schemas.

@silverspringhusker
Copy link

I am experiencing the same thing as referenced in 13836

I have no problem in SSMS expanding Databases and within a database, Tables. But in both ADS 1.27.0 for Mac and Windows 10, c

I am no longer experiencing this issue in ADS 1.28.0.

@kburtram kburtram added Triage: Postponed Items that were planned for a release but didn't make it for one reason or another. and removed Approved labels May 12, 2021
@kburtram kburtram modified the milestones: May 2021 Release, Backlog May 12, 2021
@tbolon
Copy link

tbolon commented May 18, 2021

This error is still there in the latest version. Please note that in my case it seems to be a performance problem, and not a regression. See #3270 (2018) for the initial report. Azure Data Studio has never been able to browse our Azure SQL Server.

See this full reproduction cases for our server. You can observe:

  • that the "Manage > Search" pane works perfectly and list all databases within a few seconds, without any virtualization on the list
  • that the server has (only) 450 databases
  • that the database folder times out

Animation2

For me there is a N+1 queries problem, where the folder expansion time is relative to the number of databases, or something running for each database in the node list... which is quite problematic.

@JamesAlexander42
Copy link

I've noticed this only occurs to me if I have a Serverless SQL Azure DB that's asleep. If I refresh it after a couple minutes it works just fine. Why would Serverless DB's be an issue?

@kburtram kburtram added this to Object Explorer in ADS Core Committer and Fundamentals Aug 16, 2021
@ddejohn
Copy link

ddejohn commented Mar 15, 2022

Still having this issue in 2022 with 1.35.0

@Charles-Gagnon Charles-Gagnon self-assigned this Apr 27, 2022
@Charles-Gagnon
Copy link
Contributor

@kburtram FYI

I've been looking into this and have identified what seems to be the main cause of the issue - SMO is currently attempting to connect to every database when the databases folder is expanded. This can easily cause timeouts on Azure servers with many DB's.

We're currently working on addressing that by doing a best attempt to not require connecting to every DB on a server unless a db-specific property is accessed (which ADS isn't doing currently). That should help address the most common timeouts that people are having.

There's still an issue that if any of the databases are inaccessible for some reason then SMO will attempt to connect to that database multiple times (retry logic was added for serverless scenarios among other reasons), but if the DB is completely inaccessible then we have to wait for the login attempt to time out which can take 5+seconds for each attempt and thus very quickly go over the 45sec limit.

Unfortunately this second one is a lot trickier to solve - we're looking at ways to do that but unfortunately there doesn't seem to be a way that we can do that without breaking other scenarios. The only true way to solve this completely would be to use SFC queries directly (like SSMS does) to populate the list and only when a database node itself is expanded use the full SMO DB object. This would require a bigger refactor of the core OE logic though so I'm planning on getting these SMO changes in and seeing how much of an improvement we're seeing there, and then looking into the refactoring if there's still enough cases of people hitting the timeout issues.

@firxworx
Copy link

Interesting insight @Charles-Gagnon.

Since you mentioned "many Azure DB's" I wanted to confirm that this issue can be encountered with very few DB's. To add more context -- this issue totally prevented adoption of Azure Data Studio in an environment where I don't recall more than a handful of SQL servers (the company in question was connecting to SQL Server on Windows-powered AWS EC2 instances via VPN). It was a while back now (what an old issue!) but IIRC it was 3. I wanted to offer it over SSMS because it could also connect to a couple Postgres servers and train users on a common client/tool that would (or was at least expected to) play really nice with SQL Server.

@Charles-Gagnon
Copy link
Contributor

Thanks for the info, I'll be adding more logging too so we can track down exactly what issues are in different cases which should help.

You're saying that you were seeing this even with on-prem instances on a VM? That's definitely pretty odd - the investigation I'm doing is centered around Azure since that's where we get the most latency issues since we haven't seen an indication that box/standard SQL instances have these same issues.

@xaxum
Copy link

xaxum commented Jul 19, 2022

For Azure SQL with 10 DBs I can confirm if a serverless DB is asleep it fails to show the databases in the connections window. If I wake up the database and they all are online it shows the DBs pretty quickly without issue.

@cheenamalhotra
Copy link
Member

Issue is now fixed with #21475 addressed.
The latest insiders build contains fix, it will be available in the next stable release.

@cheenamalhotra cheenamalhotra moved this from Object Explorer to Done in ADS Core Committer and Fundamentals Jul 12, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Area - Object Explorer Bug Impact: Performance Triage: Done Triage: Postponed Items that were planned for a release but didn't make it for one reason or another.
Projects
Development

No branches or pull requests