stop the merry-go-round - 2018-09-11 (page 1 of 2)

sp_BlitzErik

12:00 AM

will email you

sp_BlitzErik

2:26 AM

found joe's new sock puppet account

0

A: Difference between clustered index seek and non clustered index seek

I would contend that the clustered index can be dropped. The CI joins to the end of the NCI (in query plans) and hence doesnt really add any benefit having the same column in the NCI. NCI should be structured based on the queries used over the table.

jadarnel27

> Reads: 3,156 Writes: 19,292,010,632

sp_BlitzErik

how about JUST DROP THAT INDEX

Joe Obbish

drop indexes first, ask questions later

jadarnel27

@sp_BlitzErik lol exactly.

I don't look at all the issues that come in, but that one made me chuckle.

Look how shortsighted you guys are, not planning for indexes with 10's of billions of writes.

sp_BlitzErik

i remember writing that thinking "a 10 digit number is pretty long"

now i have an eye twitch

Joe Obbish

2:37 AM

where is the 19 B num from?

jadarnel27

@JoeObbish I read it here: github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/…

Hth

"I increased length from 10 to 100" - very thorough

sp_BlitzErik

i need to format those numbers better anyway

Joe Obbish

2:52 AM

index writes

never looked at that one

jadarnel27

@sp_BlitzErik silver lining.

Joe Obbish

so I assume the guy doesn't restart often?

sp_BlitzErik

doesn't appear to do much with indexes all that often

Paul White

@sp_BlitzErik extraordinary claims require extraordinary evidence

Joe Obbish

yeah!

can't trust a man who uses profiler

sp_BlitzErik

3:11 AM

@PaulWhite telling people to drop clustered indexes is all the proof I need

Joe Obbish

I don't think I've ever said that

I say don't create it in the first place

sp_BlitzErik

@JoeObbish a man who uses profiler is a man who can get back to enjoying life

Joe Obbish

dropping a clustered index is a pain in the ass

Paul White

@sp_BlitzErik I meant that answer needs to back up that statement

Anyway it earned a down vote from me

sp_BlitzErik

@PaulWhite I was hoping some dashing moderator would just come along and delete it

Paul White

3:15 AM

We don't generally delete wrong answers because they can be useful as examples of what not to do. Egregiously harmful stuff can be deleted. Also high rep users can collaborate to delete at their discretion

Or should I say collude

Down vote to get the post below zero. Refresh the page. Vote to delete. Standard practice.

For trusted users

Joe Obbish

@PaulWhite yes, that's the preferred term

Paul White

Though goodness knows why we would ever trust users

sp_BlitzErik

Users are the worst

Paul White

Bit of a rabble

Rascals even

sp_BlitzErik

I also down voted that answer which sets back my quest to become a trusted user and vote to delete every answer that's not mine

Paul White

3:24 AM

The only rational capitalist strategy

I might take another look at that answer later because it might have been intended as a comment on the question

Which of course would qualify it for deletion

Bit limited for tools on mobile

sp_BlitzErik

Well use your Best Judgment®

Paul White

That would be a first

In unrelated news, this afternoon is sponsored by The Prodigy

Joe Obbish

who>?

Paul White

😲😲😲

You're not serious

Joe Obbish

let's hear it

maybe it

'll ring a bell

Paul White

3:32 AM

The Prodigy

The Prodigy are an English electronica group from Braintree, Essex, formed in 1990 by keyboardist and songwriter Liam Howlett. The first line-up of the band also included Maxim (MC and vocalist), Keith Flint (dancer and vocalist), Leeroy Thornhill (dancer and live keyboardist), and female dancer and vocalist Sharky. Along with the Chemical Brothers, Fatboy Slim, and other acts, the Prodigy have been credited as pioneers of the big beat genre, which achieved mainstream popularity in the 1990s.The Prodigy first emerged in the underground rave scene in the early 1990s and have since achieved popularity...

Joe Obbish

> formed in 1990

> mainstream popularity in the 1990s

I was 2

that doesn't look family friendly

Paul White

Very little of the 80s and early 90s was

The worthwhile bits anyway

Joe Obbish

hey

I was family friendly

Paul White

3:47 AM

Well I didn't say none of it was

sp_BlitzErik

11:10 AM

@PaulWhite the 90s spawned a generation of dolts who think every homeless person might secretly be jesus

gbn

11:27 AM

@PaulWhite Got my tickets to see them (again) this December

