SQL Server Management Studio – Page 21

SQL Server Connectivity In VS Code

Published 2016-10-04 by Kevin Feasel

Max Trinidad looks at the VS Code plugin for SQL Server connectivity:

After installing, we need to customized their setting by creating connection(s) to our SQL Server. We do this by opening VS Code “User Preferences” and under “Default Settings.json” we search for the “vscode-mssql” settings to be copied over to our working folder “settings.json” file.

I played with this very early on and would like to see it continue to be developed, but it’s no replacement for Management Studio.

Comments closed

Bug Scripting Sequences

Published 2016-09-29 by Kevin Feasel

Daniel Janik notices a small bug in how Management Studio scripts out sequences:

Have you ever scripted an object from SQL Server Management Studio (SSMS)? It does a really good job. You get nice cleanly formatted scripts that start with USE statements to select the database. They even have some simple comments?
Have you ever written a Sequence? Turns out if you script one you’ll notice that Microsoft left you an extra surprise. Double USE statements. Does it matter much? No. Should they fix it? Yes. I noticed this behavior when sequences were first released and it still exists in the latest version of SSMS for SQL 2016 (13.0.1500.23) as of this posting.

Yeah, that’s a tiny bug, but I can see it being annoying.

Comments closed

SSMS Query Shortcuts

Published 2016-09-28 by Kevin Feasel

Andrew Pruski shows where to set Management Studio query shortcuts:

Following on from my last post Changing connection colours in SSMS I thought I’d write another quick about this cool but also often unused feature in SSMS.
These shortcuts allow you to run pre-determined queries by assigning a hot key within SSMS. To do this in SSMS go to Tools > Options > Environment > Keyboard

I love query shortcuts. I have three dedicated to different sp_whoisactive commands (one to get everything going on; one to get everything related to my username; and one to get everything going on plus query plans, which I don’t always use because of the additional overhead).

Comments closed

Understanding Query Durations

Published 2016-09-28 by Kevin Feasel

Kendra Little explains some of the intricacies behind query durations:

I typically look at the ‘CPU time’ metric when tuning instead of ‘elapsed time’ (duration). This can work well for tuning because you’re measuring how much more efficient you made the query in terms of CPU cycles.
But ‘CPU time’ isn’t perfect, and it can get a little weird for reporting results to users, because:

If the query uses parallelism, CPU time can be higher than the duration — which may make the query seem “slower” than it actually is to anyone reading a report
‘elapsed time’ includes all the time that it takes to display the results in Management Studio, which is probably a different duration than it would take to return the results to an application server. If you’re just returning a few rows, this may be negligible– but once it gets into the thousands of rows, it can be very noticeable.

Moral of the story: also use SQL Sentry Plan Explorer…

Comments closed

Creating Snippets With Powershell

Published 2016-09-26 by Kevin Feasel

Allison Tharp has a script to create SSMS snippets:

One issue with SSMS’s Snippet Manager is that the code has to be stored in a .Snippet file in a specific location on your local machine. The .Snippet files are in XML format, which is a little inconvenient since we don’t write SQL in XML format. This means that in order to add a new snippet, you need to create a new .Snippet file in the correct format, add the SQL to it, and save it in the right location. After that, you must import the file through SSMS.
I have had ideas for snippets, but it is enough of an inconvenience to sometimes prevent me from going through the motions of making an actual snippet file. I wanted to write some kind of code that would allow me to save a SQL file into a specific folder and let an automated program convert it into the Snippet format. I’ve also been wanting to learn PowerShell, so I thought this would be a good opportunity!

The next step would be turning that into a cmdlet.

Comments closed

SSMS Connection Colors

Published 2016-09-22 by Kevin Feasel

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 closed

Live Query Stats Progress Changes

Published 2016-08-29 by Kevin Feasel

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.

Comments closed

SSMS In 4K

Published 2016-08-25 by Kevin Feasel

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.

Comments closed

High-DPI SSMS

Published 2016-08-19 by Kevin Feasel

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.

Comments closed

SQLPS Is Dead; Long Live SQLPS

Published 2016-08-17 by Kevin Feasel

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.

Comments closed

Category: SQL Server Management Studio