The Heap™ – Consultancy ©® - 2018-05-03 (page 2 of 2)

Max Vernon

3:00 PM

@202_accepted are you using WITH (NOLOCK)?

Lamak

@202_accepted the execution plan might come handy

202_accepted

@MaxVernon Yes, for this one because I don't want to hold up our website.

Max Vernon

for something where you're looking for the max length of a column, I would certainly suggest using nolock.

202_accepted

@Lamak Grrr, forgot to pull it. :(

The query is super simple: SELECT DATALENGTH(...) FROM ... WITH (NOLOCK)

I think it's a disk issue, because the first run took ~3:30, and the second took ~4 seconds.

jadarnel27

You should run sp_BlitzFirst during one of your slow queries against that table.

Whatever you do, don't run @sp_BlitzErik. It has undocumented behavior, and has been known to introduce corruption (example: this very chatroom).

9

202_accepted

3:05 PM

FFS, two of these TEXT columns are empty for all rows.

Who built this POS?

Paul White

nvm

Lamak

don't go censoring yourself

you are too committed to been a mod

Paul White

Yeah nah I just realized I should edit the answer instead.
It is a daft comment because any delay will not change the chance of a collision anyway, just offset things a bit.

hot2use

@Taryn I saw a sensible comment. That's strange. Am I still in The Heap?

jadarnel27

Daft is an underused adjective.

202_accepted

3:13 PM

@PaulWhite Last question: is there any way I can estimate how many rows would still be entirely in-row vs. out-of-row if I were to convert these TEXT columns to VARCHAR(MAX)?

sp_BlitzErik

@202_accepted your best option might be to take a copy of the database, design the table to your liking, then insert the old data into it

then take your measurements

202_accepted

Aye, that's a good idea.

Thanks guys, really appreciate the brainstorming.

Well that was neat, only took 1:15 to insert the entire source table to the test table.

type_desc	total_pages	used_pages	data_pages
IN_ROW_DATA	145625	145601	145061
ROW_OVERFLOW_DATA	0	0	0
LOB_DATA	41	37	0

@sp_BlitzErik (or anyone): what else would be in LOB_DATA? Is that where the VARCHAR(MAX) overflows are?

Paul White

yes

202_accepted

Oh alright, so that looks a lot better than.

Paul White

well off-row data isn't necessarily bad, it depends on how you access the data

202_accepted

3:24 PM

SELECT * ;)

Paul White

of course

202_accepted

I want to remove the * and replace it with solid columns, but one thing at a time.

Lamak

@PaulWhite now it's the moment when you say you were just joking around about the whole thing

Paul White

I never joke

202_accepted

Why is my IN_ROW_DATA for my test table (which is a dup of the live one) so much smaller than the live table?

Paul White

3:25 PM

I have no sense of humour

Lamak

@PaulWhite we know

Paul White

:'(

@202_accepted Probably more compact. The original had space left by deletes/page splits

202_accepted

@PaulWhite Huh. There should never be deletes on this table, but that makes sense.

Paul White

Page splits then

sp_BlitzErik

updates and inserts can cause data to shuffle as well

202_accepted

3:27 PM

Yeah, I forgot about that. I did all these in one big INSERT to the test one, so I'm sure it reorganized it more tightly.

I assume replacing the TEXT with VARCHAR(MAX) also changed some of the layout.

sp_BlitzErik

yeah

202_accepted

Good thing is the new table size is smaller (by a shockingly large amount) too.

Lamak

@202_accepted don't forget the weather

202_accepted

@Lamak Rainy here, so DC is probably moving pretty quick because the water is getting all those electrons flowing. ;)

About to try to have the "TEXT is bad, mkay?" conversation with the other dev so we can swap these columns out tonight. Wish me luck. ;)

Zane

Woaaaah! this is nuts. Windows is citing memory pressure and taking from both my SQL server instances and then loosing to seemingly nothing.

We have a total of 90 of 520 gigs of ram committed and windows is citing 100% memory and still stealing from those apps.

