The Heap™ – Consultancy ©®

Joe Obbish

00:53

batch mode hash joins seems pretty amazing from the small look that I've taken at them

I suspect that it'll be hard to go back to row mode hash joins :(

Paul White

Yes they're awesome.

Joe Obbish

had a query go from 2:30 to 0:05 after I got rid of the row mode hash join

although I suspect a few other changes that I did helped too

aware of any pitfalls?

Paul White

I'm sure there are some, but nothing really springs to mind, just general best practices for column store and query tuning in general. Small simple things work best, as usual.

It's important to be on the latest version and build.

Joe Obbish

01:17

if only we lived in a small and simple world ;)

I hear you on the patch issue... we've hit what I would describe as a large number of memory dumps in our CCI testing

but I suppose I have no way of judging that

Paul White

That's just the newness wearing off ;)

Doing unusual things is a great way to encounter bugs.

I prefer not to encounter such things (unless I'm writing about it).

Joe Obbish

we weren't doing anything weird

just a MERGE into a CCI with an OUTPUT clause that modified the same CCI

ahem

Paul White

Ha!

nikoport.com/2017/02/26/columnstore-indexes-part-99-merge

> This blog post is focused on the MERGE statement for the Columnstore Indexes, or as I call it – the worst enemy of the Columnstore Indexes.

Joe Obbish

01:33

seen that one

Paul White

Yeah you just reminded me of it

Joe Obbish

my issue was that I thought that poor UPDATE performance against CCIs was MS's fault

eventually sql_handle explained it to me in a way that finally clicked

Paul White

What was it?

Joe Obbish

if an UPDATE is a logical delete + insert then the UPDATE needs to get all of the column values for the affected rows

as opposed to rowstore which just cares about the updated column

so if you have an irrelevant VARCHAR(1000) column in your table, that's a lot of extra IO in the CCI case

Paul White

Well. That can happen on row store too, but yeah.

Joe Obbish

01:36

Oh, are you talking about page splitting and stuff along those lines?

Paul White

sqlblog.com/blogs/paul_white/archive/2010/12/14/…

Same thing fundamentally; update Spilt into a delete and insert

Though the details are different of course.

Joe Obbish

"sneaky reads"

3 words in and I already like it

Paul White

One of my favourites. Not reflected in the number of page views sadly.

Joe Obbish

"Beating the Set-Based Update with a Cursor"

........

Paul White

Yeah!

Joe Obbish

01:48

it was interesting to hear Adam Machanic talk about the "RBAR" concept

he didn't name any names but it seemed as if he was implying something

Paul White

I believe Jeff Modem invented that acronym.

Joe Obbish

yes

I can't remember why I did it

once I wrote a single update query in a cursor and got a rate of like 1 row per second

it was so terrible

maybe I was trying to test something around page splitting? or something

but that article looks very interesting. I'll need to read it fully when I'm less tired

Paul White

I like cursors, aside from the performance. They're very instructive, and quite fundamental.

I never use them of course.

Except everyone does, in a sense, when they execute a query plan.

Joe Obbish

I suspected that was coming

what if my query returns no data?

that's how against cursors I am

all of my queries return no data

Paul White

It still executes row by row (or batch by batch).

Joe Obbish

01:52

all of my queries use SELECT WITHOUT QUERY

Paul White

Oh well then you're very special :)

And #1 on the rep leader board I see.

Joe Obbish

let me tell you

it's really difficult to get some query results

Paul White

I can believe it.

Joe Obbish

which leaderboard?

Paul White

@JoeObbish stackexchange.com/leagues/78/quarter/dba

Joe Obbish

01:54

2/5 isn't bad

maybe I'll get knocked down after answering an azure question despite never using it

Paul White

@JoeObbish 2/5?

Joe Obbish

quarter and month

Paul White

Ah.

Joe Obbish

02:13

time to play around with DBCC PAGE

Joe Obbish

02:44

"Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario."

well now I really want to do it!

Paul White

1

A: Moving SQL Server instances from one datacenter to another

So the challenge here is to keep the server name, database name, and IP the same. I've done this many times in the past without any issue. I always get another SQL Server built and get everything defined over on it as is currently on the SQL Server I'm migrating from such as linked servers, cred...

It's nice when people convert comments to a good answer

Joe Obbish

03:11

four close votes on the question though

Paul White

03:28

Yep

Two upvotes, no downvotes, four close votes, and an answer in comments.

Joe Obbish

03:52

@PaulWhite Speaking of doing things the wrong way, you got a shout out in my latest answer!

0

A: Quickest way to NULL or wipe a column

First let's do a quick review of how SQL Server generates the default ordering of columns when you do a SELECT * query. There are probably a few edge cases but I believe that columns are returned in the order that they are created. If a column is dropped the ordinal ID associated with that column...

feel free to control+F

Paul White

Amateur!

Joe Obbish

everyone is an amateur on SE because no one gets paid to write answers

puts on sunglasses

Paul White

04:13

@JoeObbish As far as you know...

Paul White

04:26

Anyway, that's a horribly imprecise question.

Joe Obbish

05:04

which one?

Paul White

@JoeObbish The wiping a column one.

Joe Obbish

05:21

I actually don't see the issue. Care to elaborate?

Paul White

06:08

@JoeObbish Sorry had a phone call. Well it doesn't say what the need is, what is and isn't an option, which version, DDL etc.

So it's vague.

You'd think the last thing you'd want to do is physically update all the rows in a table.

It's like: how can I do this thing that will always be slow and resource-intensive, without it being slow or resource-intensive.

If the data needs to be hidden/obfuscated, well there are ways to do that.

That don't involve physical updates.

Hey @MikaelEriksson

Mikael Eriksson

Good morning!

Paul White

@MikaelEriksson Morning. May I ask you a quick xml question?

A friend just asked me this:

Mikael Eriksson

Sure, I'm just waiting for a build to finish. Takes about 20 minutes or so.

Paul White

Table looks like:
Col1 Col2
A   X
B   Y
C   Z
I want:
Col1Agg   Col2Agg
A,B,C    X,Y,Z
...in a single pass.

@MikaelEriksson Is there anything funky with e.g. sequences we could use to do that?

It sort of feels like it ought to be possible.

Mikael Eriksson

I remember seeing a question on SO about that. Let me try for a bit.

Paul White

06:14

Of course it's a daft requirement, but nonetheless a real one.

@MikaelEriksson Thanks, appreciated.

Mikael Eriksson

06:25

@PaulWhite I don't believe it is possible to do this with a single pass without having the entire table in XML at one point.

declare @T table
(
  Col1 char(1),
  Col2 char(1)
);

insert into @T values
('A', 'X'),
('B', 'Y'),
('C', 'Z');

declare @X xml = (
                 select ','+T.Col1 as Col1,
                        ','+T.Col2 as Col2
                 from @T as T
                 for xml path('')
                 );

select stuff(@X.query('Col1/text()').value('text()[1]', 'varchar(max)'), 1, 1, ''),
       stuff(@X.query('Col2/text()').value('text()[1]', 'varchar(max)'), 1, 1, '');

I will let you know if I think of something different.

Paul White

@MikaelEriksson Cheers!

There's CLR, variable concat of course, but it feels like there ought to be a neat xml solution without a huge number of UDXs/XML Readers. The real example has 50ish columns and hundreds/thousands of rows.

And for Azure/2017 STRING_AGG.

Mikael Eriksson

06:52

What about using a cursor?

Paul White

Well.

Mikael Eriksson

Yea, it does fail the requirement of a "neat solution" :)