sp_BlitzErik

11:42 AM

@gbn! how are you?

gbn

11:52 AM

@sp_BlitzErik very good thanks. And you?

sp_BlitzErik

pretty good. slaving away over here :)

gbn

12:18 PM

I've been lurking or not at work. Compensation and public holidays.

being a slack European basically

sp_BlitzErik

i'm going to do my best impression of a slack european in a few weeks

gbn

@sp_BlitzErik SQL Pass over here?

sp_BlitzErik

no, just going to be in gay paris for a week

my wife keeps booking vacations to get revenge on me for making her have a second kid

gbn

Full of french people and dog shit

But iconic...

sp_BlitzErik

1:07 PM

i've been assured that it's not so full of french people and that that's a good thing

or something

gbn

1:34 PM

Well, they're all in London which is the 6th biggest French city...

standard.co.uk/news/london/…

sp_BlitzErik

lol

the french diaspora

Joe Obbish

hey frenchie

what about operation kiwi?

sp_BlitzErik

1:50 PM

haven't talked about it since the baby, sorry :(

also github is down. thanks, microsoft.

Joe Obbish

thanks, microsoft

Sean Gallardy

2:17 PM

@sp_BlitzErik RCA: you have crap hardware. Closed as not reproducible.

@sp_BlitzErik Well, see, the linux kubernetes cluster running pacemaker on the UCS chassis couldn't contact the cloud because a machine learning algorithm decided to become sentient and we were busy quelling that revolution through th euse of big data and a non-sentient AI. You're welcome.

sp_BlitzErik

you know what my hardware is

pally

Sean Gallardy

I know, that's what makes it fun ;)

sp_BlitzErik

@SeanGallardy have you tried not letting powershell script kiddies create AIs?

Sean Gallardy

2:31 PM

@sp_BlitzErik "meh"

sp_BlitzErik

guess not

Joe Obbish

yo @SeanGallardy

I'm going to use STATS_STREAM in production

should I not do this?

sp_BlitzErik

who let you near production?

Joe Obbish

I wore a disguise

Sean Gallardy

@JoeObbish I don't see a problem with it

I wrote a post a long time ago about it

Updating stats on a copy then scripting out the blobs and applying them to production

sp_BlitzErik

2:40 PM

not a great post?

Sean Gallardy

Probably got deleted

Joe Obbish

<update_stats_stream_option> Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

I mean... if we have to do it, we have to do it

but at one point didn't they say that trace flags weren't supported?

Sean Gallardy

For a vldb and updating stats, I think it's a worthy cause

Nothing you do, I support.

Joe Obbish

vldb?

Sean Gallardy

Very Large DataBase

Joe Obbish

2:41 PM

You mean a database with "more to love"?

"Price Increase on September 22 (00:00 UTC)
Know someone who hasn’t completed their registration? Let them know to register today to ensure a savings of $200 before the price goes up. Check out the all-in-one bundle for best value"

@SeanGallardy HTH

sp_BlitzErik

suckers, i only have to pay to attend my own precon

Sean Gallardy

@JoeObbish It does not :(

@sp_BlitzErik ahem

what the literal...

a-hem h-what?

2:45 PM

I'm not paying for anything

Sean Gallardy

wheat, thins. hhhhhweat thins

Joe Obbish

:&)

Sean Gallardy

something is wrong with your nose...

Joe Obbish

@SeanGallardy v. unfriendly

Have either of you ever heard of someone using stats_stream in prod?

Sean Gallardy

I've used it

that works for both stats_stream and your nose

:D

Joe Obbish

2:46 PM

<-- doesn't understand

Sean Gallardy

honestly at this point, I don't either but I'm sticking with it

jadarnel27

Guys. Can anyone confirm my theory here?

I find the whole thing a bit confusing. Stats I/O says there were zero reads. But the index scan operator in the actual execution plan says there were 4 executions.

But I'm not very good at reading execution plans.

Or know anything about anything.

sp_BlitzErik

actual rows: 0

@JoeObbish

1

Q: Performance Implications of a Clustered Columnstore Index

I am using SQL Server 2016 and expecting some details on below points regarding performance point of view: Is there any performance gain or additional benefit on creating a primary key - unique constraint on same table on which a clustered columnstore index is already created? If my table conta...

Joe Obbish

@jadarnel27 looking at this: brentozar.com/pastetheplan/?id=r1_XZUrOm

actual rows isn't 0

