Customizing SSMS Using Powershell

Josh Simar shows us that you don’t need a GUI to customize SQL Server Management Studio:

There has been many a blog post out there that shows you some of the great ways you can customize the look and feel of your management studio windows. One of my favorite recently published ones is from the great people at Brent Ozar Unlimited (Brent himself in this case) here.

There is even many a blog post out there about how you can export your settings from one and import it to another here.

All of the blog posts I’ve read however have one problem for me. They seem very “clicky”, as in a lot of clicking of the mouse (or keyboard shortcuts) has to happen every time you want to import your settings and I thought there has to be a more programatic way.

After all, many of us espouse to the DRY (Don’t Repeat Yourself) technique when coding. Why wouldn’t we want to trim time and energy off of something as basic as a settings import.

And Josh has that solution.

SSMS 17.9 Released

Alan Yu announces a new version of SQL Server Management Studio:

SSMS 17.9 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.9 comes with several new features:

  • ShowPlan improvements
  • Azure SQL support for vCore SKUs
  • Bug Fixes

View the Release Notes for more information.

It looks like the big push for this release was bug fixes, and there are quite a few of them.

Saving Newlines When Copying From SSMS Grids

Kenneth Fisher shows us how to retain CR/LF values when copying and pasting from SQL Server Management Studio grids:

Now, you do need to be aware that the settings Tools -> Options are just for new query windows. If you want to change the settings for an already existing window then either go to Query -> Query Options or right click in the window and go to Query Options from there.

Either way, once you are done and re-run your script you get this when you copy and paste:

Click through for the tip.

Capturing UDF CPU Times

Jonathan Kehayias notes an improvement in recent versions of SQL Server:

Microsoft has been enhancing the contents of the ShowplanXML output for SQL Server over the last few releases and in SQL Server 2017 CU3, they introduced user-defined function (UDF) execution statistics into the QueryTimeStats node of the XML output. This was also back ported to SQL Server 2016 in Service Pack 2 for actual execution plans. This feature allows you to definitively know the impact of scalar UDF execution as part of the performance characteristics of a query. However, there is an interesting catch associated with using this feature; you have to collect the actual execution plan using an up to date version of SQL Server Management Studio or using SentryOne Plan Explorer, or the information will be removed from the execution plan.

This is a good improvement.  Historically, user-defined function costs were hidden in SSMS, as you’d see the cost of a single execution.  This made them look a lot more benign than they actually were.

SSMS: Analyze Actual Execution Plan

Grant Fritchey shows us something that the SSMS tools team snuck in on us:

One of the many new sets of functionality introduced in SQL Server Management Studio 17 is the new option “Analyze Actual Execution Plan.” If Microsoft continues down this path, there will be a lot of useful functionality at some point. If you haven’t yet looked at Analyze Actual Execution Plan, well, read on.

I hope they do expand this out.  I can see it being very beneficial, but it needs to look at a lot more than just cardinality estimations.

Configuring SQL Server Management Studio

Brent Ozar shares his configuration settings for SQL Server Management Studio:

Under Query Results, SQL Server, Results to Grid, I change my XML data size to unlimited so that it brings back giant query plans. (Man, does my job suck sometimes.)

A lot of presenters like to check the box for “Display results in a separate tab” and “Switch to results tab after the query executes” because this gives them more screen real estate for the query and results. I’m just really comfortable with Control-R to hide the results pane.

And I just went and removed a bunch of menu bar icons I never use…  Good advice from Brent.

De-bracketing SSMS Scripts

Andy Mallon hates brackets and likes regular expressions:

There are cases where you need brackets, such as having objects with “illegal” characters in them. These rules apply to database names, column names, and all object names. (I’m going to simply refer to “object names” for simplicity, though I concede that “identifier” might be a more correct term.) If you want to put a space or hyphen in an object name, then you’re going to have to use [brackets] to “quote” the name every time you reference it. Similarly, you can’t start an object name with a number, even though it’s a valid character in any other position of the object name: 8Ball is an illegal object name, but AM2 is perfectly OK. There are a bunch of other scenarios, but I won’t go into them all.

If SMO tried to only use [brackets] where necessary, that would likely be a complicated and error-prone branch of code. It’s safer to always include [brackets], and there’s no time when [brackets] will break your code.

Read on to see how Andy gets rid of those pesky things.

The Power Of Registered Servers In SSMS

Nisarg Upadhyay introduces us to Central Management Server and Registered Servers in SQL Server Management Studio:

We will:

  1. Designate a SQL Server instance as “Central Management server”.

  2. Create server groups named Production servers and Development servers under CMS server.

  3. Register “QA Testing server” and “Staging server” under the development server group, and “HR DB” and “Sales DB” under the production server group.

  4. Connect and access the CMS server from another server inside the same domain using SSMS.

Once you get past a few SQL Server instances, having a CMS in place is a fantastic idea.

When Multiple Missing Indexes Exist

Brent Ozar shows what happens when there are multiple missing indexes for a query:

SQL Server Management Studio only shows you the first missing index recommendation in a plan.

Not the best one. Not all of them. Just whichever one happens to show up first.

Using the public Stack Overflow database, I’ll run a simple query:

But that behavior isn’t the case for all tools; SQL Operations Studio is a bit different.

Running SSMS As A Different Windows Account

Jason Brimhall shows a couple ways of running SQL Server Management Studio with different Active Directory credentials:

One of the tasks I find myself doing on a fairly regular basis is running SSMS as a different Windows User. The two biggest use cases for this are: a) to test an account to prove that it is working (or not) and has the appropriate level of access, and b) to use SSMS to connect to a Domain SQL Server from a computer in a different domain (or not on the domain).

In addition to needing to do these tasks for myself, I find that I need to show somebody else how to do the same thing on a fairly consistent basis. Considering the finite keystrokes we all have (which I referenced here), it is time for me to “document” how to do this task.

The “/netonly” command line parameter is one I’ve occasionally forgotten to my inevitable chagrin.


September 2018
« Aug