BTW, thousands of rows is probably not an issue but 50 columns makes for a pretty impressive query plan.

Paul White

For certain values of "impressive" ;)

dezso

08:12

> But unfortunately because of business reasons, the column order is important

too many SELECT *?

> As far as I can tell there is known/supported way of making COL3 have a column_id of 3

@JoeObbish ^^ isn't a 'no' missing there? also, sorry for sending a zillion DOINGs

Paul White

Haven't heard anything from Master Database in a while.

dezso

@PaulWhite may be also on holiday

Paul White

True. Is there a real holiday on at the moment?

dezso

08:27

@PaulWhite I don't even know where Master Database lives

Master Database

0

Q: How to ask a question when unable to reveal table data/structure

I recently asked a question about a Full Text Search to find out if anyone had experience or tips on how to handle a wildcard search (was about one wildcard on each side of the search string, making normal indexes unusable) Someone in the comments asked me to show the structure or the table (Ex...

dezso

Please tell us if you are affiliated with the company selling that product. Read dba.stackexchange.com/help/promotion for more help. — dezso 4 mins ago

seems she/he was on call @PaulWhite

Paul White

Weird huh.

dezso

@PaulWhite you took the harsh action - did the user post similar posts elsewhere, too?

Paul White

@dezso Spam ring

Tom V

08:39

Seems like monyog is into that crap

Jack Douglas

@Philᵀᴹ OK, but there is a high bar with PL/SQL arriving on MySQL: mariadb.com/kb/en/mariadb/what-is-mariadb-103

McNets

SAP morning

0

A: How to create a database from an XML file's DTD

Use the following tools: 1) create full sql schema from DTD. DTDParse + XML::RDB 2) convert XML doc with data into SQL. http://stackoverflow.com/questions/13121456/transform-xml-using-xslt-to-sql References DTD Tools (the link no longer works) XML Matters: Using Python modules ...

0

A: xml dtd -> sql schema

