OS Redhat 7 Memory 100GB CPU usage 1% User db size 2 gb each on avg total 7 user databases SSMS installed on a jump server Azure Data studio is also installed App Gui on application server Latency between jump server to db server <1ms Latency between application server and db server <1ms all are in same network, only switch is between them all are physical machines
SSMS - When I connect to SSMS, the databases in object explorer takes around 30 seconds to populate. Opening database properties takes upto 50 seconds to open Query analyser/sqlcmd from SSMS runs "select name from sys.sysdatabases" instantly without delay
Azure data studio - Populates Databases list fairly quickly but takes more than a minute to show database properties.
App Gui - also have the same behaviour as SSMS.
From every location, querying seems fast enough but GUI is found to be slow.
I have other SQL Server on windows OS, the SSMS, Azure data studio behaviour is usual where they populate information on GUI instantly. We also have few database on Azure SQL Database and Azure VM, they also connect instantly and populate information instantly at SSMS GUI
What we want to achieve?
We need our App gui, SSMS to perform as fast as SQL Server on Windows OS at gui level.
So what could be the issue and how I can fix this?
I missed the most important part. The said instance is Express edition. So the super impressive hardware they put into it was not being fully utilised. Plus the default auto close feature.