I've never seen anything like it.

sp_BlitzErik

3:35 PM

@Zane docs.microsoft.com/en-us/sysinternals/downloads/rammap

Zane

Windows guy has kicked me off so we'll see what he finds.

sp_BlitzErik

i hate windows guy

Lamak

@sp_BlitzErik Evan?

202_accepted

I just emailed the SVP of BIS "I want to destroy and replace our orders table."

sp_BlitzErik

has he changed his name to Evan X yet?

202_accepted

3:46 PM

Let's see how long it takes for him to flip out. (He's also in Vegas for a conference, so that should be even better.)

sp_BlitzErik

maybe he's drunk enough to go with it

202_accepted

I hope so.

Even if he doesn't, I'll do it anyway. Trollface.gif

Zane

@sp_BlitzErik He is already proving to be quite inept

sp_BlitzErik

@Zane bitcoin mining is tough on memory

2

202_accepted

Before we do this, he wants our contracted DBA to look into it, is there any specific verbiage that would let any of you know that this is what I think it is?

sp_BlitzErik

3:58 PM

about which part?

changing the columns or the normalizing?

202_accepted

The TEXT vs. VARCHAR(MAX).

(Baby steps.)

Zane

This guys brilliant theory that SQL server is bleeding memory from itself doesn't have me convinced.

Lamak

@202_accepted it's been deprecated since SQL Server 2005?

sp_BlitzErik

well what's the plan? alter the columns or migrate tables?

@Zane yeah must just need to reboot the printer service

202_accepted

@sp_BlitzErik I'm thinking migrate the entire table to a new one.

Zane

3:59 PM

@sp_BlitzErik bwahaha we can't use that here.

202_accepted

Though, I'm up for other options. Can I ALTER TABLE ... ALTER COLUMN SpecialInstructions VARCHAR(MAX) without major issues? (I know there's no guarantee, but in general.)

sp_BlitzErik

i wouldn't do that just because you may lose some of the gains from moving the data

202_accepted

That's what I was thinking. I figured a full migration would be best, as it also gives the pages a chance to reorganize.

sp_BlitzErik

so i'd say our table is using text for a number of columns, which is deprecated. when i migrate the data to a table using supported types the metrics that improve are ...

202_accepted

Excellent, that's exactly what I'll do. Thanks again, Erik!

sp_BlitzErik

4:02 PM

np

202_accepted

So far, some rough estimates have time to do a full-table-scan at 15% faster, and storage use is down 30%, which I personally like both of.

If I DBCC DROPCLEANBUFFERS, will that affect all databases?

Lamak

@sp_BlitzErik which was deprecated almost 15 years ago

you dinosaur

and maybe add "you are a dba, you shouldn't need someone to explain this to you"

jadarnel27

@202_accepted Yes

sp_BlitzErik

@Lamak so short sighted. this is a good time for whatever personage to gain the trust of the dba

jadarnel27

At least, all databases that have data in the buffer cache =)

sp_BlitzErik

4:06 PM

so they can be MURDERED later

202_accepted

@jadarnel27 Ooof, I don't think I want to hit the production database like that.

Lamak

@sp_BlitzErik you might be right this time

jadarnel27

@202_accepted Production? Absolutely not.

202_accepted

@jadarnel27 Ehh, what do I care anyway. May-as-well live a little.

BRB BURNING IT ALL TO THE GROUND

sp_BlitzErik

@jadarnel27 clean buffers. you'd have to checkpoint a time or two and then drop the clean ones to make sure you get them all.

buffers are like herpes. especially the dirty ones.

202_accepted

4:08 PM

@sp_BlitzErik So I want to CHECKPOINT; DBCC DROPCLEANBUFFERS?

sp_BlitzErik

yeah

202_accepted

Awesome, will do.

Paul White

@sp_BlitzErik As sure as Kilimanjaro rises like Olympus above the Serengeti

202_accepted

@PaulWhite I blessed the rains

jadarnel27

May as well shrink the transaction log file while you're in there.

