stop the merry-go-round - 2019-06-25 (page 1 of 2)

You mentioned this: The problem is load related. With 4 worker processes running it did not happen. But with 10 workers it happens consistently. So it sounds like the failover was not the only thing that changed - you also increased concurrency on the application side of things. I tried lo...

Erik Darling

👋

Paul White

@JoshDarnell You might like to try rerunning your test with the index hint suggested in the other answer.

Erik Darling

i can't believe he linked to the docs page and not my smash hit youtube video

NOFRIENDO

Paul White

It's not unexpected to see two concurrent U locks though, that's the point of READPAST. The issue is two queries returning the same row.

You can easily repro that by running the SELECT query inside a transaction in one session, leaving the transaction open, and running the same query in a second session and transaction. The second session will not block, and there will be two concurrent U locks on different rows.

Erik Darling

does logic exist anywhere for sql server to try to use indexes that aren't currently locked?

i doubt it but eh

Paul White

11:06 AM

@ErikDarling no

@JoshDarnell Likewise you can repro the same-row selection by using an INDEX(1) hint on one query and INDEX(2) on the other. Both will select the same record, using different access paths.

Still I gave your answer an upvote because it is nicely written

Erik Darling

nice to be new

Paul White

11:34 AM

Can't believe my wife had the last glass of Les Courtilles.

Erik Darling

11:47 AM

The true gourmand emerges

George.Palacios

12:09 PM

@PaulWhite Cotes du Rhone?

Paul White

yesterday, by Paul White

@ErikDarling Tonight is brought to you by Les Courtilles Côtes du Rhône 2016

George.Palacios

I do like a good red

Found one using grapes grown on Mt Etna the other day

was very earthy. nice though

Erik Darling

Probably better than Mt. Aetna

George.Palacios

@ErikDarling Welp. Enough of this rabbit hole: en.wikipedia.org/wiki/Aetna#1800s

I have not spent my time wisely today.

Erik Darling

12:24 PM

that's how most people feel after joining this chat

it's not too late for you

George.Palacios

I just fear that I won't be accepted elsewhere now

Joe Obbish

I had a dream about excessive PAGELATCH_UP waits

I tried fixing it with in memory OLTP

then I wanted to get rid of the filegroup and couldn't

I'm not gonna make it

Erik Darling

yeah i'd be worried if i had a dream that i was josh too

Josh Darnell

@ErikDarling -_____-

@PaulWhite Thanks for the information, Paul!

And it's nice to know I can write well, even if I can't seem to write the correct things lately.

Joe Obbish

12:46 PM

@ErikDarling wut

Paul White

@JoshDarnell Don't feel bad at all - 95%+ of the users on this site couldn't answer that question correctly.

So answering it well is a very good start.

Joe Obbish

looks like a low percentage

George.Palacios

5% answering that correctly is generous

Paul White

There was a plus on the end

And that doesn't account for the fraction of users that would answer.

Josh Darnell

@PaulWhite Well that's encouraging 😃 I'll attempt to put a stop to my Eeyore attitude now.

Answer edited.

Paul White

12:57 PM

Everyone learns here. I often think answerers learn the most.

George.Palacios

@PaulWhite Is Answerer a real word?

Josh Darnell

@JoeObbish Erik was referencing the fact that I have a production app with an in-memory filegroup I can't get rid of.

George.Palacios

Does that mean I can say Answeree

Erik Darling

joe doesn't read the transcript confirmed

Paul White

@George.Palacios There is written evidence for it right there

George.Palacios

12:58 PM

@PaulWhite Paul White is the Queen of England confirmed

I'm pretty sure she can just make up words

and they're automatically real

Josh Darnell

The guys from the SQL Server Radio podcast suggested in their latest episode that "Trace Flag" should be renamed to "Paul White Flag."

Thoughts?

Peter Vandivier

@JoshDarnell dbareactions.com/post/97493204910

Paul White

All seems perfectly ondulous.

Josh Darnell

@PeterVandivier Hahaha that is great.

Erik Darling

because you're waving the white flag of mental surrender to sql server?

@JoshDarnell i think you just got called a jr dba

Paul White

1:05 PM

Hekaton is so awesome you'd be stupid to drop it.

SQL Server is just protecting you from yourself.

Josh Darnell

@ErikDarling Sounds like a promotion.

To be fair, we were tricked by Microsoft into using Hekaton.

Erik Darling

