Plan Comparison

Max Vernon looks at plan comparison in SQL Server Management Studio:

The table has 10,000,000 rows. I’ve create a non-clustered columnstore index on the table, which I’ll talk about in a future post. I’ve included it here because it provides a succinct difference in the two plans.

To compare the plans visually, side-by-side, you need to save the first plan by right-clicking on the plan window, clicking “Save Execution Plan As…”, and specifying a filename. Next, right-click on the plan window, and choose “Compare Showplan”:

I’ve only used this once or twice, but it is an interesting feature.

Managing Azure SQL Database Firewall Rules

Cedric Charlier shows how to manage Azure SQL Database firewall rules from within Management Studio:

When you create a new Azure database, you usually need to open the firewall to remotely administrate or query this database with SSMS. An option is to create rules from the Azure Portal. It’s surely a convenient way to do it when you create a database but I prefer to keep a minimum of tools and when the Azure portal is not open, I prefer to not have to open it just to define a few firewall rules.

Opening the firewall with SSMS is a kind of chicken and eggs problem: to connect to your database/server, you need to open the firewall. Hopefully, SSMS has a great suite of screens to call the underlying API of Azure Portal and open the firewall for the computer running SSMS.

Cedric shows off sp_delete_firewall_rule but there’s also a corresponding sp_set_firewall_rule.

Always Encrypted Data And SSMS

Justin Goodwin shows how to view Always Encrypted data from within Management Studio:

I am now able to view the encrypted data. Why is this?

Because I am connecting to the database from the database server, I have access to the Encryption Certificate that was generated. I can verify this by opening certmgr.msc and browsing to Personal -> Certificates:

Justin then goes on to show what happens when you don’t have access to the certificate and how to provide access to another machine.

Modifying SSMS Shortcuts

Slava Murygin shows how to update shortcuts in Management Studio:

A lot of people in the Internet complain about their version of SSMS “forgot” some hot-key combinations. The oldest complain I remember was about the most useful combination “Ctrl-R”.

The reason why SSMS “forgets” is within code sharing and reusability with other Microsoft development products.
If you have that problem, most probably I have (or had in the past) installed something else from Microsoft, such as Development Studio, Data Tools etc.

It can get annoying when another tool clobbers your expected shortcuts.

Closing SSMS Tabs

Andy Mallon shows how to close a tab in SSMS and change the shortcut value:

CTRL + F4

Huzzah! This will close your query tab! Easy as pie. Also, note that CTRL+F4 is a pretty universal shortcut that works to close tabs/windows in other applications, too–including your favorite web browser. If you can switch your muscle memory away from CTRL+W to CTRL+F4, you can use that shortcut pretty much everywhere.

Click through for more information on changing shortcuts.

Show Hidden Cubes

Chris Webb explains how to show hidden Analysis Services cubes in Management Studio:

Unfortunately this doesn’t make any objects in the cube that are not visible, like measures or dimensions, visible again – it just makes the cube itself visible. However, if you’re working on the Calculations tab of the Cube Editor in SSDT it is possible to make all hidden objects visible as I show here.

Read on for the command and watch out for that caveat.

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.

Categories

July 2017
MTWTFSS
« Jun  
 12
3456789
10111213141516
17181920212223
24252627282930
31