Category: SQL Server Management Studio

What I needed was to be able to check each one of those tables, and see if any of the should-be-deleted rows still exist. Of course, I can certainly type this all out by hand…
IF EXISTS (SELECT * FROM dbo.Table1 WHERE val = 1) SELECT 'Table1 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table2 WHERE val = 1) SELECT 'Table2 has undeleted rows!' as TableName;
*Sigh*. I’m already bored. And in reality, I had 45 of these statements, not 15. I’m not a big fan of repetitive, monkey-button work.

SSMS regular expressions are great. I got to see a version of Sean’s linked talk in person and it helped things click in my mind.

Comments closed

Decrypting Always Encrypted Columns In SSMS

Published 2017-12-14 by Kevin Feasel

Monica Rathbun shows how to view Always Encrypted data within Management Studio:

Viewing decrypted data within SQL Server Management Studio (SSMS) is very easy. SSMS uses .NET 4.6 and the modern SQL Server client, so you can pass in the necessary encryption options. SSMS uses the connection string to access the Master Key and return the data in its decrypted format.

First create a new SQL Connection and Click Options to expand the window.

Then go to the Additional Connections Parameters Tab of the login window and simply type column encryption setting = enabled. Then choose Connect.

Click through to see the whole demo.

Comments closed

SQL Vulnerability Assessment

Published 2017-12-12 by Kevin Feasel

Ronit Reger shows off the new SQL Vulnerability Assessment, available in SQL Server 2012 and later:

Not only does VA expose some of the possible security flaws you have in your database system, it also provides remediation scripts to resolve issues within a couple of mouse clicks. In addition, you can accept specific results as your approved baseline state, and the VA scan report will be customized accordingly to expect these values.

Ronit and Alan Yu also mention it being available via the latest version of Management Studio, 17.4:

The VA service runs a scan directly on your SQL database or server. VA employs a knowledge base of rules that flag security vulnerabilities and deviations from best practices, such as misconfigurations, excessive permissions, and exposed sensitive data. The rule base grows and evolves over time, to reflect the latest security best practices recommended by Microsoft.

Results of the assessment include actionable steps to resolve each issue and provide customized remediation scripts where applicable. An assessment report can be customized for each customer environment and tailored to specific requirements. This process is managed by defining a security Baseline for the assessment results, such that only deviations from the custom Baseline are reported.

VA is supported for SQL Server 2012 and later, and can also be run on Azure SQL Database.

This looks like a good reason to upgrade SSMS.

Comments closed

Availability Group Latency Reports

Published 2017-12-08 by Kevin Feasel

Sourabh Agarwal points out some new reports in Management Studio 17.4:

The Latency data collection functionality and the associated reports allows a database administrator to quickly discern the bottleneck in the log transport flow between the Primary and the Secondary replicas of an Availability Group. This feature does NOT answer the question “Is there latency in the Availability Group deployment?” but rather provides a way to understand why there is latency in the Availability Group Deployment. This functionality provides a way to narrow down the potential cause of latency in an Availability Group deployment.

There are some things that this report doesn’t capture, but it does give us a bit more insight.

Comments closed

Vertical Selection In SSMS

Published 2017-11-30 by Kevin Feasel

Bert Wagner shows off vertical selection in SSMS (using the Alt key):

Sometimes when writing an ad hoc query you might want to take the results of one query and put them into an IN() statement of another query.

Sure, you can write a subquery to put into your IN() statement…but that’s too much work for a one-time use disposable query.

What you can do instead is:

Copy your values of interest
Paste them into your IN() statement
Hold down the ALT key while dragging the mouse down in front of all of your pasted values
Type a comma (see video above for an easier demonstration).

For SSMS speedrunning strats, you can also hold down ALT + SHIFT and use your keyboard arrow keys instead of using the mouse.

Comments closed

Actual Execution Plan Enhancements

Published 2017-11-13 by Kevin Feasel

Pedro Lopes points out some additional data available in the properties section when you generate an actual execution plan:

Looking at the actual execution plan is one of the most used performance troubleshooting techniques. Having information on elapsed CPU time and overall execution time, together with session wait information in an actual execution plan allows a DBA to use showplan to troubleshoot issues away from the server, and be able to correlate and compare different types of waits that result from query or schema changes.

A few months ago we had introduced exposed in SSMS some of the per-operator statistics, such as CPU and elapsed time per thread. More recently, we have introduced overall query CPU and elapsed time tracking for statistics showplan xml (both in ms). These can be found in the root node of an actual plan. Available using the latest versions of SSMS v17, when used with SQL Server 2012 SP4, SQL Server 2016 SP1 and SQL Server 2017. For SQL Server 2014 it will become available in a future Service Pack.

Also be sure to check out Geoff Patterson’s Connect item asking that the execution plan results show the top ten waits in descending order rather than ascending order. That’s the appropriate ordering in my mind: show me the most important things first.

Comments closed

SQL Server Operations Studio

Published 2017-11-02 by Kevin Feasel

Rohan Kumar announces a new management interface for SQL Server (among other things):

These new cross-platform capabilities have made SQL Server accessible to users of Windows, Linux and Mac. At PASS Summit, we are excited to provide a sneak peek at Microsoft SQL Operations Studio. In a few weeks, users will be able to download and evaluate this free, light-weight tool for modern database development and operations on Windows, Mac or Linux machines for SQL Server, Azure SQL Database, and Azure SQL Data Warehouse. Increase your productivity with smart T-SQL code snippets and customizable dashboards to monitor and quickly detect performance bottlenecks in your SQL databases on-premises or in Azure. You’ll be able to leverage your favorite command line tools like Bash, PowerShell, sqlcmd, bcp and ssh in the Integrated Terminal window. Users can contribute directly to SQL Operations Studio via pull requests from the GitHub repository.

Steve Stedman comments on it:

This appears to be a new cross platform (Mac, Linux, and Windows) for running queries against SQL Server.

Perhaps this is a replacement for SQL Server Management Studio, or may just a subset of what SSMS does today, however it works cross platform, unlike SSMS.

You won’t be giving up Management Studio anytime soon, but there are some really cool parts of SQL Operations Studio forthcoming.

1 Comment

New Management Studio Features

Published 2017-10-12 by Kevin Feasel

Wayne Sheffield looks at two new features in SQL Server Management Studio 17.3:

For this simple test, it worked pretty well, and it should work well for most of the requirements that you have. Time will tell how reliably this new feature does work.

The Import Flat File is available when connecting to SQL Server version 2005 or higher. I haven’t tried this on a lower version, but I don’t see any reason why it wouldn’t work there either. You can read more about this feature in Microsoft’s documentation.

You can definitely break the Import Flat File feature, but I appreciate it being smoother than the SSIS-based wizard of yore. Wayne also shares his thoughts on the Extended Events Profiler.

Comments closed

Extended Events Profiler

Published 2017-10-09 by Kevin Feasel

Marek Masko shows off the new Extended Events Profiler In SQL Server Management Studio 17.3:

XE Profiler looks promising and can be really a great feature. We can use it with no issues on any version of SQL Server which supports extended events – not only with newest SQL Server 2017. I tested it with SQL Server 2014 and it was working well. Currently, lack of configuration of new templates, and logic based on hard-coded names is the biggest concern and discomfort for the user. However Microsoft didn’t officially release yet this version of SQL Server Management Studio, so it’s hard to say what will be the final feature functionality.

I’m hoping that when the final version appears, it will be good enough to get people finally to kick the Profiler habit.

Comments closed

Live Query Doesn’t Show Blocking

Published 2017-09-27 by Kevin Feasel

Erik Darling points out that the Live Query Plan view does not show wait events:

What the live query plan doesn’t tell you about your query.

That it’s being blocked

What it’s waiting on

No seriously, nothing turns red or beeps or starts checking its watch, and there’s no Clippy asking you to hang on a moment. It just sits there.

And since the query hasn’t finished running yet, we don’t have any wait stats in the plan XML yet.

Assuming that’s fairly easy for Management Studio to get at the operator level—Erik does note that you can get this blocking information from DMVs at the session/query level—it’d be nice to add this information.

Comments closed