202_accepted

4:10 PM

Ehhh, we use that for backups.

I just want to force it to do a comparison of loading both these tables from disk.

(To see if it's actually faster or not.)

jadarnel27

@202_accepted Schedule an agent job that does this every 15 seconds - keep your buffers extremely clean :P

@202_accepted Sorry, I was just kidding =)

202_accepted

@jadarnel27 I'll just do a WHILE loop and let it run infinitely.

Lamak

@jadarnel27 tsk, tsk....we don't joke here

jadarnel27

@202_accepted There might be some value in doing a before and after with set statistics io on (docs). If you want to show that less reads are being done.

Lamak

one of our mods doesn't have a sense of humour

202_accepted

4:12 PM

@jadarnel27 That's not a bad idea, but I'm running this test from a .NET program, so I have no idea how to do that.

jadarnel27

@Lamak ...maybe I should go =P

Zane

Had to log off here since I was screen sharing and didn't want the windows guy to see me calling him a moron.

sp_BlitzErik

holy moly

0

Q: Why does my scalar UDF perform so differently on two different (but extremely similar) servers?

Recently, I was troubleshooting a strange performance issue that impacted an application's production environment, but not any of the lower environments. I managed to replicate the issue in its simplest form with this query: SELECT product_id, dbo.TranslateStatusToActive(status_id) FROM prod_Pro...

Zane

Different but similar... Well that was an easy answer.

202_accepted

SOMEONE RESET MY MAXDOP WTF

Lamak

4:14 PM

extremely similar

Zane

@Lamak sorry you can edit my answer then.

Lamak

@202_accepted your personal MAXDOP?

202_accepted

No the one on the stupid database.

jadarnel27

@Lamak That looks suspiciously like a joke.

Lamak

@202_accepted Must.Remember.Be.Nice

202_accepted

4:17 PM

We had some problems with the server doing a lot of weird parallelism that it shouldn't have, so the DBA we hired said to set MAXDOP to 1 because we're not doing anything special.

sp_BlitzErik

son of a bitch i got peanut butter on these shorts already

2

202_accepted

Someone set it back to 0.

jadarnel27

@sp_BlitzErik I will probably feel silly for asking, but why "holy moly?"

202_accepted

@jadarnel27 "perform so differently on two different (but extremely similar) servers" probably

sp_BlitzErik

> At a loss for what to do next, I enabled "Optimize for ad hoc workloads" on the production server. This fixed the performance issue! One thing, though: neither of the other environments had this setting enabled. I had been regularly clearing the procedure and system caches in each environment during testing

"i had a brain tumor until i huffed an air horn"

5

202_accepted

4:20 PM

@sp_BlitzErik What is "Optimize for ad hoc workloads"? (I guess I can Google that too.)

Paul White

@sp_BlitzErik Seems like a structured approach to troubleshooting

> At a loss for what to do next, I set the server on fire...and it worked!

202_accepted

Didn't one of you guys tell me to never use scalar UDF's?

sp_BlitzErik

everyone will tell you that

2

Paul White

Except Dr. Freud

202_accepted

@sp_BlitzErik What's the actual issue with them? I've never actually really known why they are bad.

Lamak

4:25 PM

@202_accepted they are terribad

sp_BlitzErik

run once per row returned by the query, force serial plans, hide all of the work they do in a compute scalar, don't surface work they do internally so stats time/io

Paul White

running many statements per row can be especially bad if there is any monitoring/stats collection running on statement completion

202_accepted

So even something as simple as RETURN CASE WHEN @Value = 1 THEN 'Active' ELSE 'Inactive' END has those problems?

