The Heap™ – Consultancy ©®

sp_BlitzErik

00:50

@JoeObbish boidh

@JoeObbish do you mind if I piggyback your post on row group elimination to test some other stuff?

Joe Obbish

@sp_BlitzErik b?

@sp_BlitzErik Please do, that's part of why I post them

blog?

sp_BlitzErik

@JoeObbish blog.

Is it similar to this? queryprocessor.com/sudf-ce

Joe Obbish

@sp_BlitzErik No, it's not a CE change

the scalar UDF only gets executed once per query instead of once per row without any tricks

8

Q: How can I force a scalar UDF to be evaluated just once in a query?

I have a query which needs to filter against the result of a scalar UDF. The query must be sent as a single statement (so I can't assign the UDF result to a local variable) and I cannot use a TVF. I am aware of the performance issues caused by scalar UDFs, which include forcing the entire plan to...

As far as I know, it used to just not be possible

sp_BlitzErik

01:06

Interesting. Under what circumstances?

Joe Obbish

very limited, most likely

I plan to write up a short post about it this week

SELECT MAX(l.ID)
FROM dbo.MILLIONAIRE_CCI l
WHERE l.ID = dbo.CHEAP_UDF();

it happens in that query

didn't realize what was happening at the time

sp_BlitzErik

What if you put it in the select list?

Joe Obbish

@sp_BlitzErik It's executed twice per query, instead of twice per row

which is surprising

the query plan is very misleading

even more than usual for scalars

sp_BlitzErik

01:27

On which version?

Joe Obbish

02:05

> 2016

sp_BlitzErik

Weird I've run tests with a very similar function and didn't see a reduction in executions. Did you check the stats functions DMV?

Joe Obbish

using sys.dm_exec_function_stats

what's your repro?

sp_BlitzErik

03:13

@JoeObbish will share tomorrow, going to bed

Paul White

06:28

@JoeObbish Can you share a repro and plan? What you're saying seems to be expected behaviour, but it's hard to be sure without seeing details.

McNets

06:59

MOrning

Mikael Eriksson

Good morning all.

0

Q: Hash keys probe and residual

Say we have below query , select a.*,b.* from a join b on a.col1=b.col1 and len(a.col1)=10 Assuming above query uses Hash Join and has a residual,probe key will col1 and residual will be len(a.col1)=10 But while going through an example,i could see both probe and residual being the same colu...

sql-server sql query-performance optimization

Not sure what OP really is after but if the question is why there is a probe residual at all it is interesting.

After some digging I found something called unique hash optimization.

There ought to be a blog post about that one somewhere right?

If not perhaps in a near future (@PaulWhite nudge nudge :) )

Some info about it here queryprocessor.com/hash-join-execution-internals

Paul White

07:52

@MikaelEriksson Also Join Performance, Implicit Conversions, and Residuals

But basically if the hash function isn't perfect, you need to test the items in the hash bucket for an actual match

Added a brief answer, I might expand it later

Andriy M

Forgot about the tags. Thanks, Paul

Paul White

No worries, thanks for saving me a job on the question body!

Andriy M

It was the ASK that prompted it.

Mikael Eriksson

@PaulWhite Thanks!!

Paul White

08:09

@AndriyM Yes that's a strange modern usage. I'm happy with the phrase "a big ask", but labelling a question as an "ask" grates on me.

3

Mikael Eriksson

08:26

Apparently bit (no surprise there) and smallmoney and smalldatetime can be added to the list of columns that has a perfect hash.

Tom V

@PaulWhite I think it's an Indian thing

ypercubeᵀᴹ

08:42

26

Q: Can or should "ask" ever be used as a noun?

"The ask is that you provide me with..." I started hearing "ask" being used as a noun a few years ago. Is this a recent trend? Is it an East Coast thing, unique to North America, or just unique to the in-house vocabulary of telecommunications companies?

verbs nouns american-english

Sami

08:57

Hello

Tom V

Hello

Sami

I face a weird thing , but I don't know if I can ask this Q in DBA

That weird thing is:

We all know rextester and use it to compile codes

so I do the same

as follow rextester.com/WTAPK28678

The result is not the same if you run this code in SQL Server

Tom V

In what way is it different? formatting?

Sami

No , the result

In rextester the result is:

06.08.2017                    12.08.2017

but in SQL Server:

