Press "Enter" to skip to content

Category: SQL Server Management Studio

SSMS 19.1 Startup Performance Improvements

Glenn Berry breaks out the stopwatch:

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 hero image alone convinced me to install 19.1.

Leave a Comment

Stopping an SSIS Package from the SSIS Catalog

Olivier Van Steenlandt hits the big red button:

I developed my package in Visual Studio 2019 and deployed my changes to my SSIS Catalog (SSISDB in my case). As soon as my SSIS Package was deployed I started the SSIS Package execution.

After a couple of minutes, I realised I made a mistake. I didn’t want to lose too much time, so I looked for a way to stop the SSIS Package Execution.

Since I was not executing the package from a SQL Server Agent Job, I couldn’t just stop the job. So I needed to find another way.

Click through for a way to do this via SQL Server Management Studio.

Comments closed

April Tools Day

Erin Stellato dispels some myths:

Myth #1 Azure Data Studio is the only standalone solution now that SSMS is deprecated.

SQL Server Management Studio (SSMS) is not deprecated.  We thought about writing that in ALL CAPS, but figured bold is sufficient.  SSMS has not been deprecated, and we are not planning on deprecating it.  You will see new functionality being added to Azure Data Studio, but we have a fair number of things lined up for SSMS, including migration to the Visual Studio 2022 shell, which brings 64-bit support.

Bold plus all caps might have been a bit too much, yeah.

Click through to see what’s happening in the world of SQL Server tooling from Microsoft.

Comments closed

Changing the Browser SSMS Uses

Meagan Longoria doesn’t want the built-in browser:

Did you know that you can change the browser used by SQL Server Management Studio to authenticate using Azure Active Directory to a SQL database in Azure?

I had been experiencing serious delays with the window that pops up to accept my credentials taking 30 seconds or more to populate. I also once got a warning that the browser I was using was old.

Click through to see how to resolve annoyances around SSMS’s built-in browser and change to the default you use for everything else.

Comments closed

Handling Optional T-SQL Code via SQLCMD

Louis Davidson shows off a bit of SQL Server Management Studio’s SQLCMD mode:

In the creating a database script, there are quite a few parameters you will want as part of your script. The database name, the settings, whether or not to drop the database or not. Built into SQL Server Management Studio is a cool tool called SQLCMD mode. Scripts using this mode get a few scripting tools that are really useful. These tools allow you to do things like insert other script files, set environment variables, and one I particularly like, make sure you don’t accidentally execute a script if no code is highlighted using EXIT to start your script ().

Where it is really lacking is in the area of control of flow language. In this blog I am going to share a few techniques I have used to get around this when building scripts that need to optionally execute different bits of code.

Click through to see how you can use it. Going one step further, you can directly script against sqlcmd.exe, which can be useful for automating deployments.

Comments closed

ORDER BY Clause in Subqueries and SSMS Warnings

Ronen Ariely explains a warning message:

Warning: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself. Click CANCEL to discard your modifications. Click OK to save the view.

Read on for the full context of when you might see this warning message in SQL Server Management Studio.

Comments closed

SSMS 19.0 Released

Erin Stellato has a new version of Management Studio for us:

In addition, we received a lot of feedback about the dialog box, “Querying transaction count”, we introduced back in SSMS 18.11.   In 18.12, we added an option to cancel the check if it took more than two seconds, but user comments indicated that was not enough.  Therefore, in SSMS 19.0 you have the ability to disable to dialog box completely.  Within Tools > Options > Query Execution, simply uncheck the option “Check for open transactions before closing T-SQL query windows”.  

That alone might be worth it, especially if you work via VPN and have a bunch of SSMS tabs open.

Comments closed

SSMS and Additional Connection Parameters

Tom Zika gets spun around a bit:

The other day I managed to confuse myself. I was looking up some information from an Extended Events (XE) session, but my eyes were playing a trick on me. The database ids were off by one, and I couldn’t find some query hashes in the Query Store, even when they were supposed to be there. So my first thought was that I must be connected to a different server with a drift. But the information in the SSMS tab, status bar and even colour coding (courtesy of Redgate’s SQL Prompt) – all pointed to the correct server.

I don’t think I’ve ever run into this before but could see how it’d be really confusing.

Comments closed

Multiple Rows of Tabs in SSMS

Kevin Hill needs more than one row:

If you spend much time at all in SQL Server Management Studio (SSMS), you’ve no doubt had trouble figuring out which query tab is the one you need.

Many DBAs and SQL Devs find themselves (despite best intentions and New Years Resolutions) with dozens of tabs open, and many of them unsaved.

Kevin shows the built-in solution. My solution is Tabs Studio. I paid $50 for it and have zero qualms with the purchase.

Comments closed

Row Mode vs Batch Mode Plan Timings

Paul White notes a change in SQL Server 2022:

Batch mode plans report CPU and elapsed time spent within each individual operator.

Row mode plans report cumulative CPU and elapsed time for each operator including all its child operators.

This is confusing enough, but modern versions of SQL Server can produce execution plans with a mixture of batch mode and row mode operators in the same execution plan.

But there are some nice changes in SQL Server 2022 to improve the situation a bit.

Comments closed