does anyone else find it odd that if you add 16 cores to a 16 core sql server you only get 256 more worker threads but if you get a whole other sql server you get 704 threads?

George.Palacios

Pssh. Those threads are hardly working then

Paul White

2

A: Why is our query suddenly returning rows it should not (using READPAST and UPDLOCK options)?

Getting the same row from different indexes As David mentions in his answer, you can get the same row from multiple sessions if you happen to access that row via different indexes. The UPDLOCK hint only applies to the specific access method. Having a nonclustered index row U locked does not pre...

Much better, sorry about adding a typo, fixed now.

Josh Darnell

No worries, thanks for the edits! That is helpful stuff.

17 mins ago, by Paul White

Everyone learns here. I often think answerers learn the most.

I think I've learned far more from answering questions than any other method of learning.

Erik Darling

1:14 PM

the nice part about paul editing your question is that it usually ends up as a hnq

Paul White

@JoshDarnell Did you find HK didn't help?

Josh Darnell

@ErikDarling Sounds like evidence of collusion.

Erik Darling

hard to say

Peter Vandivier

@JoshDarnell maybe you should ask the secret cabal behind @StackDBAs

Paul White

1:35 PM

31

A: How does the twitter bot work?

Looking at the Server Fault and Super User Twitter streams, it looks like each account is set up to automatically tweet a new status every 3 hours. According to this answer by Nick Craver there is a "hotness" algorithm based on views over a certain period of time. (This is probably similar to t...

Peter Vandivier

mind += blown

Joe Obbish

@PaulWhite v. imprecise

Peter Vandivier

i've thought that was a Paul sock for years

George.Palacios

@PeterVandivier Thank god you didn't miss an equals sign out there

Peter Vandivier

@George.Palacios fixed

George.Palacios

1:43 PM

hahahaha

Actually that's incorrect syntax

SELECT mind += blown

FROM body

Josh Darnell

@PaulWhite We were going from the in-process session model to SQL Server session, so there was never a good comparison made. In other words, we didn't try the non-Hekaton SQL Server session state provider.

It works great in production, no problems at all.

In development it has caused weird problems that we can't seem to get rid of.

Sep 11 '18 at 20:02, by jadarnel27

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

I never set up a dedicated resource pool on my laptop for Hekaton like Paul suggested.

I forgot about that conversation until just now.

Joe Obbish

your laptop needs to go to the gym

George.Palacios

Just needs brain training. Not a good enough memory

Peter Vandivier

@JoshDarnell i gave up on hekaton when assigning resource pools ended up seeming to increase the number of OOM events

well... not "gave up" so much as "changed jobs shortly thereafter"

actually heard from that job a while later that the great Ned Otter ended up giving an assist in root-causing that little chestnut

Josh Darnell

2:00 PM

@PeterVandivier Yikes.

Maybe I should email Ned.

Peter Vandivier

he's been known to do a post or two on the subject

;p

Josh Darnell

@JoeObbish no u

@PeterVandivier Heh, yeah.

Erik Darling

2:15 PM

0

Q: Setting up SQL Server Developer Edition

I just installed SQL Server Developer Edition and SQL Server Management Studio, how can I configure the server? I want to configure it to have server name localhost-sql-server, with a login/password. I found the configuration manager but not sure where I can modify this.

sql-server

George.Palacios

That's babbies first SQL install

poor guy has no idea the pain he's about to go through

Erik Darling

yep

Joe Obbish

comment answer detected

Erik Darling

nope

comment does not detail renaming process; does not answer question

Joe Obbish

bad answer comment detected

Erik Darling

2:25 PM

flag and flog

Max Vernon

Erik - I thought you might like to know all our nightly utilities jobs defragment indexes and then update stats. ;-)

I laughed when I first saw that

Erik Darling

sounds like a job for a consultant

they should be running every 15 minutes

Max Vernon

yah, that's what I suggested lol

Erik Darling

also you should back up, drop, and restore your databases every night

Peter Vandivier

too much work, pros auto close

Joe Obbish

2:28 PM

@MaxVernon are you enterprise?

Max Vernon

@JoeObbish yes sirree bob

Joe Obbish

why aren't you rebuilding indexes 24/7 then?

Max Vernon

lol. just run the job in a tight loop?

we love unnecessary I/O

@PeterVandivier we have a couple of vendor-provided databases that were configured like that. FML.

Erik Darling

