So you've got a problem, and you want to solve it using SQL_VARIANT

Now you've got a bigger problem, because using SQL_VARIANT for anything is a terrible idea. It's a memory and storage hog, it generally can't be indexed, it produces inconsistent and unpredictable behavior in code, and it's not supported by most client libraries. Don't use it.

Of course, you may not have a choice, if some esteemed predecessor has implemented database structures with it. In that case, you should definitely read up on it, but I just wanted to throw out one particularly useful function that I encountered yesterday while tearing my hair out trying to solve a problem with our resident SQL_VARIANT infection.

CREATE TABLE dbo.tbFoo (

ID INT IDENTITY(1,1),

bar SQL_VARIANT)

That function would be SQL_VARIANT_PROPERTY(). The primary use of this function is to get the BaseType of the SQL_VARIANT column or variable, as in:

SELECT SQL_VARIANT_PROPERTY(bar, 'BaseType') FROM dbo.tbFoo

This will let you know whether your variable or column is actually being used as an int, varchar, nvarchar, etc. There are other properties that can be checked as well using this function; essentially anything that sp_help would usually give you.

Knowing the base type can be enormously helpful. For example, when you encounter code that joins the table to itself to compare SQL_VARIANT values directly...

SELECT * FROM dbo.tbFoo f1

INNER JOIN dbo.tbFoo f2

ON f1.bar = f2.bar

AND f1.ID < f2.ID

Now say you have dozens of processes inserting into this table, and they're not particularly standardized. Say one of them inserts non-unicode strings (VARCHAR) while another inserts unicode strings (NVARCHAR).

INSERT INTO dbo.tbFoo SELECT CAST('A1B2C3' as varchar)

INSERT INTO dbo.tbFoo SELECT CAST('A1B2C3' as nvarchar)

If you had CAST them in your query, they would be comparable, but without the cast, they may or may not be, and you might not get the row back that you expected.

The best fix is to avoid writing this code, and be sure to CAST your SQL_VARIANTs into the type you're expecting to use, but if you need to restore functionality to this type of code, you can update your SQL_VARIANT column base type.

UPDATE dbo.tbFoo SET bar = CAST(bar as NVARCHAR) WHERE SQL_VARIANT_PROPERTY(bar, 'BaseType') = 'varchar'

All this may or may not help you with your SQL_VARIANT problems, as there's plenty of unexplored territory even within the issues I've touched upon here. But it would have helped me yesterday. Good luck.

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?