The Heap™ – Consultancy ©®

i'm willing to bet that op's indexes are only as good as the initial join, so thinking that a non-join where clause that resulted in sep filters would be an interesting plan choice

may happen if you wrote a case for each possible value in the driver table rather than one long case

Joe Obbish

oh

sp_BlitzErik

anyway, i've been awake for quite a while. going to bed.

Joe Obbish

you basically mean getting the plan with the variables without variables

yeah, I don't know how to write that query either

later

Michael Green

3:16 AM

@sp_BlitzErik Thanks. In this case it is PII. Write once, read once, delete (all being well!).

Mikael Eriksson

4:05 AM

@PaulWhite Thanks, much appreciated.

Paul White

@JoeObbish Just an ordinary performance spool, no?

hot2use

4:33 AM

@PaulWhite No. Just too vague.

hot2use

4:53 AM

@PaulWhite thanks for answering and reopening

...and Good Evening

Paul White

@hot2use Was reopened by five regular people, not me.

@hot2use Fair, thanks.

Andriy M

5:39 AM

Morning

gbn

6:32 AM

Morning all

@PaulWhite This really annoys me. I used to use it quite a lot but now I copy between SSMS and NotePad++ for my own sanity

McNets

7:04 AM

@PaulWhite I'm sorry. I'm late.

Morning

I have a new phone and I didn't receive SE notifications till now.

Andy K

Evening / Morning

Paul White

7:22 AM

@McNets You're forgiven :)

Morning

wv_party_id is nvarchar(100). Welcome to my world of Dynamics CRM online ;-) This is a data lake with tables coming from Dynamics. The person(s) who designed them should be flogged - in other words your typical COTS app. — Saxman 8 hours ago

Ah, Dynamics

@gbn It seems like an odd bug to have. Hopefully an easy and quick fix now SSMS is regularly updated.

gbn

7:37 AM

@PaulWhite One year and one quarter year later...

Paul White

@gbn Well some of that delay will be because Bill submitted it.

It got some extra visibility today in a place that often (not always) produces results.

hot2use

8:08 AM

Anybody any thoughts on this one:

9

Q: Neither DBCC FREEPROCCACHE nor DBCC FREESYSTEMCACHE('SQL Plans') do anything to free CACHESTORE_SQLCP memory

CACHESTORE_SQLCP Sql Plans takes up > 38 GB after a few days. We are already running with "optimize for ad hoc workloads" option on. (Entity Framework and custom reporting creates a lot of ad hocs!) SQL Server 2016 SE 3.00.2164.0.v1 on AWS RDS with multi-AZ mirroring When I run: DBCC FRE...

I gave it my best, but apparently to no avail.

ConcernedOfTunbridgeWells

8:39 AM

@PaulWhite Sometimes I get paid currency for bringing intelligence to the business. Normally this is an exercise in futility.

Paul White

It might arguably be harder to remove intelligence from the business

4

ConcernedOfTunbridgeWells

I've never seen a gig where that was a part of the acceptance criteria.

I could try, though.

gbn

8:53 AM

Our collective job is "futility for cash"

2

Paul White

9:16 AM

Where was that suggestion when the site name was decided

Andriy M

Is it too late to rename it?

Andy K

@gbn +1

Paul White

@AndriyM to meta!

hot2use

9:33 AM

@AndriyM @PaulWhite would that be a bug or a feature?

Andriy M

It would certainly be a discussion

Andy K

we have a question in the sql room that you may be able to answer

in SQL on Stack Overflow Chat, 6 mins ago, by Abaddon

is it posible to restore a backup on a database that's currently being replicated?

Andriy M

9:49 AM

While I've never worked with replication, the question looks to me so far from contrived that I'm sure it's been discussed many times in various forums, probably on DBA.SE as well.

gbn

There are answers here, I found some via Bingling (aye, right, like I used that)

Tom V

10:31 AM

Just came back from the marketing department

I didn't realize we went from industry 2.0 to industry 4.0 in a few years time

ypercubeᵀᴹ

10:43 AM

