A Very Silly Performance Tuning Trick In SQL Server

If you like what you see here, you'll love my advanced performance tuning training:

[mujtaba.tirmazi]

This is awesome, thankyou for sharing

[fpost]

I like this short video! I like short videos. And I really look forward to your video on parallel nested loops. Then again, I might be a bit creepy.

[frostysnowdog]

Omg. I love these stories. I did something similar to change the Join order and therefore change the plan 😂. I kept looking at the chosen plan and asking “why do you think that’s a good plan???” I can’t remember specifically what - but it was string math or a Cast on a column that wasn’t in an index, therefore poisoning the plan choice. I stared at it and thought “that can’t be the answer” and added a big comment next to it because I knew future me would be tempted to remove it thinking what kind of crap is that? 😂

[Garstor]

Here I am, fighting devs and report writers to never use VARCHAR(MAX) columns... :-) Party in the giggle-bush!

[filipzivkovic]

I honestly never thought I'd see that in my life hahahaha

Anyways, great insight, thanks!!

[SQLDevDBA]

Hahaha this is fantastic, thanks for sharing! Also… certs aren’t good you say? ACKSHUALLY my Crystal Reports 11 Certification from 2011 begs to differ. I use any chance I get to flaunt it.

[anonymousbutterfingers]

Clearly, I do not have access to your data source so I do not really know what is going on.
However, it seems to me that when you CAST "p.Id" field you are effectively killing usage of index on this field (based on name it is PK) so you are forcing SQL Server to create a new execution plan.
Also, based on my experience, taking joins on "DateTime" field is not a good idea and often results in poor performance.
My advice( a guess rather) would be to create computed (and persistent) columns of type "int" for each "DateTime" columns which would measure, say, number of minutes from a fixed date to proper value. Then index these columns and use them in your query in joins.