1

Server information

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

Issue

  1. 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

  2. Azure data studio - Populates Databases list fairly quickly but takes more than a minute to show database properties.

  3. App Gui - also have the same behaviour as SSMS.

From every location, querying seems fast enough but GUI is found to be slow.

Different scenario

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?

Please help!

Edit -

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.

9
  • A quick one is to check that autoclose database setting is off. Feb 26 at 18:48
  • I think you're right, let me check real quick! Feb 26 at 18:51
  • Low latency doesn't help much if your connection speed is very low for whatever reason. One example would be mismatched duplex settings - one side at half duplex and the other at full will result in a really slow connection soon as there is a decent amount of traffic.
    – Brian
    Feb 26 at 18:53
  • @TiborKaraszi although I disabled it, not much changed. Feb 26 at 19:20
  • 3
    If a regular SELECT is normally quick, but some particular operation in some tool (Object Explorer ini SSMS for instance) is slow, then if you are really interested it is time to pull out your favorite trace tool. Be it Extended Events or Profiler/SQL Trace. Trace the SQL commands as see which one causes the slowdown. There should be some SQL command that is significant slower compared to a Win server. Then take it from there. Feb 27 at 16:53
0

Auto close was found to be the culprit and it was decided to turn off this option for all databases. Express edition have this feature on by default.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.