stop the merry-go-round

CREATE EVENT SESSION MonitorWaits ON SERVER
ADD EVENT sqlos.wait_info
(WHERE sqlserver.session_id = 53 /* session_id of query */)
ADD TARGET package0.asynchronous_file_target
(SET FILENAME = N'C:\folder\WaitStats.xel',
METADATAFILE = N'C:\folder\WaitStats.xem')
WITH (max_dispatch_latency = 1 seconds);

That will record all of the waits for the query, if you compare hot vs cold the differences we know where to look instead of guessing

You can do it yourself, here: sqlskills.com/blogs/paul/…

So you know I'm not doing any hocus pocus

Joe Obbish

what's wrong with server level if only the interesting queries are running during that time?

Sean Gallardy - Microsoft

It's not fine grained enough

It's like working with the SAN vendors, "I don't see a spike in latency" yet storport records a 15 second wait

Joe Obbish

as in, can't assign a wait to which query? or stuff in the internal pool would add to it?

Sean Gallardy - Microsoft

server level everything adds to it

hard to attribute it to one single thing

plus we only get what changed

This way we get what changed, how much it changed, and each individual change so we can look for spikes

Magic number: 23 minutes, that's how long I waited

Joe Obbish

11:26 PM

for the phone thing?

are wait stats from the xml of an actual plan sufficient?

Sean Gallardy - Microsoft

@JoeObbish There are issues with those...

Yeah for the phone

I'll assume I'm fired

Joe Obbish

ok

so four extended event sessions

one per session

Sean Gallardy - Microsoft

1 at a time

just change the session id and copy the xml

Joe Obbish

oh

Sean Gallardy - Microsoft

or 4

lol

Joe Obbish

11:28 PM

well, the issue seems related to concurrency

but I can start with individual queries

(the reason that it's taking so long is that some partition switching and other stuff happens during ETL and I don't have the code on hand to do that)

sp_BlitzErik

jesus that script is barbaric

Sean Gallardy - Microsoft

@sp_BlitzErik Well jesus was like 2000 years ago so it'd barbaric abouts then

sp_BlitzErik

pastebin.com/grGCbUm7

that's the one i use

suppose i should add a couple of the newer waits to that, eh?

Sean Gallardy - Microsoft

--> IF (@waitfor LIKE '%:%:6%.%') <-- how to speak teenager

sp_BlitzErik

srsly

Joe Obbish

11:34 PM

would you accept the session waits dmv?

Sean Gallardy - Microsoft

@JoeObbish If I have to

Joe Obbish

I'm just not sure what you're looking for

sp_BlitzErik

we're not sure what we're looking at

so it's fun for everyone

Sean Gallardy - Microsoft

@JoeObbish Waits, individual per query

With the ability to know if it's a resource wait or a signal wait

If you want to do server level waits that's fine I guess

at least it something

sp_BlitzErik

show him how to do it with pssdiag ;)

Sean Gallardy - Microsoft

11:41 PM

... seriously the up arrow thing Paul told me today has changed my chatting life

Dude I'd totally configure a pssdiag

The issue I have, personally, with pssdiag is it's a bit heavy handed

sp_BlitzErik

check out this guy, angling for a job at sql skills

Joe Obbish

how do you want latch info?

Sean Gallardy - Microsoft

@JoeObbish medium rare

@sp_BlitzErik Paul white?

sp_BlitzErik

i don't think he wants to work that much

they use pssdiag to monitor client servers

Sean Gallardy - Microsoft

because it works pretty damn well, just a bit heavy

less havy when you take trace and swap it with XE

if you pair down the events it runs fairly ok

sp_BlitzErik

11:49 PM

oh god, i still have the xml index creation in there

what an idiot

Joe Obbish

first query is running

Sean Gallardy - Microsoft

@JoeObbish I charge by 15 minutes increments

Joe Obbish

it

it's going to be all LATCH_EX waits

based on sys.dm_exec_session_wait_stats

and the actual query plan XML

Sean Gallardy - Microsoft

@JoeObbish you watch to speed that up?

Joe Obbish

hm?

I already know what the latch waits are going to be: BUFFER

sp_BlitzErik

11:53 PM

> mfw he still doesn't calculate avg ms per wait

> mfw it's not broken down by cores

Sean Gallardy - Microsoft

So basically the main waits are parallelism

Joe Obbish

-_-

sp_BlitzErik

if only sp2 were out

ahem

Sean Gallardy - Microsoft

1.3 seconds on latch_ex for something that takes that long is negligable

@JoeObbish I feel you

but like, not a creepy perv way

Joe Obbish

the waits don't add up

SQL Server Execution Times:
CPU time = 244328 ms, elapsed time = 91275 ms.

MAXDOP 8, remember

Sean Gallardy - Microsoft

11:57 PM

you know that 1) MaxDOP doesn't mean linear 2) there are waits

3) it's a vm

Joe Obbish

yeah, I know that

but this is a carefully constructed query

which I know has some issues currently with a cold buffer pool

sp_BlitzErik

vm cpu timing is full metal potato

Joe Obbish

same query with a hot buffer pool:

sp_BlitzErik

it even messes up stats time

Joe Obbish

SQL Server Execution Times:
CPU time = 231344 ms, elapsed time = 29623 ms.

Are all parallel queries linear? Of course not. But this query can do as well as 7.8

Transcript for

♦ stop the merry-go-round