online = totes

Max Vernon

we also have a single vendor-database where their software auto-shrinks the database and log frequently.

Peter Vandivier

2:31 PM

@MaxVernon dbareactions.com/post/58415916150

Erik Darling

some dba max is

letting this hanky panky go on

Max Vernon

@PeterVandivier lol

Joe Obbish

@MaxVernon get more RAM

Max Vernon

@ErikDarling its so hard to prove that indexes don't need to be defragged

Joe Obbish

you should have enough ram to hold all indexes in the buffer pool

that way rebuilds don't do I/O

hth

Max Vernon

2:32 PM

@JoeObbish always need more ram

Erik Darling

@MaxVernon no it isn't. just stop doing them.

Joe Obbish

I bet Max's PLE is under 300 seconds

Max Vernon

@JoeObbish you know it is

Joe Obbish

@ErikDarling No one ever got fired for doing index rebuilds

Erik Darling

no one ever gets fired

George.Palacios

2:33 PM

^ this

Joe Obbish

Problem Solved

Max Vernon

I got fired the day after I came back from vacation once. that was fun.

George.Palacios

"Why's the server running slowly?" "We're increasing performance"

Peter Vandivier

@ErikDarling i quit over index rebuilds

Erik Darling

way to take a stand

Peter Vandivier

2:34 PM

fight the power

George.Palacios

Why even rebuild indexes? Just drop them

Max Vernon

even better - add an index to every column

George.Palacios

Beat me to it

Max Vernon

single-column indexes, with no include columns. and also have no clustered indexes.

George.Palacios

Everyone knows that performing 100000 key lookups is faster than a scan... right?

Peter Vandivier

2:35 PM

if the missing index suggestion pops, you're not done yet

Max Vernon

@George.Palacios common knowledge

George.Palacios

(100000 was the biggest number I could be bothered typing)

MAX(BIGINT)

MAX(VERNON)

lol

2:36 PM

oh boy

He just called you the biggest number he can imagine

Max Vernon

its easy to get smaller when you start big

George.Palacios

The only higher number that scientists understand than MAX(VERNON) is the number of ongoing legal cases Oracle has at any one time

Joe Obbish

I need to navigate the American healthcare system

I became annoyed after 2 minutes

my time is already being wasted

Max Vernon

it's built with annoyance as the primary concern

Erik Darling

i'm trying to get new insurance and i'd rather die penniless from hospital bills

Josh Darnell

2:39 PM

@Erik any suggestions to improve the performance of the xquery here? dba.stackexchange.com/a/241307/6141

Max Vernon

@ErikDarling whispers (single-payer health care)

Erik Darling

@MaxVernon then i'd die waiting for a doctor

@JoshDarnell how long does it take now?

George.Palacios

@ErikDarling It's not bad over here if it's an emergency to be fairt

Although 2-3 weeks for a routine appointment with a GP isn't fun

Josh Darnell

@ErikDarling It takes 10-15 seconds on my laptop.

George.Palacios

the nurse I spoke to the other week at the surgery basically told me to says it's an emergency even if it isn't if I need it looking at in under 2 weeks

Erik Darling

2:41 PM

@JoshDarnell 3 seconds on mine. get a better laptop.

Josh Darnell

@ErikDarling OP says they canceled the query after 10 minutes.

On one of their busier servers.

Erik Darling

@JoshDarnell

SELECT
    files.[file_name],
    MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event
FROM sys.fn_xe_file_target_read_file
(
    @file_path + 'system_health*',
    null, null, null
) files
GROUP BY files.[file_name]
OPTION(NO_PERFORMANCE_SPOOL, QUERYTRACEON 8649);

Josh Darnell

What do you get if you change min to count? I've got around 50k events.

Paul White

@George.Palacios which country is that?!

Josh Darnell

@ErikDarling 4 seconds 😱

George.Palacios

2:44 PM

@PaulWhite the UK

We're in a pretty bad state at the moment

Erik Darling

@JoshDarnell yep but remember spools are always good

Max Vernon

most likely

Erik Darling

politics are dumb

Josh Darnell

Thanks, @ErikDarling.

By the way, does anyone know how to fix the code highlighting in the post? The backslash within single quotes is messing things up.

Jun 20 at 13:41, by Joe Obbish

can you guys cool it with the political stuff? that's not the point of this room

Erik Darling

@JoshDarnell could maybe maybe get it faster by dumping some stuff in a temp table but eh

