The Rare, Simple SQL Wait Fix

A "power user" reported some slowness on one of our big servers today while it was being pummeled by about 60 simultaneous executions of the same script. The wait type was PAGELATCH_UP, which I vaguely recalled to mean something about allocation contention. The Microsoft Waits and Queues tuning guide confirmed that, so I checked the wait resources. They all started with 2:6 and 2:9, so it definitely had to do with tempdb.

This server is pretty beefy, a 4-socket quad-core with Xeon 7350s, but tempdb only had 8 files. I know that the rule of thumb of 1 file per core is no longer quite so hard and fast, but I figured it probably wouldn't hurt here. Created an extra 8 files, equalized the file sizes on the current ones, and had the user kick off the process again.

No waits! Or at least, no PAGELATCH_UP waits. Some SOS_SCHEDULER_YIELDs and CXPACKETs, but I took that to mean that we had successfully shifted the bottleneck off of allocations and onto CPU, where it should be.

It's rare that 5 minutes of configuration change can effect a significant gain in process speed, but it's pretty satisfying when it happens.

In SSIS, memory corruption error == bad parameter passing

Had a strange situation with an SSIS package that would occasionally produce the following error message from an Execute SQL task:

"Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

After several rounds of machine reboots, file checking, and messing with the connection manager, I finally checked the actual query being executed (I know, way to work backwards). It had a parameter being passed in that must have been hand-entered, because it was lacking the namespace, i.e.

ParamName1

instead of

User::ParamName1.

Corrected that, and everything worked fine. So the error message is thoroughly misleading, but if you investigate the query execution, you may find a bad parameter name.

More Resolver One nice-to-haves

Working on Resolver One stuff again, and came up with a few more things I'd like to see.

I've been having problems moving some of my IronPython code out of my worksheet and into an IronPython module, but I assume that's due to my own lack of understanding of the clr - the external libraries seem to get hung up on the import statements and not get any further.

Which leads me to my first request: enable an easy way to reload a workbook. At the moment, any time I make a change in an external file, I have to close the entire workbook and re-open it. I can't just recalculate and have it re-import the module, I have to close the workbook (which generally means the entire program) and re-open it. It would be nice to have a way to "Reload from disk" or something similar that does a thorough reinitialization of the environment.

The second thing is something I just posted about on the Resolver forums, so I'm hopeful that I'll come upon a solution via that line of inquiry, but basically, I want SSL support in Resolver. I was trying to connect to a gmail server and use starttls() from smtplib, but couldn't do it because of the lack of SSL support in IronPython 2.6.0, which I believe is what's under the hood in Resolver One.

I'm using Resolver for a couple projects now, so here's hoping that development continues and that I can see some of these things working in the future.

Context switching is more expensive when bandwidth is limited

This post is just made up of more idle musings and could really be shortened to a tweet, but while on the road this week with access to the office via questionable and/or slow internet connections, I've noticed that context switching, both literal and figurative, is much more expensive with low bandwidth.

This applies across all sorts of situations. I get a lot of IMs from co-workers, for example. Normally I don't notice their impact much, since I have two big monitors worth of screen real estate and I can switch back and forth between IM windows and coding or testing quickly. But with less screen space (1280 x 800 instead of 3840 x 1080), the IM windows take up more space and I have to move them around frequently, which takes up more time.

Also, with slow internet connections, actually switching between windows is sometimes difficult, as redrawing a bigger chunk of the screen causes the whole connection to freeze for as long as several minutes at a time. And as upstream bandwidth is always more limited than downstream, sometimes my typing or clicks don't get transmitted for extended periods of time. It's very frustrating, to the point where I'm planning to get a mobile internet solution of some kind very soon. The travel has also cost me quite a few mobile phone minutes, as I can't use VOIP over the sketchy internet connections.

In other remote working news, I've noticed two startups in the space mentioned recently on TechCrunch: LiquidSpace and LooseCubes. I'm hoping that one or both will help provide the co-working experience I've been seeking and not finding in DC (and potentially elsewhere).

The Perils of Laptopistan

I'm out in Laptopistan again today, this time in the principality of Starbucks, but not by choice. My power is out for the 3rd or 4th time since moving to the DC burbs, and in order to get any work done I had to get somewhere with Wifi. This Starbucks is packed, of course, with other people whose power is also out, making the internet access slow as molasses, and seating and power very difficult to come by. Fortunately, I brought a mini power strip, so I could add my laptop and Krystal's phone to an outlet without displacing anyone.

Anyway, my complaint is not so much that the situation is impossible, it's just small extra costs to this type of remote working:

- 20 mins looking for street parking in the snow = lost work time

- slow internet = lower productivity

- large latte = 4x the unit cost of coffee at home (which is better anyway, thanks to our Baratza grinder and French press)

- 20 mins spent waiting for a table = lost work time

- no power outlets available = additional capital goods required (power strip) plus the need to remember to bring them

- limited-time parking meters requiring movement of car after 4 hours = lost work time

If anyone at my office is reading this, don't worry, I'll make up the lost time, but my complaint still stands, because that cuts into my free time.

Anyway, obviously some establishments are better-suited to remote working than others; Peregrine Espresso is paradise compared to Starbucks, but it would have taken 90+ mins to get there this morning through the snow and non-working traffic lights. All of these complaints could be remedied by having access to a proper co-working space, so I suppose the best approach is to quantify these costs, find the appropriate break-even point, and seek co-working space at or below that price point.

Daily WTF: Financial Data Provider edition

A data provider that shall remain nameless (at least while I'm still employed at my current office) sends us daily CSV files. I was looking into a failure of the load of one of these files and noticed this... (values modified to protect the sanctity of the data)

[header row]

[first data row]

...

DAM, 0.2309674, 0.2309676,

POC, 0.000301957, 0.000301963,

* The [big data provider] Closing Spot Rates provided by [big data provider a] plc in conjunction with [big data provider b].The [big data provider] plc shall not be liable for any errors in or delays in providing or making available the data contained within this service or for any actions taken in reliance on the same except to the extent that the same is directly caused by its or its employees' negligence.

Thanks, [big data provider]! But next time, maybe you could shove that disclaimer someplace other than a file produced and consumed exclusively by machines, which happen to appreciate a specific format for their files, especially when said files are called, explicitly, Comma-Separated Values?

Back in Laptopistan

At Port City Java in Capitol Hill today, just up the street from Peregrine, where I was last week, and there's a fair concentration of laptop users, but certainly not the packed house that there was at Peregrine. I'm guessing that's correlated to Port City's 3 stars on Yelp vs Peregrine's 4. But the WiFi is fast and there are some comfy chairs. Judgement on coffee still forthcoming.

One of the difficulties of working remotely in a shared environment is phone calls. I had a meeting today which is often cancelled, so I was hoping that it would be again today. But it wasn't, so I had to fire up Skype (which, thankfully, worked fine here), dial up the conference line, and participate in the call, wincing every time I had to speak from fear of offending the people around me. Fortunately, there was only one other person besides my girlfriend sitting nearby, so I wasn't as concerned as I would have been, but in a tighter space like Peregrine, it would be impossible to take a phone call without bothering people. The alternative would be to go outside or sit in one's car, both of which I've done in the past, but it's below freezing outside and my car is more than a block away, making neither of those options particularly palatable.

I suppose that in a true co-working space, phone calls would be expected and unavoidable, so both my concern and others' irritation would be lower. But I have yet to identify a suitable co-working facility in DC, and it's nice to get to change scenery each time I go to a new cafe, so for now I'll just live with the minor difficulties and keep hunting for the best WiFi and coffee.