Press "Enter" to skip to content

Category: SQL Server Management Studio

Improving The SSMS Scroll Bar

Published 2018-12-26 by Kevin Feasel

Michelle Haarhues shows how you can enable an enhanced scroll bar in SQL Server Management Studio:

There are so many tools within SQL Server Management Studio (SSMS) that can make your job as a DBA or Developer easier that you may or may not be using.  One of the tools available is the customization of the Scroll Bar.  You can change the display and the behavior of the scroll bars, which can make working with code a lot easier and more efficient, especially when working with long code.  The two options we will discuss are Scroll Bar Display and Behavior.

I didn’t like this a lot at first, but as I used it a few times, it grew on me.

Comments closed

What Happens With Multiple Missing Indexes

Published 2018-12-21 by Kevin Feasel

Arthur Daniels shows us what happens when there are multiple missing indexes in an execution plan:

This is missing index request #1, and by default, this is the only missing index we’ll see by looking at the graphical execution plan. There’s actually a missing index request #2, which we can find in the XML (I know, it’s a little ugly to read. Bear with me).

I am of two minds on this. It probably should be easier to see multiple index candidates, but there’s already so much risk of people just copy-pastaing missing index recommendations that adding more seems like a bad idea.

Comments closed

Static Data Masking In SSMS 18.0

Published 2018-12-20 by Kevin Feasel

Monica Rathbun introduces a new feature in SQL Server Management Studio:

Ever need to have a test database on hand that you can allow others to query “real like” data without actually giving them actual production data values? In SQL Server Management Studio (SSMS) 18.0 preview Microsoft introduces us to Static Data Masking
. Static Data Masking is a new feature that allows you to create a cloned copy of your database and replace sensitive data with new data (fake data, referred to as masked). You can use this for things like development of business reports and analytics, trouble shooting, database development and even sharing data with outside teams or third parties. Unlike Dynamic Data Masking
 added in SQL Server 2016, this feature does not hide the data with characters, rather it replaces the entire value.  For example with dynamic data masking the name Peter = Pxxxx, whereas Static Data Masking changes Peter to Paul.  This makes it very easy to use in place of production. Let’s see it in action. If you are not on a newer version on SSMS, don’t worry, you can download it

It looks like there are a few limitations to keep in mind, so click through to read about those.

Comments closed

Check Those SSMS Warnings

Published 2018-12-14 by Kevin Feasel

Arthur Daniels recommends you review any warning signs in execution plans:

Some things in life we ignore. For example, the “check engine” light. That’s just there as a suggestion, right?
But when you’re performance tuning, you can’t afford to ignore the warning signs. I can’t count the number of times that I’ve found the issue with a query by looking at the warnings.

The example Arthur uses involves implicit conversion, but there are several important warnings SSMS bubbles up.

Comments closed

Operating Management Studio With Multiple Active Directory Accounts

Published 2018-11-20 by Kevin Feasel

Kenneth Fisher shows how to use different Active Directory credentials when using SQL Server Management Studio:

To help promote the seperation of duties one of the things my company has done is to divide our permissions into two accounts. We have one account that is for our daily tasks. Reading email, searching the internet, basic structure changes in a database etc. The other account is our admin account. It’s for remoting to servers, security tasks, really anything that requires sysadmin. I’m not going to argue the advisability of this because honestly, I’m kind of on the fence. That said, I do have to deal with it and there are a few tips in case you have to deal with it as well.

And if you’re not on the domain as well, runas /netonly /user:[domain\username] ssms.exe will do the job.

Comments closed

Management Studio 18 Preview 5 Released

Published 2018-11-16 by Kevin Feasel

Dinakar Nethi announces a new public preview of SQL Server Management Studio 18:

We are very excited to announce the release of Public Preview 5 of SQL Server Management Studio (SSMS) 18.0. This release has a number of new features and capabilities and several bug fixes across SQL Server Management Objects (SMO), UI, etc.

You can download SSMS 18.0 Public Preview 5 here.

The most interesting thing in it for me is probably the menu item for CREATE OR ALTER with scripts.

Comments closed

Automatically Enabling SQLCMD Mode In SSMS

Published 2018-11-15 by Kevin Feasel

Greg Low shows how to have every Management Studio tab open in SQLCMD mode:

Note the :CONNECT command is used to connect to another server.

Because everything else works pretty much the same, and you get a whole lot of additional options, you might choose to open all your new queries in SQLCMD mode. That’s easy to do.

SQLCMD mode is one of those things where I thought I’d use it a lot, but aside from deploying database projects, I don’t.  Granted, this could be a failure of imagination on my part.

Comments closed

Using Snippets In SSMS

Published 2018-11-13 by Kevin Feasel

Eduardo Pivaral shows us how to use snippets in SQL Server Management Studio:

If you work with SQL Server on a daily basis, it is very likely you have a lot of custom scripts you have to execute frequently, maybe you have stored them on a folder and you open them manually as you need them, or have saved them on a solution or project file, maybe you execute a custom .bat or PowerShell file to load them when you open SSMS…

Every method has its pros and cons, and on this post, I will show you a new method to load your custom scripts on any open query window on SSMS via Snippets.

Click through for more details, including an example.  Snippets are a good tool implemented adequately in SSMS.  A few third-party extensions make working with snippets better and really valuable (until you’re stuck on a machine without your snippets).

Comments closed

Actual Versus Estimated Rows In SSMS 18

Published 2018-10-12 by Kevin Feasel

David Alcock is happy with a feature in SQL Server Management Studio 18:

Last week Microsoft released SQL Server Management Studio 18.0 into public preview, here’s a link so you can read about the new and improved functionality it offers.

One significant change is the addition of actual vs estimated row counts onto the showplan operators in execution plans (only actual, not estimated…which kinda makes sense).

Here I’m running a very simple bit of code on some DMV’s (namely exec requests and sessions) to demonstrate this addition.

Read on for the example.

Comments closed

SSMS On A Diet

Published 2018-10-09 by Kevin Feasel

Brent Ozar is happy that SQL Server Management Studio has dropped a few pounds:

SSMS 17.9 on the left has Database Diagrams at the top.

SSMS 18.0 does not. Database Diagrams are simply gone. Hallelujah! For over a decade, people have repeatedly cursed SSMS as they’ve accidentally clicked on the very top item and tried to expand it. One of the least-used SSMS features had one of the top billings, and generated more swear words than database diagrams.

The good news continues when you right-click on a server, click Properties, and click Processors.

The comments show that not everyone is happy about this, but I do think it’s for the best—the database diagram tool hadn’t been updated in a long time and is missing many features that an ER tool needs.  I’d rather use Visio (or a better tool).

Comments closed