Live Query Stats Progress Changes

Pedro Lopes mentions that the latest version of Live Query Stats in SSMS 2016 has some improvements:

Below the finished query. Again, overall query execution cannot go over 100%, but at the operator level, percentages are shown as the real ratio between actual and estimated rows, with no caps.

So you can see how the actual rows from the clustered index scan on PhoneNumberType table was 14500% above estimations, and how a series of severe misestimations are coming from the bottom right area of the plan (where actual is not even 1% of estimated rows), worsening as it goes up in the nodes.

I think this makes Live Query Stats a better tool for query analysis.  I haven’t used it much in production, but this makes me want to give it another try.

SSMS In 4K

Aaron Bertrand compares SQL Server Management Studio now that there’s better support for high-resolution monitors:

At first I used Gianluca’s solution (“SSMS in High-DPI Displays: How to Stop the Madness“), but it wasn’t perfect – fonts in some places were really blurry, and some dialogs were totally unusable. He has several examples in his post if you’re curious. But I have several too.

This is the previous version of SSMS (13.0.15600.2), out of the box, which now foregoes any type of DPI scaling at all, using the old-fashioned jaggy type we’ve been suffering for decades (except check out the smooth text on the About dialog title bar!).

Cf Gianluca Sartori.  There’s still some work to do, but more and more of us are moving to high-resolution and 4K monitors; 1080p isn’t cutting it anymore.

High-DPI SSMS

Gianluca Sartori is happy that SQL Server Management Studio now looks better on 4K and high DPI screens:

One of the most popular posts on this bog describes how to enable bitmap scaling is SSMS on high DPI displays, which is a sign that more and more people are starting to use 4K displays and are unhappy with SSMS’s behaviour at high DPI. The solution described in that post is to enable bitmap scaling, which renders graphic objects correctly, at the price of some blurriness.

The good news is that starting with SSMS 16.3 high DPI displays are finally first class citizens and SSMS does its best to scale objects properly. By default, SSMS will keep using bitmap scaling: in order to enable DPI scaling you will have to use a manifest file.

There is some setup work, but I am pleased that they’re doing this.

SQLPS Is Dead; Long Live SQLPS

Mike Fal thought he had escaped his SQLPS nightmare:

The second issue is that even if you do install SSMS 2016, SQL Agent won’t recognize and give you access to the new module if you use a PowerShell job step. When you create a PowerShell job step, the script in that job step runs within a specific context. It’s hidden from you, but whenever that script runs the first thing that happens is SQL Server launchessqlps.exe.

Check out the links Mike provides to Connect items and the Trello board if you want to see the issues he brought up fixed.

Enabling Table Changes

Sander Stad shows how to enable GUI-based table changes in SQL Server Management Studio:

When you’re developing on an instance you might want to change something in a database where the change might require to re-create the table. By default, the SQL Server Management Studio (SSMS) will prevent saving changes that require the table to be recreated.

Examples of changes that require table re-creation:

  • Change a column to no longer allow NULL values

  • Adding columns in the before another column

  • Moving a column

I agree with Sander:  this is a useful feature, but not something you want to abuse.  If you don’t understand the magnitude of your change, it could cause production problems.  And if you do understand the magnitude of your change, typically you’ll want to script it out for later.

Generating Change Scripts

Steve Jones shows off a rather valuable feature in SQL Server Management Studio:

One of the really basic things I think everyone should understand is how to get scripts from Management Studio (SSMS) and saving them. In fact, I’ve written that everyone should use this button and really not ever execute their GUI changes. Capture the script, save that, and automate things.

However, that’s not what this post is about. This post is about how you get a script to look at changes, or better understand how SSMS might implement your changes.

The ability to script out your changes has a number of benefits, one of which is that you’ll get to learn the code you need to write to perform an action, which could make all the difference in a production-down situation.

SSMS And Azure SQL Data Warehouse

Rob Farley reports that we can now use Management Studio to connect to Azure SQL Data Warehouse:

One of the biggest frustrations that people find with SQL DW is that you need (or rather, needed) to use SSDT to connect to it. You couldn’t use SSMS. And let’s face it – while the ‘recommended’ approach may be to use SSDT for all database development, most people I come across tend to use SSMS.

But now with the July 2016 update of SSMS, you can finally connect to SQL DW using SQL Server Management Studio. Hurrah!

…except that it’s perhaps not quite that easy. There’s a few gotchas to be conscious of, plus a couple of things that caused me frustrations perhaps more than I’d’ve liked.

Yes, it’s never quite that easy…  Read the whole thing.

Watch Those Parentheses

Kenneth Fisher shows how to see open and close parenthetical locations:

You’ll notice that when I go over the parentheses the one I’ve selected and it’s pair turn yellow, unless there isn’t a pair of course. You can also use Ctrl-] to flip between the open and close parenthesis in a pair. This can be particularly useful to make sure that you remembered a close parenthesis at the end of a subquery. In this case that last close parenthesis doesn’t have a match. Now finding out that you are missing an open parenthesis doesn’t mean you know where it’s supposed to go. But you can track the different pairs, making sure that each time you open a parenthesis you close it in the correct place. In this case it belonged right at the beginning.

FYI yellow isn’t the default (it’s a light gray). I find the default hard to see (I’m getting old) so I changed it to yellow in the options under fonts and colors.

Read the whole thing.

Alternate Credentials

Daniel Hutmacher shows us various techniques for starting Management Studio under different Windows credentials:

The easy way to solve this is to just log on directly to the remote server using Remote Desktop and use Management Studio on that session, but this is not really desirable for several reasons: not only will your Remote Desktop session consume quite a bit of memory and server resources, but you’ll also lose all the customizations and scripts that you may have handy in your local SSMS configuration.

Your mileage may vary with these solutions, and I don’t have the requisite skills to elaborate on the finer points with regards to when one solution will work over another, so just give them a try and see what works for you.

I prefer Daniel’s second option, using runas.exe.

Negative Width And Height In SSMS

Manoj Pandey ran into an issue with Management Studio wanting to open a window with a negative size:

By checking the error its obvious that there is something wrong with Width or Height of SSMS Query-Editor window.

So, I went to REGEDIT (In RUN, type regedit.exe) and after navigating here n there got the location where to update this property.

Navigate to folder: HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\13.0\

Here check the MainWindow property value (image below), it was showing: 0 451 109 -120 876 1

Change it to a positive value considering the width of your SSMS editor window, I replaced -120 with 1400

I had no idea that the main window size details were kept in the Registry.

Categories

August 2017
MTWTFSS
« Jul  
 123456
78910111213
14151617181920
21222324252627
28293031