07-08-2017           13-08-2017

Andriy M

Can you run SELECT @@DATEFIRST; in Rextester and in your SQL Server instance and tell us the results?

Sami

09:04

Is @@DATEFIRST have an effect

Andriy M

I think it might

Sami

Ah , ok i got it now

thank you very much :)

ypercubeᵀᴹ

> When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST.

From DATEPART function

Sami

Yeah , thank you for help

ConcernedOfTunbridgeWells

09:23

Morning all

hot2use

09:37

Kopisanangan dongkosuabon (Central Dusun)

Andriy M

Central Dusun?

No wonder it sounded unfamiliar

6

Paul White

10:04

@MikaelEriksson oh that's interesting I wonder if that changed at some stage. Want to edit something about that into my answer?

McNets

10:16

@hot2use Is different at North Dusun?

Mikael Eriksson

10:33

@PaulWhite not really, might encourage some bad design choices :). I tested in 2016. I thought perhaps date would work as well since it did with other "smallish" data types. But it did not.

hot2use

@McNets not sure

Paul White

10:49

@MikaelEriksson Ha, fair enough

Mikael Eriksson

@PaulWhite or it could be a lesson about right sizing and an unexpected benefit of using smaller data types. But still no.

Paul White

11:15

@MikaelEriksson Also (var)char(1) with binary collation. Added a small footnote.

ConcernedOfTunbridgeWells

@hot2use Wilujeng siang (Sundanese)

Mikael Eriksson

@PaulWhite Oh, tested char(1) but never thought about collation.

ConcernedOfTunbridgeWells

11:39

@MikaelEriksson You can calculate a perfect hash function for just about anything but it's a trial and error process so it can get quite long winded.

Perfect hash function

In computer science, a perfect hash function for a set S is a hash function that maps distinct elements in S to a set of integers, with no collisions. In mathematical terms, it is an injective function. Perfect hash functions may be used to implement a lookup table with constant worst-case access time. A perfect hash function has many of the same applications as other hash functions, but with the advantage that no collision resolution has to be implemented. == Application == A perfect hash function with values in a limited range can be used for efficient lookup operations, by placing keys from...

gnu.org/software/gperf

Tom V

12:01

The developers are going to hate me this afternoon :) I'm rebuilding a bunch of indexes with compression and I'm starting to see their requests being blocked

McNets

13:02

@TomV Good job

sp_BlitzErik

13:35

@TomV no online rebuilds?

Tom V

@sp_BlitzErik Not from dynamics AX no

sp_BlitzErik

:wat:

Lamak

@sp_BlitzErik is this your sockpuppet account?

0

A: SQL Performance when containing a CASE statement within an ISNULL

There are probably better places to focus your attention. For the fun of it, I tried this particular question which you posed and the query plans were identical. If you're having performance issues, perhaps get yourself your free copy of the Brent Ozar's tools: https://www.brentozar.com/askbr...

Tom V

@sp_BlitzErik To be precise, if you change the compression (or anything else in the index definition) it drops & recreates the index

(y)

Of course your maintenance can do online rebuilds

sp_BlitzErik

@Lamak @PaulWhite is my sock puppet account

Paul White

13:42

sh!

Tom V

13:55

@sp_BlitzErik The implementation is really neat, I only needed to change one line of code to make it work with SQL 12.something

if (sqlVersion = '11.')

Joe Obbish

15:01

@PaulWhite looks like you're right

dbfiddle.uk/…

no VIEW SERVER STATE but you'll get the idea

did I just never notice this behavior before? maybe the problem is the lack of WITH SCHEMABINDING at work...

Lamak

@JoeObbish I could've told you that, no testing needed

sp_BlitzErik

15:13

@JoeObbish try this function with your query

	CREATE FUNCTION dbo.ScalarID (@Id INT)
	RETURNS INT
		WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING
	AS
	BEGIN

	RETURN 2147483647

	END;
	GO

it'll execute 2537 times

or however many rows you have in master..spt_values;

SELECT *
FROM   udf_test
WHERE  id > dbo.ScalarID(id);

so perhaps it's not schemabinding, it's passing something in

your function returns a static result with no inputs

Joe Obbish

never seen WITH RETURNS NULL ON NULL INPUT before

inputs are ok if they don't do anything

sp_BlitzErik

an awe-struck little doe appears

Joe Obbish

