Press "Enter" to skip to content

Category: SQL Server Management Studio

SSMS 18.2 Available

Published 2019-07-26 by Kevin Feasel

Dinakar Nethi announces SQL Server Management Studio version 18.2:

We are excited to announce the release of SQL Server Management Studio (SSMS) 18.2. For this update, while we added some features, our focus was dedicated to fundamentals such as stability, reliability, performance, etc.

You can download SQL Server Management Studio 18.2 today.
Some of the new features in SQL Server Management Studio include:

– Intellisense/editor: Added support for data classification
– Query execution: Added a completion time in the messages to track when a given query completed its execution.
– ShowPlan: Added new attribute in query plan when the inline scalar UDF feature is enabled.

There are several bugfixes in there as well.

Comments closed

Changing Query Store Report Interval

Published 2019-07-18 by Kevin Feasel

Arun Sirpal wants to change the report interval for a Query Store report:

While not specific to SQL Server 2019 (I was using this version to do some testing) I was struggling to find how to change the time period of analysis for the Query Store reports within SSMS.

This is not a ground breaking post but hopefully a helpful one! So, I load up the “Top 25 resource consumers” report and by default it will show data for the past hour. So what do you do, or should I say what do you click to change the time interval for the report?

Read on for the two screenshots which answer this question for you.

Comments closed

Stats Time Versus Plan Operator Time

Published 2019-07-10 by Kevin Feasel

Erik Darling explains why SET STATISTICS TIME ON can give you different timing results from what the execution plan states:

Here are the relevant details:

SQL Server Execution Times:
CPU time = 3516 ms,  elapsed time = 3273 ms.

What looks odd here is that CPU and elapsed time are near-equal, but the plan shows parallelism.

Thankfully, with operator times, the actual plan helps us out.

The query itself ran for <900ms.

The answer makes perfect sense.

Comments closed

SSMS Query Plans and Arrow Sizes

Published 2019-06-25 by Kevin Feasel

Brent Ozar clarifies what arrow sizes actually mean in execution plans:

That means the entire concept of the arrow is made up by the rendering application – like SQL Server Management Studio, Azure Data Studio, SentryOne Plan Explorer, and all the third party plan-rendering tools. They get to decide arrow sizes – there’s no standard.

SSMS’s arrow size algorithm changed back in SQL Server Management Studio 17, but most folks never took notice. These days, it’s not based on rows read, columns read, total data size, or anything else about the data moving from one operator to the next.

There’s an answer, but it’s not particularly intuitive. I think SentryOne Plan Explorer has the upper hand on this one.

Comments closed

Scripting Objects with SSMS

Published 2019-06-20 by Kevin Feasel

Michelle Haarhues shows two ways to script out database objects in SQL Server Management Studio:

There are a multitude of reasons why users script existing objects within SQL Server.  Depending on the reason will dictate whether you are scripting one object, a few objects, or the entire database.  There are different tools within SQL Server Management Studio (SSMS) that will help you create object scripts.

Click through for the two methods.

Comments closed

SQL Server Management Studio 18.1 Now Available

Published 2019-06-12 by Kevin Feasel

Dinakar Nethi takes us through some changes in SQL Server Management Studio version 18.1:

We’re excited to announce the release of SQL Server Management Studio (SSMS) 18.1. It’s been just over a month since we released SSMS 18.0. While we brought in many fantastic capabilities, we also regressed some functionality for some of our users. We are happy to share that we’ve fixed those and are also bringing in some new features along with bug fixes.

The big thing for a lot of people is that database diagrams have returned. I was never the biggest fan of those, but there was enough of an uproar to bring them back.

Comments closed

Generating Scripts from SSMS

Published 2019-06-03 by Kevin Feasel

Jeff Mlakar shows how you can use Management Studio to generate scripts for database objects:

Sales.SalesOrderDetail looks like a good choice. Let’s generate a script for that table, all associated objects, and its data.

The safest way to create structure including all indexes, keys, defaults, constraints, dependencies, triggers, etc. is to use SSMS Generate Scripts.

I would also recommend becoming familiar with the Powershell command to generate scripts and what dbatools has on store.

Comments closed

Choosing Between Management Studio and Azure Data Studio

Published 2019-04-26 by Kevin Feasel

Brent Ozar gives us the lay of the tooling land:

Every time there’s a new release of SQL Server or SQL Server Management Studio, you can grab the latest version of SSMS and keep right on keepin’ on. Your job still functions the same way using the same tool, and the tool keeps getting better.

And it’s free. You don’t have to ask the boss for upgrade money. You can just download it, install it, and take advantage of things like the cool new execution plan est-vs-actual numbers (which also cause presenters all over to curse, knowing that they have to redo a bunch of screenshots.)

I spend a lot of time jumping back & forth between SQL Server and Postgres, and lemme just tell you, the tooling options on the other side of the fence are a hot mess.

Yeah, Management Studio is the best of the bunch. I’m using Azure Data Studio more at home but still need a couple of plugins to use it often at work. And those two beat pretty much every other tool I’ve ever worked with.

Comments closed

SSMS Grid Options

Published 2019-04-22 by Kevin Feasel

Michelle Haarhues provides details on the different options for sending results to a grid in SQL Server Management Studio:

What is Results to Grid and what can it do for you?  Results to Grid are Query Results options in SQL Server Management Studio (SSMS) that can help users customize their query results in a variety of ways that can help make users more efficient.  Some of these might be little changes, but when used often throughout the day, they can make a big difference.  Once you change the setting, you will need to open a new query window for the change to go into effect

The two I wish were on by default are column headers when copying or saving results, and retaining CR/LF on copy or save.

Comments closed

Connecting with Read Intent

Published 2019-04-22 by Kevin Feasel

John McCormack shows two ways to connect to an Availablity Group listener with read-only intent:

SQLCMD
The -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. This simple omission cost me hours of troubleshooting work, because I was convinced my listener wasn’t working correctly. In fact, I just wasn’t testing it correctly.

There’s some good information in here for sqlcmd and for SQL Server Management Studio.

Comments closed