The Heap™ – Consultancy ©®

« first day (5141 days earlier) ← previous day next day → last day (23 days later) »

Erik Reasonable Rates Darling

00:01

No, not a stored procedure

That other thing is interesting though I’m not sure what to make of it wrt the parameter stuff

Paul White

6 hours ago, by Erik Reasonable Rates Darling

okay, stored procedure compile time parameters aren't in the execution plan. there's no optimize for unknown hint, there are no local variables in use, and parameter sniffing is not turned off as a database scoped configuration. is there anything else that would mess that up?

I think that's why I thought it was a stored procedure

What makes them parameters then?

StatementParameterizationType = 1 but I'm missing the mechanism for that

There's also spatial stuff, openjson, and indexed views. Attempting use all known technologies at once?

Oh, and it's a SELECT INTO ofc

Erik Reasonable Rates Darling

It’s a parameterized statement sent from EF or some such

The indexed view is to avoid a scalar udf in one of the base tables 😆

I’m aware there’s a lot going on just not sure why parameter values are missing

Paul White

00:17

So, it arrives by sp_executesql?

Erik Reasonable Rates Darling

But of course that’s why I’ve asked for clarification

Paul White

@ErikReasonableRatesDarling Yep

Erik Reasonable Rates Darling

That’s the way all the other statements arrive, so I am guessing they’re not breaking that habit here

Which can only mean they are

Paul White

🤣

I don't think compilation replay should affect things, but you could always check other plans with the parameter list to see what they have

And being Azure SQL Database adds OPTIMIZED_SP_EXECUTESQL into the mix

There's certainly enough going on

Erik Reasonable Rates Darling

I’m not sure if that’s enabled there yet

I will check all of these things

And after an exhaustive search they will tell me they’re doing something weird

Paul White

00:22

Yes

Did you check the plan cache plan?

Erik Reasonable Rates Darling

The plan cache is not terribly reliable for some reason

Also I may have lied. I think they’re on hyperscale now.

Paul White

I have no idea what difference that makes

Must be fun running things in the cloud where everything interesting is hidden and you're running on an experimental code base

Erik Reasonable Rates Darling

Yeah they’re having a great time

Let the invoices flow

Paul White

Always a silver lining

Erik Reasonable Rates Darling

00:46

Yes except I have to suffer with azure

Oh I get it

The invoices are the silver lining to that cloud

Paul White

Yes

Erik Reasonable Rates Darling

Guess that’s why they call it a way homer

1 hour later…

Josh Darnell

02:14

EF does run some queries without using sp_executesql, but I think it's only those that have no parameters.

When is a parameter not a parameter?

Paul White