(I'm genuinely curious, because in our case there are a few that would be nice that do simple things like that.)

sp_BlitzErik

yeah

Lamak

@202_accepted yup

202_accepted

4:29 PM

Wow, that's completely unintuitive. That's good to know.

Zane

@sp_BlitzErik I'm becoming more and more convinced that this person works for my windows support team.

Lamak

they might be even worse than text

jadarnel27

@sp_BlitzErik I tl;dr'd after 4 paragraphs. Yikes.

Zane

@202_accepted entire room raises hand

6

202_accepted

So I send an email that I want to burn and unburn that table, and the response I get is amazing.

Thanks Elliott,

Is there any impact on the Special Instruction comments content or as it pertains to how a client integration writes to that field?

Also in that case, can we add additional fields to separate out Special Instructions to permission by who they are intended for? Such as SpecialInstructionsVendor vs. SpecialInstructionsAdmin? To allow us to add another box to the order detail.

My boss completely ignored everything in regards to burning it, and literally only cares about whether or not it'll break more stuff, and can we add more stuff while we do it.

jadarnel27

4:32 PM

402 columns \o/

sp_BlitzErik

you should ask him what the stripper told him the email said

202_accepted

@sp_BlitzErik Lmao

@jadarnel27 WOOT WOOT

Told him I can add those columns: "Please do, as extras intended for future use."

sp_BlitzErik

lol

202_accepted

I think I just got approval to burn our entire database table to the ground.

sp_BlitzErik

i mean i'm not a lawyer but sure

202_accepted

4:38 PM

What's the worst that can happen? I get fired? At least then I don't have to maintain this dumpster fire.

And one less garbage fire in my life is always welcome.

Lamak

@202_accepted "intended for future use"....meaning those 2 columns will be empty forever....so don't worry, just make sure they accept NULLs

202_accepted

@Lamak Oh absolutely.

Technically, we only need one.

We have a SpecialInstructions, which he basically implied reusing, and then adding either a SpecialInstructionsAdmin or SpecialInstructionsVendor.

jadarnel27

Don't forget: best practices say that you should put two hyphens in front of the column definition for future-use columns.

202_accepted

@Lamak Oh yeah, last time we "added something for future use" was like 2011 and those columns still aren't used.

@jadarnel27 I thought it was X-?

Lamak

@202_accepted you should be proactive and add SpecialInstructionsVendor2 to SpecialInstructionsVendorN.....for future use

Paul White

4:43 PM

Reserved xml null

@sp_BlitzErik Provide me with a song to listen to before I retire for the day

jadarnel27

"for the day"

Paul White

Words are so inadequate sometimes

jadarnel27

@PaulWhite True. And I was just joshing you.

sp_BlitzErik

@PaulWhite currently playing

Screaming For Emily - The Love

►

Lamak

"joshing".....just learned a new word

Paul White

4:46 PM

@jadarnel27 I know. But I did struggle with what to write. "for the day" is limp and quite inaccurate.

@sp_BlitzErik ty in progress

202_accepted

@Lamak Well I need 001..999.

I should cheat and build a view to replace this table instead. Muahahaha

sp_BlitzErik

@202_accepted you should make the new table with the columns he asked for

two birds

Paul White

--stone

202_accepted

@sp_BlitzErik I am, currently dropping the new table and rebuilding it with those columns and everything, then going to run some basic perf stuff and see how it rolls.

Paul White

@sp_BlitzErik super++ would request again

Lamak

4:50 PM

@202_accepted the boss will later see that the performance went up....and surely think it was because of his recommendation of adding new columns

2

sp_BlitzErik

@PaulWhite anytime, friend

Lamak

better screw it up before that

202_accepted

@Lamak For sure, about to remove some disks from the SAN to see how it goes. ;)

Paul White

@sp_BlitzErik bit presumptuous

seriously thanks that was perfect. off few a couple of hours zzz

sp_BlitzErik

adios

202_accepted

4:52 PM

I may as well standardize the index names while I'm doing this.

Lamak

@PaulWhite because he said "friend"?

Paul White

@Lamak yes stop pinging me I'm asleep

7

Lamak

@PaulWhite ah, yes

@PaulWhite sorry for that

@PaulWhite good night

Paul White

😀😀😀

Zane

lol

202_accepted

4:56 PM

Woah, there are three identical indexes on this table.

sp_BlitzErik