3 hours ago, by Paul White

wv_party_id is nvarchar(100). Welcome to my world of Dynamics CRM online ;-) This is a data lake with tables coming from Dynamics. The person(s) who designed them should be flogged - in other words your typical COTS app. — Saxman 8 hours ago

Tom V

CRM, I'm not touching that

ypercubeᵀᴹ

@sp_BlitzErik did you notice that ^^^ comment? (or was it obvious from the plans when you were writing the answer)

gbn

@TomV I have an upgrade of ours in the next week or 2. Some, er, .. interesting queries

And more ASYNC_NETWORK_IO errors than I've ever seen before

Tom V

11:09 AM

@gbn Good luck

sp_BlitzErik

11:23 AM

@ypercubeᵀᴹ no, but now i'm even more annoyed. hahaha.

all that going on, but 2016 is the problem.

ypercubeᵀᴹ

@sp_BlitzErik I suggested punishment ;)

The flogging should be extended to whoever wrote or was using the condition where wv_partyid = 343083 without quoting (what should be a) string literal. — ypercubeᵀᴹ 43 mins ago

> ... or posting questions without all the facts

sp_BlitzErik

heh

ain't that somethin

ConcernedOfTunbridgeWells

11:51 AM

@TomV There's CRM and Dynamics, where everything is a GUID and your eyes will melt.

Tom V

@ConcernedOfTunbridgeWells Don't remind me, I integrated Dynamics CRM and Dynamics AX data in a datawarehouse some time ago

Joe Obbish

12:27 PM

@PaulWhite yeah, you're right. still useless for this query as far as I can tell

ypercubeᵀᴹ

1:26 PM

Firefox is crap - when you have 100 tabs open

bluefeet

@ypercubeᵀᴹ can't be as bad as Chrome with 100 tabs open

hot2use

@ypercubeᵀᴹ or Edge

ypercubeᵀᴹ

what's Edge? ;)

gbn

Legacy GSM mobile network data standard

ypercubeᵀᴹ

To be honest I'm not sure if the trouble is the 100 tabs. It could be some memory leak, as I usually have it open for hours / days. It seems to behave after restarting it

hot2use

2:10 PM

@ypercubeᵀᴹ A horrible idea born somewhere in a distant country as a punishment because the EU wanted to separate IE and Windows.

Joe Obbish

Is the only way to set REQUEST_MEMORY_GRANT_TIMEOUT_SEC through resource governor?

sp_BlitzErik

2:26 PM

→ 2 messages moved to Trash

ypercubeᵀᴹ

2:52 PM

@hot2use oh, I didn't know that.

So it's related to that decision/judgement that IE should be a removable part of the OS?

Joe Obbish

@sp_BlitzErik around?

sp_BlitzErik

yeah, what's up

Joe Obbish

Seeing what I would describe as weird behavior on a server, wondering if you've seen it before/have any ideas

have 45 active sessions

only six of them actually got memory grants

12 CPU server, server CPU is around 50%

sp_BlitzErik

go on

Joe Obbish

keep seeing PAGELATCH_SH waits for the sessions without a memory grant

I'm not a wait stats guy, but is that weird?

it doesn't even have a memory grant yet

server has 60000 MB of memory, every query wants a memory grant of 2662216 KB

sp_BlitzErik

2:59 PM

why would they need a memory grant for that?

oh boy

are you sure there's no resource semaphore waits on there?

wonder if the waits are because grants are knocking stuff out of cache that queries are trying to read

Joe Obbish

oh

I think I know what happened

serial required memory = 0 KB for all of them

they're actually all running without a memory grant

this is horrible

sp_BlitzErik

are you running who is active?

Joe Obbish

yeah

for one session

grant_time was 2017-10-20 07:47:30.297

25017 wait time for resource semaphore

sp_BlitzErik

so they're waiting on getting memory grants

Joe Obbish

but it's only 25017 ms

sp_BlitzErik

3:04 PM

total or for the query?

Joe Obbish

total for that session

sp_BlitzErik

so that session has waited 25 seconds on getting a memory grant

