Shane O'Neill

He/Him. DBA. Automation. Food, Coffee, Whiskey (not necessarily in that order)...
@[email protected]

[Shane O'Neill @SOZDBA]

Hopping back on here to say:
@MattMcgiffen ... TDE... thank you 🙏

[Shane O'Neill @SOZDBA]

Dice and Desire – I enjoyed every one of the 790 minutes I spent with you in 2023. Thank you. #SpotifyWrapped
https://t.co/cMPeJKyrlX

[Shane O'Neill @SOZDBA]

@HalbaradKenafin 🫂

[Shane O'Neill @SOZDBA]

Based on the number of connection requests I received today and yesterday, I assume people are talking during the PASS Summit.

I can only say thank you; the stories are grossly overestimated in my favour.

[Shane O'Neill @SOZDBA]

First #blog back in a while - to do with #PowerShell and Read-Host
https://t.co/grYwsbTpuT

[Shane O'Neill @SOZDBA]

Happy Birthday @SQLSeeker77 !

[Shane O'Neill @SOZDBA]

My #tsql2sday post is up just before the deadline:

https://t.co/VCRQdcQ3rl

Thanks for hosting @erikdarlingdata

#sqlblog

[Shane O'Neill @SOZDBA]

Support EightKB! on @Bonfire https://t.co/LmuyJye85f

[Shane O'Neill @SOZDBA]

I've decided to highlight blog posts that have helped me out when they help me out.
Rather than just read, action, then forget.

So thank you @Kendra_Little for your post on "NO_PLAN and NO_INDEX: Breaking a Forced Query Store Plan"
https://t.co/9YX2cabW2U

#SQLServer

[Shane O'Neill @SOZDBA]

Thanks to all at #DataCeili. Sorry, I have to head off early, but I'm looking forward to catching up with you all again!

[Shane O'Neill @SOZDBA]

🙌🙌

[Rob He/Him - Not here @sqldbawithbeard]

Dbatools FTW with replication
@sql_williamd #dataceili

[Shane O'Neill @SOZDBA]

Getting ready for CI/CD at #dataceili with @kevchant & @SQLStad

[Shane O'Neill @SOZDBA]

Now, to check out #Fabric at #DataCeili

[Shane O'Neill @SOZDBA]

#dataceili kicking off with Partitioning

[Shane O'Neill @SOZDBA]

#tsql2sday 161 Having fun with SQL
Thanks @2meterDBA
#sqlblog https://t.co/MdZfUN35bd

[Shane O'Neill @SOZDBA]

Hey! I know this guy!

[Shane O'Neill @SOZDBA]

@SQLGeordie @PaulRandal Didn't know this, cheers!

[Shane O'Neill @SOZDBA]

#SQLServer challenge for anyone who likes figuring things out.

I've created the index it asked me to.
Run the query again, and it suggests the same index
**even though it uses it**

I can replicate but I can't figure out why.
Anyone want to try to replicate it? 😇😈

[Shane O'Neill @SOZDBA]

Final trick, and thanks to those that responded, I now know more than before.
I can only replicate this with these conditions:
1. FULL optimization
2. Range comparison on 1 of the columns,
3. Partitioned table (Yes, I was sneaky and didn't mention this)

[Shane O'Neill @SOZDBA]

Not sure if this is a hint, but the issue is only present when the query goes through Full Optimization.
When it's Trivial Optimization, no missing index shows, and the query uses the index fine.

That's why I had to add
AND (SELECT 1) = (SELECT 1);
to the WHERE clause

[Shane O'Neill @SOZDBA]

@feaselkl @sqL_handLe And why SQL Server suggests the index that already exists, and the one it uses, when a range is supplied

[Shane O'Neill @SOZDBA]

cc @sqL_handLe

[Shane O'Neill @SOZDBA]

cc @jodouglass 😇

[Shane O'Neill @SOZDBA]

@feaselkl @sqL_handLe Interesting, the range could be something to do with it.
Changing the range to an equality does change the order.
When that index replaces the order one, no missing index recommendation shows
(must read that link fully to try and figure out why)