“Pretty Plans vs. Performance” or “Grant Gets Pwned”

SQL Server 2008, SQL Server 2012, SQL Server 2014
If you get an execution plan that looks like this: I wouldn't blame you for immediately thinking about query tuning. Especially if the code that generated it looks like this: [crayon-5a6d2a444d3e6563825111/] Let's replace this with a table variable, maybe even one passed in as a parameter. The plan then looks like this: Ah, much prettier. I'm happy now, all's right with the world... But, just in case, let's look at performance. The first query ran in about 2.2ms and had 599 reads. The second query ran in about 24ms and had 598 reads... crud. Well, let's modify everything again. Instead of a table variable, we'll use a temporary table and get some statistics into this puppy which will clean things right up. Here's the new plan: Looks pretty familiar, although there are…
Read More