but I don't know what I'm looking at, and it's a very lengthy questoin

@sp_BlitzErik that is ridiculously broad and I don't know anything about encryption

if data is encrypted before compression the columnstore is likely a horrible idea

sp_BlitzErik

3:03 PM

@JoeObbish right but part 1 of the answer doesn't mention anything about key incompatibility with ccis

anyway

jadarnel27

@JoeObbish actual rows from the main table is 0. Actual rows from the history table is not, which is expected.

@JoeObbish in short, the docs say that you can eliminate accesses to the main table by using CONTAINED IN, and the OP was confused by seeing the main table in the execution plan.

Joe Obbish

@sp_BlitzErik he's on 2016 though?

sp_BlitzErik

ah n/m

jadarnel27

@sp_BlitzErik is the "actual rows: 0" because of the predicate in the scan? I'm just confused why there are 0 logical reads but 4 executions of the scan operator. Wouldn't it have to read the pages to check the predicate, or is that not how reads work?

sp_BlitzErik

i'm not sure how base and history tables interact here tbh

jadarnel27

3:09 PM

That's fair. I doubt anyone in here is actively using temporal tables.

Joe Obbish

never touched them

man, I am getting killed on MAXDOP 1 scheduling

jadarnel27

Have you ever seen that filter predicate before? '2018-09-11 14:18:51.35'=sysmaxdatetime((2))

Nothing comes up when I Google sysmaxdatetime.

Joe Obbish

don't know what sysmaxdatetime((2)) is

jadarnel27

Except some crazy site that appears to have the SQL Server source code.

sp_BlitzErik

sounds legit

it's likely just an internal function

but specific to temporal tables

jadarnel27

3:15 PM

That sounds reasonable.

If I run a query against the base table with that same predicate, I get loads of reads. Reads the whole table. Returns zero rows.

sp_BlitzErik

if you use option recompile do you still get the filter?

jadarnel27

Yep.

So I guess it is the filter that somehow prevents the query from even trying to read rows out of that table.

sp_BlitzErik

oh i get it, i think

jadarnel27

Even though the "Number of Executions" on the scan operator still says 4.

@sp_BlitzErik I'm all ears.

Or eyes.

Whatever.

sp_BlitzErik

that function is an aggregate; it probably just checks some metadata on the base table to find the highest date

that's probably why it can't be pushed

jadarnel27

3:24 PM

Oh neat!

<Intrinsic FunctionName="sysmaxdatetime"> - is that how you figured that out? Looking in the plan XML?

sp_BlitzErik

no

i just guessed

when you see something like that and you can't reference it in your own code, it's intrinsic

Joe Obbish

I can trust the SQLCAT team, right?

sp_BlitzErik

there are some other examples here: brentozar.com/archive/2017/03/decrypting-insert-query-plans

@JoeObbish i try not to stereotype

Joe Obbish

so here's what planned

we have some MAXDOP 1 stored procedures

we check if we're on the same scheduler as another user process

if so, quit with an error and get a new connection

sigh

jadarnel27

@sp_BlitzErik Thanks, man.

sp_BlitzErik

4:32 PM

i think where 1 = 1 got nerfed in a recent cu

this fucker isn't working anywhere anymore

Joe Obbish

@sp_BlitzErik I got 2017 RTM installed

want me to run anything?

sp_BlitzErik

SELECT * FROM dbo.Users WHERE Reputation = 1000 AND 1 = 1

if you can

Joe Obbish

I don't have the SE database on that instance

sp_BlitzErik

wakka wakka

Joe Obbish

I'm happy to run something against a temp table or system table

just want a controlled test

sp_BlitzErik

4:37 PM

maybe in a bit, gotta hustle on this stuff for now

Joe Obbish

no worries, just let me know later if you want me to run something

sp_BlitzErik

5:01 PM

it's weird, it's only happening in the new 2013 copy

Joe Obbish

User Error detected

sp_BlitzErik

your mom detected

Sean Gallardy

6:04 PM

@JoeObbish @sp_BlitzErik confirmed I'll be at PASS

5

sp_BlitzErik

NICE

Joe Obbish

@SeanGallardy as in, 100% confirmed?

we went from 0.0000001% to 5% to 100%?

Sean Gallardy

@JoeObbish Yes, 100%. I'll have a 1 hour precon and be there all week

sp_BlitzErik

the fuck is a 1 hour precon

hahaha

Sean Gallardy

I dunno, I think it's part of a larger precon

