SQL Server Management Studio 22 Preview 3 was released 2 days ago, and since I didn’t have the time to try out the previous preview versions, I took it out for a spin and here are my first impressions so far.
Download
You can download the bootstrapper file named vs_ssms.exe and get more details about the installation from here.
You don’t need to uninstall your existing version(s) of SSMS. And you can run this preview side by side with any of the previous versions of SSMS, including SSMS 21.
Installation
The installation process is fairly similar to SSMS 21, so I won’t go over that in too much detail.
And, just to reiterate from my SSMS 21 post: the fact that SSMS 22 uses the Visual Studio installer experience does not mean that Visual Studio also needs to be installed, nor does this mean that SSMS now requires a VS license.
One thing to note is that, if you already have SSMS 21 installed, you have the option to copy workloads, components, settings, and extensions to SSMS 22.
Note also the mention about extending support for extensions from the VS Marketplace.
Even if I opted to copy the configuration from SSMS 21, I’m prompted to pick the workloads and components I want to install due to a change in one of the Package IDs.
Note the 3.67GB required for the full install, with an additional 1.08GB being used by the download cache.
And this is the list of individual components.
Initial startup
After the required reboot, I start SSMS 22 and I’m greeted by the same sign in window that SSMS 21 had, I opt to skip for now.
I’m also prompted to import settings and connection history from SSMS 21, which I opt to do.
But, even if I opted for that, the connection history still did not import, and I’m met by a blank connection menu.
It also looks like none of the settings (current theme, options) have been imported from SSMS 21.
And, judging by the fact that Ctrl+K+Q doesn’t do anything, it looks like the SQL Formatter extension also wasn’t copied from SSMS 21.
This is a bit disappointing at this point, but hopefully it gets addressed before SSMS 22 goes GA.
Update: this is a known/documented issue with this preview release and it will be addressed in a future preview release.
Changes at a glance
Redesigned icons, new themes, and GitHub Copilot chat menu
SSMS 22 comes with a bunch of new themes, with the default one being Cool Breeze (which reminds me of a theme Yahoo Messenger had back in the Windows 7 days).
Looks like some of the icons in the Standard bar (1) have been redesigned, and SSMS now offers GitHub Copilot integration (2).
This is good news for people (like myself) who don’t have access to an “Azure OpenAI Endpoint”.
I’ll get back to SQL Server Management Studio 22 Preview’s GitHub Copilot integration in a minute.
Refined Options Experience
SSMS 22 builds on the new Options experience introduced in the previous version of SSMS.
And it feels closer to a finished feature than how it felt in SSMS 21.
But there still are options that are only available in the classic Options experience.
In this case, the additional Text Editor options can be accessed by clicking on More Settings then on either File Extension or Editor Tab and Status Bar.
SSIS related connection options are back
Since SQL Server Integration Services capabilities have been reintroduced in SSMS 22, the related connection options are no longer grayed out.
Results grid can be zoomed in and out – Finally!
While the query editor could always be zoomed in/out with Ctrl + Scroll Up/Down, the results grid was rigid and required going into Options to change the font size and restarting SSMS for the changes to take effect.
Always a hassle, and sometimes not an option (you might have a result set that was specific to that point in time and you didn’t want to loose it).
Presenter Mode did help, but it sometimes wasn’t enough.
With SSMS 22, the results grid can be zoomed in and out without requiring any options changes or restarts.
Line numbers not enabled by default – update
Erin Stellato pointed out in a LinkedIn comment that line numbers are not actually enabled by default.
So, it appears that out of all my SSMS 21 configurations, only the line numbers setting got carried over to SSMS 22.
Startup time
I mentioned this for the SSMS 21 Preview so it’s only fair that I run the same test for SSMS 22 Preview 3 as well.
With my current setup* it takes 8.6 seconds from clicking on the icon until the connection dialog is available, 3 seconds faster than SSMS 21.
*At the time of writing this post, my configuration is as follows:
- CPU – AMD Ryzen 9 5950X 16 cores/32 threads
- RAM – 128GB 3200MT/s DDR4
- System drive – 1TB Samsung 980 Pro SSD
- GPU – NVIDIA RTX 4070 12GB GDDR6X
- Motherboard – Gigabyte B550 VISION D-P
- OS – Windows 11 Pro 24H2
GitHub Copilot
As I’ve mentioned earlier, SSMS 22 comes with GitHub Copilot.
Authentication is as straightforward as the one for Git in SSMS 21.
After that, you can ask Copilot anything you’re curious about and it may or may not be confidently incorrect the same way it is in VSCode.
Out of curiosity, I’ve asked Copilot what it has access to and this was its answer.
Asking about what predefined queries it can run returns a pretty long list with queries ranging from queries for resource usage, top resource intensive queries, configuration issues, backups, and more.
I asked Copilot “what’s the most resource intensive query that ran on this instance?” and it took me longer to explain what “ran” means than it would have taken me to just run sp_BlitzCache or PSBlitz.
Snark aside, I can see how it can be helpful in some situations, especially if you don’t have some diagnostics queries already at hand.
But if this is the amount of coaxing it usually needs, then it still feels half-baked.
Bonus question for Copilot
I couldn’t resist, and I asked this question in a couple of different ways previously, but Copilot insists that it doesn’t send any data back to Microsoft.
Query Hint Recommendation tool
This is the thing that caught my attention more than anything else in the announcement post.
Mainly because it instantly reminded me about Quest SQL Optimizer for Oracle.
Brent Ozar mentioned there was one for SQL Server as well, but I never had the chance to use it.
Small detour
For those of you who might not be familiar with Quest SQL Optimizer for Oracle, it worked like this:
- Pick an option (Query Rewrite, Plan Control, Optimize Indexes).
- Feed it a query.
- SQL Optimizer would then proceed to run that query multiple times with various changes (adding hints, changing join orders or moving filtering conditions around, creating and dropping various test indexes) between each run.
- When it was all out of ideas it would give you an overview of each run’s execution times with the one that had the best time and resource usage highlighted.
- You pick which option was the best and then you could implement it right from SQL Optimizer or it would give you the steps to implement it yourself.
Note, I haven’t used this in almost 4 years, so I might have missed something, but that’s the ballpark description of the process.
Back to the Query Hint Recommendation tool
I’m getting the sense that Microsoft wants to do something similar here, and hopefully they’ll build on it more.
Brent already has a post with a great demo of it.
But I figured it wouldn’t hurt to document my test as well.
I used the following query which isn’t something I’d expect to see running in production anyway.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | /*create index first*/ CREATE INDEX [comments_creationdate] ON [Comments]([CreationDate]) WITH (ONLINE=OFF, MAXDOP=0); GO /*some horrible query*/ SELECT [Co].[UserId], [Us].[DisplayName], [Us].[DownVotes], [Co].[Score], [Co].[CreationDate], [Co].[Text] FROM [dbo].[Comments] [Co] INNER JOIN [dbo].[Users] AS [Us] ON [Co].[UserId] = [Us].[Id] WHERE [Co].[CreationDate] BETWEEN '2009-08-01 16:24:57.767' AND '2009-09-01 16:24:57.767' AND [Co].[UserId] % 3 = 0; |
And the tool managed to find two combinations of hints that shaved off a decent amount of execution time.
For reference, the original execution time is 856 milliseconds.
And the version of the query with the best performing query hint suggestion looks like this:
| 1 2 3 4 5 6 7 8 9 10 11 12 | SELECT [Co].[UserId], [Us].[DisplayName], [Us].[DownVotes], [Co].[Score], [Co].[CreationDate], [Co].[Text] FROM [dbo].[Comments] [Co] INNER JOIN [dbo].[Users] AS [Us] ON [Co].[UserId] = [Us].[Id] WHERE [Co].[CreationDate] BETWEEN '2009-08-01 16:24:57.767' AND '2009-09-01 16:24:57.767' AND [Co].[UserId] % 3 = 0 OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'), FORCE ORDER, LOOP JOIN); |
The execution plan comparison between the original query (1) and the one with the suggested hints (2) looks like this:
Note the original plan is single-threaded, and does a clustered index scan on the Users table.
The second plan goes parallel and only does a clustered index seek on the Users table.
I’m not necessarily a fan of slapping multiple query hints together.
Luckily, the Query Hint Recommendation tool gives you the option to not try combined hint suggestions.
I purge the plan cache and buffer pool, and rerun the test with the newly checked option.
So, FORCE_LEGACY_CARDINALITY_ESTIMATION is the one that does the trick in this case.
And the execution plan comparison with the updated hint suggestion confirms this.
Conclusion
SQL Server Management Studio 22 Preview 3 comes with some nice features and improvements.
I find the Query Hint Recommendation tool really promising, and I hope the SSMS team will continue to bring improvements and features to it.