Press "Enter" to skip to content

Day: February 5, 2016

Bulk Loading Text Files With Line Feeds

Steve Jones runs into a scenario in which he wants to bulk load a file not in standard Windows CRLF format:

That’s not good. I suspected this was because of the format of the file, so I added a row terminator.

BULK insert MyTable
from ‘C:\SampleFiles\input.txt’
with ( ROWTERMINATOR = ‘\r’)

That didn’t help. I suspected this was because of the terminators for some reason. I also tried the newline (\n) terminator, and both, but nothing worked.

Since I was worried about formatting, I decided to look at the file. My first choice here is XVI32, and when I opened the file, I could see that only a line feed (0x0A) was used.

Little annoyances like this make me more appreciative of Integration Services (and its mess of little annoyances…).

Comments closed

Runaway Queries

Aaron Bertrand talks about “runaway” queries:

Sometimes people will start a query, wait five seconds, and then declare that this must be a runaway query. Sometimes a query’s runtime can vary based on other things going on in the system, so four seconds on one run and six seconds on another is not necessarily “running forever.” Be sure that you’ve given a query adequate time to start returning results before giving up on it, and remember that Management Studio might seem “stuck” before it starts to render any grid results, especially if the resultset is large. If you feel you’ve waited a reasonable amount of time, and you’ve tried both Results to Grid and Results to Text, then…

This is a big question and can take years of experience to get correct.  Aaron’s post is introductory-level on purpose and does a great job of answering the initial “what are some things I can try?” question after you determine that yes, there is a problem.

Comments closed

Plan Explorer Row Estimates

Joey D’Antoni shows how in SQL Sentry Plan Explorer, estimated counts can differ from what SSMS shows:

I quickly honed into the fact that the bad query was doing a lazy spool with 6 BILLION rows versus the 229 million in the good query. However, my friend who was looking at Management Studio was asking where I was getting those numbers. This is what the XML from the original plan showed:

<RelOp AvgRowSize=”51″ EstimateCPU=”0.00889634″ EstimateIO=”0.01″ EstimateRebinds=”0″ EstimateRewinds=”139581″ EstimatedExecutionMode=”Row” EstimateRows=”48868″
LogicalOp=”Lazy Spool” NodeId=”55″ Parallel=”true” PhysicalOp=”Table Spool” EstimatedTotalSubtreeCost=”1242.86″>

This is a helpful feature in scenarios like this, where operator weight is skewed because it only shows a single run but in reality happens more than once.

Comments closed

Embedded Power BI Dashboard

Rob Collie has a great example of what Power BI can do with his embedded, “real-time” (realish-time, at the very least) analytics dashboard:

Yeah, that’s a DAX-powered, Power BI dashboard, right here in our website – a website that runs on WordPress, which is Linux for crying out loud.  Don’t know what Linux is?  No worries, just translate it as “there’s zero Microsoft software behind PowerPivotPro.com, and yet – BAM!  Power BI, right here!”

And the dashboard in question is a near-real-time view of the traffic on this very site!  Check back in an hour and you will be able to “see” yourself on the map (especially easy if you use one of the “rarer” browsers.)

Check out the technical walkthrough if you’re interested in doing something similar yourself.

Comments closed