Joe Obbish

right, but it's been running for 2 hours

sp_BlitzErik

do you have any of the blitz scripts installed

interrogation mark

Joe Obbish

no comment

sp_BlitzErik

3:08 PM

can you install them?

Joe Obbish

not immediately

I think the queries run for hours with a memory grant of 0 KB

that makes the most sense out of anything

ypercubeᵀᴹ

@JoeObbish regarding your answer in this one

2

Q: Efficient way to handle multiple CASE statements in SELECT

I run a report in which I have a situation where based on a column value which is basically a Key or Id, I need to fetch corresponding value from the mapping Id table. Something like below: SELECT (case when [column1='A'] then (select value from Table1) when [column1='B'] th...

sql-server

sp_BlitzErik

it sounds to me like they're waiting on memory grants -- there may be other waits in there, too

resource semaphore query compile might also show up

ypercubeᵀᴹ

When you rewrite with LEFT joins, the CASE expression can be rewritten as a COALESCE() of the 30 columns. Maybe you want to add that.

sp_BlitzErik

@ypercubeᵀᴹ WHO'S BETTER THAN YOU?

ypercubeᵀᴹ

3:12 PM

Not any efficiency improvement, just slightly more readable

@sp_BlitzErik Pardon?

sp_BlitzErik

i'm asking a rhetorical question

SELECT *
FROM sys.dm_exec_query_resource_semaphores AS deqrs

SELECT *
FROM sys.dm_exec_query_memory_grants AS deqmg

Master Database

0

Q: Unable to add comments for posted question due to javascript error

I'm able to post any question but sometimes unable to add comments to the answers posted and below error message is displayed on the top of the screen. Database Administrators Stack Exchange requires external JavaScript from another domain, which is blocked or failed to load. I'm using IE 11.0 ...

bug comments questions

sp_BlitzErik

well that's stupid

Joe Obbish

3:30 PM

WE FIGURED IT OUT

Andy K

good weekend. cya

Joe Obbish

@sp_BlitzErik they were running with a 0 KB memory grant

and it was terrible

sp_BlitzErik

but waiting on resource semaphore?

Joe Obbish

no

at least not for a long time

serial required memory was 0

so they ran very inefficiently without a memory grant (more or less)

@ypercubeᵀᴹ feel free to edit it in

sp_BlitzErik

3:48 PM

well

this has been vague and confusing as usual

Joe Obbish

you were helpful, thanks

anything that I can clarify in particular?

sp_BlitzErik

i guess not

Joe Obbish

I'm happy to try to answer any questions...

sp_BlitzErik

so they should have asked for a memory grant but didn't?

ypercubeᵀᴹ

@JoeObbish Done

Joe Obbish

4:20 PM

@sp_BlitzErik They all had a desired memory grant of 2.6 GB but a required memory grant of 0 KB

there were too many of them running at once so some of them ran with a memory grant of 0 KB

the lucky ones got a memory grant of 2.6 GB

@ypercubeᵀᴹ thanks!

sp_BlitzErik

were there any forced grants?

Joe Obbish

do you mean with RG or hints?

sp_BlitzErik

neither

SELECT *
FROM sys.dm_exec_query_resource_semaphores AS deqrs

there's a column called forced grant count that'll tell you if sql is stealing memory to give to queries

Joe Obbish

4:46 PM

interesting

never used that dmv

Jack Douglas

9:14 PM

→ 1 message moved to Trash

Joe Obbish

10:07 PM

@PaulWhite @sp_BlitzErik (or anyone else): Anyone have a demo for a SELECT query with a high (250 KB+) value for serial required memory?

sp_BlitzErik

@JoeObbish lemme dig around, running through a deck atm

how soon do you need something?

Joe Obbish

thanks

not urgent

Wednesday or so?

sp_BlitzErik

word

Joe Obbish

10:52 PM

also I'm actually looking for 250000 KB+

Evan Carroll

11:26 PM

@JackDouglas you deleted my ultra cool image.

=(

Transcript for

The Heap™ – Consultancy ©®