Press "Enter" to skip to content

Category: SQL Server Management Studio

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.

  1. That it’s being blocked
  2. 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

Scripting Multiple SQL Agent Jobs With SSMS

Published 2017-09-15 by Kevin Feasel

Steve Stedman shows how to script multiple SQL Agent jobs from SQL Server Management Studio:

While doing a recent server move, I came across the need to script all agent jobs and save them to a file so that if we needed to reference what was there after the move we could get at the agent jobs without having to restart the old SQL Server.

It made me realize that sometimes what you want to do is available in SQL Server Management Studio, but that perhaps it is not always entirely obvious.

Click through for the demo.  I’d probably use Powershell over this, but if you just need to do a one-time job move, this gets you going quickly.

Comments closed

Searching In Execution Plans

Published 2017-08-10 by Kevin Feasel

Pedro Lopes gives a reason to upgrade to SSMS 17.2:

But what if I could search for node id 30 while looking at the graphical showplan?

Starting with SSMS 17.2, just use CTRL+F to start a search in graphical showplan (or right-click on a blank area of the plan, and in the context menu click on Find Node option), and you can quickly see exactly where node id 30 is:

I will have to see whether that also lets you quickly find the origin of Expr#### columns.

Comments closed

SSMS Performance Dashboard

Published 2017-08-09 by Kevin Feasel

Pedro Lopes announces that the SQL Server Performance Dashboard is now built into SQL Server Management Studio:

Back in 2007, we released the Microsoft SQL Server 2005 Performance Dashboard Reports, which were designed to provide fast insight into performance issues from some newly created system views – DMFs (Dynamic Management Views). These were updated for SQL Server 2008 and later to SQL Server 2012, and while being very helpful they had a significant drawback – required separate download and install. This meant that when needed, most probably they were not installed in a specific SQL Server, and therefore were unusable when they were needed the most.

With the new SSMS 17.2, we are releasing the Performance Dashboard embedded as a built-in Standard Report. This means that it is available for any SQL Server instance starting with SQL Server 2008, without any extra downloads or running any extra scripts. Just connect to your server in SSMS and open the Performance Dashboard.

Aside from making it built into Management Studio, they’ve also added a few helpful things to the product, so it is worth checking out.

Comments closed

Cycling The SSMS Clipboard Ring

Published 2017-07-26 by Kevin Feasel

Bert Wagner shows how you can use the clipboard ring in SQL Server Management Studio:

Copying and pasting is a feature that’s available in nearly every text editor (“nearly” — anyone remember the days before iOS had a clipboard?).

However, SQL Server Management Studio goes above and beyond the regular copy and paste feature set — it has a clipboard ring.

What’s a clipboard ring you ask?

The clipboard ring let’s you cycle through the last 20 things you copied onto your clipboard when you go to paste in SSMS.

Read on to learn how to use the clipboard ring.  When I remember that it exists, it’s quite useful.

Comments closed

Top Jobs Report

Published 2017-07-26 by Kevin Feasel

Kenneth Fisher only looks at the best SQL Agent jobs:

But sometimes what you want is an overall history. Maybe you want to see your longest running jobs? Or the most frequent jobs? Or jobs that are failing? There is a great pre-built report just for that.

Now, be warned. The report and the history view both pull from the system views and they only hold so much history. You need to make s

With that warning stated, this is a good report if you just want to get some idea of what’s happening on your server.

Comments closed