Connect Digest : 2011-12-01

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

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)