Category: SQL Server Management Studio

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.

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.

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.

Wayne Sheffield clues us in on a nice Management Studio feature:

SSMS is a wonderful tool. You can drag Windows around, grouped with others, split, docked, undocked, hidden… it seems endless what you can do with them. You can even change what columns you see. Invariably, with all of this customization, things go wonky. I’ve even seen windows opened up on invisible monitors. Sometimes you can’t find the windows that you need. Conversely, windows that you don’t need are open all over the place. Sometimes, you just need to reset everything and start over. Short of reinstalling SSMS, how do you do this? You just reset the window layout.

It’s one of those options which stares you in the face but you can easily miss.

John Morehouse shows that the file growth rate GUI for Management Studio doesn’t report values under 1MB correctly:

While I was recently,  doing a review of a client’s environment I discovered that the GUI can lie to you when it comes to the database file growth rates.  By default, the data file is set to a 1MB growth rate and the log file is configured for a 10% growth rate.  Both are horrible settings for most OLTP environments.  However, starting with SQL Server 2016, the default growth rates are configured for 64MB, which in my opinion is better than the previous defaults.

Using the GUI to look at a 2017 Scratch database I have, we can see that the data file is configured for 1MB and the log file is set for 64MB growth.

I don’t think there’s a good reason for a file growth rate under 1 MB at this point.  That could have made sense in the late ’90s, but the idea of growing 128KB at a time is funny.

Daniel Hutmacher has a trick to right-align results in SQL Server Management Studio:

Here’s something I’ve found: the space character is roughly about half the width of a typical number character. So replace every leading space with two spaces, and it will look really neat in the grid:

Click through for the script.  This one goes near the back of the toolbelt, but it can come in handy when trying to troubleshoot values by eyeball and without Excel.

Claudio Silva shows how to make UTF-8 the default file encoding for SQL Server:

The file that is used to create a new query window has ANSI encoding but when I save the file on the PowerShell script I save it as UTF-8 because the client have comments on the code with unicode characters.

On this process, the unicode characters are replaced by some symbols.

Read on for the solution.