Site icon Glenn's SQL Server Performance

SSMS 19.1 Performance Improvements

Glenn Berry

On May 24, 2023, Microsoft released SQL Server Management Studio 19.1. The release notes are here, and they include this item:

SSMS 18.X cold startup performance is a big issue for me, since I regularly have to start SSMS 18.X on various small VMs running on older, slower server-class processors. BTW, “cold startup” is the first time that a program is loaded into a process since the last reboot of the operating system, so nothing from that program is already loaded in memory.

The cold startup process can often take several minutes on some VMs, so I waste a significant amount of time every day looking at the SSMS splash screen, waiting for the initial “Connect to Server” dialog to appear.

SSMS 19.1 Connect to Server Dialog

I had noticed that SSMS 19.1 seemed to startup noticeably faster than older releases, but maybe that was just wishful thinking on my part. Rather than speculate, I decided to do some testing, comparing the cold startup performance of SSMS 19.1 to SSMS 18.12.1 on six different bare metal desktop machines in my lab (that all had different CPUs). I wanted to confirm whether there were any SSMS 19.1 performance improvements.

Testing Methodology

SSMS 18.X and SSMS 19.X can exist side-by-side with no problems, which made this testing effort easier. Here is how I went about it:

Test Machines

My real world observations of SSMS 18.X cold startup performance over an extended period led me to believe that it was a CPU-bound process. The amount and speed of RAM and the storage performance for the drive where SSMS was installed seemed to have very little effect on the elapsed time for cold startup.

Single-threaded CPU performance seemed to be the biggest bottleneck, followed by multi-threaded CPU capacity/performance to a lesser extent. For example an 8 vCPU VM will load SSMS faster than a 4 vCPU VM (where both VMs had the same host CPU). Having a “fast” host CPU (with good single-threaded CPU performance) makes a big difference!

I used six different desktop machines for these tests:

This includes three different generations of AMD machines and two generations of Intel machines. Even the slowest client machine in the test (the AMD Ryzen 3 3300X) has significantly faster single-threaded CPU performance than most server processors.

The AMD machines were all running Windows 10 Professional 22H2, fully patched, along with the lastest native drivers and firmware. The Intel machines were running Windows 11 Professional 22H2, fully patched, along with the lastest native drivers and firmware. In addition, the Windows machines had their P-cores and E-cores enabled. All of the machines had XMP/DOCP enabled to get better RAM performance.

I also ran the quick and dirty CPU-Z CPU benchmark test on each machine, just to get a rough comparision of the ST and MT CPU performance of each processor. Spoiler alert: SSMS cold startup time seems to track pretty closely to CPU-Z Single Thread benchmark scores.

CPU-Z Bench Scores

Test Results

As you can see, SSMS 19.1 cold startup performance was faster than SSMS 18.12.1 on all six processors. The elapsed time reduction ranged from 10.3% to 24.8%. Keep in mind that most server processors have lower single-threaded CPU performance than these desktop processors. This means that they will probably see a larger reduction in cold startup elapsed times with SSMS 19.1

SSMS cold startup performance seems to correlate pretty well to CPU single-threaded performance as measured by CPU-Z.

One interesting point is the difference between the Zen 2 8C/16T AMD Ryzen 7 3700X and the Zen 2 4C/8T AMD Ryzen 3 3300X. They both have nearly identical ST performance, but the higher core count 3700X can startup SSMS more quickly.

Updates From Microsoft

Erin Stellato has a couple of blog posts (so far) that go into more detail about improvments and changes in SSMS 19.1

Final Words

I think these test results help confirm that SSMS 19.1 has better cold startup performance than SSMS 18.12.1 in most scenarios. The actual difference is not that great if you have an extremely fast CPU, but many people don’t have that luxury. If you are running SQL Server in a cloud VM or you are using a jumpbox to run SSMS, you might have a much slower CPU than any of the client CPUs that I tested.

I really want to thank Microsoft’s Erin Stellato and her team for getting this performance improvement into SSMS 19.1! This is yet another reason to upgrade to SSMS 19.1, even if you are using an older version of SQL Server (SQL Server 2014 and newer).

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!

Exit mobile version