5:15 PM

you would know that if you used sp_blitzindex

Taryn

+1

202_accepted

@sp_BlitzErik I know I know, I need to install the sp_Blitz stored procedures sometime, just haven't had time yet.

sp_BlitzErik

@202_accepted hit f5 once

202_accepted

Is this all just one procedure?

sp_BlitzErik

it's several procs in one file

that should fit into your schedule

202_accepted

5:25 PM

Ah, each "CREATE" is above the proc.

Done.

jadarnel27

Erik has unfettered access to your server now.

202_accepted

That's fine it's a piece of shit anyway.

sp_BlitzErik

which means two things

1. it will be fast

2. all results will be wrong

jadarnel27

lol

202_accepted

Acceptable.

jadarnel27

5:28 PM

@sp_BlitzErik So you turn it into a document database?

202_accepted

This server is a real-life garbage fire and I honestly am debating shutting the fire-suppression off.

@jadarnel27 May as well, we have more TEXT columns in here anyway.

type_desc	total_pages	used_pages	data_pages
LOB_DATA	635707	635557	0
IN_ROW_DATA	371387	371335	370264
IN_ROW_DATA	55619	55597	55385

FUUUUU

Forgot this other table had a TEXT on it as well.

sp_BlitzErik

@jadarnel27 i return it to its intended form

all is documents

documents is all

praise json

202_accepted

FLAT FILES ALL THE WAY

Zane

@sp_BlitzErik the results are another persons problem. I just make it go fast.

2

202_accepted

5:43 PM

About to start this migration and go to lunch. ;)

sp_BlitzErik

@Zane if it takes forever to get the right results you might as well not even get them

Zane

@sp_BlitzErik also if the data is wrong they might not even notice... They definitely notice when it's slow lol

sp_BlitzErik

and how

Zane

Good lord Sentry is telling me file cache took 429 of my 520 gigs. Yikes.

Zane

7:25 PM

Well that was a fun day. Excited for the part where there can be drinking.

Max Vernon

@Zane Winnipeg Jets are playing Nashville tonight... 4th game. Series is 2-1 so far for the Jets. I think there might be some drinking tonight.

 ________  ________             ___  _______  _________  ________           ________  ________  ___
|\   ____\|\   __  \           |\  \|\  ___ \|\___   ___\\   ____\         |\   ____\|\   __  \|\  \
\ \  \___|\ \  \|\  \          \ \  \ \   __/\|___ \  \_\ \  \___|_        \ \  \___|\ \  \|\  \ \  \
 \ \  \  __\ \  \\\  \       __ \ \  \ \  \_|/__  \ \  \ \ \_____  \        \ \  \  __\ \  \\\  \ \  \
  \ \  \|\  \ \  \\\  \     |\  \\_\  \ \  \_|\ \  \ \  \ \|____|\  \        \ \  \|\  \ \  \\\  \ \__\

Zane

I think the jets are going to take that one tbh. Nashville is tough but they tend to either score in buckets or not at all.

Jets are much more consistent.

Max Vernon

@Zane cheers for that! I sure hope Jets win, lol!

Zane

lol

Max Vernon

7:51 PM

I'm listening to a podcast, one of the guys just said "presumption is the mother of all fuckups".

Zane

8:03 PM

lol

jadarnel27

> If you have any management software installed, such as IBM Tivoli Server Monitor, and it decides to use 100% of one core, you probably won't be able to do anything other than cold shutdown the machine. Don't ask me why I'm bringing that up.

@MaxVernon has battle scars =P

Max Vernon

@jadarnel27 damn Tivoli!

damn IBM!!!

jadarnel27

You must have been at a very enterprise-y place.

Max Vernon

2500 SQL Server databases spread across something like 250 servers. Yah, enterprisey captures it pretty well.

@BrentO - what a great Review of SQL Server 6.5!

2

Microsoft recommends at least 16MB of memory.

no, not CPU cache. RAM!

jadarnel27

8:30 PM

