The Heap™ – Consultancy ©®

I have a blog post about the SQL terminology for ESL in my to-do queue. But it's tough to write, and sometimes I'm not sure about the definitions myself

Paul White

3:30 PM

akina is another. Very smart dude, poor English.

Zikato

for example I still don't know what an enclave is

Erik Darling

at least you know they're secure!

Zikato

not really. My first exposure to that word was in Fallout 2 - Navarro Enclave. Right after the tutorial I've sneaked in there and stole a Power Armor

Erik Darling

can you try that again in english

Paul White

czech your privilege

Zikato

3:43 PM

https://fallout.fandom.com/wiki/Navarro_underground
Oh, the Enclave was the name of the faction

Erik Darling

crimea river

Zikato

Erik - The famous Twitch streamer doesn't know Fallout?

Erik Darling

i've never played it

Paul White

potential consequences too high

Zikato

So just Street Fighter 2 and Modern Warfare?

Erik Darling

3:47 PM

i used to play tf2 quite a bit

i started playing COD during lockdown v1 out of boredom

Zikato

nice

Erik Darling

4:10 PM

eh i guess

problem is three years on i'm still terrible at it

Paul White

4:21 PM

6

Q: What can I add to an Extended Event to return correct row_counts?

I'm running an Extended Event session in SQL Server 2017 that's only recording module_end events for a specific database. The reason for this trace is two-fold: I'm wanting to see the frequency (or lack-thereof) for which a stored procedure has been called. I'm trying to get some specifics for ...

peculiar

Erik Darling

yeah i can't

extended events are such a weird time

Paul White

My first guess is a timing issue, where things get slowed down by the extra profiler data

Erik Darling

is that really the max length replica name?

Zikato

4:38 PM

@PaulWhite Yeah, how can the Profiler (which was discontinued in 2008 IIRC) affect XE?

Erik Darling

what's the max length of a varbinary 64 value?

Paul White

@Zikato How did you repro given the example in the question returns 1 row, then zero rows twice?

@ErikDarling wat

Zikato

I've added an extra statement that always returns 3 rows (just to see if it works). The last execution is a Service Broker activation procedure

Paul White

@Zikato ok but what is the statement that returns three rows

Erik Darling

@PaulWhite most of the ones i find for sql handles are around 90 bytes in length

Paul White

4:45 PM

oh that

Erik Darling

SELECT LEN('0x02000000806DDE029715566FF9A70ECCE81CEE68F7A000FC0000000000000000000000000000000000000000')

but i'm willing to go longer to avoid odd errors

128? 256?

1024?!

Zikato

@PaulWhite Just a select from values (1) (2) (3)

Paul White

@ErikDarling You're measuring the length of a string there

A binary digit requires two characters

SELECT DATALENGTH(0x02000000806DDE029715566FF9A70ECCE81CEE68F7A000FC0000000000000000000000000000000000000000)

So, as a (non-Unicode) string with a 0x prefix, the answer is 130.

Two for each byte (e.g. FE) x 64 = 128 plus two for the 0x.

@ErikDarling But why are you treating binary as strings?

Erik Darling

5:03 PM

good question

i'm adding functionality to quickiestore to search for plan/query hashes and sql/plan handles, because that's often stuff you can get from other sources

so they could pass in something like 'hash1, hash2' etc

and i don't want my string splitter to suffer any conversion issues

i also want one base splitter to use so it has to be binary(8) and varbinary(64) agnostic

Zikato

convert it all to bigint and then you can match the XE as well

Erik Darling

no

why would i do that for a proc that's searching query store

Paul White

add some regex too

Zikato

good idea!

I'll send over the assembly

Paul White

HtH

Erik Darling

5:08 PM

reginald extein didn't ^kill himself$

Zikato

that's a bit of a stretch

Erik Darling

says the guy who wants me to convert hashes to bigints and call xe from a stored procedure that searches query store

Paul White

might have been a going to jail joke

bit of a stretch

hard to know in English

@ErikDarling ok well varchar(130) would be sufficient then

Zikato

@ErikDarling I don't want to call XE from a stored procedure. I usually find problems in XE first and go to QS for more data.

Erik Darling

@PaulWhite thx4u

Zikato

5:12 PM

you are doing conversion anyway, might as well pick the common type

Paul White

in which version did the bigint bug get fixed?

you know, the unsigned thing

Erik Darling

@Zikato if you would like to contribute that code you are welcome to

@PaulWhite 2016 sp something cu something

iirc

Paul White

fairly recent then

Erik Darling

12

Q: SQL Server Query Plan XML: QueryPlanHash Length

This is definitely a bug. Details were on the old Connect site, and are no longer available, as it has been retired in favor of an indeterminate number of other solutions, both third and first party. While testing some changes to sp_BlitzCache (full disclosure, I'm one of the authors), I came acr...