Even if some client tools in SQL seem to be forward compatible with later versions, you may be missing something important

Today, a colleague exposed the following case:

 

In the execution plan I do see some exclamatory marks on Sort, Hash Joins operators but there is no warning information available. I am using SQL Server 2012.  How do I interpret the exclamatory marks?

 

 

 

Here goes my explanation to this problem:

New execution warnings have been included in SQL Server 2012. Among those, you have SpillToTempDb which can be raised by spilling sorts, spilling hashes, or spilling exchange pipes.

Since you are rendering the showplan using a version of SSMS which is previous to the one that comes with SQL Server 2012, it doesn’t understand that new warning type. It only notices there was some Warning associated to that iterator so it overlays the warning icon on top of the regular Sort icon, but the Properties pane doesn’t have the logic included to show some human readable text describing what the warning was. If you run the same thing with SSMS 2012, you will see something like this:

Also notice that, if you either “Save Execution Plan As…” or “Show Execution Plan XML…” from a version of SSMS previous to the one released with 2012, both operations deserialize the XML-based Show Plan received from the server to the ShowPlan strong type (as defined in that version) and will serialize it back to strings. In that process, you will lose any new types and attributes like the SpillToTempDb and SpillLevel and it will just be replaced by a <Warnings/> element.

The problem with that is that if you send that plan to someone who is going to assist you with analyzing that information, that person will also miss something even if he uses the latest version of SSMS.

So tend to use latest version of GUI tools even if you are targeting previous versions of the engine from it. Backward compatibility is guaranteed, forward compatibility is not. And this is one of those cases where a previous version of SSMS partially works with a higher version of the engine, although you don’t clearly notice at first sight what is it that is not working or that is missing.