@MaxVernon Haha wow! That's a ton. Yeah, the only time I was around Tivoli was at an electric utility. So I mentally associate it with big setups like that.

Max Vernon

the bar none craziest setup I ever saw was 3 servers with 30,000 databases. and they had zero DBAs.

jadarnel27

"3 servers + 30,000 databases = 0 DBAs" - that adds up

Lamak

better 0 DBAs than 1 DBA that needs explaining on why not to use the text data type

jadarnel27

lol

Max Vernon

8:55 PM

lol, what gives: dba.stackexchange.com/questions/205749/…

jadarnel27

Awww, super upvote from me because it's awesome that is still a thing.

Lamak

how can you give super upvotes?

jadarnel27

@Lamak You have to shave half of your beard first.

10:06 PM

that's a very interesting watch

Evan Carroll

I like that guy a lot. Hate that putrid country's domestic policy, but you've got to give it to him for hitting the nail on the head, being well spoken, and having all of the facts on his side

Paul White

@EvanCarroll Yes I can hardly imagine President Trump making such a video in Farsi.

Evan Carroll

Farsi

Paul White

Also I know very little about their domestic policy, or local culture

@EvanCarroll Ah! Thanks, I think I might have known that after all :-/

Evan Carroll

10:22 PM

Javad is a child prodigy, he may be one of the most intelligent people working in the limelight of geopolitics

ypercubeᵀᴹ

Persian language

Persian ( or ), also known by its endonym Farsi (فارسی fārsi [fɒːɾˈsiː] ( listen)), is one of the Western Iranian languages within the Indo-Iranian branch of the Indo-European language family. It is primarily spoken in Iran, Afghanistan (officially known as Dari since 1958), and Tajikistan (officially known as Tajiki since the Soviet era), and some other regions which historically were Persianate societies and considered part of Greater Iran. It is written in the Persian alphabet, a modified variant of the Arabic script, which itself evolved from the Aramaic alphabet. The Persian language i...

Paul White

I guess there is always a risk in a making a deal with someone/a nation much stronger than you are

@ypercubeᵀᴹ Also thank you

Response by Donald Trump:

I Am Altering The Deal

►

Evan Carroll

hahahahha

Paul White

@EvanCarroll Lavrov is also impressive, from what I have seen

Evan Carroll

Yeah, his quote on the space program was hilarious too

he's pretty funny

Paul White

10:25 PM

Yes he does have a very dry sense of humour

Evan Carroll

Said when the Russia-US ties crumbled under Obama, "are you going to send your astronauts to the space station on a trampoline?"

(we had just scrapped the shuttles)

Paul White

It is a great mental image

ypercubeᵀᴹ

@EvanCarroll Little he knew, you may build a Trumpoline now! After you complete the Wall of course.

Evan Carroll

A trampoline that allows people to jump over the wall would be an awesome piece of art.

On the Mexican side.

ypercubeᵀᴹ

@EvanCarroll that would make a great cartoon!

Evan Carroll

10:29 PM

"You'll either die from gravity and concrete, a mechanized drone air force, or a racist border patrol agent. Jump at your own risk."

ypercubeᵀᴹ

El Trumpolino

Evan Carroll

Lol, yes!

I wonder if any news outlet reporting on the Iranian statement on youtube, actually linked to the statement on youtube.

CBS did

CNBC did not.

ypercubeᵀᴹ

atap.google.com/spotlight-stories/melies

Today's google doodle

Evan Carroll

Routers did not

Paul White

Reuters?

> An alliance with the powerful is never to be trusted - Phaedrus

Evan Carroll

10:36 PM

This... Whatever it is. The world is laughing at us if this is the best we can do. https://t.co/aFH4OoAO23

Tweeted by ALT_uscis on May 3, 2018 at 10:28 PM

hahahahahh

Don Blankenship

