Search results matching tag 'Management Studio'

SSMS 2012 Restore GUI Gotcha

merrillaldrich — Fri, 15 Mar 2013 16:30:07 GMT

Today I want to bring to your attention an issue in the SQL Server Management Studio 2012 restore GUI. In many ways the new restore dialog is nicer than the old one, with new features and added convenience – but, as is always the Achilles heel of GUI tools like this, if you don’t know what’s really going on it can bite you. I’m not sure what to call this issue, maybe just a UI design flaw. Technically it works as designed, and there’s nothing really wrong with it, so it’s not a bug. But I can imagine it really causing someone pain who is careless or doesn’t know what’s happening behind the scenes.

Restoring a copy of a database make take the original down.

Among the new features is some management of “Tail-Log Backups,” which is a wonderful idea. Taking a backup of the tail of the log will take down the database and put the whole end of the log into a backup file, preserving basically all modifications to the DB from the log. This is ideal, for example, at the last step of migrating a database using log shipping, because all the activity is preserved and the source database is locked against further modifications. The log chain is preserved in moving to the new copy of the database, or in a DR scenario, to your other server.

The problem is, I know a lot of people don’t know this. Often its people who use the GUI a lot, such as “accidental DBAs.” I think the GUI is really important despite those who rarely use it, or frown on it.

Here’s the issue: Suppose I have a production database that has some user-created problem (like a user accidentally updated some rows.) It may be reasonable to restore a copy of the database to a point before the issue and investigate whether it’s possible to merge the data back in. The original database isn’t damaged from a technical point of view – there’s no corruption, for example.

Right-click the original source database and choose Tasks > Restore > Database … from the context menu.
The resulting dialog will go out and discover the backup files for that database from MSDB, which is very helpful.
Change the name of the target database, in order to restore it as a copy.

There’s a note at the top of the dialog that, while accurate, may not help some in this scenario. It says, “A tail-log backup of the source database will be taken.” Sounds innocuous if you don’t know what that implies. In past versions, restoring a copy of a database would never affect the original – or not by default, anyway – so I cringe as I imagine people merrily clicking past this warning.

The script you get with these settings is indeed composed with a tail-log backup of the source database:

That will, as indicated, take a tail-log backup – and thereby take the original database down. Nice if you wanted that, but a mean surprise if you didn’t.

If you act on the warning and click the Options tab, and then uncheck the offending setting, you do get the expected behavior (the original database is unaffected because the tail-log backup is not included at the top of the script):

So, be careful out there!

Connect Digest : 2012-07-06

AaronBertrand — Fri, 06 Jul 2012 14:37:00 GMT

I've filed a few Connect items recently that I think are important.

In #752210, I complain that the documentation for DDL triggers suggests that they can prevent certain DDL from being run, which is not the case at all.

http://connect.microsoft.com/SQLServer/feedback/details/752210/doc-ddl-trigger-topic-suggests-that-rollbacks-run-before-action

In #745796, I complain that scripting datetime data in Management Studio yields output that contains a binary representation instead of a human-readable string. I suspect this is to avoid ambiguity but I guess someone forgot that there are unambiguous formats available (*cough* YYYYMMDD *cough*).

http://connect.microsoft.com/SQLServer/feedback/details/745796/ssms-generate-scripts-for-data-scripts-datetime-as-binary

In #752629, I complain about the broken procedure sp_msforeachdb, which I've complained about many times before and have even written a more functional, alternative version that actually works. I really think they need an ultimatum here: either fix the thing or turn it off by default. The only downside to turning it off by default: this will break maintenance plans that are probably breaking silently anyway.

http://connect.microsoft.com/SQLServer/feedback/details/752629/disable-sp-msforeachdb-by-default-or-fix-it

Why people think some SQL Server 2000 behaviors live on... 12 years later

AaronBertrand — Tue, 28 Feb 2012 16:57:00 GMT

A long, long time ago in a galaxy you can reach out and touch right now, ORDER BY in a VIEW meant something. If you had a view like the following:

CREATE VIEW dbo.Rubbish
AS
    SELECT TOP 100 PERCENT Trash
        FROM dbo.Garbage
        ORDER BY Trash;

And then you ran a query such as:

SELECT Trash FROM dbo.Rubbish;

You could reliably expect the rows to come back ordered by Trash in ascending order, even though it is relatively clear that the ORDER BY here serves a completely different and overloaded purpose - to dictate which rows should be filtered by TOP.

When SQL Server 2005 came around, changes to the optimizer meant that this behavior - which was never documented, by the way, hence never guaranteed - ceased to work as reliably as it had in previous versions. In fact in this case the optimizer sees TOP 100 PERCENT and simply throws out both TOP and ORDER BY. Compare the differences in the plan from the view with and without the ORDER BY:'

Notice how there is no sort operator in the query without ORDER BY? Because we didn't tell SQL Server what order we want, it's free to make its own choice. In this case it is choosing a clustered index scan which is not based on the Trash column. You can see the end result of the difference in these plans by comparing the results from the select:

Obviously only the latter result honors the outer ORDER BY, and in fact both cases have discarded the inner TOP/ORDER BY combination.

