Operator Elapsed Time

Kendra Little shows off a really cool feature in SQL Server 2016 & 2014 SP2:

SQL Server now shows Actual Elapsed CPU Time and Actual Elapsed Time (duration) for each operator in an Actual Execution Plan

For SQL Server 2016 and 2014 SP2 and higher, actual execution plans contain a bunch of new information on each operator, including how much CPU they burn, how long it takes, and how much IO is done by that operator. This was a little hard to use for a while because the information was only visible in the XML of the execution plan.

Check out Kendra’s post for more details, including a couple caveats.

Parameterizing Always Encrypted Statements

Jakub Szymaszek shows off Parameterizing for Always Encrypted in SSMS 17.0:

First thing to note is that SSMS has rewritten the query as a parameterized statement. The literal, used to initialize the @SSN variable in the original query, is being passed inside a parameter, with an auto-generated name (@pdf9f37d6e63c46879555e4ba44741aa6). This allows the .NET Framework Data Provider for SQL Server to automatically detect that the parameter needs to be encrypted. The driver achieves that by calling sp_describe_parameter_encryption that prompts SQL Server to analyze the query statement and determine which parameters should be encrypted and how. Then, the driver, transparently encrypts the parameter value, before submitting the query to SQL Server for execution via sp_executesql. SQL Server can now successfully execute the query.

Read the whole thing.  Setting this up does obviate part of a benefit to using Always Encrypted:  the ability completely to lock out a database administrator from certain pieces of data.

Cannot Connect To WMI Provider

Andrew Peterson troubleshoots an error after installing SSMS vNext:

After installing SQL Server Management Studio for vNext, the Configuration Manager no longer opens, with a message similar to the following:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]

Read on for the solution.

Specifying A Database For Connection In SSMS

Denis Gobo shows how to specify a database when connecting to an instance using Management Studio:

One of our database on the development went in suspect mode today. This database was the default for a bunch of logins.  These people could not login now. Someone needed to use a different database but he couldn’t login because the database that was in suspect mode was the default database for the login he was using.

I told this person to click on the Options button in the connection dialog and specify another database. I guess there was an misunderstanding because this person couldn’t get it to work. This means it is time for a blog post.

Connecting to the default database is usually fine, but sometimes you need to specify one.  Fortunately, Management Studio makes it pretty easy.

Database Restoration In Linux Via SSMS

Andrew Peterson walks through the easy way of restoring a database backup to a Linux installation of SQL Server:

But my Backup file is still not visible in the wizard!

Permissions.  If you drill down into the folders in Linux, we found that the files already present in the /data/ folder are owned by the user mssql.  Our recently copied backup file is NOT owned by mssql, and it not accessible to other users. So, our wizard cannot see the file.

The whole process is pretty straightforward.

Changing Font Sizes

Jay Robinson has a couple Visual Studio Settings files to change Management Studio font sizes:

I present technical sessions now and then – my local PASS group, SQL Saturdays, internal groups at my workplace, etc. I frequently find myself adjusting the fonts inside SQL Server Management Studio to make sure my material is readable on the big screen. I’ve also been in the audience plenty of times, watching with sympathy as one of my cohorts agonizingly navigates this problem.

Usually, it goes something like this. They first find the [100%] tucked away in the lower left corner of the text window, and blow that up to 150 or 200 percent. Then they run their query to find that the results are still at 100%. So then they eventually find the Options dialog under the Tools menu, find the Fonts and Colors branch of the tree, and then groan when they realize they have to figure out which three or four of the 30 different fonts they need to change. Sometimes, they’ll give up there and just go use ZoomIt (which any good technical presenter should have available anyway), but constantly bouncing around with ZoomIt will get old quickly over the course of an hour-long session.

Having VSSettings files is a good idea, although I’ve noticed oddities when also trying to change colors (e.g., darker theme for regular development but a lighter theme for presentations), so test out any settings files you want to use and make sure you can swap back and forth without seeing weird behavior.


In this blast of new things, Andy Leonard makes mention that there are new versions of Management Studio and Data Tools available:

It’s Release Day! 🙂

New versions of SQL Server Data Tools (SSDT) are available here. SSDT 16.5 and 17.0 (RC1) are available. Also available are Data-Tier Application Framework (DacFx) versions 16.5 and 17.0 (RC1).

New versions of SQL Server Management Studio (SSMS) are available here. SSMS 16.5 and 17.0 (RC1) versions are available for SSMS.

It’s going to be a busy couple of days for some people…

Cached Azure Analysis Services Logins

Chris Webb shows how to log into Azure Analysis Services from Management Studio as a different user:

When Azure Analysis Services was announced I had to try it out right away. Of course I didn’t read the instructions properly so when I tried to log in to my Azure Analysis Services instance from SQL Server Management Studio, like an idiot I logged in with the wrong username. The problem is that once you’ve done this, with current versions of SQL Server Management Studio there’s no way of logging out and logging in as a different user. Luckily Igor Uzhviev of Microsoft had a solution for me and I thought I’d share it for anyone else who’s made the same mistake. Here’s what you need to do:

This seems a bit much, but should just be a temporary workaround.

Manage Compression Bug In SSMS

Arun Sirpal found a bug in Management Studio 2016:

The actual message that you are presented states: Tables with sparse columns cannot be compressed – I have no sparse columns.

I never had this issue with SQL Server 2014 Management Studio

It looks like that bug has been fixed in the latest version of Management Studio, so if that affects you, it’s worth an upgrade.

Upgrading SSMS

Melissa Coates argues that you should upgrade to the latest version of Management Studio:

SSMS is supported for managing SQL Server 2008 through 2016 (except for SSIS instances which sadly still require a version-specific SSMS at the time of this writing). If you manage numerous servers on different versions, this unification is fantastic. There is partial support for managing pre-2008 instances. And, of course as you’d expect, the newest SSMS release supports various new features in SQL Server 2016 such as Query Statistics, Live Query Plans, Compare Showplan, Security Policies for row-level security, and so on with all the new 2016 goodies we have.

SSMS also supports managing Azure components such as Azure SQL Database and Azure SQL Data Warehouse, as well as hybrid cloud features such as StretchDB and Backup to URL. This additional unification is really, really handy.

I have a copy of SSMS 16 for reading Query Store, but not all of my plugins have been updated yet, so I’m still living in SSMS 2014 for now.


August 2017
« Jul