The Heap™ – Consultancy ©® - 2016-03-16 (page 2 of 2)

Max Vernon

6:02 PM

@PaulWhite classic!

swasheck

2

A: Why are people correcting my British English answers for the American spelling?

I would say that conventions like this are going to gravitate toward the majority of the user base. I don't know if SE keeps stats on this but I suspect it is mostly American english (or ESL where E is really American E). This is also why the site is in English and not Spanish or Russian or Frenc...

tl;dr; ... "not all (us) americans are donald trump"

here's what i want to know ... how did the notion of a "story" ever make it into SDLC parlance? i mean, you're a coder, not jk rowling!

Paul White

Ask on meta.

Zane

6:31 PM

0

Q: Large Variation in Bulk Insert time

So I have a simple Bulk Insert process that to take data from our staging table and move it into our datamart. The process is a simple data flow task with default settings for Rows per batch and the options are tablock and no check constraint. The table is fairly large. 587,162,986 with a data...

Paul White

Pretty.

Max Vernon

half-a-billion rows. nice.

Paul White

goes to sleep

Max Vernon

is that an American billion, or a British billion.

Zane

@MaxVernon I've had tables that size on crappier machines and could still bulk insert a million rows in a few minutes.

Lamak

6:38 PM

@MaxVernon billion or billioun

Zane

@PaulWhite It's cool my question will still be there when you wake up :)

Max Vernon

@Lamak but definitely not bouillon

Lamak

@MaxVernon definitely

swasheck

@MaxVernon you stole my comment. @Zane ... my natural proclivity is to look at the storage subsystem. is it still running like crap? can you run some perfmon counters?

Zane

They are local. Working on getting the drive details at the moment.

I don't recall the exact setup on UAT.

Max Vernon

6:47 PM

@Zane dumb question - is there any blocking?

Zane

Nope

Max Vernon

so, is there anything else going on on the server at the time of the slow bulk insert?

specifically anything that might slow down the disks

for example, are backups taking place at the same time?

Zane

no backups. This is a UAT server.

No restores.

swasheck

@Zane .... soooooooooooooooooooo can you run perfmon counters?

Max Vernon

offloaded DBCCs?

Zane

6:53 PM

I was the only one running and the only thing on this disk drive are these files.

@swasheck I can the next time I run the process.

I can't run it at the moment because my coworker is attempting to alter the partition at the moment.

That's another thing I forgot to mention. It's partitioned in a silly manor.

swasheck

partitioning = silly

Max Vernon

ahhhh the devil is in the details

Shawn Melton

Quick question on Availability Groups...I'm thinking the answer is yes but wanted to check...Can a database be added to an AG primary and be accessed via the listener before the database is fully restored to the secondary replica?

Zane

@swasheck it is but it's not like it wasn't still there when this was going fast.

@MaxVernon commented.

Max Vernon

7:10 PM

@Zane - do you have a way to see wait stats over time while you run the process?

Zane

Yeah.

I can add what they were last night if you'd like.

swasheck

@Zane @MaxVernon SQL Sentry and system_health

:)

Max Vernon

maybe helpful.

Zane

Do you want them from when they ran like shit or from when it was running well.

Max Vernon

both

I guess the interesting thing would be "what is different" comparing bad-to-good.

Zane

7:24 PM

@MaxVernon Done

swasheck

everything waits a lot more

it appears

Zane

Yeah but CXPacket doesn't really mean waiting.

@swasheck about to run it now what exactly did you want me to PERFMON on?

swasheck

7:56 PM

@Zane logical disk counters

@Zane i ignore cxpacket mostly

Zane

@swasheck Same.

Max Vernon

CXPacket waits accumulate when parallel threads are waiting to synchronize with other parallel threads. If you're seeing very high CXPACKET waits it can be an indicator that stats are out of date. Is "auto update stats" turned on?

have you tried manually updating the stats involved using WITH FULLSCAN ?

automatic updates normally do a sampled scan, which may or may not be a good thing.

Zane

8:12 PM

@MaxVernon yeah but on a 500 million row table it's not likely to happen during the week. We have maintainance on these tables but they can really only run on saturday.

Max Vernon

@Zane the source is 500 million rows? Are you truncating the target and reloading the whole thing each time?

swasheck

@Zane sorry. i was going to give you a perfmon logman script but then i got hit with my own issues. are you still running it? have you already started it?

Max Vernon

just in case it helps; here is a script to check when each stats object was last updated, and how many rows were scanned.