Use the following tools: 1) create full sql schema from DTD. DTDParse + XML::RDB 2) convert XML doc with data into SQL. Transform XML using XSLT to SQL References DTD Tools XML Matters: Using Python modules xml2sql and dtd2sql

Is it the same answer?

Paul White

@TomV Yep.

Kenshin

Sup Dawgs

Paul White

@McNets Yes.

Tom V

08:55

@Kenshin Hi

McNets

@PaulWhite and is this behavior allowed or must he reference the SO answer as I did on my comments?

Paul White

@McNets I pointed him to dba.stackexchange.com/help/referencing

McNets

@PaulWhite Ok, thanks

Philᵀᴹ

09:23

@McNets no swearing!

@JackDouglas Crikey

Philᵀᴹ

10:15

Yawn. The morning has flown by

McNets

10:57

SAP, Dynamics, Dynamics, SAP, I'm dizzy...

Tom V

12:30

@McNets Do you have to decide all by yourself?

McNets

@TomV I'm gathering information.

Consultants of both systems, visiting some colleagues that have SAP, others Dynamics

But at least direction has accepted one solution for all companies, all departments.

Philᵀᴹ

13:10

Both need to die a horrible, very painful death

McNets

@Philᵀᴹ ;)

Matt

13:27

Is there such a thing as a Database Inventory Application? I have inherited several "sql server" servers that have poorly named instances and databases with similar names across those instances with a mix of production and test/dev data. I need to clean them up and move them around but it is hard remembering all of the db names and their associated applications, programs etc. I could just do something like an Excel spreadsheet but that sounds like a horrible idea.

I could use the extended properties of the databases to put in things like descriptions, file paths, programs and then inventory that which would satify my needs for the time being. However I started to wonder if I am reinventing the wheel and something like this already exists. Searching for "database inventory ..." is useless since every physical inventory / supply chain management software pops up.

I expect something exists that could do what I am asking for an more that might make my administration easier.

Its also nice that when I am reading on SQL Server and DB admin stuff that I keep seeing names I recognize from here. People here are real!

3

McNets

@Matt idera.com/productssolutions/sqlserver/sql-elements

Matt

@McNets Kewl beans. That is at least something to look at. Now if it was only free or open source.....

Since my budget for everything is apparently $0

McNets

getapp.com/p/sem/…

search for 'sql server inventory tools'

or better 'free sql server inventory tools'

Joe Obbish

@dezso Fixed, thanks. Feel free to edit. I can always revert if I don't like it

also no need to worry about the DOINGs

McNets

@Matt MS Tools blogs.technet.microsoft.com/mapblog/2012/11/08/…

Joe Obbish

13:49

@PaulWhite Right, but he doesn't know that. I guess my take on it is that it seems like he tried to solve the problem himself in a number of different ways. I'm willing to play along with restrictions as long as they aren't too onerous. I know that I sometimes ask questions to which the natural reaction is "Why would you ever do that?"

I can think of a reason to need to update every a column to NULL for every row, but not on a fixed schedule

Matt

@McNets This is what I needed to really get me started. Thanks again.

mikeTheLiar

16:32

In SSIS I've got a "Execute SQL Task" that's attempting to run a simple stored procedure that takes a single parameter and returns the ID of the inserted row. I'm getting the following error:

[Execute SQL Task] Error: Executing the query "exec [Foo].[Bar] ?, OUTPUT" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

From this I double checked the data types and everything appears to check out.

Any guesses? All of the other Google results that I've looked at don't appear to be related.

And this error message is.....not super helpful.

Lamak

@mikeTheLiar how did you set up the ResultSet property?

mikeTheLiar

Previous control flow tasks are executing successfully so I don't think it's a problem with the connection.

Lamak

and how are you passing parameters?

mikeTheLiar

I'm not sure I understand the question 100%. I'm just using the "Parameter Mapping" and "Result Set" panes in the options.

One sec though, I think I see something dumb.

Lamak

@mikeTheLiar yes, but what did you put there?

;)

mikeTheLiar

16:37

I think the results are incorrectly mapped, double checking. Apologies in advance if this turns out to be a rubber duck situation.

Lamak

I see

mikeTheLiar

Crap. Nope, it's still broken. Unfortunately I have to run to a meeting but thanks anyway.

Andriy M

@mikeTheLiar Where is the statement itself generated? That line has a syntax error (the comma before the OUTPUT)

More specifically, if the procedure's got just one parameter, then there should just be no comma between the ? and the OUTPUT. If there's supposed to be a second parameter and it is the output one, then another ? (directly before the OUTPUT) clearly is missing.

Joe Obbish

17:54

I got a subquery that increases a cardinality estimate from 3122870 rows to 28515200000 rows

can't escape the 10% estimate of doom

Max Vernon

18:22

Sounds like you're consistently dealing with an "interesting" schema.

Joe Obbish

18:33

that's very polite of you

