whoah. when did the whole bluefeet -> @Taryn migration occur??

@sp_BlitzErik yay managed instance.

@swasheck about 1.5 weeks ago.

Morning all

morning and evening

Thnx Paul

@ypercubeᵀᴹ Merging chats is a nightmare. The end result is about the best I can do.

No worries. We didn't have much anyway.

@Zane We do have @billnkc

morning

Only in america ...

Good afternoon

Good afternoon

@ConcernedOfTunbridgeWells for those who otherwise got no balls?

@dezso I guess it's aimed at much the same demographic that buys balls for their pickup trucks.

@ConcernedOfTunbridgeWells sounds like I don't know enough about the world (also sounds like luckily I don't)

Or ball lights for a bicycle

Truck nuts

Sadly I have actually seen these in the wild in the UK.

Is that a form of "over-compensation"?

it's been quiet lately

Sorry...?

@Lamak HELLO

wadenhadendudenda?

@JoeObbish hi

Hi.

@JoeObbish what would be the main risks when upgrading SQL Server 2008 to 2016?

@Lamak sounds like something I did the other day - went from 2012 to 2017 on a server.

@Lamak not even R2? shameful

how big is your server?

Hello :)

@Taryn oooh....and what would you say was the biggest headache?

@JoeObbish what does "big" mean here?

@hot2use hey

CPU count

24

@Lamak I surprisingly didn't have any, yet. :)

@Taryn nice

I'll take that as what will happen here too

thankfully, we only had one server running 2012

how many sockets?

@Lamak We were talking about you last night

You never visit us any more

@Lamak I have to run for a bit, but my non-expert opinion would be:

@JoeObbish don't know

2016 has the new CE

even without the new CE, all of the query optimizer fixes from TF 4199 are on by default

@TomV the transcript....yeah, I forgot to read it

your server might qualify for auto soft-NUMA, which means that you might see different performance depending on your workload

also you may want to make a decision about indirect checkpoints

ok, thanks for the expert opinion

that's from the point of view of "what's new in 2016 which is generally believed by MSFT to be a good thing, but isn't always"

no clue about the actual upgrade process

@JoeObbish pretty straightforward

results of this query? SELECT memory_node_id FROM sys.dm_os_memory_nodes

the server was already upgraded....what's missing is changing the database compatibility level

QO fixes and new CE are based on compat

don't know about the others

thanks, dude

Yay, new Jessica Jones

\o/

@sp_BlitzErik you around?

@Lamak I haven't done that yet. :|

At least for me personally, I never upgrade in-place. Create a new VM (or get a new server) and install the new SQL version on a fresh Windows install, then backup/restore databases across + copy server-level objects (logins, jobs, certs etc.). It might be less of an issue these days but I know older versions (2005 and earlier mostly) sometimes weren't entirely reliable after an in-place upgrade (blue screens, access violations, things like that)

I freely admit I could well be too paranoid and/or an old curmudgeon

@Taryn I see....

@SimonRigharts you sound like one ;)

@Lamak that will happen soon.

@SimonRigharts Unfortunately new servers cost money so it's got to be upgrade in place

@Lamak <croaky voice> Back in my day we had to walk uphill in the snow before starting a SQL server. Both ways. Barefoot.

@SimonRigharts so you had blue feet as well

@SimonRigharts with no backpack?

@Taryn good luck

@Lamak thanks, you too.

@Taryn thanks!

@Taryn Side by side instance? No possibility for a swing server at all?

@gbn We'll do secondaries first of course, and before that we'll start with dev/internal servers, but it's all in place upgrades for us

@Taryn Not moving to Linux? :-)

@Philᵀᴹ lol, no plans

@SimonRigharts aye, you 'ad it lucky, my son!

^^^ sorry, I just had to.

@Lamak HELLO!

hey

sooo, I have a quick question for you...

g'wan

can sp_blitzIndex recommend filtered indexes?

I mean, does it have that capability?

