Press "Enter" to skip to content

Category: SQL Server Management Studio

Creating Custom T-SQL Snippets for SSMS

Published 2020-11-24 by Kevin Feasel

Dave Mason follows up on yesterday’s post:

I have a number of scripts and queries I’ve written and curated over the years. They’re not organized that well, scattered across different folders. Some are named poorly or grouped in a questionable manner. There are a handful that I tend to use the most. And yet with that small number, I sometimes have difficulty quickly finding a particular script (if I can find it at all), or spending too many mouse clicks to find it. It dawned on me recently to make use of code snippets.

Code snippets may have been intended primarily to aid code writing, but they can assist with administrative tasks too. 

These can provide a considerable benefit for data platform specialists.

Comments closed

T-SQL Snippets in Management Studio

Published 2020-11-23 by Kevin Feasel

Dave Mason uses an external memory:

There are certain T-SQL statements whose syntax I have trouble remembering. When those situations arise, I might look up the syntax online; find the same type of object in SSMS, right-click it, script out the object, and use that as a starting point; or find one of my own scripts that has the syntax I’m looking for. Another option that I often overlook is T-SQL code snippets.

Click through to see how to use code snippets in SQL Server Management Studio. You can also create your own as well.

Comments closed

SSMS 18.7.1 Released

Published 2020-11-03 by Kevin Feasel

Glenn Berry takes us through the latest edition of SQL Server Management Studio:

One big change with SSMS 18.7 is described by Microsoft this way:

“Beginning with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically installed alongside SSMS. Users of SQL Server Management Studio are now able to benefit from the innovations and features in Azure Data Studio. Azure Data Studio is a cross-platform and open-source desktop tool for your environments, whether in the cloud, on-premises, or hybrid.“

So far, this has been a pretty controversial change. Erik Darling created a User Voice suggestion on October 20th that has already gotten over 234 votes, and many comments.

I’m not going to weigh in too much here, though I would prefer this to be an optional installation. Do watch out for an annoyance, though, if you have Azure Data Studio installed as a User instead of System.

Comments closed

Working Around an SSMS Extended Events Bug

Published 2020-10-27 by Kevin Feasel

Emanuele Meazzo finds a bug:

1. Open the “New Session…” GUI for extended events in SSMS
2. Give it a name, it’s not important
3. Add an event with a Duration Field, I’ve added sp_statement_completed and configure it to be higher than an arbitrary value

And then watch it go boom in SSMS 18.7. Click through for a demo of the issue and a workaround.

Comments closed

SSMS and Ignoring Certain Waits

Published 2020-10-27 by Kevin Feasel

Erik Darling has a plea to the SQL Server Management Studio team:

Lock waits are particularly annoying. Imagine (I know, this might be difficult) that you have a friend who is puzzled by why a query is sometimes slow.

They send you an actual plan for when it’s fast, and an actual plan for when it’s slow. You compare them in every which way, and everything except duration is identical.

It’d be a whole lot easier to answer them if LCK waits were collected, but hey. Let’s just make them jump through another hoop to figure out what’s going on.

CXCONSUMER has a similar problem — and here’s the thing — if people are going through the trouble of collecting this information, give’em what they ask for. Don’t just give them what you think is a good idea.

Click through to see the issue and what you can do to work around this limitation.

Comments closed

SQL Server Management Studio 18.7

Published 2020-10-21 by Kevin Feasel

Drew Skwiers-Koballa announces SQL Server Management Studio version 18.7 is now generally available:

Policy-based management is accessed in SQL Server Management Studio under “Management” in the object explorer as “Policy Management”. Getting started with policy-based management can be accelerated by importing the sample policies available for SQL Server. In September, these policies were added to the open source collection of SQL Server samples to facilitate their use and improvement. You can access these sample policies on the GitHub repository and your contributions to these best practices are welcome. For more information on Policy-Based Management, please check out the documentation.

I think Policy-Based Management is one of the biggest missed opportunities in SQL Server. They came out with a good start in 2008 but the product stagnated after that and it remains under-utilized as a result. Perhaps open-sourcing the policies will help, as the key problem with PBM was how limited it was.

Comments closed

Generating Scripts in SSMS and ADS

Published 2020-09-08 by Kevin Feasel

Rob Farley walks us through automation options in SQL Server Management Studio and Azure Data Studio:

User interfaces are great, but I simply don’t want to have to remember to do everything the same way each time.

To that end, I want to wax lyrical for a moment about the Script button on most dialog boxes in SQL Server Management Studio (SSMS), and make a quick mention of what things (currently) look like in Azure Data Studio (ADS). (I say “currently” because ADS is still under very active development and could have changed even by the time I publish this.)

The Script button is one of the best features of Management Studio. The UI lets you know what can be done, and the Script button lets you know the process. It’s also a nice reminder that this functionality is not unique to the UI. Management Studio’s implementation of the Script button isn’t perfect, but it is good.

Comments closed

Recovering SSMS Scripts After a Crash

Published 2020-08-03 by Kevin Feasel

Jonathan Kehayias shows where those recovery scripts for SSMS are located:

It happens to the best of us and this post is more of a reminder for myself the next time it happens to me than anything else.  You are working in SQL Server Management Studio, you have a few tabs open (OK, it was 123 this time, but lets stay focused on the purpose of this blog post – You should see my Desktop and all the icons on top of other icons…), and then suddenly you get the dreaded SSMS has stopped responding/crashed window.  Sure you have been saving the important things along the way, but there are plenty of tabs that were just working queries for analysis that don’t really need to be saved but you still need them.  What do you do?

Even if you do use a tool like SSMS Tools Pack or DevArt’s SQLcomplete (which is what I use), it is still good to know where these scripts are just in case. I’ve also noticed that Azure Data Studio has been quite a bit better about maintaining scripts on close.

Comments closed

SQL Server Management Studio 18.6 Now GA

Published 2020-07-23 by Kevin Feasel

Drew Skwiers-Koballa announces SQL Server Management Studio version 18.6:

The 18.6 release is the second major release of SSMS in 2020 and packs several high impact changes, including a fix for crashes in database diagrams. Key fixes include:  

– Save to XEL file error fix. 
– Bacpac file import error fix. 
– Database diagrams crash fix. 
– Addressed sources of three common application hangs. 

Lots of bugfixes in here, but there are a few new things as well.

Comments closed