Donald Leon Blankenship (born March 14, 1950) is an American politician, former business executive, convict, and currently a candidate for the United States Senate from West Virginia. He was Chairman and CEO of the Massey Energy Company — the sixth largest coal company (by 2008 production) in the United States — from 2000 until his retirement in 2010. A federal grand jury indicted Blankenship on November 13, 2014, for conspiracy to violate mandatory federal mine safety and health standards, conspiracy to impede federal mine safety officials, making false statements to the Securities and Exchange...

Paul White

Well he's a natural in front of the camera eh

Evan Carroll

That guy was a CEO of a major energy company and has a net worth of 40 million

Paul White

Always amazes me what one can get away with saying in campaign ads in the US

Cade Roux

Yo heap!

2

Paul White

Yo Cade!

Cade Roux

10:39 PM

Paul White

That may be the first time I have ever said "yo"

Cade Roux

OK, so I got the table clustered, and indexed, even got the DBA to make me an SSRS report that shows the plan cache problems that were showing up in spBlitz so I could look at them any time (since I have no access to PROD) AND it even appears that the developers parametrized the query for the latest customer! But even the parametrized query is still showing up in the single query plan thing.

The top one now contains no literals. I don't believe force parametrization is on.

Paul White

Perhaps @sp_BlitzErik would be able to comment

Evan Carroll

This is probably better for a question?

Paul White

Most chat questions are :)

Evan Carroll

10:43 PM

Yea, or you could wait for the dude from the Cartoon Company to buzz in.

Taryn

@PaulWhite hush, now

Cade Roux

So you're saying this is good enough for a question!

Paul White

@CadeRoux We don't really have any standards

2

Evan Carroll

Our low-bar for questions is somewhere around subterranean.

Paul White

As an aspiration

Cade Roux

10:45 PM

I just prefer the collaborative back and forth - just me

I will post

Evan Carroll

Learn PostgreSQL and join IRC!

It's a blast.

Paul White

@CadeRoux I'm pretty sure Erik will answer here when he's awake

But it will still be useful to have something on main

Chat search is a bit limited

Cade Roux

It's possible that the APEX one is so new it's hardly been hit - went to production today, was surprised to even see it. But the other two airports are established DBs.

Paul White

@CadeRoux Yeah that's fine, we don't really object - just read the recent transcript it happens quite a lot

Evan Carroll

Yep, goes for me too ^ wasn't an objection. Anything that's not cats, I'm cool with.

Paul White

10:50 PM

meow

take that

@Taryn help! I'm being oppressed!

Taryn

@PaulWhite I'm far too tired to help right now, please check again tomorrow.

blame 12+ hours at my desk/computer

Paul White

huh it's not like much is going on at SO Inc. right now

Cade Roux

0

Q: Resolving sp_BlitzCache Finding: Queries with Multiple Execution Plans

So I was running sp_Blitz to get a handle on some systems. Got some code to get cleaned up as usual. Some heaps that should have been clustered indexes. etc. This particular query was using literals, seemed to be resulting in a lot of plans. Got the indexing and stuff sorted out on the tab...

sql-server sql-server-2014 sp-blitz

Taryn

@PaulWhite yeah today was busy. During the launch I had a different server go offline, so I had to fix that as well

good times

Evan Carroll

I had a question yesterday, does /jobs read from dba.se?

ie, do the unicorn points on this site (or that site) mean anything from the perspective of employers?

Paul White

10:56 PM

no idea

@Taryn Have you finished for the day now? Have a glass of wine!

Evan Carroll

My assumption is "no". Seems like the idea of splinter sites was always pretty half baked.

I couldn't find anyone talking about it and /jobs as a path doesn't work on dba.se

Paul White

@EvanCarroll You have to splinter if the core concept doesn't scale

@EvanCarroll Ask on our meta?

Evan Carroll

They could have fixed that a hundred different ways.

Cade Roux

...and I see the developer didn't change the LEFT JOIN to INNER JOIN (even though the WHERE clauses are turning it into an INNER JOIN)... lol. Still the question is probably still valid.

Paul White

Could've. Should've. Would've.

@CadeRoux Yes I'm hoping it's a BlitzCache-specific thing

Taryn