not directly, but it will look at column names like flag, archive, deleted, and some others as potential targets for filtered indexes

I'd like to thank sp_blitzindex to creating a giant red flag on one of our tables

@sp_BlitzErik ah, I see

identity with type int, last value of 1,549,036,741, seed of 1, increment of 1, and range of +/- 2,147,483,647

ooh

yeah, that's rough

yeah, that's gonna be fun

and based on my estimates we have anywhere from 70 to 250 days left

@sp_BlitzErik I was asking because one of the recommended indexes I got starts with a bit column....and found it kind of weird

but I don't know enough to actually say it is indeed weird or not

well those just come from the DMVs

@Lamak maybe

@sp_BlitzErik yeah, I read that one

then you're already smarter than me

but it has a 60/40 distribution

what kind of queries filter on it?

@sp_BlitzErik most of the queries use the value where it should get 60% of the rows

soooo....I was thinking that a filtered index would be the best thing in this case

right but are they procs with variables or hard coded?

most of the procs have that value hardcoded

okay, then filtered might be a good bet. if any of them use variables they can't use the index though.

@sp_BlitzErik ah, wasn't aware about that one

do you have a blog post about it?

brentozar.com/archive/2017/01/…

brentozar.com/archive/2013/11/filtered-indexes-and-dynamic-sql

It Wasn't Known

^^^ this

@sp_BlitzErik what kind of syntax is WHERE Reputation >= 400000 ;-)

@MaxVernon wrongendian

:-D

we switched code format plugins a while back and it's tedious going through all the old code samples to fix stuff

ahhh. and I suppose it's not stored in a database where you could search-and-replace reliably.

(you can give me a smack any time!)

wordpress = mysql = not happening

ahhh. Tell me about it. I run a wordpress blog with mysql... so much fun.

do you ever actually blog?

@sp_BlitzErik sqlserver.science

Noob

> 2017-01-25

@_@

recent

yah, those are really not so recent. I intend to blog. Does that count?

@MaxVernon I'm not judging. My last blog post was 15 years ago

You need archive.org to read them

@TomV hey, at least you still can if you feel like it!

@MaxVernon want to set up a courier imap webmail thing on Debian 3.0?

Oh the memories

@TomV sounds like so much fun! Debian 3.0... that was during the year of Linux on the desktop, wasn't it?

;-)

@MaxVernon no the year before

ahhhh. my bad.

It was the year where next year was the year of Linux on the desktop

ahhh. So, every year for the last 14 years then.

so, my answer is kinda shite. Should I nuke it from orbit and act like I never wrote it? Can you even understand the requirements from the OP? It feels like a bowl of spaghetti to me.

I mean, I think I have the answer correct, I just am not really sure.

Wtf that huge lazy spool :-o

Removing that is a good thing in itself

> I have this sentence:

nope

I spent a bunch of time on that pile'o'rubble. It could have been so much easier if the column names in his three tables were named in some kind of sane manner. Foreign keys from AggregationChildID to CodeID and AggregationID to CodeID just scream stupid to me.

anyway, moving on.... :-)

i was gonna ask why you spent that much time on a terribly unclear question

i assume it's a canadian thing

I love the misdirection. "The query is running slow." What he really meant was, "I don't know what I'm doing".

@sp_BlitzErik I'm dumb, and thought I understood it.

@MaxVernon love eh

you know it!

I would've skipped that one too

I saw that Mr White edited that question, and thought "if Paul upvoted it, it must be a good question". I assumed, incorrectly it would seem, that he was the one that upvoted it. I can only assume now that the OP used his own sock-puppet to upvote it.

I should have realized the trouble I was in when I started the answer with "I had a hard time parsing your query,"

@MaxVernon Just for formatting. Seemed a reasonable enough question on the face of it, supplied data and table defs for example.

@PaulWhite ahhh, so it was you... ;-) no worries at all. It was deceptively simple looking at first glance, and like you say it does have DDL, which is a bonus!

@Lamak a lot: Risks from high altitude sickness ;)