;WITH StatsDetails AS
(
	SELECT ObjectID = o.object_id
		, SchemaName = sch.name
		, ObjectName = o.name
		, StatsName = st.name
		, StatsID = st.stats_id
		, StatsDate = CONVERT(VARCHAR(30), dsp.last_updated, 120)
		, Rows = dsp.rows
		, RowsSampled = dsp.rows_sampled
		, ModificationCounter = dsp.modification_counter /* number of modifications to the stat's
															leading column that have been modified
															since the last stats update
															*/
		, UpdateStatement = 'UPDATE STATISTICS ' + quotename(sch.name)

Zane

@MaxVernon Destination is 500 million rows.

@swasheck It's running super fast.

My counterpart think its that she moved the more recent partition to a new file but I am leaning towards SSIS memory pressure.

Max Vernon

@Zane are new rows being appended to end of the clustered index?

ie is DOC_ID in the newly inserted data monotonically increasing?

are the source and target on the same SQL Server instance?

Pin Crash

8:31 PM

Hey @ all. We got a SQL Server question on CR recently that, while it is completely on-topic there, I figured perhaps someone here might be interested in it, as it has some aspects that I think a DBA could address better

3

Q: SQL procedure for Archiving and Deleting

I have a requirement for archiving 5 huge tables from PROD to ARCHIVE server without losing the integrity of the tables. The query makes use of the Linked Server functionality and current idea is to host it on the PROD server. It is a distributed transaction which makes use of Microsoft's Distri...

sql sql-server t-sql stored-procedure

> The query makes use of the Linked Server functionality and current idea is to host it on the PROD server. It is a distributed transaction which makes use of Microsoft's Distributed Transaction Coordinator service.

The key requirement is that the PROD server will be live all the time and the performance of the server should not be affected by this procedure being executed.

Zane

@MaxVernon No. It's the from the source.

@MaxVernon Not necessarily.

@MaxVernon Yes.

Max Vernon

@Zane so you're splitting potentially a miillion pages in the target... fun.

swasheck

@MaxVernon depends on a lot of things

Max Vernon

@Zane have you set "max server memory" to a sane amount? i.e. leaving enough for SSIS and other things going on on the server?

@swasheck agreed. Just trying to point out that potentially some inserts may be a lot slower than others.

Zane

@MaxVernon Not really. I've been saying that we don't leave enough for the OS.

Zane

8:48 PM

I think I'm going to dump this as too localized.

Max Vernon

or "Tip of the Iceberg"...

I'd leave it open for a while.

you never know who'll see it and go "oh, it's that"

I must admit there are a lot of possibilities

Zane

It's tons of things.

Right now it's running great but it could be because we have less stuff running and SSIS is being choked out(which it is). It could be that the files are all too damn big(which they are). It could be that the partitioning scheme Throws everything after 2013 into one partition.

But it's probably all of those things so I'm going forward with a few changes.

Tom V

9:33 PM

@TomV there's a fair amount of emotion conveyed in the words "perfectly correct" and "incorrect" that indicate this isnt simply a P&P question — swasheck 4 hours ago

@swasheck I know, but I tried to comfort him a bit given the fact he probably didn't expect all those trolls responding to his question :D

swasheck

@TomV comfort is for the weak

but i understand. i was just in something of a mood earlier today

Tom V

@PinCrash It looks more as a design review question than a code review question to me, not sure if that's on topic on your end of this network

Pin Crash

@TomV We can certainly review the query code provided (and I started on an answer to that regards)

swasheck

@PinCrash " i've reviewed your code. it sucks "

Pin Crash

Do you think perhaps they would benefit posting a separate question on DBA specifically addressing the non-code / design & environment concerns?

Tom V

9:41 PM

@PinCrash "the performance should not be impacted" is a hard requirement to meet if you run queries on huge tables, and I would have extra questions about how often this needs to be run and what the latency requirements are for his replica, if it were posted here

swasheck

Pin Crash

@TomV Yeah I can see that. I would've thought SQL Server would have more "appropriate" ways of transferring data in bulk between servers than linked server queries though

Tom V

@PinCrash There is also no info on table definitions and stuff to be able to make it run faster by just reviewing the code

swasheck

@PinCrash that's a whole other area not core to the functionality of a database engine, in my opinion. that's a middleware activity (informatica, ssis, pentaho, etc. )

Pin Crash

Roger that

Tom V

10:42 PM

@PinCrash your answer +1

Transcript for

The Heap™ – Consultancy ©®