→ 6 messages moved to Trash

George.Palacios

2:47 PM

hah

"Erik Darling has invited you to join Trash"

well thanks Erik

Way to say how you really feel

Erik Darling

yet you're already here

George.Palacios

It's some weird quantum effect.

Erik Darling

i wonder how many xml queries in blitzcache would be better with the no perf spool hint

or 8690

hm

George.Palacios

1%+

Minimum.

Peter Vandivier

@JoshDarnell think that's "just how it is" - does nchar(92) work as a sub?

Erik Darling

2:55 PM

@George.Palacios you spelled 100% wrong

Peter Vandivier

in code i mean, it def fixes the highlighting

Paul White

@JoshDarnell I'm not aware of a great solution, but meta.stackexchange.com/q/124024

George.Palacios

This is certainly not how it works in Transact-SQL. Perhaps we need more specific dialects than ? — Aaron Bertrand May 31 '12 at 15:18

heh

Peter Vandivier

or i could just... you know... try it...
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=21cd0a1bbb074a42ee29090ae9853940

Paul White

Or you could just abandon syntax highlighting for that code block

George.Palacios

3:00 PM

Code with no syntax highlighting? What are we... savages?

Paul White

I don't know. How long does it take you to get a GP appointment?

That seems like a pretty good metric.

Josh Darnell

@PaulWhite Your search-fu is so strong. I tried to find that and couldn't.

He's even using the same basic code that I am as the example.

George.Palacios

@PaulWhite hah. We have video GP appointments now as a result of our failing services so... silver lining?

Paul White

@JoshDarnell It helped that I remembered Aaron asked it.

Though it is the top result for a search of: single quote highlight*

Josh Darnell

3:53 PM

@Forrest In your new blog post, your DMV screenshot shows total_worker_time of 5,114,683. The next paragraph refers to that as "5s of CPU for the query." I assume the 5 million number is in ms, which seems like that would be ~5,000 seconds of CPU. What dumb thing am I missing here?

Erik Darling

microseconds?

Josh Darnell

Oh snap.

You're right.

Erik Darling

oi oi oi

Josh Darnell

> Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled.

That is serious OP behavior from SQL Server right there.

Erik Darling

it's like the duration column in profiler

Josh Darnell

3:56 PM

By the way, @Forrest - thanks for linking to my blog 😍

Paul White

perfmon is the only tool you can trust

Josh Darnell

Erik linked to Richie's EF post the other day and it hurt my feelz.

5 hours ago, by Erik Darling

NOFRIENDO

Joe Obbish

@PaulWhite my perfmon guy would both agree and disagree

sometimes the ingredients expire when you try to cook them

Erik Darling

@JoshDarnell where?

Josh Darnell

3

A: Large variances between two similar queries when doing WHERE EXISTS on related table

I understand the need to anonymize, but it makes analysis really difficult. There's also not a reasonable way to guess at why when you query two different tables, you get different performance without seeing the table and index definitions (aside from the fact that they're, well, two different ta...

Erik Darling

4:10 PM

fixed

Paul White

3 hours ago, by Josh Darnell

@ErikDarling Sounds like evidence of collusion.

Erik Darling

i'll fix you too

Paul White

i'll fax you back to 1985

That'll fox you

Erik Darling

i'll flex my pex and cash some chex

Paul White

😀

Erik Darling

4:15 PM

i would love to go back to 1985

man

now i'm angry that it's current_year()

Josh Darnell

But then you'd be missing out on the collective works of Weezer.

Paul White

might take a while at 9600 baud

but worth it

the 80s were superior to every subsequent decade in all respects

Erik Darling

and prior

Paul White

excellent point

Erik Darling

i think if i turned 21 in 1980 and died at 31 in 1990 i'd be fine

Paul White

4:21 PM

yep seems like a good deal

I might go for 16-26 though

Erik Darling

how about 18-28?

Paul White

good times

well yeah sure but I'd want a couple of years of illegal drinking

oh it's 21 in the US eh

Erik Darling

hah

yeah it does build character

Paul White

absolutely

Josh Darnell

> so we end up with process that starts rather quickly, but as soon as fragmentation kicks in its performance drops drastically

Is this @MaxVernon's coworkers?

0

Q: Indexes getting highly fragmented during normal usage of system

I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users. Currently indexes are rebuild once per day during maintenance window, but after certain operations some of t...