People are surprised by this change, and in fact variations on the symptom come up in forum discussions all the time. Further to the confusion, that still exists today, early on there were some very vocal customers who screamed bloody murder and were very stubborn about changing their code to put the ORDER BY on the outermost query where it belongs. After upgrading to SQL Server 2005 they cried foul until Microsoft finally submitted - creating a trace flag that forced the optimizer to behave the old way. Note that this trace flag is only valid in SQL Server 2005 and SQL Server 2008 - in SQL Server 2008 R2 and SQL Server 2012, the trace flag seems to be a no-op (at least in every case that I've tried). Even if you are still on 2005/2008, I highly recommend using this option as a last resort only, because it changes the way the optimizer works and this can have other undesirable impacts on your entire workload.

Hopefully none of this is news, as it has been discussed at length both online in general, and more specifically on Connect. Where it gets more interesting is that there have been several bugs reported that tools within Management Studio, such as the view designer, actually encourage this behavior and help perpetuate the myth. The issue I noticed this weekend is that, while most bugs have been closed as won't fix, one (#249248) has been closed as fixed. So I immediately fired up the latest build of Management Studio I have, right-clicked the views node, and selected New View. Once I added a table, what I saw was not very promising, as I was able to choose Sort Order and Sort Type:

So Management Studio for SQL Server 2012 still allows me to specify a sort order and sort direction for any column in the view! And what does it do to the SQL? It injects TOP 100 PERCENT / ORDER BY! Bad, bad, bad!

On the plus side, if I try to *save* this view, I at least get some kind of warning:

For Google/Bing-fu:

Warning: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
Click CANCEL to discard your modifications.
Click OK to save the view.

It's great that they're finally trying to stop people from believing that ORDER BY in a view means anything about ordering the result. But the problem that's being overlooked here is that not everyone uses the view designer to create and save a view in a single session. They may be generating code for a view that they're going to cut & paste into a CREATE/ALTER view script elsewhere, to use in a CTE or derived query in a more complicated script, or to send to a colleague. In a lot of cases they are never going to see this warning, because this won't magically pop up when they try to use the syntax in other scenarios.

So I left a comment on the Connect item with a little detail about that, because I'm unfortunately going to have to disagree that this issue has been "fixed." I'm not sure why the Sort options in the view designer can't just be hidden without changing the behavior of the tool in general - can't it just always behave as if I hadn't changed those options at all?

I do acknowledge that there are some edge cases here, where someone really does want to create a view that selects the top (n) rows ordered by some criteria. So perhaps my suggestion to remove the columns altogether is a bit harsh. But when I choose a Sort Order for one or more columns, it should not just throw TOP 100 PERCENT into my query; the query that's produced at this point shouldn't be valid. Instead it should prompt me with a similar warning to what I get now when I click Save, with additional wording that implies, "If you really do mean to take some subset of the rows in the table, please add a TOP (n) clause to indicate the number or percentage of rows you want to return."

Agree with me? Please go comment and vote!

Comparing multiple plans in SQL Sentry Plan Explorer

AaronBertrand — Fri, 06 Jan 2012 15:54:00 GMT

I’ve seen the question a few times now: “How do I compare two plans side-by-side in Plan Explorer?” Management Studio allows you to view multiple graphical plans that are visible simultaneously, either by generating plans for every statement in a batch, or by generating plans in separate query windows and then splitting the panes. However, at least in my estimation, a lot of the other metrics and properties are more valuable when performing comparisons. In SSMS this is tough, because you can only have one tooltip visible at a time (regardless of how many plans are visible). Same for the Properties panel – only a single plan or operator can populate this panel at one time. So even though you can compare high-level graphical details quickly, you will likely end up with multiple instances of SSMS to perform true side-by-side comparisons of the lower-level details.

In Plan Explorer (free download) it is very easy to compare a lot of the metrics side-by-side for two (or more) plans. Since Plan Explorer can generate estimated or actual plans for you, it’s as simple as entering multiple queries in the Command Text pane, and then generating an estimated or actual plan:

I recommend trying the Vertical Layout, so that the various plan-related tabs and the Statements Tree are a lot closer together (reducing mouse and eye movement):

Here is a quick comparison of the two default options (vertical on the left, horizontal on the right – click to enlarge):

(There is also a Custom Layout option if you wish to change from the default options.)

And you can still compare a lot of the other details about two different statements by looking at the other columns in the Statements Tree tab. For example, comparing costs, row counts and operation counts between estimated or actual plans can give you a much more accurate picture of the performance impact of different statements:

And you can switch between graphical plans quickly by using arrow keys or mouse clicks to switch between statements:

Since the other tabs are context sensitive, you can do this for any tab: simply highlight a tab (e.g. Top Operations) and, as you switch through the rows in the Statements Tree, the tab below will update accordingly.

For more complex plans, and depending on how much screen real estate you have, you may prefer to open two instances of Plan Explorer so that you can interact with the plans a little more independently. In this case you won’t be able to see percentages or other relative comparisons directly against one another, but for most eyeballing, it’s still better than what you get in Management Studio IMHO.

That all said, comparing two graphical plans side by side is definitely on the list for a future update; in the meantime, I hope the above is helpful.

T-SQL Tuesday #25 : T-SQL Tips 'n' Tricks

AaronBertrand — Tue, 13 Dec 2011 18:50:00 GMT

This month's T-SQL Tuesday is being hosted by Allen White (@SQLRunr) and is about sharing your T-SQL tips and tricks. Since I know many people will share their T-SQL magic and wizardry, I thought I would share a couple of productivity tips - so still T-SQL related, but more how to take advantage of some features in SSMS.

Drag column list onto your query window

Whenever I give my Bad Habits to Kick presentation, I remind people that - even if they're not using 3rd party code completion tools (many of which offer SELECT * expansion) - there's little excuse to use SELECT *. This is because Management Studio makes it very easy to list out all of your columns in one motion. I'm always amazed when I ask how many people didn't already know about this - and most times it's more than half the room.

The process is quite simple - expand your table, click on the Columns node, drag it onto your query window and let go:

Now, it's probably not in the exact format you want, it won't properly delimit any [keyword] or [reserved word] you use for a column name, and you'll have to do this multiple times when you're performing a join (and you'll have to add your own table/alias prefixes to each column as well). But I'd rather spend time using TAB and ENTER to correct formatting than to try and type out all the column names individually.

Use snippets

If you find yourself writing the same type of code over and over again, the new snippets feature in SQL Server 2012 will likely be very useful for you. What does this have over templates? For one, I find it much easier to use once you have your snippets created. There is no hokey VB6-style dialog to perform token replacements, and you can actually store all your custom snippets in a network location - so if you want to enforce coding standards you have a fighting chance by pointing everyone's SSMS at the same snippet location. I'll be honest, though, setting up the template is a bit of a pain - you need to hand-massage XML manually because as of yet there is no easy way to generate these from within Management Studio. But once they're in place, they're very easy to work with. Just right-click within the query window, choose "Insert Snippet", and follow the rabbit:

One of the pieces of code I write a lot for administrative tasks (or for helping folks out in the wild) is a cursor. I tend to have a hard time memorizing the options I always try to use to make a cursor as efficient as possible (something else I cover in my Bad Habits to Kick presentation). The snippet eliminates this problem. Once I've selected the "Declare Cursor" snippet I've saved, I get the following in my query window:

You'll notice in the diagram that "c" is currently highlighted and the cursor is there; all other instances of "c" are lightly outlined. I can type any name I want to overwrite "c" and, once I hit tab (which moves me to the next token), it will replace all instances of that token with the new name I've typed. So this is a little easier to work with than the floating window in templates, though you lose this easy editing capability if you start doing other things with the script first (need to remind myself to file a bug or suggestion about that). Changing "c" to "x" throughout this code sample took two keystrokes, and I'm ready to add the actual query:

I plan to write a much more thorough blog post on the snippets feature, because I think it will become a pretty popular time saver once the latest version of Management Studio becomes more common on the desktop. In the meantime, if you're already playing with SQL Server 2012 RC0, I highly recommend you check this feature out!

Avoid dynamic SQL in generated scripts

I see a lot of people complain that when they script objects from Management Studio the object text ends up in dynamic SQL. I don't blame them - this makes it very cumbersome to read, never mind modify, the object text before copying, running or saving the script.

In SQL Server 2008 R2, this is controlled by the option Tools > Options > SQL Server Object Explorer> Scripting > Include IF NOT EXISTS clause:

In SQL Server 2012, the option has changed slightly to Tools > Options > SQL Server Object Explorer > Scripting > Check for Object Existence:

Why did it change? Because, depending on the action, it actually performs an IF EXISTS check (e.g. for a DROP or ALTER), not always an IF NOT EXISTS check (for a CREATE). Some background in the following Connect items:

http://connect.microsoft.com/SQLServer/feedback/details/242799

http://connect.microsoft.com/SQLServer/feedback/details/264310

http://connect.microsoft.com/SQLServer/feedback/details/624075

(As an aside, I think it's funny that they call the options category "SQL Server Object Explorer" - good thing they're specific, because you might get it confused with the Lego Object Explorer, Oracle Object Explorer or Windows Explorer Object Explorer.)

In any case, when these options are set to True, the scripts you generate often yield dynamic SQL, and even when they're set to false they still sometimes perform the check for existence (specifically in 2008 R2). Here is a brief wireframe of the options and how they affect each scripting method:

Note that with the "DROP and CREATE" option, the existence check for the CREATE is unnecessary - after all, if the object already exists, the script should have just dropped the object (though both checks may have failed if there is an object with the same name that is *not* a procedure). In general, most people won't want to perform a DROP and CREATE anyway - they lose dependencies and permissions when they do this (though permissions can be scripted with a different option in the same category). Until they develop "CREATE or REPLACE" functionality (please vote and comment!), ideally you should have a script that creates a stub for the object only if it doesn't already exist, then runs an ALTER - then you don't have to re-apply permissions, you don't have to worry about whether the object already exists, and as an added bonus you retain dependency chains. So your scripts can look like this (still using dynamic SQL, but it's very simple and doesn't present parsing or reading issues):

IF OBJECT_ID('dbo.proc_name') IS NULL
BEGIN
    EXEC sp_executesql N'CREATE PROCEDURE dbo.proc_name AS SELECT 1;';
END
GO

ALTER PROCEDURE dbo.proc_name
AS
... -- actual script body from source control or elsewhere goes here

Unfortunately there is no way to train SSMS to do this today, unless you write a scripting add-in.

Wrapping up

I hope that at least one of these little tricks helps you in some way. It's always fun participating in T-SQL Tuesday, even if I'm often trying to bend the rules a little bit.

Connect Digest : 2011-12-01

AaronBertrand — Thu, 01 Dec 2011 14:36:00 GMT

Where is my graphical ShowPlan?

Many of you on SQL Server 2008 R2 are experiencing a major annoyance with SSMS since installing service pack 1 - when clicking a plan from grid results, you get raw XML instead of the graphical plan. I wrote about this symptom and a workaround in a previous post. In the original Connect item, they state that it will be fixed (likely in the next CU). But @AdamMachanic points out that there are some cases that still aren't correctly covered - they have marked this as "By Design" but I think it needs more input that all valid plans should be able to show valid graphical plans. This could use some more votes and, more importantly, some comments urging Microsoft that if SQL Server can produce a plan, Management Studio should be able to present it graphically. After all, these "invalid" plans work fine if you save them as .sqlplan files and then open them manually - it is something different about the path SSMS takes when evaluating the XML. So please vote and comment!

#652855 : Valid showplan XML fails to automatically produce graphical showplan

When will my evaluation edition expire?

I'm seeing more and more people thank me for my instructions on how to remove an expired evaluation edition. I also wrote a warning post when the first SQL Server 2012 CTP would start expiring for people. I think it's more important than Microsoft is willing to admit to make it more obvious that an evaluation edition is about to expire - so that people have a chance to prevent this hassle. Hell, it might even lead to an earlier sale. Since quality > quantity at Connect, please tell Microsoft why you think this is a good idea!

#257649 : Make expiration date of eval edition more visible

What database am I in?

When you use the "SELECT TOP n ROWS" feature in Management Studio, it creates a new query editor window with a query like "SELECT TOP (1000) * FROM [db].[dbo].[table]" - no semi-colon, no order by, and no USE statement. The query comes up in the master database (or perhaps your default database, I haven't tested this thoroughly). This means if you want to modify the query, you either need to manually switch to the correct database, or you need to keep adding the database prefix to each object. In this Connect item I point out the issue with arbitrary order, and the fact that the code is similar but not identical to the Script > Table As > SELECT options, but most importantly urge them to make the database context correct. They've deferred it for now, but may reconsider if they get more pushback.

#684247 : SSMS : SELECT TOP n ROWS should have correct database context

Why is SSMS so slow?

I've complained in the past about right-clicking a table hanging SSMS, eventually did experience the hang caused by hovering over the query line in an execution plan, have been very frustrated by query window behaviors when a database connection has been temporarily lost, and @PaulRandal has complained about delays when a network location is no longer available. Recently Erland Sommarskog filed an issue demonstrating that in SQL Server 2012 RC0, SSMS has some new chatty behaviors that can be quite detrimental on a slow connection:

#708296 : SSMS 2012 generates stray queries on single keypress

Why doesn't my CASE expression short circuit?

While CASE usually evaluates its clauses in left-to-right order, this behavior is not guaranteed. I demonstrated this and pleaded for an update to Books Online to make it clear to users that short-circuiting cannot be relied upon. I'm hoping you will add a comment to reinforce that the current documentation doesn't tell the whole story and in fact may be misleading users into believing that the behavior they see in one expression will remain true for all expressions...

#690017 : CASE / COALESCE won't always evaluate in textual order

Getting graphical ShowPlans back in SQL Server 2008 R2

AaronBertrand — Fri, 28 Oct 2011 17:42:00 GMT

For those of you using the SQL Server 2008 R2 version of Management Studio, and have applied Service Pack 1, you have probably noticed that clicking on a ShowPlan in grid results no longer shows the graphical plan, but rather opens a new window with the raw XML.

For example, running the following query:

SELECT TOP (5) qp.query_plan
   FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp;

...yields the following results:

Clicking on any of those hyperlinks, prior to applying Service Pack 1 (or in earlier versions of SSMS), would launch a graphical plan -- which you could examine inline, save to the file system, or open in SQL Sentry Plan Explorer. After applying Service Pack 1, however, this launches a new XML editor window:

...which isn't very useful, unless you're really good at speed-reading XML. This was reported by @AdamMachanic in Connect #652855 (for some reason, closed as "by design") and has been observed by countless others. The problem is caused because, due to slight changes in SP1, SSMS has difficulty determining that the resulting XML is, in fact, a ShowPlan document.

One workaround is to save the XML as a .sqlplan file, and then launch it by double-clicking or using File > Open in either SSMS or Plan Explorer. And as @Kendra_Little pointed out on twitter, you can also just right-click the result, Copy, and then paste into Plan Explorer.

Another workaround, if you have a ~~Denali~~ SQL Server 2012 installation handy, is to copy the newer showplanxml.xsd file from SQL Server 2012 and use it with SQL Server 2008 R2. To do this, close down all instances of Management Studio, open Windows Explorer on the machine running SQL Server 2012, and go to the following folder:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2004\07\showplan\

Copy the showplanxml.xsd file. On the machine that is running the 2008 R2 client tools, go to the following folder:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\

Rename the file showplanxml.xsd to showplanxml.xsd.old, and then paste the newer version of the XSD file in its place. (Note that your Program Files folder may be in a slightly different location, and on 32-bit systems, the (x86) won't be there.)

After you have the newer version of the XSD file in the showplan folder, when you open Management Studio and repeat the above steps, you should see the graphical plan as before. This works because it corrects the issue with the invalid XSD file and, even though there have been substantial changes to the XSD schema for SQL Server 2012, the plans you're retrieving from older versions are not going to produce anything that interferes or conflicts with the older XSD, so the new stuff is just fluff.

Now, I haven't done exhaustive research on this (I've tested a few dozen plans with no issues), and it's certainly not supported, but if you want to get the old behavior back without waiting for some future cumulative update, you now have a workaround that doesn't require multiple extra steps for every plan you open. Still, I'd like to hear if you encounter any issues with this workaround.

If you want an official, supported fix from Microsoft, they will be happy to provide you with an updated 2008 R2 version of the XSD file. Before it is released through a cumulative update, however, you'll have to go through official CSS channels to get it. Make sure to reference Connect #652855.

SQL Sentry Plan Explorer v1.3

AaronBertrand — Tue, 25 Oct 2011 16:43:00 GMT

On Friday, we published a new version of our free query plan analysis tool, SQL Sentry Plan Explorer. As noted in the change list, we've added a couple of important features.

SQL Server 2012 support

With a whole slew of changes to the showplan schema in Denali, we've added operators and columns to reflect these changes. For example, you will notice the new Window Spool operator:

As well as the ColumnStore index scan operator, and details about the new batch execution mode:

And while we don't add all new columns by default, you can also optionally show data for new items such as the FORCESCAN hint (also added in SQL Server 2008 R2 SP1).

Table-level I/O metrics

When you generate an Actual Plan from Plan Explorer, we collect additional runtime metrics you won't get from Management Studio's showplan - duration, CPU, and I/O. While it is easy to show the total reads for a query, it is not always easy or intuitive to decipher which object(s) those reads came from. We've added a Table I/O tab that breaks it down by object, making it very easy to see where the majority of your I/O is coming from:

Note that this and other runtime information is not available with an estimated plan of any kind, or with an actual plan generated by Management Studio - the information simply isn't there. We can only show runtime metrics when you generate an actual plan within Plan Explorer (or if you've captured plans using SQL Sentry Performance Advisor).

We hope you find these enhancements useful and, as always, please let us know if you have any issues or questions.

As a side note, the SSMS add-in hasn't changed.

A demo kit for SQL Sentry Plan Explorer

AaronBertrand — Tue, 13 Sep 2011 16:31:00 GMT

Plan Explorer is a free application from SQL Sentry, designed to make execution plan analysis faster and easier. It was developed in response to customer demand for deeper query analysis, but was originally intended only for the paid version of the software. When the team realized how useful the tool could actually be, they decided to give it to the community for free. When I was deciding whether to join SQL Sentry or stay at my previous gig, this selfless gesture quite literally sealed the deal.

You can read more background about Plan Explorer in this blog post from Greg Gonzalez.

Download Plan Explorer

Before you get started, please make sure you have the most current version of Plan Explorer installed. You can always download the latest version and the companion SSMS Add-In from the following site:

http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp

This Kit

This kit is designed to help you present Plan Explorer to your colleagues at the office, a SQL Saturday, or a local user group meeting. We have had multiple requests for some kind of demo to help people share the tool with their audiences without having to fabricate their own problem queries or interesting plans. So I put this post together in response to that demand and to help give people a jump-start on showing off the tool. That said, this guide can also be used to familiarize yourself with Plan Explorer even if you don't have an audience to present to...

You can download the kit from here:

http://bit.ly/PlanExplorerDemoKit

In this kit you will find a ZIP archive containing a very brief PowerPoint presentation and 7 sample plans. The plan files are named with successive numbers and descriptive names; the numbers just represent the order in which I present them. If you have better plans to showcase, by all means, do not limit yourself to the plans I’ve provided. Also, do not feel like you have to present the plans in the same order.

This Page

This page is intended to be a living, breathing guide on how to present Plan Explorer. Since it will be updated as we add new features to the tool, you should always check back here for updates. I think this will be a much more useful approach than including a Word document or PDF file in the kit itself.

When I’m presenting about Plan Explorer, I first walk through the limitations of plan analysis in Management Studio. This isn’t meant to knock SSMS or Microsoft or the SQL Server team, but it can’t help from sounding like it. After all, there really hasn’t been much development in this area since SQL Server 2000, unless you count the missing indexes recommendations or new operator icons. :-) Again, these are not jabs at the team or the product – just a symptom of focus being elsewhere, with the end result being that plan analysis isn’t as efficient as it could be. Most people familiar with execution plan analysis in Management Studio will already be aware of these weaknesses, but if you have a mixed audience, you may want to spend more or less time on these points. I won’t list them out individually up front, but I will call them out at key points in the description of the included plans below. (I’ll post a separate discussion about the weaknesses of Management Studio’s built-in plan analysis features.)

The Plans

1.ActualWide.sqlplan

I open this plan in Plan Explorer (in fact before I start I have all 7 plans open in Management Studio, and 7 instances of Plan Explorer open with each plan pre-loaded). To demonstrate the functionality of the add-in, you could just as easily load each plan in Management Studio, then right-click and select “View with SQL Sentry Plan Explorer.”

I point out several visual aspects, starting with the color. I ask folks what stands out to them, and invariably someone shouts out, “Red!” I point out how you can spot the expensive nodes pretty quickly – I can also point out that the operator costs are scaled by color (red -> yellow), and that certain operators are also highlighted below the operator icon (key lookups in orange, scans in yellow).

I show how we’ve optimized plan layout by pointing out that the horizontal space is re-used to reduce the amount of vertical scrolling. This plan isn’t fantastic for showing space efficiency because it’s wide, but the vertical impact is clear if you compare it to the same plan in Management Studio (these are the same 8 operator nodes, taking up much more space in Management Studio):

Next we can move to the Plan Tree tab. This is a text-based diagram similar to the familiar SET SHOWPLAN_TEXT mode, however it comes with loads of additional data. You’ll notice that, like the graphical plan, key lookups are highlighted in orange, and scans are highlighted in yellow. You’ll also see highlighting on the Actual Rows / Est Rows columns, when the difference exceeds a certain threshold – if you hover over that space it will tell you the variance and suggest that there may be a statistics issue. This can be helpful in quickly identifying potential issues by highlighting significant differences in actual vs. estimated:

The Top Operations tab shows the same data as the Plan Tree tab, but now the data is sortable. So you can order by actual executions, highest first, and see that perhaps that key lookup is something you want to look deeper into. There is a LOT of data on these two tabs, and I don’t want to influence what you want to focus on – if there are metrics here that you typically use to help guide performance troubleshooting, please feel free to focus on those. Also don’t forget to right-click any column header and look at the Column Chooser… there are many more metrics available that we don’t show in the UI by default. If you want to add any metric to the column list, you can simply drag it from the Column Chooser onto the column header:

I use the Query Columns tab to show one thing – the key lookup on the table PerformanceAnalysisPlanOpTotals. By highlighting the non-covered columns that are fetched via the lookup, we provide a much quicker way to figure out either (a) what columns to add to they key or include list of the index, (b) to drop those columns from the query (since often these are the result of SELECT * or ambitious/aggressive column inclusion), or (c) to just leave the query alone:

If time is flying you can view the lookup on PerformanceAnalysisTraceCachedPlanItems; this looks like a simple case where you might add the CausedRecompilation column as an INCLUDE column rather than change the index key, but if you scroll over to the filters column, you will see that it is used in a filter predicate. This information can help guide whether adding it as an INCLUDE column really does makes sense (it may be okay in some cases, but it is certainly a valid data point to consider).

You may want to show the Parameters tab, where you can rule out parameter sniffing in this case – since the compiled and runtime parameter values are equal – but point out that you could sniff out such discrepancies here.

You can also point out that when you have an actual plan, you can quickly and easily switch between the actual and estimated plan, without having to re-generate them, by simply clicking the following button on the toolbar:

This lets you spot major differences between the two versions of the plan, whether it be which node is the most expensive, the movement of data between operators, or the types of joins or operators involved.

Finally, you can show the Text Data tab in the middle, and scroll around to show how much more of the statement we can see, that it is syntax highlighted, and that it is much easier to read than the tooltip in Management Studio. Now as it turns out, the XML node that hosts the statement text is limited to 8k, so we truncate the statement as well – but we show many more complete statements that way. Ultimately, though, we can only show you the information that is in the plan.

2.EstimatedTall.sqlplan

I open this plan in Management Studio, and zoom to fit. I usually make a joke about challenging anyone in the audience to find the most expensive node within one minute. This isn’t really a fault of Management Studio, but rather of the plan itself.

Then I open the plan in Plan Explorer. I admit that there are certainly some plan shapes that we can’t make look good, either, and that finding the most expensive node visually in Plan Explorer isn’t any easier in this case.

But this is where the power of the Statements Tree comes in. Forget about the graphical plan and move to the Statements Tree node. Click on the “Est Cost” column header to sort by that column, and highlight the row with the 33.2% cost:

Boom, you’ve found your problem statement, and now you can focus on the graphical plan for just that statement. You don’t have to tune this any further; it’s just to demonstrate how quickly you can focus in on the important parts of even an extremely complex batch, and ignore the rest of the noise.

3.ParallelSeek.sqlplan

I show this plan in SSMS, explaining that a user complained about this query - it's suddenly much slower than it used to be. Nothing on the graphical plan in Management Studio stands out to me – you can ask the audience if there is anything obvious to them. They might ask to see the tooltips on each operator, and they may even spot the difference between estimated and actual rows on the Nested Loops or Index Seek nodes, but you can point out how cumbersome this can be to try and track that down.

With Plan Explorer you can reinforce how much quicker you can get to data that is hidden in properties panels and/or not immediately obvious in Management Studio. When you open the plan you can immediately see the expensive red node:

This might also be a spot to show a couple of the ways we can change the appearance of the data:

Right-click the plan and change Costs By > to I/O – you immediately see that the most expensive node switches from the Sort operator to the Index Seek:

Right-click again and change Line Widths By > to Data Size. You can point out the difference in the thickness of the arrows, and also that the indicators above/below the lines change to MB instead of row counts.

But perhaps more importantly, we can switch to the Plan Tree tab and immediately see a cause for concern – on the rows that show the Nested Loops and Index Seek operators, we can see that parallelism was intended, but the work was not evenly distributed across threads. While this can sometimes be attributed to spills to tempdb and other causes, in this case we can clearly see the orange highlighting that shows a huge discrepancy between estimated and actual… which means this parallel plan gone wrong is most likely due to out-of-date statistics:

4.CorrectPercentages.sqlplan

This plan is just used to demonstrate a bug in Management Studio where the math doesn’t always work – if you work the plan from right to left, top to bottom, you can count out the percentages and add – 16 + 62 + 11 + 14 + 11 + 1 + 11… you quickly get way over 100%. You don’t necessarily need to show this plan in SSMS, but you can just ask a question like, “Has anyone ever seen a plan in SSMS not add up to 100%?” If you don’t get some nods or raised hands, you might not be at a SQL Server event. (For more info see Connect #267530 - closed as "by design" and a few others such as Connect #370798 and Connect #621330.)

If you open the same plan in Plan Explorer, the math actually adds up, and you can repeat the spot checks – or just tell them to trust you that it adds up to 100%. This might also be a good plan to demonstrate the costs by cumulative cost rather than by individual node – if you right-click the plan and choose cumulative costs, you will see that the color scale changes from reds to yellows to blues. With this you can follow sub-trees to see the path the most expensive costs take, and you can see that it’s different if you switch between CPU and I/O.

5.NestedViews.sqlplan

This plan shows some operations involving nested views, where it isn’t always easy to wade through all the hierarchy and land at the actual base tables involved. In Plan Explorer, open this plan and move to the Join Diagram tab. You may want to expand the area vertically or pre-arrange things before the presentation for the most optimal layout. You’ll see what resembles a database diagram in Management Studio, but it is showing only the join columns in each base table that are used, with arrows to illustrate the relationships. This is much easier than trying to piece together the views and their underlying tables as shown in Management Studio:

6.MissingIndex.sqlplan

In Plan Explorer, the missing index is suggested only on the appropriate plan – in current versions of Management Studio, there are scenarios where the same index is recommended on every plan in the batch, even statements that don’t involve the table in question (and some that don’t involve any tables at all - see Connect #518467 for more details).

In the Statements Tree tab, we automatically add a column to show Missing Indexes if any of the plans contains information about an index suggestion. For this plan, you will see a row that has a value of 1 in this column:

If you highlight that row, then look down at the graphical plan, you can see the little yellow exclamation mark / warning sign on the lead node. If you hover over that you will see a warning at the bottom that mentions missing indexes; right-click the node and choose “Missing Index Details…” to show the CREATE INDEX statement.

You’ll note that we generate the exact same CREATE INDEX statement that Management Studio does, but we add a little extra disclaimer that points out that just because adding an index will help this query, does not mean it’s a good idea overall – adding or dropping indexes should never be entertained solely in the context of a single query but rather with a healthy knowledge of all the objects and queries it will affect over a full business cycle.

7.RowsPerExecution.sqlplan

This plan shows a case where we auto-correct rows and cost information for lookup and spool operators. If you compare this plan in Plan Explorer and Management Studio, you will see that while SSMS gets the total operator costs right (disregarding the incorrect percentages, of course), in the tooltips you will see that it does not extrapolate the number of executions correctly and, therefore, total number of rows, estimated CPU and estimated I/O costs are all wrong.

Here are the properties for the most expensive Merge Join operator in SSMS and Plan Explorer. You will notice a significant different in the estimated I/O cost for the operator, meaning if you were trying to focus on I/O alone, you might ignore this node altogether if you were looking in Management Studio:

And here are the properties for one of the Key Lookup operators, which demonstrate numbers off the mark for estimated CPU cost, estimated I/O cost, and number of rows:

These differences are also well illustrated on the Query Columns tab in Plan Explorer.

This is not an existing plan

If you have an instance of SQL Server on your presentation machine, you can illustrate the fact that Plan Explorer can derive runtime metrics without a lot of work. Take a query, that can be as simple as:

SELECT * 
    FROM       sys.all_objects AS so
    INNER JOIN sys.all_columns AS sc
    ON so.[object_id] = sc.[object_id];

Run the query and generate an actual plan from within Management Studio. Observe the data you get back with that, and ask people how they might capture duration, CPU and I/O runtime metrics to go along with this (the answers will likely be SET options or profiler).

Then open a new instance of Plan Explorer, paste the same query into the Command Text window, and click on the “Actual Plan” button on the toolbar. There is a warning (more on that below); once you click OK on the warning, you will be prompted for connection credentials; provide them, and hit Enter.

When the query has completed, you’ll see the plan diagram, but up in the statements tree you’ll see some columns that were previously empty – you can now collect Duration, CPU, Reads, Writes, etc. without explicitly setting those options and without having to run a profiler trace to capture them. This shows how you can make minor adjustments to the query, and immediately see how it impacts the plan, these runtime metrics, or any of the other data we show on the various tabs.

More about the warning: you’ll want to point out that Plan Explorer is not a query tool, and as such, while the query is executed at the server, the results are simply discarded. To reinforce a point that Kalen brought up in the comments, this includes all DML queries - INSERT, UPDATE, DELETE, MERGE as well as SELECT - so you should consider generating an actual plan identical to running the same query in Management Studio, without the output. You can turn off the warning, using the Help menu option, "Warn on Actual Plan Execution."

Further Exploration

When I’m giving this presentation as part of a vendor session, I usually go into the integration with SQL Sentry Performance Advisor – this adds a ton of additional functionality, such as automatic plan collection with Top SQL, allowing you to look at plan differences over time, correlation of the entire call stack, side effects such as auto-stats (StatMan calls), and various other things including the fact that you can work from a problem down to investigating the plan at the time without having to have generated the plan first – if the plan has changed, in real time, you don’t have that capability unless you were actively capturing it at the time. But I realize this isn’t a sales pitch – if you want a more substantial demo that includes information about how plan analysis works in Performance Advisor, we’re always available to help out. :-)

I do plan to extend this demo kit over time, or to create new and separate entities altogether - ideally I'd like to focus on known databases, such as AdventureWorks, and step through at a very low level the process of getting from problem detection to resolution.

Questions? Comments? Other Feedback? Please let me know at abertrand@sqlsentry.net

Dear SQL Tools Team(s): Stop Starting Over. Seriously.

merrillaldrich — Sat, 20 Aug 2011 22:52:20 GMT

I have two little boys at home, and my parents were both teachers. It gives me a strange relationship with our public schools – I am passionate about them, and the quality of education I want for my kids, but I have to keep my distance a little, lest they drive me completely around the bend. One reason it’s so frustrating: among all the complex challenges our schools face, administrators and school reformers alike are stuck in an infinite loop of starting over again before anything is finished. Before one curriculum is complete, someone wants a new one. A new educational idea arrives on the scene before the last one even gets a chance to be evaluated. We have to break eggs to make an omelet, but all we do is the egg-breaking part. And our school system is the very apex of the idea, “too many cooks in the kitchen.”

Sadly, the people working on all the flavors of SQL Server management tools seem to me to be stuck in the same type of self-destructive loop. For now six or eight years, depending how you count, the management tools for SQL Server have gone sideways, or around in a circle, instead of forward.

Five Versions 2.0 != One Version 10.0

This post was prompted by a short exchange I had on Twitter this morning. Someone asked a simple question: Why is there, again, another management & dev tool for SQL Server. My off-the-cuff response was that the team at Microsoft keeps on starting over again instead of moving forward. But the more I thought about it, the more I realized that that is literally true: through Management Studio, BIDS, Team System/Data Dude and now Juneau, and even to some degree Crescent, the various tools teams continue to slide sideways, and deliver sort of disjoint, bland, V 2.0 mediocrity. We need version 10 at this point, not version 1 or 2 again. Really.

This isn’t an attack on any of the developers working hard on those tools and features. Some of the specific features and capabilities are really interesting. The problem is with the process – just as in some fundamental public school issues. In the current vernacular:

(Smart People + Good Intentions) * Deeply Flawed Coordination = #EPICFAIL

There are a lot of good ideas. There are a lot of challenges. There are a lot of conflicting requirements and conflicting opinions about what a tool like this should be. That’s what design is for. If the problem were simple, design would not be necessary. This is just a design problem.*

80% of Good Design is Synthesizing Conflicting Requirements

So it’s a hard problem. Administrators want administration tools, monitoring, troubleshooting. Developers want development tools, code writing environments. BI people maybe want graphical programming tools like BIDS SSIS projects. We all want the tools to be elegant and simple to use. I get that. But that doesn’t mean you’ll solve the problem by ten years of starting over again, annually, creating different flavors of the same tools. It gets solved by leadership, visionary design, and synthesis. It is possible to make one environment that meets all these needs. But it takes conviction, courage, time and, most of all, design.

What do we have now, six years or so into the SSMS “era?”

Still total inconsistency in the way SSMS treats basic features like scripting objects.
One passable query writing environment in the SSMS query window itself, which somehow(?) has not made it into any other place in the whole suite of products. There is no even C- level query editing capability in any MS BI tool, for example. It’s not even in the Table Designer. But the code’s been sitting there in SSMS all this time.
B- Intellisense, with better provided by a third party.
No code formatting, again with a fine third party solution.
The object tree in SSMS has had the same cruel joke of a design, that most people seem to either barely tolerate or hate, this whole time.
Activity Monitor. Ugh.
A query plan viewer that is so stale, so old fashioned, that a third-party company (and thank you SQL Sentry) could walk in and create a free one that is many times better.
Decent diagnostic data from the engine (DMV’s) with NO decent UI associated.

The reasonable thing would have been to put someone with some design sense on this problem to improve the tool. Version 3, version 4, version 5. Forward progress.

Instead, people complained from outside MS, and people within perhaps had some new ideas, but instead of having the courage to synthesize those challenges into a better product, we keep going in a circle. So I am begging: would someone please stand up over there in Redmond and say, “enough.” I want SSMS 3.0.

* This is the thing Steve Jobs, love him or hate him, “gets.”