Sql-server – Filter not working on Showplan XML in SQL Server trace

First let me just say - fantastic looking site :)

This sort of thing is exactly what I wrote that XSLT for - I'm glad that others are finding it useful!

I have to confes its something that I wrote a while ago and then got kind of side-tracked, there are some improvements that I've been planning to make for a while now that I hope to get around to actually implementing soon!

Some links:

• html-query-plan Project homepage
• Tools for visualising execution xml plans as HTML (Stack Overflow Question)

Let me know if you have any suggestions for improvements!

Before a replay, I would:

1. Run the Upgrade Advisor and Best Practices Analyzer

2. Run a trace looking for deprecation events

3. Look for some of these issues; the above methods are unlikely to find all of them.

Note that none of the above, nor replaying the trace, are guaranteed to find all compatibility issues (consider the case where a stored procedure will only have a problem with very specific parameters are used, and those don't happen to be used during your trace).

And when you replay the trace, you'll have to parse the output file(s) or table(s) yourself, and perhaps even perform diffs to see any cases where there are errors on the new version that didn't happen on the old version. The replay doesn't produce a nice "here's what worked differently from the original activity" report. You can do this by simply performing a join against .

Here is a quick example I whipped up on my own system - you need to add the Error column to your trace, and note that I am absolutely not advocating Profiler for this, it was just the easiest way to demonstrate. First, on 2014, create a database using 100 compatibility level:

CREATE DATABASE Compat100;
GO
ALTER DATABASE Compat100 SET COMPATIBILITY_LEVEL = 100;
GO

Now, run these two batches:

USE Compat100;
GO
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- not style 121
INSERT @x SELECT GETDATE();
-- error:
SELECT CONVERT(INT, LEFT(x,4)) FROM @x;
GO

USE tempdb;
GO
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- style 121
INSERT @x SELECT GETDATE();
-- not an error:
SELECT CONVERT(INT, LEFT(x,4)) FROM @x;

A trace will yield these:

(Again, do not use Profiler for the actual work, this was just to quickly illustrate that a trace can capture error conditions, and that if you had these two batches run identically on two different servers, you should easily be able to join on TextData equality and Error inequality. And yes, I do realize that this example works the opposite way - the error happens on 2008 but works on 2014, but hopefully it still demonstrates the approach.)

Also see this tip.