I can get it to work with inputs

sp_BlitzErik

this is what i see when i run it

Joe Obbish

begin
RETURN @Id;
end;

sp_BlitzErik

15:22

Joe Obbish

can you make the picture longer? thanks

sp_BlitzErik

stop imitating @Lamak

Joe Obbish

low blow

Lamak

Aug 2 at 17:52, by Lamak

@sp_BlitzErik can you make that smaller?

Joe Obbish

it's weird

if this works how I think it does then there's actually a use case for using these in queries

sp_BlitzErik

15:25

sys.dm_exec_function_stats agrees

Joe Obbish

I don't believe it

it works with a table

create or alter function stupid_udf (@Id INT) returns bigint
with schemabinding
as
begin
RETURN (SELECT val from dbo.map_table where id = @id);
--RETURN @Id;
end;

sp_BlitzErik

i think you've had too many shirley temples

Joe Obbish

that doesn't work for you?

I'll post the full thing

sp_BlitzErik

i'm on 13.0.4422.0

Joe Obbish

dbfiddle.uk/…

I'm on 13.0.4435.0

> still being on 13.0.4422.0

@Lamak Can you try the demo? or would that be too helpful?

Lamak

15:35

@JoeObbish I can

what demo?

Joe Obbish

@Lamak dbfiddle.uk/…

need to run it somewhere you have VIEW SERVER STATE

thanks!

Lamak

ok

Joe Obbish

Erik, what I just posted really doesn't work for you?

sp_BlitzErik

the first one does, yes. as soon as i added an input to it, it stopped working though.

Lamak

@JoeObbish you'll have to wait though, I only have that permission in an SQL Server 2008R2 instance, and apparently I can't see sys.dm_exec_function_stats

Joe Obbish

15:40

it's new in 2016

@sp_BlitzErik Works on my machine (TM)

you're using the exact code that I posted here? dbfiddle.uk/…

sp_BlitzErik

21 hours ago, by sp_BlitzErik

@JoeObbish socks to be you

so a function that takes no input and returns a static result may run once per query rather than once per row

that sounds useful

Joe Obbish

I have a demo that takes an input and queries a table

you know what

fine

sp_BlitzErik

show me that one

Joe Obbish

you asked for it

the pain is coming

I was going to make a gif

but now it doesn't work

sp_BlitzErik

the virgin daiquiris must be kicking in

Joe Obbish

15:50

I blame dm_exec_function_stats

bet there's a bug

there are some use cases

are they stupid use cases? yes

sp_BlitzErik

it can't possibly be the known behavior of scalar functions

gotta be a bug

Joe Obbish

but that's all it was going to be anyway

known according to who? Pablo Blanco?

sp_BlitzErik

well, it's known to me, so it can't be too deep in the debugger

Joe Obbish

I can get it to work with an input and an output that changes based on the input

What are the criteria for a function to be executed once per query?

sp_BlitzErik

returning one row?

:D

Joe Obbish

15:53

ahem

I think that you meant "processing one row"

sp_BlitzErik

yes

yes indeed

mon petite pedant

ypercubeᵀᴹ

NEWID() returns one value. Is it executed once per query?

Joe Obbish

@ypercubeᵀᴹ it can be

depends on the query

sp_BlitzErik

i was just trying with RETURN @@SPID and RETURN @@ROWCOUNT

Joe Obbish

probably won't work, not sure though

@ypercubeᵀᴹ for example:

SELECT *
FROM udf_test
WHERE RIGHT(BINARY_CHECKSUM(NEWID()), 2) = 10

it's a startup expression

but if I do this:

SELECT RIGHT(BINARY_CHECKSUM(NEWID()), 2)
FROM udf_test

get lots of different values

sp_BlitzErik

16:46

so how's the demo?

Joe Obbish

17:00

@sp_BlitzErik which demo?

sp_BlitzErik

1 hour ago, by Joe Obbish

the pain is coming

Joe Obbish

@sp_BlitzErik it turned out the pain was coming for me

I was going to make a gif of the demo but the demo stopped working

Lamak

@JoeObbish did you brace yourself?

Joe Obbish

I did not

Lamak

that's where it went wrong

sp_BlitzErik

17:07

Lamak

17:18

> Engage with the SQL Server engineering team
Stack Overflow (tag sql-server) - ask technical questions

first time I notice that

Max Vernon

