A Fine Slice Of SQL Server
Andrew Pruski shows how to change window bar colors within SQL Server Management Studio:
A simple but effective setting in SQL Server Management Studio is using custom colours to identify which server you are about to execute a query on. It’s simple to setup but not everyone who uses SSMS is aware of it so I thought I’d quickly run through the steps here.
This is a nice visual way of figuring out you’re in production before you run that truncate table script.
Comments closedPedro 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.
Comments closedAt 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.
Comments closedOne 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.
Comments closedMike 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.
Comments closedSander 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.
Comments closedSteve 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.
Comments closedRob 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.
Comments closedKenneth 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.
Comments closedThe 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.
Comments closed