@AndyK did you solve that?

@ConcernedOfTunbridgeWells /dev/null aaS - Absolutely brilliant!

@ConcernedOfTunbridgeWells I liked their product so much I thought I'd apply for a job. Sent my resume to the URL they publish. Never heard back.

Turns out it's my DBA birthday! Who's up for a drink after hours, my shout?

morning

@MichaelGreen Congratulations

Morning

quick one. Is this a valid usage of DBCC TRACEON(2312) in a script?

DBCC TRACEON(2312)
go
execute as user='someuser'
go
SET ARITHABORT OFF
go
SELECT .....
go
REVERT
go
DBCC TRACEOFF(2312)

It show up in the query execution plan as :

<TraceFlags IsCompileTime="true">
  <TraceFlag Value="2312" Scope="Session" />
  <TraceFlag Value="2335" Scope="Session" />
  <TraceFlag Value="4199" Scope="Session" />
</TraceFlags>
<TraceFlags IsCompileTime="false">
  <TraceFlag Value="2371" Scope="Session" />
  <TraceFlag Value="2312" Scope="Session" />
</TraceFlags>

yes

Thank you.

I love Hash Joins

Especially on 50 Mio rows HASH JOIN 30 Mio rows JOIN 28 Mio rows statements

Is there a TF to bash the QO into submission? (ie. not use NESTED LOOPS)

Is OPTION (HASH JOIN) not right for you?

Nope. Can't change the statements.

Well it's undocumented but 9165 will work. You could use the resulting plan in a plan guide if you want to stay in a supported configuration.

But likewise you can add OPTION (HASH JOIN) using a plan guide.

Assuming the statements are known ahead of time.

They are known, but they are non-parameterised and in the plan cache mulitple times, because of white-space and client settings..

I was thinking query store.

But as long as the query never gets executed like it should, I'm not going to be able to pin the plan. Or am I?

You can only force a plan that QS has captured.

I have worked with queries before that required hundreds of plan guides to capture client variability. Painful, but it did work in the end.

CacheType            Total Plans          Total MBs                               Avg Use Count Total MBs - USE Count 1                 Total Plans - USE Count 1
-------------------- -------------------- --------------------------------------- ------------- --------------------------------------- -------------------------
Adhoc                158999               6359.765075                             2             3267.218200                             126489
Prepared             930                  85.234375                               6518          38.781250                         …

ugly ---^

but doesn't using sp_create_plan_guide require that the statement be run using option (use plan ...?

The ugly plan when I force it to work (Paste the Plan)

The ugly plan when statistics are out-of-date (Paste the Plan)

46 seconds or 4 minutes 36 seconds.

@Johnakahot2use no

Ok. Thanks. Ramesh Meyyappan didn't really point that out during the trainings.

tickled by this: blog.dbi-services.com/…

Nice

Does MySQL support anything like Table Valued Parameter for getting a bulk load of data into MySQL?

That I can then push to a stored procedure in some format

howdy