17:35

approving DDL created by data architects and developers is depressing.

Joe Obbish

@MaxVernon I'm a developer!

Max Vernon

@JoeObbish ok, not you :-)

I should have stated "present company excluded, of course"

because at heart I'm a developer too.

but really, why do you need an indicator column to be a char(1) with "Y" or "N"

and it to be NULLABLE.

Joe Obbish

What's your preferred data type for that?

Max Vernon

and, never heard of RI of course.

a bit

preferably not nullable.

Joe Obbish

I don't like bits personally

Max Vernon

17:40

I mean, you either are or you aren't.

Joe Obbish

can't take aggregates of them

it's weird

seems like there are unnecessary restrictions in how you can query them

Lamak

@JoeObbish this is my main issue with them too

Max Vernon

ok, then an INT if you really want to do that.

or even a tiny int

a char is just total overkill.

storing a "percent complete" in a char(15) seems a bit strange to me too

Joe Obbish

sure, I get what you mean

at this point I don't even notice minor (?) mistakes like that

don't know if that's good or bad

the percent complete one is definitely odd

Max Vernon

and apparently since the data comes from a source system that has RI, he doesn't need RI in this database.

^^^ And Now for Something Complete Different

ypercubeᵀᴹ

17:59

@MaxVernon with data like this?: 'A', 'AA', 'AAA', 'AAAA', 'AAAAA', ..., 'AAAAAAAAAAAAAAA'

Max Vernon

lol exactly

ypercubeᵀᴹ

makes sense.

AAAAAAAAAAAAAAA sense

Max Vernon

each 'A' indicates 6.666% complete.

because that totally makes sense. And it's flexible. You could use 'B' to mean something else. ;-)

swasheck

18:57

@MaxVernon like what?

sp_BlitzErik

3.333% complete?

swasheck

ah. yeah. i guess i can see that

Lamak

@MaxVernon I refuse to believe this

swasheck

YOU CAN'T HANDLE THE TRUTH

Max Vernon

ok, so the source data is like "Under 25" or "25-49" or "50-74" or "75-99" or "100".

kill.me.now.

seems to me like that would be perfect example of a lookup table, with RI

sp_BlitzErik

19:11

are you doing market research?

Max Vernon

@sp_BlitzErik you can't make me answer that.

:-)

sp_BlitzErik

N/A

Lamak

@swasheck I can't

swasheck

19:37

ugh. tapped for a "cloud strategy" meeting at the office.

my strategy is, "don't"

at least not for mission-critical things

sp_BlitzErik

which cloud?

some clouds are bigger than others~

swasheck

amazon

sp_BlitzErik

as far as clouds go, that's the least crappy

swasheck

that's like saying that the best STD is syphilis

Max Vernon

does varchar2 handle unicode data in Oracle?

sp_BlitzErik

19:39

it's not, it's chlamydia

trust me

Max Vernon

@sp_BlitzErik that's definitely star-worthy

sp_BlitzErik

:D

gbn

I quite like AWS

Avoid RDS though

swasheck

@gbn well that's the problem (good to see you). it's not so much that i dislike AWS ... it's that i dislike the direction we're taking it. saying on 1 AZ (good call!) and they're pushing RDS even though i've highlighted many of the drawbacks

sp_BlitzErik

what about it do you consider a drawback?

i'm not arguing, just curious

swasheck

19:48