part of the problem is this code needs to run as is in both SQL Server and Oracle

Forrest

what about making a view in each environment you can reference for the subquery?

Joe Obbish

top marks

that's what I told the guy to do

it's a very silly view but it should solve the problem here

depending on the query using a view can be counterproductive

there may not be a way to push down predicates far enough, for example

Forrest

do you have a rule of thumb for when using views will start causing problems?

Knowing that the accurate answer will of course be It Depends™

Zane

What do you mean by views causing problems?

Joe Obbish

18:48

sure, my rule of thumb is if you don't views then they can't cause problems

Forrest

interesting viewpoint :)

@Zane I take it to mean hindering the optimizer. There's probably a lot more to it though.

Joe Obbish

unfortunately I don't really have one. I wish I did

a write-up on my thoughts on a similar question is here: dba.stackexchange.com/questions/172208/…

Lamak

@JoeObbish you just wanted to brag

Joe Obbish

@Lamak about what

Lamak

your great answer

Joe Obbish

18:58

yes, forrest and I are in cahoots

you have uncovered the conspiracy

Lamak

@JoeObbish there should be more of that kind of conspiracies

Joe Obbish

try scrolling though my answers list

Forrest

In that case Lamak, let me look through your top-voted answers to come up with a topic

Lamak

@JoeObbish I don't want to be a serial upvoter guy

@Forrest please don't

Forrest

@Lamak Too late. I've already found your highly-rated answer on SO.

Joe Obbish

19:04

no need to vote

Lamak

@Forrest oh. no.

@JoeObbish well....if I see a good answer, then I upvote it. So, in your case, would be a lot of upvotes

Joe Obbish

logout first, problem solved

Lamak

so much work

Lamak

19:53

this is quite interesting

insights.stackoverflow.com/…

McNets

why is Postgres down under?

Lamak

it's the percentage of questions of the month that were tagged with postgresql

it's an interesting tool from SE.

I was just trying some tags

McNets

@Lamak have you ever worked with SAP HANA?

Lamak

20:10

nope

Paul White

20:45

@FMashiro How would made up data trivialize the question? It's just data. If you have a piece of data that has "TopSecretThing" in it and you replace it with "Ponies" I think code that would do a wildcard search for "Ponies" would be pretty similar to the wildcard search for "TopSecretThing." — Aaron Bertrand ♦ 8 hours ago

Ponies :)

@JoeObbish I guess I just find XY problems more frustrating than some. Anyway, he seems happy with your answer, so all's well that ends well.

McNets

May 6 at 15:23, by Paul White

There are cows!

@PaulWhite cows better than ponies? ;)

Paul White

From a farming perspective, surely yes.

2

Tom V

21:12

@McNets it's a really hard decision to ask for advice in a random chat room when you're undecided on what ERP system you should pick, or even if you should pick any over your current setup

Having worked for multiple consulting firms with different products over the years I have one opinion though

Lamak

@TomV but...but...we are not random

Tom V

The consultants on your project define success more often than the product they are implementing

McNets

@TomV I know and I'm sorry. Today I've scheduled a meeting with 2 different consultant companies. I have my own preference, but in fact I don't know none of these ERP system. And the opinion of people with more experience than me on database systems is important to me.

Tom V

No need to be sorry, you didn't do anything wrong, but there isn't a "best" system or everybody would be using it

It really depends on which solution fits your needs with the least amount of customization. And the implementation team both on the side of the consulting firm as on the customer's side (it team and key users for example)

Lamak

@TomV and the money

Tom V

21:23

@Lamak Sure but 800K or a million doesn't matter that much over a 10 year horizon

and that's what you're looking at for any off-the-shelf solution

Lamak

yeah, true

Tom V

Key is, you need a strong team on both sides of the consulting/customer side

And the team includes the business folks, not only the IT department

McNets

That's is the point, I'm refusing any consultant without a good FDA certification experience. And by good I mean "demonstrable".

Tom V

Each and every project I've seen attempting the "consultants should just fix it for us, and IT will do the technical stuff" has failed

Consultants (hopefully) know their product, your key users (hopefully) know your business

one of both parties fails and the project fails

These types of projects are a team thing, where the team has to come from both the vendor and the customer

McNets

And the boss

Tom V

21:36

The boss should just approve the budgets

or not

but not get into the details too much

Philᵀᴹ

@McNets TBH, you need to hire me

McNets

I think that he must be totally convinced to implement one of this system, if not, it's a fight versus final users.

@Philᵀᴹ don't rule it out ;)

Philᵀᴹ

Decide on which system to go with based on the business requirements, not the current technical skillset.

The skillset can be sorted, the business fit is harder

If you think otherwise, you're naive

McNets

Unfortunately we have 3 companies, and 3 different business rules.

Transcript for

The Heap™ – Consultancy ©®