Back in the day people used to talk about making sure you used all the parts of the name to prevent extra lookups on default schemas and so on.

Surprised that no one has commented on the overhead of the table variable.

As a matter of interest -
did you have the same block size disk format?
did you experience file growth?
If so did you have the same size db...

What utter flamebait.

Congratulations.

As to people who are saying it's the DBA's job to index - Developers can't possibly be expected to grasp the subtleties... hogwash.

Curious to see how it works with a non unique clustered index.

Might be worth noting that the default collation on a modern 2014 install appears to be different depending on your os locale settings... so some of the servers will...

May I suggest showing an extra couple of screenshots of the query plans showing where to see the implicit conversion? It might add a little to the learning experience....

I've had similar issues before, also being an sp_helptext fan, but typically got round it by adjusting results to text and upping the output in query options.

I suspect that some...

Thanks, guess it's obvious when you know 😉

Cheers for the info!

I do tend to think that if it's important enough for you to want to analyse it in depth, it's important enough for you to learn how to do that...

Graphical...

So... to summarise my findings:

Server - Physical, 2x8CPU, 128GB Ram

Parallelized:

MAXDOP 5 or above

and

(

Numa ALL Auto

or

Numa 1 or more Cores...

Looking into this in more detail over at http://msdn.microsoft.com/en-us/library/gg415714.aspx

This is a whitepaper Understanding and Controlling Parallel Query Processing in SQL Server

Writers: Don Pinto, Eric Hanson

Guessing it will...

Thanks for the input, found it a bit puzzling tbh.

Further fiddling suggests it is happy to parallelize even with only one core per numa node... I always understood it preferred to keep it on a single node.

Yes, I don't doubt it.

Any idea why using a single Numa node with 8 cores would throw away the parallel plan, whereas using both nodes, but limited to maxdop 6...