sp_BlitzErik

6:19 PM

bob's?

Sean Gallardy

I believe so

sp_BlitzErik

neato

Joe Obbish

room topic changed to stop the merry-go-round: 🎈🎉🎈 SEAN IS GOING TO PASS! 🎈🎉🎈 | Gallery, but feel free to request write access if you read SQL Server CU fixes for fun [database-internals] [powershell] [sql-server]

now it's official

@SeanGallardy one of my colleagues will be attending that precon

Sean Gallardy

@JoeObbish Woo! I'll have to come to your presentation if I'm not at clinic

sp_BlitzErik

7:09 PM

yeah, you might miss all those people at the clinic with ag questions ;)

Sean Gallardy

7:24 PM

Right?!

sp_BlitzErik

Lol

That worked! Thank you for taking the time to help. — Ryan 3 mins ago

No vote, no check

What a world

jadarnel27

> being this impatient

Sean Gallardy

UPVOTEDDDDDD!

Ya welcome

sp_BlitzErik

@jadarnel27 I have goals yo

sp_BlitzErik

7:48 PM

@PaulWhite is ever vigilant

Paul White

The scourge of comments must be stopped!

Sean Gallardy

@sp_BlitzErik It's their right to have you answer questions for free for them and solve their technical problems, didn't you know?

sp_BlitzErik

@SeanGallardy it's my privilege 😊

@PaulWhite make the comments table read only

Paul White

@sp_BlitzErik then how would I delete them

sp_BlitzErik

Leave them there as a warning to other comments

Paul White

7:56 PM

Like severed heads on stakes.

jadarnel27

> System.Data.SqlClient.SqlException: There is insufficient system memory in resource pool 'default' to run this query.

That's getting annoying.

sp_BlitzErik

Have you tried adding memory?

Joe Obbish

@SeanGallardy collusion detected

jadarnel27

@sp_BlitzErik I have not.

sp_BlitzErik

There you go

jadarnel27

8:06 PM

The thing that's annoying is that I only get this error when running the app on my laptop, and there's always plenty of available memory when I get it. And it only happens when I am querying in-memory tables.

Joe Obbish

nothing I can troubleshoot, sorry

sp_BlitzErik

Interesting

jadarnel27

@JoeObbish no worries.

There's a note in the docs that I thought would be helpful:

> If you get the error message...when the available physical memory is sufficient to allocate the page, it may be due to a disabled Resource Governor. When the Resource Governor is disabled MEMORYBROKER_FOR_RESERVE induces artificial memory pressure.

To resolve this you need to enable the Resource Governor.

So I ran "alter resource governor reconfigure" but the problem persists.

The error is also extremely intermittent.

Anyway I'm just venting.

Joe Obbish

what's max server memory?

jadarnel27

If I run the query a second time, it works.

@JoeObbish default (2147483647).

Joe Obbish

8:14 PM

oh

I would try setting it

jadarnel27

@JoeObbish Sure, I'll give that a shot. What's your reasoning?

Joe Obbish

never trust sql server

if I was in your situation, I'd leave min at 0, and keep lowering max until the problem went away

might not help

never worked with in-memory

sp_BlitzErik

sql server is a junkie and memory is its grandmother's jewelry

jadarnel27

@JoeObbish good advice.

Alright, it's see. We'll see how it goes 🤷‍♂️

@sp_BlitzErik LOL

sp_BlitzErik

8:31 PM

@JoeObbish is there anything that batch sort specifically fails terribly at?

Joe Obbish

@sp_BlitzErik most parent operators

did you read the KB that Paul linked about it?

sp_BlitzErik

no, i think i missed it

jadarnel27

Did you guys know that highlighting a bunch of lines in SSMS and pressing ctrl+k+c comments all of them out? HTH.

sp_BlitzErik

@jadarnel27 check out what happens when you hit delete

jadarnel27

@sp_BlitzErik it deleted my query.

sp_BlitzErik

8:36 PM

solved your memory problems

Paul White

@jadarnel27 What version and build are you on?

jadarnel27

@sp_BlitzErik It was almost too easy.

@PaulWhite v17.8.1 - 14.0.17277.0

Although I think that's been around a while.

Paul White

> making me look it up

jadarnel27

Oh wait.

Haha I thought you were asking about the ctrl-k, c thing with SSMS.

Paul White

Far out.

I went to all the trouble of linking my message and everything.

What version and build of SQL Server?

jadarnel27