10:59 PM

@PaulWhite Just about. I'm also enjoying this. twitter.com/onlybluefeet/status/992170640722378753

Evan Carroll

Well, that saying only applies to past-perfect. We still struggle because the splintering mechanism blows and I imagine it'll get fixed sometime in the future.

Taryn

@EvanCarroll you can link your dba.se account to your dev story on SO

Paul White

@Taryn Oh good call. I haven't even looked at the tweeter yet today.

Evan Carroll

@Taryn but does it get me anything?

@Taryn Ie, if I had 20k more exp on StackOverflow, would that look better to employers wanting PostgreSQL skills then if I spent that effort here?

Taryn

@EvanCarroll shows potential employers you're active elsewhere

if you add tags you're interested in to it, that helps as well

Paul White

11:01 PM

And any employer worth working for would know that dba.se has a better class of Q & A

Taryn

you can also link to posts you're proud of on it from any SE site

@PaulWhite hi what

@PaulWhite harsh

@Taryn yep

I did that, I was just wondering if I add that tag there -- on their job site, do my accomplishments here (the exp and gold badges -- evidences of perspiration) benefit my presence there. Or would someone with a gold badge or something on StackOverflow for PostgreSQL be better off?

(because I don't have it there)

Paul White

11:02 PM

@sp_BlitzErik Cade's question dba.stackexchange.com/questions/205760/… (see also recent messages here)

Taryn

@EvanCarroll you should be able to customize it to display the things that show you as a dev the most. If that's on DBA.SE, then great. If it's on SO, then great as well.

Paul White

I did some work on the SO review queues yesterday for the first time in a long time

sp_BlitzErik

@CadeRoux was it blitz or blitzcache that reported it?

Evan Carroll

Meh, maybe I'll just sign up as an employer or something and look for myself

sp_BlitzErik

your question mentions

well, both

if it was blitz, did you follow the more info url?

BrentOzar.com/go/parameterization

Evan Carroll

11:04 PM

@PaulWhite I was thinking about gold-badging on SO too for fun. But my real contention there is that if I was to be totally selfless, I'd advocate questions get migrated here rather than answering them there.

Paul White

I understand your point of view on this.

Evan Carroll

I think with a gold badge you can dupe-migrate by yourself.

then I could bot-solve the problem. =)

Paul White

ha ha ha well that would be one approach

Evan Carroll

I think it would be pretty easy to parse the question and tagging see if it just has [postgresql], and [sql], and no comp-language-specific stuff in tags or content and flag for migration here.

Paul White

might be somewhat controversial, but hey it's been a while since the network had any drama

yeah, nah

Evan Carroll

11:08 PM

Looking forward to seeing where the site goes when he gets a yacht?

Cade Roux

@sp_BlitzErik Sorry sp_Blitz, then when I was trying to find the page describing the problem, I got the other one. I'll correct the question. I'm not sure what the query behind the SSRS report is. Let me get that from the DBA so I can make sure it's relevant. It's probably the one from

Paul White

@EvanCarroll Surely the shog owns many yachts already

I hear they pay in gold bars at SO Inc

Evan Carroll

All I got was a yo yo, and a few t-shirts and stickers. =(

sp_BlitzErik

@CadeRoux ah, okay. looks like aaron commented. seems about right to me unless you wanna chase down the plan handle and dbcc freeproccache it to speed up the process.

Paul White

@EvanCarroll Count yourself lucky, all I got was a hat

Evan Carroll

11:15 PM

Yahoo Answers prolly would have bought us new Thinkpads and steak dinners.

WE BET ON THE WRONG HORSE.

Paul White

20/20 hindsight

Cade Roux

Thanks all. Looks like trivial really is trivial. Which will be good because I am talking to the devs about SQL Server performance basics for an hour next Friday, which might not have time to get to parameterization since I have to start with making sure they understand the difference between heaps and clustered indexes.

Evan Carroll

11:57 PM

Who is learning something new?

Transcript for

The Heap™ – Consultancy ©®