Press "Enter" to skip to content

Category: SQL Server Management Studio

Intellisense and the DAC

Published 2019-09-09 by Kevin Feasel

Slava Murygin doesn’t like severity 20 errors just popping up for no good reason:

Yesterday I’ve needed to use Dedicated Administrator Connection (DAC) once in a while, and because I have all kinds of notifications in my system, I immediately got an “Severity 20” alert.

As you probably know, Severity 20 Errors “Indicate system problems and are fatal errors” (See books online: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-2017)

Even though “Severity 20” does not indicate any problems with data and belong only to a user process it is still worth to investigate the problem.

Read on to see the cause of Slava’s problem and how there’s no way to fix it in SSMS.

Comments closed

Estimated Execution Plans in Azure Data Studio

Published 2019-08-26 by Kevin Feasel

Dave Bland walks us through the “Explain” button in Azure Data Studio:

At first I was thinking maybe it is a short cut to help.  Nope, it is actually an easy way to get the estimated execution plan. The key is estimated execution plan. While in SQL Server Management Studio we have been able to easily get both the estimated or actual execution plan.

Just as a reminder, the actual execution plan requires the query to actually run. While the estimated plan will use statistics to generate the plan.  Generally, if the actual and estimated are capture very close to each other, you are more than likely not going to see a difference between the tow.

Now let’s talk about the Explain button.  It will return two things, the XML of the execution plan and a graphical representation of the execution plan.

There are a few differences between Azure Data Studio’s implementation of execution plans and SQL Server Management Studio’s.

Comments closed

Management Studio’s Staying Power

Published 2019-08-15 by Kevin Feasel

Kendra Little explains why SQL Server Management Studio isn’t going away anytime soon:

After all, SSMS is no longer the cool new kid on the block: Microsoft has shown consistent effort to develop their new tool, Azure Data Studio (the artist formerly known as SQL Operations Studio), since November 2017. Azure Data Studio is built on the modern foundation of Microsoft’s VS Code, whereas SQL Server Managed Studio is related to the legacy Visual Studio Shell.

Based on this overview, it might seem like a new SQL Server DBA or developer should primarily learn Azure Data Studio, not SSMS. And it might similarly seem like vendors should focus on developing new tooling only for Azure Data Studio.

But when you look into the details of how Azure Data Studio is being developed, it becomes clear that SSMS is still just as relevant than ever:

User base inertia is another reason, one that Kendra doesn’t mention directly. I like where Azure Data Studio is going and try to use it at least half-time. But there are a lot of people with a specific workflow they’ve developed and don’t want to change. As long as that’s a large percentage of the SQL Server population, SSMS isn’t going anywhere.

Comments closed

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