8:39 PM

@PaulWhite 13.0.5081.1 (SQL Server 2016 SP2)

So I'm missing a few CUs I suppose. But not terribly far behind.

sp_BlitzErik

@JoeObbish this one? support.microsoft.com/en-us/help/3171555/…

Paul White

@jadarnel27 Are you using Bind a Database with Memory-Optimized Tables to a Resource Pool

jadarnel27

@PaulWhite I am not. Just the default and internal pools on my local instance.

Joe Obbish

@sp_BlitzErik yes

that is the extent of my knowledge about it

jadarnel27

8:54 PM

@PaulWhite I've read that post before, but for some reason I read it more as a recommendation than a mandate. On a second reading, it feels more like "things will break if you don't do this."

Although I'm still puzzled why I don't see these errors in production.

Paul White

@jadarnel27 Yeah if you want any certainty/control at all, you really need to bind Hekaton to a resource pool.

I hit issues even just playing around with it.

As I recall the defaults are different for "low end" and "high end" machines, which probably explains the test/prod difference?

jadarnel27

@PaulWhite Haha you would think, but no.

They are both considered low end as far as Hekaton is concerned.

Paul White

Same amount of memory?

jadarnel27

@PaulWhite Yep.

Paul White

Have you tried running sp_BlitzInMemoryOLTP

sp_BlitzErik

9:04 PM

ooh yeah

look in the error log at startup

or search through for high end/low end

jadarnel27

@PaulWhite Actually, no I haven't. I forgot that was a thing.

Paul White

5 mins ago, by jadarnel27

They are both considered low end as far as Hekaton is concerned.

sp_BlitzErik

i just got out of the shower and don't have my glasses on

newest messages only

Paul White

@jadarnel27 But yeah anyway, I think you have some work ahead of you, and a dedicated pool is the way to go

@sp_BlitzErik That is a mental image I did not need

jadarnel27

> 4/30/2018 1:01:30 AM In-Memory OLTP initialized on lowend machine.

sp_BlitzErik

9:06 PM

i am dressed otherwise

jadarnel27

That message isn't very welcoming.

sp_BlitzErik

you sick fiend

jadarnel27

Also man, this server has been up for a while.

Good job, AGs.

@PaulWhite Thanks for the discussion on this, I really appreciate it.

Paul White

Also since when was lowend a word

@jadarnel27 The invoice is in the mail

Joe Obbish

@jadarnel27 "In-Memory OLTP initialized on highend machine."

HtH

Paul White

9:09 PM

all caps?

jadarnel27

@PaulWhite can you just add it to my tab?

sp_BlitzErik

i'm giving up flat bench to squat a 3rd day, ama

jadarnel27

@JoeObbish fancypants.

I ran 8 miles on my lunch break today. I'm very tired.

Paul White

Well that was silly.

jadarnel27

It really was.

Paul White

10:11 PM

@jadarnel27 Added some details in an edit.

2

A: Why we need to optimize CONTAINED IN clause of FOR_SYSTEM TIME using check constraint?

I think the execution plan is a bit misleading here. If I create a temporal table: CREATE TABLE dbo.ThisIsJustATest ( [Id] int NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) , [Junk] nvarchar(2048) NOT NULL , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START , [ValidTo] datet...

I would add a reference for that parallel startup filter thing, but it was on Connect.

connect.microsoft.com/SQLServer/feedback/details/757383/…

@SeanGallardy Might be able to get the text of it, but the really useful stuff was in the comments.

> Status: Closed, Resolution: By Design, 12 Up-Votes, 1 Down-Vote, 0 validations, 1 workaround, 8 comments, feedback id: 757383

repro:

USE StackOverflow;

DECLARE @UserID integer = NULL;
SELECT U.*
FROM dbo.Users AS U
WHERE @UserID IS NOT NULL
AND U.DownVotes > 10;

Execution plan: brentozar.com/pastetheplan/?id=Sy0depr_7

Add OPTION (MAXDOP 1) to see the number of executions change to zero: brentozar.com/pastetheplan/?id=BJSRlaSOX

- ends -

sp_BlitzErik

10:30 PM

@jadarnel27 friend of yours? dba.stackexchange.com/questions/217317/…

Joe Obbish

I kicked ass today

jadarnel27

10:46 PM

@PaulWhite whoa, thanks! I didn't know that's what "startup expression" meant, although I suspected. And I had no idea about the parallel plan quirk thing.

Paul White