performance is worse than a similarly-configured EC2 instance
restoring requires creating a new instance (yes. we restore a lot - especially to preprod)
minimal control over file layout and other knobs (yes, i'm a control-freak DBA)

sp_BlitzErik

ooh. what's up with performance?

disks?

swasheck

didnt take the time to isolate the issue, but IIRC, in my testing (in the us-east-1a region only, because BIDNESS) RDS was ~10%-15% slower than EC2.

sp_BlitzErik

ah, gotcha

i'm with you on restores though

the 30 database per instance limit can be a hassle for people, too

swasheck

@sp_BlitzErik we dont have that problem (yet)

sp_BlitzErik

devs are silly like that

(removed)

Jack Douglas

20:06

@MaxVernon unless your database character set doesn't — in which case you could use nvarchar2

Joe Obbish

I'm going to write an answer that recommends a scalar UDF to be used in queries

come at me, haters

sp_BlitzErik

can you be sent to jail for answers on this site?

find out in 10 minutes!

EBrown

Anyone have any intuition as to why a SELECT * FROM .... query would perform worse than a SELECT * FROM .... WHERE x = '' query, when the queries return the same data minus two rows for the second? (SQL Server 2012)

(I don't have actual access to the database this happened on, but if what the person reported is true, that goes against everything I thought I knew.)

sp_BlitzErik

i wish i could downvote chat messages

Joe Obbish

@sp_BlitzErik you can move them to the trash

EBrown

20:12

@sp_BlitzErik Why's that? Each one returned ~240k rows, the second returned two rows less than the first, but that's the only difference. I assumed that SQL Server used available indexes for the second to avoid a raw table scan, but I honestly have no idea.

Joe Obbish

@EBrown there are dozens of reasons. you should post a question on the main site

sp_BlitzErik

which indexes did they use?

EBrown

@JoeObbish Eh, I wasn't the one who found it, was helping a colleague in a different chat room find out what was going on, I don't have access to any of the database used here.

sp_BlitzErik

when you say table scan, do you mean the table is a heap?

EBrown

@sp_BlitzErik That I'm not certain, colleague couldn't post execution plans.

@sp_BlitzErik Unclear, I assume not, but it could have been. What I do know is performance was several orders of magnitude different, like several seconds for SELECT * FROM... and less than half a second for WHERE x = ''.

sp_BlitzErik

20:14

tea leaves and chicken bones

EBrown

The WHERE clause only filtered out 2 rows apparently, so I'm wondering what could have made it perform superior to the raw SELECT.

Forrest

@EBrown Why persist in helping him when he won't help you?

EBrown

@Forrest Eh, not really helping him at this point, I just wondered for my own education/knowledge.

Joe Obbish

@EBrown ok, that person should post the question then

there are lots of smart people on the main site who don't come into here

EBrown

Yeah, and I also require a somewhat concrete example to post on the main site.

I don't have any of the specifics, going to try to recreate it as best I can, but no guarantees.

sp_BlitzErik

20:18

how can you hope to repro it without any specifics?

Joe Obbish

@sp_BlitzErik I do that all the time on here

but only for those sweet, sweet unicorn points

Max Vernon

@JackDouglas thanks!

sp_BlitzErik

@JoeObbish but you know what you're doing, kind of

EBrown

@sp_BlitzErik I can't, but I can at least give it a minimal effort attempt.

sp_BlitzErik

@JoeObbish i'm having a joyful time with the piggyback post

Joe Obbish

20:21

@sp_BlitzErik you mean the blog?

sp_BlitzErik

yeah

Joe Obbish

good

I have high expectations

sp_BlitzErik

don't forget the ®

Joe Obbish

is it "great" or "good"?

sp_BlitzErik

usually great

gbn

20:27

@swasheck this. Lack of ad integration unless you use their Open LDAP

Fine control of backups

Joe Obbish

→ 1 message moved to Trash

you might even earn a "Great Post Brent! ®"

sp_BlitzErik

that is my goal in life

well, that and a 500lb squat

EBrown

@JoeObbish I have an actual data-set to test with now, and I'm seeing ~0.2-0.3s faster times for WHERE StrValue3 = '' which only filters two rows (the first two) out of this table. Possibly going to make it a main-site question if you care to comment on it.

swasheck

21:13

@gbn si

Joe Obbish

21:39

@EBrown if you post a question with one of my tracked tags I will look at it

can't promise an answer or anything like that

Max Vernon

23:38

@sp_BlitzErik - I love the OPs answer to your question about 27 indexes on individual columns. I don't think he gets it at all, and I don't think he knows about INCLUDE.

sp_BlitzErik

@MaxVernon yeah, this is a nuclear potato

Max Vernon

Well, he pasted the plan.

I love that he has an index named [<name of missing index>]

3

sp_BlitzErik

<sigh>

support.microsoft.com/en-us/help/4022435/…

>Consider that you execute a query that retrieves data from a clustered columnstore index in Microsoft SQL Server 2014 or 2016. If the data pages are too far apart, you may receive an access violation error.

This CU is wild

support.microsoft.com/en-us/help/4034056/…

Max Vernon

lol:

the plan you pasted is different. It has no Key Lookup. — Max Vernon 17 secs ago

sp_BlitzErik

23:59

Smh

Transcript for

The Heap™ – Consultancy ©®