NYC DBA

Refreshing Intellisense in SQL Management Studio 2008

2012-01-19T07:31:00.000-08:00

If you're like me, you create and drop a lot of tables while developing, and you may be getting annoyed with SQL Server Management Studio because it fills in the names of old tables while you're typing unless you hit Escape. Turns out that refreshing the Intellisense cache is as easy as

Ctrl-Shift-R

If your DATE comes through pyodbc as a unicode, it's probably the driver

2012-01-18T10:12:00.001-08:00

File this under "should have been obvious." I had a test script break today because I was trying to do date math on an object that wound up being a unicode instead of a date. I had gotten the variable value from a pyodbc query against a SQL 2008 database table with a DATE column. I knew that DATETIME columns came in as datetime.datetime objects, but the DATE column came in as a unicode, which seemed strange.

Turns out it's the ODBC driver. Demo:

In [78]: cnxn = pyodbc.connect("DSN=local;")

In [79]: crsr = cnxn.execute("select cast('2012-01-01' as date) as bar")

In [80]: r = crsr.fetchone()

In [81]: type(r.bar)
Out[81]: unicode

Then, when switching to SQLNCLI10:

In [96]: cnxn = pyodbc.connect("Driver={SQL Server Native Client 10.0};Server=localhost;Trusted_Connection=Yes;")

In [97]: crsr = cnxn.execute("select cast('2012-01-01' as date) as bar")

In [98]: r = crsr.fetchone()

In [99]: type(r.bar)
Out[99]: datetime.date

Faster, thinner, lighter

2012-01-17T13:04:00.001-08:00

It's almost time to retire my trusty old Dell E4300. The warranty just expired, which means that if Dell has optimized their average component MTBF vs warranty span correctly, the laptop will implode shortly. I replaced the original hard drive with a 2nd-gen Sandforce SSD a year ago, and it's made a big difference, but I'm ready for a little more speed. And, with the advent of the "Ultrabook" form factor, for even less weight and size. I've decided I want one.

At CES this year, there were plenty of ultrabooks on display - Engadget's roundup has a few, more on Anandtech). And most of them seemed to get at least some part of the formula right, but I don't think any of them have quite grabbed me yet. Maybe I'm being picky, but I've decided that my top priorities are:

- 1600 x 900 resolution

- Thunderbolt port (for docking)

- decent battery life (5 hrs+)

- light weight

I'm sick of my current 1280 x 800 screen - the drop in my productivity from 2 big 1920 x 1080 screens down to the tiny laptop screen is painfully apparent. 1366 x 768 is arguably worse, since I really need those vertical lines of resolution. And although I move around a lot with a laptop, which is why I want a good battery and light weight, I also use it as my primary workstation, so I need to be able to dock it and use my big monitors. In fact, a laptop that would support 3 screens instead of 2 like my current Dell would be preferable. Few of the ultrabooks seem to have this capability, but at least a Thunderbolt port would make it theoretically possible.

Other requirements are pretty normal. The number of USB ports matters, but not hugely, since most peripherals I use at home while connected to a dock. I do use my SD card reader pretty often, so it would be nice to keep that. A decent keyboard, preferably backlit, would be useful.

Requirements in mind, I took a look at the current crop of ultrabooks just demoed at CES, and... was totally disappointed. Nothing had all the features I wanted. The closest ones were:

Samsung Series 9

- no Thunderbolt but both HDMI and DisplayPort connections

- 1600 x 900 resolution (on what I hear is a great screen)

- light weight (2.5 lbs)

Sony Vaio Z

- Thunderbolt, sorta, via a proprietary connector as usual (thanks a lot, Sony)

- 1600 x 900

- light weight

- ungodly price ($1900+)

Here's my spreadsheet comparing the current crop of ultrabooks and their cousins using my completely biased and proprietary scoring system. It's a definite work in progress and the scoring may change without warning. If something comes along that scores above a 3, I'll probably buy it, but right now, none of these are worth the money.

More fun with SSIS type conversions

2012-01-04T23:00:00.001-08:00

Just finished painfully debugging a strange SSIS issue about which I could find no documentation anywhere on the web, so I'm noting it here.

I had changed a type within a large unwieldy data flow from DT_R4 (float) to DT_R8 (double), since the smaller data type was causing some weirdness around the least significant figures on some option prices. This seemed to work in testing, but I got a cryptic error about a conditional operation failing during a derived column transformation when I deployed it to Production. I opened the package, added an error redirection for the component and a data viewer, and sure enough, it was failing on a transformation that utilized some of the columns I had modified.

I couldn't find any reason why the conditional would fail, since it was faithfully spitting out BOOLs, but the resulting transformation split the float into Integer and Decimal by casting the value into a WSTR, 20 and then finding the decimal point. After trying a dozen other things, I tried adjusting the size of the WSTR cast to a WSTR, 40. That worked.

Note that the values that were failing would not even come close to 40 characters (ex: 110.68), but apparently the possibility of running into an overflow breaks something in the SSIS runtime.

So, a word to the wise: if casting from float (DT_R4 or DT_R8) to Unicode string (WSTR), make sure you leave enough room to cast any possible value.

pyodbc isn't playing well with multiprocessing

2011-12-22T07:28:00.000-08:00

Wrote my first Python script yesterday that incorporates the multiprocessing module in order to parallelize some CPU-intensive calculations that were going extremely slowly when run in the database. Mission accomplished there, and it was remarkably easy in Python, but then I hit a brick wall when trying to load everything back to the database.

As it turns out, the calculation time is dominated by the database IO (~25 mil rows in and out), so I went about trying to optimize that as best I could. The reads are coming off an SSD, so they're fine, but the writes are UPDATEs, and I didn't want to issue them one at a time and incur all the connection and transaction overhead for each one. Instead, I created a staging table as a heap and batched up INSERTs of my data in a specified batch size (1000 to start) using SQL 2008 row constructors. Then the main table gets updated from the staging table right at the end, so only one giant transaction and UPDATE statement is necessary.

This strategy appeared to work fine when single-threading, but then I figured I'd throw in multiprocessing there, too, since I already had the module imported and had crafted the functions around it. Didn't work.

The error message was 42S02, aka Invalid object specified - in other words, the INSERT can't find the staging table, even though I'm creating it within the Python script, during a single-threaded section before I Pool.map() out different calculated shards to be inserted. SQL Server can obviously accept just about as many threads as you want to throw at it, so that's not the problem. The table exists, which I confirmed by adding a breakpoint (well, a set_trace()) and looking at it through Management Studio. It's all using integrated authentication, and I'm a sysadmin on the server, so there can't be any problems with schema, although I threw in some "dbo." references just to make sure. I even tried to trace the SQLNCLI driver operations, but haven't managed to get usable output yet.

So the only thing I can come up with so far is an incompatibility between pyodbc and multiprocessing. If I get more time to investigate, I'll update the post, but for now I'm just going to have to switch the DB section back to single-threaded. Boo.

SQL could use some real string manipulation

2011-11-30T07:00:00.000-08:00

In order to remove the first "-" from a string, this is the query I just had to run:

update tbFoo set BBG_ticker = substring(BBG_ticker, 1, charindex('-', BBG_ticker) - 1) + ' ' + substring(BBG_ticker, charindex('-', BBG_ticker)+1, LEN(BBG_ticker) - charindex('-', BBG_ticker))

Does that seem like overkill to anyone else? Let's compare it to Python...

BBG_ticker.replace("-"," ",1)

Meeting new people in Laptopistan

2011-10-22T09:20:00.000-07:00

This morning I'm back at Starbucks, as we haven't yet moved closer in to DC where there are alternatives to corporate coffee world, and I made the acquaintance of a woman who

1) made me explain the meaning of my t-shirt

2) asked to use my laptop (instead of hers, for some reason?) to check her email

3) inquired about what I do for a living

I'm pretty sure the next step will be to introduce me to her daughter/niece/granddaughter who's been looking for a nice Jewish boy (which I'm not, but I'm sure it wouldn't matter).

Well, I suppose it's more interesting than doing R regressions while sitting at home.

Life as a contractor

2011-10-14T06:43:00.001-07:00

It's my own fault that I'm a contractor and not a full employee - a year ago, my girlfriend decided to go back to school in another state, and I followed her there. My office was gracious (desperate?) enough to keep me on as a remote employee, but I was converted to contractor status, for both employment flexibility (i.e. they can let me go easily if it doesn't work out) and tax liability purposes.

For the most part, this has worked out fine. Working from home can be a double-edged sword, but the non-existent commute is great and the ability to work from alternate locations seamlessly has been pretty nice - this summer we picked up and moved to Denver for 10 weeks with no interruption of my work, only a slight shift in working hours due to the time zone change.

But there are some definite drawbacks. My taxes are far more complicated now, I don't get employer-sponsored health insurance, and there's a bit of a disconnect from the rest of my team and the firm at large. This is both practical - I have difficulty hearing what's going on in many of my meetings, since I'm the only one on the phone instead of there in person - and psychological.

The latest case in point, and the catalyst for this post, occurred this morning. The chairman of my firm sent out an email announcing a minor contest sort of thing in which employees can win an electronic gizmo that shall remain nameless in exchange for updating their employee profile. I dutifully went to update mine, which I hadn't looked at since I was a Full-Time Employee... and found that I no longer have one. This is far from a big deal, but it's a reminder of the fact that although I work like an employee (I'm on a fixed rate, not an hourly one), consider myself part of the firm, and try to act in its best interest, there's a psychic distance between me and the Real Employees.

There are more examples. The firm celebrated its 20th birthday a short time ago, and Employees all received small gift bags. It should probably go without saying that I did not. On one's 5th anniversary as an Employee, one receives a very small token of acknowledgement of service to the firm. My 5th year is coming up, but I presume it will be sans token.

I assume this would be different at firms in which contracting is more widespread - contractors would be held either closer or farther away - but my situation is unusual at my office, so it's not worth HR's time to hold my hand through any weird episodes. Also, these are minor enough issues that I would feel ridiculous complaining about them in person, so I'm using this medium to work through them a little.

Anyway, I know that my blog is occasionally perused by people from my office, so let me reiterate that this is not a big deal and not intended to be a passive-aggressive complaint, it's just some musings on this strange state of employment that I, and a growing number of others, find myself in.

Loading Fixed Width files with BCP / Bulk Insert

2011-10-05T06:16:00.000-07:00

I had to craft some format files in order to load a couple of fixed width files using the SQL Server BULK INSERT / bcp tools, and had some issues with the documentation when trying to get them to work.

So let me state this explicitly: when attempting to load a file with no line breaks, you do not need a terminator for any field in your format file. Just specify all the field lengths, set the xsi:type to CharFixed, and the whole thing should stream in. Ex:

<record>
<field id="1" type="CharFixed" length="12" />
<field id="2" type="CharFixed" length="3" />
<field id="3" type="CharFixed" length="5" />
</record>
<row>
<column source="1" name="series" type="SQLCHAR" />
<column source="2" name="pool" type="SQLCHAR" />
<column source="3" name="deal" type="SQLCHAR" />
</row>

You can still mix and match - have a terminator for the last field in each line, for example - but if your file has 0 line breaks, you don't need it.

Also, the easiest way I found to handle skipping columns was to use OPENROWSET(BULK). Just make sure you're selecting the names of the columns from the ROW section of the format file, not the RECORD section.

i.e.

SELECT series, deal FROM
OPENROWSET(BULK 'source.txt',
FORMATFILE='sample.xml'
) as x;

The documentation covers that part a bit better, but just wanted to reproduce it for my own sake, since I'm sure I'll forget how I did it between now and the next time I use BULK INSERT a few years from now.

P.S. Gotta throw in a plug for tohtml.com here for making my code actually paste into Blogger and look decent to boot.

Laptopistan upgrade

2011-10-05T06:08:00.000-07:00

3G vs 4G tethering: night and day. I can now click on things through my remote desktop session and have them actually respond, instead of doing the mental 2-count (or switching to Google Reader while waiting, which is always a productivity killer).

I have some concerns about the battery life of my new HTC Thunderbolt, and I'm not blown away by the form factor, but so far I definitely prefer it to my iPhone 3GS. And the 4G factor alone makes it totally worthwhile.

SSIS, MSDTC, and SQL Server clusters

2011-08-23T09:40:00.000-07:00

Note to self: when receiving the following error from an SSIS package, the first thing to check is the Authentication setting for MSDTC.

Description: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted".

The security setting should be set to No Authentication Required. Mutual does not appear to work with clusters. This is especially significant if, like me, you have a non-clustered instance in QA and a clustered one in Prod and you have been beating your head against the wall trying to figure out why MSDTC, with all the same settings, works in the former but not the latter.

The Query Did Not Run

2011-07-01T15:31:00.000-07:00

Why thank you, Excel, that's a very helpful error message. Except I was watching the query execute on SQL Server, and it DOES run. Perhaps there could be a different problem?

The actual, exact text of the error is

The query did not run, or the database table could not be opened.

The sheet in question was running a stored procedure, which had been working fine, albeit very slowly. A coworker tuned the proc and happened to use a temp table to accumulate the results for multiple queries instead of a UNION, producing better execution plans for each. However, despite being 4x faster, the proc stopped running from Excel, throwing the error message above.

At first I thought there might be a problem with the final DROP #table statement in the proc, so I removed that. No help. I adjusted various properties of the database connection from Excel. No change. I fiddled with the table setup in Excel. Nothing.

Then I remembered that I had some difficulties with pyodbc a while back that were resolved by setting NOCOUNT ON in the query. I can't recall the exact cause, but it's something about the driver seeing the first count returned as the query result, when of course it's not in a proc that populates a table with a few queries and then selects from it as the final step. Anyway, I set NOCOUNT ON at the beginning of the proc and OFF at the end and voila, it worked. So try that if you're having difficulties with Excel not executing your stored proc correctly.

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

2011-06-21T11:14:00.000-07:00

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

2011-05-11T14:27:00.000-07:00

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

2011-04-01T06:54:00.000-07:00

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

2011-03-16T14:48:00.001-07:00

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

2011-03-11T13:01:00.000-08:00

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

2011-01-27T08:42:00.000-08:00

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

2011-01-05T08:45:00.000-08:00

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

2010-12-17T10:31:00.000-08:00

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.

More notes on Resolver One

2010-12-16T13:10:00.000-08:00

Still generally liking Resolver One, but there are some limitations that are bothering me and associated requests I'd like to make.

1) Sorting!

How is there no sorting in place yet? They implemented CHITEST before sorting-in-place!?

2) Assignment

Some more "magic" assignment methods would be welcome. Something like

myCellRange = myListOfLists # aka 2D array

myRow = myList

That seems like a straightforward thing to implement, but instead I have to iterate over both and assign each value manually.

3) Row and Column Numbers

It would be very helpful if the code editor had a display with row and column numbers at the bottom.

Notes from Laptopistan

2010-12-10T14:27:00.000-08:00

Credit to David Sax of the NYT for the name. Trying the working mode advocated in the article and occupying a spot in a cafe once a week or so. Clearly it requires some technique, however...

This afternoon, I worked out of Peregrine Espresso in Capitol Hill. It's a nice place, with good tea and coffee, so I guess I shouldn't have been surprised that it would be full of laptoppers. Every seat was full when I walked in. Fortunately, one person was packing up, so I got the table he vacated, but then gave it up to some people who were trying to have a conversation in favor of a seat at a bar. But after about an hour my back ached, and I didn't like fighting for elbow space with the people on either side. Plus, it's distracting to have two bright laptop screens on either side of you - I kept glancing over at each without meaning to, and alternated between feeling superior and jealous when noting that they were not doing any actual work.

Additional gear for the cafe survival kit for next time: headphones and an extension cord.

Resolver One CellRange oddities

2010-12-02T13:55:00.001-08:00

I've started working on a project in which we're using Resolver One as a dashboard. The workbook consumes multiple data sources (services, DB, YAML files, etc.), collates and formats, and may eventually do other cool stuff like kick off simulations or generate charts. But we're still in the early stages, so I'm just fleshing out the configuration routines and table displays.

What I've found so far is that Resolver One is a cool package, but seems to have some weak points. The documentation is okay, but the layout seems less than intuitive, and the volume of documentation is somewhat lacking in comparison to The Enterprise Spreadsheet app. That's to be expected, so it doesn't bother me much.

The thing that does bother me is an apparent code deficiency in combination with my inability to log into their support forums to ask questions. I assume the forum issue is temporary and will be resolved (ha) shortly, but the code issue seems like a bug.

Basically, my problem is that CellRanges don't act Pythonically, or even correctly, it would seem. For one, you can't do this:

cellrange.HeaderRow = worksheet.HeaderRow

Why not? That seems like the most logical (and Pythonic) operation you could perform. I actually couldn't set the HeaderRow through any means, no matter what sort of value I passed in.

I had a second issue, but I just determined that it was user error and fixed it. Will continue this post if I come across something else...

Connection Manager Attributes Changed error in SSIS can be resolved by cleaning up your query

2010-10-18T12:22:00.000-07:00

Just resolved a funny issue that was making me crazy for about 15 minutes. In the "Use results of an SQL query" window in the SQL Query a Lookup task in SSIS 2005, I pasted in a query I had written in SQL Management Studio 2008 R2. The formatting looked a little funny, with the little squares in place of some line breaks and tabs all over the place, but I ignored it and clicked through to the Columns tab. At the bottom of the tab, where errors and warnings appear, I got this:

Connection Manager attributes have changed. The mappings of previous columns are no longer valid.

The message stayed there even after I changed the OLE DB connection manager, added and removed columns to the lookup, and clicked every available button in the Lookup Transformation Editor. And it prevented me from clicking OK to finalize and save the transformation. I decided that I should look through the code for any bad syntax or other anomalies, but it was difficult with the screwy formatting, so I "washed" it by cutting and pasting it into Notepad++ and then back into SSIS.

And guess what? Not only did the code look better, the warning message went away. So if you're seeing that warning, try cleaning up the formatting of your SQL query.

Python scratchpad entry

2010-10-12T07:00:00.000-07:00

Simplest syntax I've found for converting dates to datetimes, given a date d:

datetime.combine(d, time())

I've got a couple other blog entries in the pipeline, but I've had blog apathy lately and haven't finished any of them. Hopefully soon.

FILLFACTOR is really not that complicated

2010-03-10T07:21:00.000-08:00

I keep coming across code that explicitly sets the FILLFACTOR option on an index for no good reason. FILLFACTOR is not complicated - it leaves some percentage of index pages empty in order to prevent page splits and resulting fragmentation and low page density during inserts into the middle of a key range. It can be a useful option iff:

- the table will actually get rows inserted into it!

- there is no monotonically increasing index key (like a primary key identity column)

- the author is intimately familiar with the usage pattern of the table and knows how to balance the additional I/O required to read the extra pages with the I/O saved by preventing page splits

Otherwise you're just taking an automatic IO performance hit of n% (where n is 100-(FILLFACTOR)) for no good reason.

Particularly ridiculous is the code that I've found which does something like this:

CREATE TABLE #tempstuff (foo int, bar varchar(20))

INSERT INTO #tempstuff SELECT foo, bar FROM dbo.sometable

CREATE INDEX ix_reallyNotUseful ON #tempstuff (foo, bar) WITH FILLFACTOR = 90

First off, make the index clustered. If this table will not see any more inserts, there's no reason to leave it as a heap and then have to create surrogate keys in order to build the non-clustered index. Might as well physically order it by the index keys and gain the additional performance of the clustered index.

Second, the table is not getting any more inserts after this. Why would you care about leaving extra space on the pages? Set the FILLFACTOR to 100!

This has been a public service rant from your irritable neighbor database engineer.

Nested CTEs

2010-01-19T10:52:00.000-08:00

Had my first need for nested CTEs today - for a table with prices by object and another with adjustment factors by object, I needed to multiply the prices by all factors occurring after the price date. This could be done in the C++ code I was working on via an ugly for loop for each price date, but I knew there had to be a better, set-based way within SQL.

The answer was nested Common Table Expressions (CTEs), which Microsoft officially says are not supported, but which can actually be implemented by defining multiple CTEs (with a comma between each) and using the first one in the second.

For this application, the first CTE orders the object ids and adjustment factors and adds row numbers. An ORDER BY would have sufficed to order everything, but in order to avoid inefficient subqueries in the next CTE, I needed consistent row numbering. The second CTE aggregates the adjustment factors for each date by recursing down the ordered factors for each object and multiplying them as it goes.

This solution condenses all the selection logic into one set of SQL statements, is compact, fairly readable, and has a better complexity profile than the for loop alternative.

(Table and column names changed to protect confidentiality.)

WITH AdjFactorsOrdered (ObjectId, AdjDate, AdjFactor, ord) AS

(SELECT ObjectId, AdjDate, AdjFactor, ROW_NUMBER() OVER (PARTITION BY ObjectId ORDER BY AdjDate desc)

FROM vwAdjFactors

WHERE AdjDate > '2009-01-01'

AND AdjDate <= '2010-01-01'

AND ISNULL(AdjFactor, 1) != 1

AND SubTypeId != 1

AdjFactorsCombined (ObjectId, AdjDate, AdjFactor, ord) AS

(SELECT ObjectId, AdjDate, AdjFactor, ord

FROM AdjFactorsOrdered

WHERE ord = 1

UNION ALL

SELECT mul.ObjectId, mul.AdjDate, base.AdjFactor * mul.AdjFactor as AdjFactor, mul.ord

FROM AdjFactorsCombined base

INNER JOIN AdjFactorsOrdered mul

ON mul.ord = base.ord + 1

AND mul.ObjectId = base.ObjectId

)

SELECT ObjectId, AdjDate, AdjFactor

FROM AdjFactorsCombined

ORDER BY ObjectId, AdjDate

Daily WTF: SSIS edition

2010-01-14T12:11:00.000-08:00

With apologies to Alex P of the real Daily WTF, here's my latest legacy code annoyance:

We have a variety of data imports that employ multiple SSIS packages in order to break the import logic into manageable, consistent stages. So far, so good. Except that a few particularly intrepid former team members of mine preferred to construct parent packages that called child packages, complete with variable passing to maintain state awareness, directory location consistency, dynamic environment configuration, etc. instead of just calling them from the job management system in successive tasks.

Oh, and none of these packages are in source control, they're just scattered about the file system like leaves in an ill-kept yard.

All of this leads me to my current exercise in SSIS surgery - I needed to change the behavior of one of these packages, so I decided to update it to our modern standards of SSIS behavior and move it to a new location. This one has a final step that calls the next package, a process I wasn't quite prepared to rework, so I wanted to simply update the directory in which it expects to find the next package. "Hopefully it does it on the fly," I thought, "since all the related packages are kept in the same directory and it could just pull the working directory at runtime..."

No such luck.

Instead, the package uses a variable to hold the path of the next package, which is configured via an expression that pulls from another variable, which pulls from another variable, which pulls from a package configuration, which pulls from a registry string. Which I don't have on my machine.

Got that?

Registry Key -> Package Configuration -> Variable A -> Variable B -> Variable C -> Script Task

I've got news for you, former developer: it's called IO.Directory.GetCurrentDirectory().

Off Topic: In-Ear Monitors (aka earbuds)

2009-12-29T10:39:00.001-08:00

Just received my latest pair of serious headphones, a set of Ultimate Ears Triple.Fi 10vis, and couldn't resist posting some thoughts.

Some background: as it says in my profile, I used to work in the live and recorded sound biz, and I'm moderately obsessed with good sound. My go-to headphones are still my Sony MDR-7506s, but the full list has included...

AKG K240
Etymotic ER6i
Etymotic ER-4P
Sennheiser CX300
Sennheiser CX400
Sennheiser HD650
Sennheiser HD280
Sony MDR-7506
Sony MDR-V600
Sony MDR-V700
UE Triple.Fi 10vi

along with plenty of cheaper junk for travel, gym, etc. I use the AKGs with a Behringer UCA202 at work, the Sony MDR-7506s when listening at home or DJing, and I have been happily using the Etymotic ER4s with my iPhone 3GS, but when the UEs came up in Amazon's Gold Box for $99, I couldn't resist and picked up a pair.

I've been A/Bing the UEs and Etymotics for the last hour, and I have to say, despite the usual price difference (the UEs usually retail for about 2x as much), the comparison is not cut and dried. My first impressions are as follows, with the "winner" in each area in bold in case you don't want to read the whole thing.

Lows

The biggest known weakness of the Etymotics is the bass response, even when the flanges are shoved all the way down your ear canal and are tickling your temporal lobe, and the UEs are the clear winner here. The thump and throb of the bass on NIN's Gave Up and Zero Sum was much tighter, clearer, and more extended on the UEs, while the Etymotics gave up on clarity as the frequencies descended toward 20 Hz.

Highs

On the other end of the spectrum, the Etymotics redeemed themselves. The opening chords of Band of Horses' The Funeral gave me goosebumps, and the NIN tracks generally had more sizzle on the cymbals and more edge to the guitars and synths. The UEs sound a bit darker, despite the better frequency separation lent by the multiple drivers.

Clarity

Still, the effectiveness of the UEs' multiple drivers can be heard in the clarity of the sound. Despite the compression of the some of the MP3s I have on my iPhone, the UEs carved out more of a pocket for each instrument and frequency range. I'm sure this difference between the headphones would be more pronounced with higher-quality amplification, but I could hear, for example, the muddying effect of heavy bass on the mids in the Etymotics, but not in the UEs.

Soundstage

Somehow, even put up against the multiple drivers, the Etymotics produced a wider, more defined soundstage. Special effects like dynamic panning were more pronounced, and subtle placement of instruments within the field was clearer, especially on quieter tracks with more headroom/less compression. The better high frequency response on the Etymotics may have something to do with this perception.

Other

Because I use these headphones in the real world - on airplanes, buses, NYC subways, etc., there are some ancillary considerations that have a real effect on my headphone preference. Despite the fact that the UEs are billed as noise-isolating, the Etymotics were clearly superior at blocking outside noise, even in the relatively quiet environment of my office. The cord is a bit longer and heavier on the Etymotics, but lacks the built-in microphone of the UEs. The earbud flanges on the Etymotics take quite a bit of getting used to, whereas the UEs, while they don't fit as snugly, come with 4 different sets of tips and are immediately comfortable and feel easier to wear for long periods.

However, with the sound quality a near wash between the two, the main reason I'm switching from the Etymotics to the Ultimate Ears is the filters. The Etymotics have dirt/debris filters that fit inside the driver enclosure behind the flanged tips and must be changed every time they get clogged. They are tiny, difficult to replace, and not tremendously cheap - $15 gets you 3 sets of filters, and I have to change mine every few months at most. I consider the filter system to be the Achilles heel of the otherwise-excellent Etymotics, and I'm hoping for substantially less maintenance with the UEs. We'll see how it goes.

Notification Services doesn't like version mismatches

2009-10-21T13:58:00.000-07:00

I was pinged today to check out a failed installation of Notification Services with a vendor app. The vendor consultant was telling us that there was a mismatch in the version of SQL Tools installed, which sounded odd to me, since generally the tools are consistent between versions and the engines are different.

I tried to start up the Notification Services instance and got a 1359 error:

---------------------------

Services

---------------------------

Could not start the NSxxxx service on Local Computer.

Error 1359: An internal error occurred.

---------------------------

Inspecting the error log, I found this:

Description:

The Notification Services instance encountered an error in one of its components and must stop.

EventParameters:

Instance Name: xxxxx

Problem Description: The database was created with or upgraded to a different edition of Notification Services. Use the Notification Services edition that the database expects.

Notification Services Edition: Standard Edition

Database Edition: Enterprise Edition

So it turns out that the consultant was partially correct - Notification Services won't start if the version is mismatched between the engine installation and its corresponding SQL Server instance. Good to know.

Regex to remove end-of-line white space

2009-09-17T08:08:00.000-07:00

Utilizing the blog as my public scratchpad (as usual), here's a regex for removing whitespace from the end of all lines. Useful when pulling up the text of a SQL object with sp_helptext and then creating it again.

Replace: [ \t]+\n

With: \n

Conflicting BULK INSERTs

2009-06-03T13:09:00.000-07:00

Let me start by saying that this is not a post about concurrent BULK INSERTs into a single table or SELECTs during a BULK INSERT, both of which the SQL CAT has covered pretty effectively, along with many other questions about bulk data loading. My problem is with concurrent BULK INSERTs into different tables on the same database.

Basically, there are two different data sets, one large and one small, that get BULK INSERTed into two different tables in the same DB on different schedules. Like Alan's bicycle in Cryptonomicon, every so often the two schedules will align and the chain will fall off, or more accurately, the big BULK INSERT will hold up the small BULK INSERT. There doesn't seem to be a reason for the SQL engine to block one with the other; they're not hitting the same table, so locking should not be a problem, and the machine in question is a dual quad-core, so SOS_SCHEDULER_YIELDs are not prevalent.

OK, if it's not the SQL engine, it's probably a shared resource issue. Sure enough, an inspection of the wait types during a concurrent execution of these inserts revealed a lot of PAGEIOLATCH_SH, indicating a disk problem. But this database is in Simple recovery mode, so it shouldn't be doing a lot of logging on a bulk-logged operation like BULK INSERT. We should just be writing dirty pages to memory and then checkpointing at our leisure, right? Why are the disks getting hit so hard? Unless we don't have enough memory to write those dirty pages...

Which a perfmon counter log revealed to be precisely the case. The box only has 3.5 GB of RAM, has a number of other applications running, and shows high levels of paging and low Page Life Expectancy. The lack of memory available to SQL means that the larger dataset being BULK INSERTed won't fit into RAM, so checkpointing starts almost immediately and blocks the smaller BULK INSERT from completing until the slow local disks can finish with the bigger one. The easiest and cheapest solution will almost certainly be to add RAM, if only I can talk Systems Engineering into digging some up.

Performance of LIKE vs = in SQL queries

2009-04-22T15:11:00.000-07:00

Got a question from a coworker yesterday about the relative performance of LIKE vs = in a SQL query, and had to think about it a bit. He wanted to know whether there was a performance penalty to using LIKE if there were no wildcards. In other words, if this:

SELECT * FROM tbFoo WHERE colA LIKE 'bar'

would perform equivalently to this:

SELECT * FROM tbFoo WHERE colA = 'bar'

My knee-jerk response was that the = would be faster, but I thought about it and realized that the query optimizer would actually see them as the same thing. A check of the query plans against a quickly-created tbFoo confirmed it. So that's what I told him.

Except that I realized a moment later that there was a major caveat - the query optimization depends on how the statement is parameterized. If it's purely ad hoc SQL and being compiled at run-time, then the statements are equivalent, but if there's going to be any plan re-use, either by including the statement in a stored proc or preparing it and executing via sp_executesql, the LIKE will impose a significant penalty.

This is because the optimizer doesn't know at compile time whether the parameter to the LIKE operator will contain a wild card, so it can't use the more specific optimization (including index selection) that it could with an = operator. So if you mostly pass parameters without wildcards, you will be executing a suboptimal query plan. Keep this in mind when designing your query!

---

I realized after writing this that this topic has been covered in more depth and with more eloquence by Erland Sommarskog and any number of other SQL MVPs and bloggers, but this post is already written, so I'm releasing it into the wild anyway.

Notepad++ 5.3.1 breaks the Compare plugin

2009-04-22T14:53:00.000-07:00

I use Notepad++ all the time for quick editing of SQL, Python, XML, and all sorts of other types of files, and when I opened it today, it told me there was an update available, so I updated to 5.3.1 .

Then this afternoon when I went to compare two SQL files using the Compare plugin, I couldn't find it, and spent a good 5 minutes combing through the menus before I realized it was really gone. Arggh!

I use the Compare tool all the time, so I immediately downgraded to version 5.2, where it still works. So just a note of caution if you use Compare in Notepad++, 5.3.1 appears to break it - hopefully they'll get that straightened out soon.

pyodbc doesn't seem to like BLOBs

2009-03-13T07:14:00.000-07:00

I tried to pull a Binary Large OBject out of a SQL Server database yesterday via pyodbc, and it blew up.

In [4]: c = pyodbc.connect('DRIVER={SQL Server};SERVER=servername;')
In [5]: r = c.execute('select Blob from dbo.tbDoc where DocID = 8')
In [6]: row = r.fetchone()
assertion failed: getdata.cpp(171)
cbRead == 0 || pch[cbRead-element_size] != 0

And crashed my IPython shell, to boot.

Python's DB API spec (aka PEP 249) doesn't appear to specify exactly how to handle BLOBs, but it does mention them, so I presume they're supposed to be supported. I wound up grabbing this particular BLOB in a C# app instead, but this seems like it could be an issue in the future, so if anyone knows what's going on, please let me know. Otherwise I'll dig into this issue next time I have some spare time or a pressing need to manipulate BLOBs in Python.

UPDATE: I wasn't on the latest build of pyodbc, so I updated to 2.1.3. After updating, I was able to fetch the binary stream from that row. Hooray!

Although when attempting to print it out, I got some weird behavior - my PC's internal speaker started beeping like mad and the window stopped responding. As my colleague said:

P.V. [1:57 PM]:
What the hell was stored in that blob?
Ira Pfeifer [1:58 PM]:
just a hex stream
P.V. [1:58 PM]:
This is like something from Gibson.

Strange...

Weird SQLCMD issue

2009-02-02T11:47:00.000-08:00

I've got a server that runs the open source Job Scheduler to execute scheduled tasks, and I encountered a strange problem with SQLCMD today. Basically, a very simple script fails to execute:

2009-02-02 13:52:01.224 [info] C:\Program Files\scheduler>sqlcmd -S TestServer1 -E -i "c:\temp\test.sql"
2009-02-02 13:52:01.224 [info] SCHEDULER-915 Process event
2009-02-02 13:52:01.224 [ERROR] SCHEDULER-280 Process terminated with exit code 1 (0x1)

This script executes without a problem on the command line, and

echo %errorlevel%

after its execution returns 0. I thought this might be an authentication issue, but switching to SQL authentication and entering the credentials explicitly on the command line produced the same result.

The strangest part of the whole thing is that switching from SQLCMD to OSQL makes the script work fine. I'd rather use SQLCMD, but I don't have time to investigate the minute difference in output that's causing JS to barf on it, so I guess it's back to the old reliable OSQL. At least until it's no longer supported.

SSIS transactions, or Another Reason To Hate Windows Firewall

2009-01-22T11:49:00.000-08:00

I created an SSIS package today to archive a bunch of tables from a source server into one table on a destination server and then drop them on the source. I wanted the process to be idempotent, such that the tables would either be fully archived and dropped or the whole thing rolled back. Thus the process could be re-run if it failed in the middle without fear of duplicating data or prematurely dropping tables.

The obvious solution was a transaction. I set the TransactionOption property on my ForEach loop to Required and ran the package. However, MSDTC was not cooperating, and I got this failure:

With the following error message: "The transaction has already been implicitly or explicitly committed or aborted."

I checked MSDTC on both servers involved, and they each seemed to be set up correctly, albeit with slightly different settings. MSDTC on my machine was also configured correctly. Then I remembered that my desktop has Windows Firewall running. Don't ask me why, it wasn't my idea, but I can't turn it off.

So I added an exception to Windows Firewall for c:\windows\system32\msdtc.exe (port 135) and the package started working. Hooray!

Except that when I killed it midway through to simulate a network or SSIS host failure, it rolled back the whole thing. I wanted the ForEach loop to commit each table load and drop as it went so that I wouldn't have to start from scratch if the package failed after a few tables. So I created a Sequence container inside the ForEach container, set the TransactionOption to Required for that container, moved the Transfer and Drop tasks inside it, and set the TransactionOption to Supported for the ForEach.

sql_variant causes SSIS metadata validation error

2009-01-07T12:10:00.001-08:00

Just a quick note about an error that stumped me for a minute.

I was setting up a Lookup task in SSIS 2005 using the results of a SQL query as the lookup, but I kept getting the following error when attempting to create the column mappings:

TITLE: Microsoft Visual Studio
------------------------------

Error at Load Position Limit Data [Lookup [49]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

Error at Load Position Limit Data [Lookup [49]]: The call to Lookup transform method, ReinitializeMetadata, failed.

------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02090F0 (Microsoft.SqlServer.DTSPipelineWrap)

This didn't seem like a very helpful error, until I thought about what ReinitializeMetadata probably did, and then realized that my query selected a sql_variant column without casting it. Casting the sql_variant to a more appropriate data type (varchar, in this case) resolved the error.

When an OUTER JOIN becomes an implicit INNER JOIN

2008-12-05T12:34:00.000-08:00

I meant to blog about SQL PASS 2008, which I attended in Seattle and where I learned some good stuff, but I haven't had time. Plus there were so many blogs and tweets from it that most of what I would have said was covered. I will note that my favorite sessions were Itzik Ben-Gan's pre-conference session on Advanced T-SQL Querying and Bob Ward's talk about SQL Server memory. If you get a chance to hear either of these guys talk, jump on it - they're both extremely sharp, and good presenters to boot.

I'm posting this quick note because I had a question from one of our developers today regarding style for various joins. He wanted to know whether a condition on a secondary table in a query should go in the ON clause or the WHERE clause. This was my response:

I prefer to see conditions on the primary table in the WHERE clause, with conditions on the secondary tables in the ON clauses. This is because a condition in the WHERE clause for the secondary table when using an OUTER JOIN will effectively convert that to an INNER JOIN, making it confusing to read the query. However, putting that condition in the ON clause keeps the OUTER JOIN functioning as expected. Does that make sense?

Here’s an example with actual tables.

Notice how the first and second queries return the same thing even though the second is a left outer join, because putting the t2 condition in the WHERE clause makes it required for the whole result set instead of just the join. In the third query, the t2 condition is in the ON clause, making it only required for that join.

create table t1
(alfa int,
bravo int)

create table t2
(alfa int,
charlie int)

insert into t1
select 1, 2
union all select 2, 3
union all select 3, 4
union all select 4, 5

insert into t2
select 1, 4
union all select 2, 9
union all select 3, 16

select * from t1
inner join t2
on t1.alfa = t2.alfa
and t2.charlie = 16
where t1.alfa < 5

select * from t1
left outer join t2
on t1.alfa = t2.alfa
where t1.alfa < 5
and t2.charlie = 16

select * from t1
left outer join t2
on t1.alfa = t2.alfa
and t2.charlie = 16
where t1.alfa < 5

drop table t1, t2

Optimizing tempdb and log disk performance

2008-10-28T11:03:00.001-07:00

We implemented a new SAN fairly recently: an HP EVA. In the enthusiasm to move to this new device, some tuning decisions were overlooked or made quickly, and are now being revisited. For example, all the drives allocated to database servers were created on VRAID5 (essentially equivalent to RAID 5, refer to HP's IT Resource Center for the nuances).

In general, it's a known fact that RAID 10 (aka VRAID1) outperforms RAID 5 (aka VRAID5) for write-heavy workloads, but at a higher cost per GB. I wanted to revisit the cost-benefit analysis that led us to use RAID 5, because I've always used RAID 10 for at least logs and tempdb in the past, and for all SQL data when possible.

To make sure that my analysis was relevant to our particular IO load, I planned to generate load on each type of drive using IOMeter, for which I would need to construct an appropriate workload. I recorded a Counter Log with the following counters from each drive using Windows Performance Monitor from our main OLTP server over the course of several days.

Avg Disk Bytes/Read
Avg Disk Bytes/Write
Avg Disk Queue Length
Disk Reads/sec
Disk Writes/sec

Using these counters, I constructed a characteristic workload for each drive.

To do this, I first bucketed each reading of Avg Disk Bytes/[Read, Write] into a size category (.5K, 1K, ...64K, 128K, etc.) and chose enough categories to add up to 80% of the total in order to determine what size IOs to generate.

Then I added the averages of Disk Reads/sec and Disk Writes/sec in order to get the percentages of reads and writes against each disk, and used the average of Avg Disk Queue Length to determine how many IOs outstanding to queue up.

Finally, I ran through this whole exercise again after deciding to filter for Disk Queues > 2. I did this because I figured that the SAN probably wasn't having any trouble when disk queues were low - RAID 5 vs RAID 1 would only show a difference in latency during those times. The trouble comes when loads (for which disk queues are a proxy) are high; that's when you need maximum throughput and minimum response time.

After all this, I came up with the following characteristic workloads for our system (yours may vary substantially):

TEMPDB
Reads - 2%
- 68% 64K read size
- 32% 8K read size
Writes - 98%
- 83% 64K write size
- 17% 512B write size
600 IOs outstanding

LOGS
Reads - 36%
- 87% 128K read size
- 13% 1024K read size
Writes - 64%
- 81% 64K write size
- 19% 16K write size
4 IOs outstanding

Next I worked with our Systems Engineering group to create a VRAID5 and a VRAID1 drive of the same size and attached both to the same server. I created Access Specifications with IOMeter for each workload shown above (see image below), and then ran those Access Specifications against each drive with the requisite number of IOs outstanding.

This yielded some fairly conclusive results. I won't post the actual throughput numbers, but here are the margins by which VRAID1 outperformed VRAID5 for each workload:

TEMPDB

IOPS	MB/s	Avg IO Response Time
117.65%	117.55%	51.48%

LOGS

IOPS	MB/s	Avg IO Response Time
80.05%	65.63%	39.69%

So according to my testing, VRAID1 is vastly superior for these types of workloads, to the point where it's probably worth the higher cost per GB. We'll see how this bears out in further testing and in real-world usage shortly...

Using local disks in a cluster

2008-10-27T09:49:00.000-07:00

With the advent of (relatively) cheap, fast Solid State Disks, DBAs everywhere are realizing that they could potentially see huge performance gains by incorporating these new disks into their database servers. This is straightforward if you've got a monolithic box: buy 2 SSDs, create a new RAID 1, move tempdb and/or logs onto it, restart SQL, shake well, and watch your IO waits go to 0! Hopefully, anyway.

However, if you've got a cluster, things are a bit trickier. The SAN vendors are still ramping up their SSD support, so it can be difficult to get an SSD into a shared storage enclosure unless you roll your own. Especially if you pick up one of the most promising new disks, the FusionIO ioDrive. This disk gets attached to a PCIe bus directly rather than a SAS or SATA backplane, so it can't be put into most commercially available shared storage devices (SANs, NASs, etc.).

Even so, we were sufficiently impressed by the IO stats on this disk to pick up a couple of them, but we still wanted to put them in our production cluster. We can't use them for shared data, obviously, as the data wouldn't persist between machines. But in theory they could be perfect for tempdb, which gets wiped out on each restart and so only needs to see the same path on each machine. I figured there was probably a way to get Microsoft Cluster Services to see the local SSDs on each machine as the same disk.

To test this theory, we (I and coworker Doug H.) took a pair of virtual machines, added drives to simulate a standard local disk, shared SAN storage, and the new SSD, built them into a cluster, and installed SQL Server 2005 on the R: drive.

Disk 0 (C:) - local - boot disk
Disk 1 (Q:) - shared - quorum
Disk 2 (R:) - shared - SQL data
Disk 3 (D:) - local - simulated SSD

When the VMs were initially configured, the fake SSDs were created on the same SCSI bus as the boot disk. The cluster administrator quickly informed us that this would not fly - disks on the same bus as the boot disk cannot be used as shared resources. So we moved the D: drives to a new bus, separate from both the C: (simulated backplane) and Q:/R: (simulated SAN) drives.

The next step was to add the D: drive to the cluster. I assumed this wouldn't work without some hacking, but I decided to try a "baseline" attempt without any modifications. The Cluster Admin New Resource wizard allowed me to add the D: drive on one of the nodes, but it wouldn't come online. So much for the baseline attempt.

I knew that Windows 2003 (though maybe not 2008) clustering relies on disk signatures to identify disks for cluster operations, so I decided to synchronize the signatures of the two fake SSDs. To this end, I found a utility - MBRWizard - that would let me manually set the signature of an MBR disk.

Using this utility, I found the signature of the D: drive on machine #1:

mbrwiz /Disk=3 /Signature

and then overwrote the signature of the D: drive on machine #2:

mbrwiz /Disk=3 /Signature=xxxxxxxx

Then I added the disk as a cluster resource again, and brought it online. Success! Failover worked, too, so I failed the SQL Server group (Group 0 in the image below) back and forth between nodes and created a Data directory on each D: drive.

The next step was to make sure it worked with SQL Server. I took SQL offline, added Disk D: as a dependency, and brought it back up. Then I altered tempdb's location to use the D: drive after a restart.

alter database tempdb
modify file (NAME=tempdev, FILENAME = 'D:\Data\tempdb.mdf')
GO
alter database tempdb
modify file (NAME=templog, FILENAME = 'D:\Data\templog.ldf')
GO

Finally I took SQL offline and brought it back up, with fingers crossed. And it worked! Failover worked too.

So my proof of concept for using a local SSD in a cluster (for tempdb only!) was successful. Stay tuned to see if it works in the real world.

Running a Buildbot buildslave as a Windows service

2008-10-03T11:48:00.000-07:00

Buildbot is an open source continuous integration system written in Python. We use it to automate the builds on one of the projects I work on, and in general, it's cool. Pretty easy to set up (just make sure you look at every setting in the config file), comes with its own simple but handy web interface, and allows quick configuration of multiple "builders" and "buildslaves."

However, like most open source projects, it's written for and by *nix users and doesn't have full Windows support. Fortunately, Mark Hammond (of win32api fame) deigned to write the buildbot_service.py file, which allows installation of the Buildbot buildmaster as a service.

But despite this, there's no out-of-the-box way to run a buildslave as a service. There are a few other hardy souls out there who have figured out various methods for accomplishing this, and I am indebted to their guides as helpful starting points. But I didn't like all the registry hacking and configuration steps, so I went another way. Hammond's buildbot_service.py scares me, but I gleaned enough from it to put together my own buildslave_service.py.

It's not terribly full-featured, and it requires reinstallation if any settings are changed, but it's compact, effective, and easy to install.

Steps:
1) Copy code below, correct spacing that Blogger's HTML window stripped out, change paths as appropriate, save as buildslave_service.py.
2) Install the service (w/ optional parameter for automatic startup if you want):
python buildslave_service.py install [--startup=auto]
3) Start the service:
net start Buildbot_Buildslave
(or use the Services mmc snap-in)

And that's it! Easier than all the registry mess and downloading Win2003 resource packs, no?

""" buildslave_service.py

Original Author: Ira Pfeifer
Email: ipfeifer -dot- tech -at- gmail
"""

import sys
import os
import subprocess
import win32serviceutil
import win32service
import win32event
import win32api
import time

from buildbot.scripts import runner
from buildbot.scripts.startup import start

# change paths as appropriate
slavepath = "c:\\buildbot\\buildslave"
homepath = "\\"
os.environ['HOMEDRIVE'] = "C:"
os.environ['HOMEPATH'] = homepath
os.environ['PATH'] = ";".join([
r"C:\Python25"
,r"C:\Python25\Scripts"
])

class BuildSlaveService(win32serviceutil.ServiceFramework):
_svc_name_ = "BuildBot_BuildSlave"
_svc_display_name_ = "BuildBot BuildSlave"
_svc_description_ = "Buildbot slave based in " + slavepath

def __init__(self, args):
win32serviceutil.ServiceFramework.__init__(self, args)
self.stop_event = win32event.CreateEvent(None, 0, 0, None)

def SvcDoRun(self):
# The service starts a subprocess that will run the actual buildbot
# so that it can be stopped by simply killing off the subprocess.
self.child = subprocess.Popen(["python",
__file__,
"start"])
isAlive = True
while isAlive:
time.sleep(10)

def SvcStop(self):

self.ReportServiceStatus(win32service.SERVICE_STOP_PENDING)
handle = win32api.OpenProcess(1,0,self.child.pid)
# returns exit code - wrap w/ error handling?
win32api.TerminateProcess(handle, 0)
win32api.CloseHandle(handle)
isAlive = False
self.ReportServiceStatus(win32service.SERVICE_STOP_PENDING)
win32.SetEvent(self.hWaitStop)

SvcShutdown = SvcStop

def start_buildslave():
config = runner.Options()
config.parseOptions(['start',slavepath])
so = config.subOptions
start(so)

if __name__ == '__main__':
if len(sys.argv) > 1 and sys.argv[1] == "start":
start_buildslave()
else:
win32serviceutil.HandleCommandLine(BuildSlaveService)

CPU Affinity Masking plus High CPU = Login Timeouts

2008-09-28T09:11:00.000-07:00

Recently we saw an alarming issue with one of our database servers. Under heavy but not prohibitive load (60-80% CPU), it stopped accepting new connections intermittently. Obviously this is bad for any server, especially one that handles thousands of client connections simultaneously.

Some of the hard drives were under heavy load, especially tempdb, but another anomaly was a long-running query using many linked server connections (11!) that had been killed but was stuck in rollback several hours later. It was consuming near 100% of the cycles on one CPU core. We tried a variety of the usual DBA tricks to get rid of this spid, but nothing worked. It wasn't clear to us how this could cause the server to stop accepting connections, however.

Another oddity was the appearance in the System logs of messages like this:

The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.

I asked our Systems guys about this, and they said that this had been noticed a few months ago and a workaround had been put in place as per KB 931279 - the CPU affinity mask had been set in SQL Server.

Hmm.

This happened again a week later, minus the spid stuck in rollback, but with one CPU slammed at 100% again. We had engaged Microsoft PSS to assist with the problem, but so far all they had told us was that we had tempdb IO issues, which we knew. (DVNation, please finish the Windows drivers for your IODrives so we can use them for tempdb!)

So here's my theory, cooked up with one of the other DBAs: the login issues are being caused by the combination of affinity masking and one CPU at 100%. This could happen because schedulers are affinitized by the mask to a single CPU, making them unable to hop CPUs when one is under heavy load. User logins round-robin between schedulers, so if a scheduler is stuck to a single CPU and that CPU is not making enough cycles available to log someone in, eventually the login will timeout and fail.

Plausible? Anyone else seen this kind of issue?

UPDATE:

I was right. After removing the affinity masking, we no longer saw login timeouts, even when the server was near 100% CPU load. Be careful with those affinity masks.

psexec Permission denied error

2008-09-22T08:57:00.000-07:00

In the course of my DBA and software dev tasks, I make extensive use of Mark Russinovich's excellent PSTools. Today I ran across an odd error while running a remote command using psexec. Filenames have been changed, obviously, but here's the gist of it:

> psexec \\remotemachine python \\fileserver\script.py param1 param2

python: can't open file '\\fileserver\script.py': [Errno 13] Permission denied

So it was obviously opening python, which I double-checked by running with the -i option and checking the remote machine's desktop using VNC Viewer. The python shell was opening successfully, but it couldn't access the file server. Executing the script directly from the remote machine's command line worked fine.

Then I realized that this was the same issue that one gets from too (two? ;) many linked server hops using integrated authentication - it's a two-hop Kerberos problem. Google that if you don't know what I'm talking about. Fortunately, psexec offers the option of passing a username and password, and as soon as I added those options, my command executed successfully.

DBA rant

2008-09-16T12:03:00.000-07:00

I'm not big on posting links, but this is the best angry DBA post I've read in some time.

SSIS Data Flow Task Error caused by too many Data Flow tasks

2008-09-09T12:55:00.001-07:00

I just threw together a very basic SQL 2005 SSIS package, the main component of which is a Data Flow task with 25 sub-flows. The flows are very simple, just direct transfers of a table on one server to the same table name on another server. As an aside, it took WAY too long to create this package - for each little flow, I had to select the source connection, wait for it to populate the table drop-down, then scroll through that drop-down through hundreds of tables to find the one I wanted, then do it again for the destination. This could be better in a number of ways:

Allow typing in the table field. It would have been much quicker to allow me to type or copy and paste the table name rather than waiting for the drop-down and then scrolling through it. 50 times.
Improve copy and paste of flow tasks. If I could have constructed one and duplicated it 25 times, it would have been much quicker, but when I attempted that, I got strange warnings about field truncation that were cleared up only by deleting the source and re-creating it.
Create a wizard that facilitates auto-generating this kind of simple package better than the Transfer Objects wizard.

Maybe one or more of these has been implemented in SQL 2008, but I can't use that yet, since my office has no plans to upgrade our SSIS runtime any time soon.

Anyway, when attempting to execute the package, I was consistently getting a failure from one flow with this error:

TCP Provider: An existing connection was forcibly closed by the remote host

This didn't make much sense to me, as it worked fine when run by itself. I found this article and added that registry key, but it did not fix the problem (although it did seem to increase throughput).

Then I noticed that the two largest flows in the Data Flow task (on the order of millions of rows) were executing at the time when the problem task was throwing an error. I thought SSIS would be able to handle the sequencing of parallel tasks, but I decided to enforce my own execution order by placing the two large flows in a separate Data Flow task.

Voila. Works fine. So if you're having issues with an overpopulated Data Flow task throwing that TCP error, one simple workaround is to manually sequence/reduce the parallel execution of the tasks.

Programming Languages I know

2008-09-08T14:04:00.000-07:00

To pick up the latest blog mini-meme (courtesy of Corey Goldberg), here's The List of programming languages I have learned:

1. AppleScript
2. C
3. Java
4. Lisp (Scheme)
5. C++
6. JavaScript
7. ANSI SQL*
8. PHP
9. T-SQL
10. VBScript
11. C#
12. Python

Kind of a strange order, no? Mostly driven by expediency, except Scheme and C++, which I learned in college.

UPDATE: Dan points out that ANSI SQL is not Turing complete and should be asterisked, so I guess I'm down to 11.5. Let's see your monster list, Dan - mcfunley.com has been pretty quiet lately.

DTS Editing doesn't work in SQL 2008?

2008-09-05T10:41:00.000-07:00

I've been running the SQL 2008 RTM for a few weeks now, and generally it works fine. But today I needed to open an ancient DTS package (it's not from my team, all our stuff is SSIS), and couldn't do it. First it told me I needed the SQL 2005 Backwards Compatibility components. "That's odd," I thought, "I could have sworn I installed all of that when I ran the SQL 2008 setup. And why is it asking for the 2005 components?"

It appears that Microsoft has simply updated the 2005 BC component install to work with 2008. Fine. I installed that, and tried to open the package again. No dice.

So I went to find those components. Found the package, installed it... to no effect whatsoever! I still get the same message! Note to Microsoft: if you don't want me to edit DTS packages in 2008 and have deprecated that functionality entirely, fine. Just TELL ME instead of popping up worthless error messages whose instructions I follow to no avail.

Float comparison tolerance

2008-08-26T13:36:00.000-07:00

Had the age-old problem with significant figures when comparing floats in Python today, and came up with this little function to deal with it. It ensures that the two values are equal down to 10 significant figures, no matter at what scale they start. This is as opposed to the conventional test, which checks at a fixed scale (e.g. return abs(a - b) < 1e-10).

def equal(a, b):
return abs(a - b) <= abs(a - b)/10000000000

Testing:

a = .0000000000000000837
b = .00000000000000004315
c = a + 100
d = b + 100

equal(a,b)
False

equal(b,a)
False

equal(c,d)
True

equal(d,c)
True

equal(1,1)
True

(Of course, we wound up defining a hard limit, 1e-10, and not using my function, but I still thought it was clever.)

UPDATE: Yes, I recognize that this is hardly a new problem or solution, but it's MY BLOG and I mostly use it as a memory aid anyway - outside readers are incidental (which is a good thing, given my Google Analytics stats).

Automated linked server test

2008-07-18T13:13:00.001-07:00

Got a big production event scheduled, so I wrote this quick automated test to be incorporated into the post-event testing. I'll probably improve on it incrementally as I notice shortcomings, but at the moment it's just a basic test of data access via all linked servers set up on a machine.

/*** Linked Server Test ***/

declare @linked table (name sysname, done bit, retval int)
declare @srvr sysname
declare @sql varchar(255)
declare @retval int
insert into @linked select name, 0 as done, NULL as retval from sys.servers
while exists (select 1 from @linked where done = 0)
begin
select top 1 @srvr = name from @linked where done = 0
begin try
exec @retval = sp_testlinkedserver @srvr
end try
begin catch
set @retval = sign(@@error)
end catch
exec(@sql)
update @linked set done = 1, retval = @retval where name = @srvr
end
select * from @linked

pyodbc returns an int instead of rows in a cursor

2008-07-14T10:12:00.000-07:00

Just a quick note about pyodbc: if you're getting a TypeError from a pyodbc cursor because it's an int instead of a rowset, it's probably because the proc or query being called is returning extra info or messages. Try turning off anything that's returning extraneous info, like ANSI_WARNINGS.

SQL 2008 installation problems

2008-06-17T07:33:00.001-07:00

Several of us at my office had issues installing SQL 2008 RC0 - the installer kept failing the "Restart computer" check.

So I started digging through the registry to find the culprit flag... when this forum post turned up and pointed me to exactly the right place.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3492057&SiteID=1

Turns out our virus scanning software always posts a pending file rename, which SQL setup interprets as the computer needing a reboot. I cleared these values and the installer ran fine.

UPDATE: Had the same problem with SQL 2008 RTM. Fix works for that too, and probably for many other installers that refuse to start due to a pending file operation.

Returning rows from a stored procedure with pyodbc, pt 2

2008-06-10T10:32:00.000-07:00

Pyodbc really doesn't like extraneous information to be returned with its (ONE AND ONLY ONE!) result set from a stored procedure.

In my latest fight with pyodbc, I had a stored proc returning a rowset with a few thousand rows. This worked fine most of the time, except in a few cases with certain input parameters. In those cases, the cursor returned by execute() would contain an int, so any cursor method I called on it would break.

At first I thought I might have hit some kind of row size limit or rowcount limit, but realized the result set wasn't big enough for that to be the case. The proc wasn't throwing off any errors when run in SQL Management Studio with the same input parameters, so that wasn't the problem.

Then I noticed that it was returning a single warning message in the Messages tab.

Warning: Null value is eliminated by an aggregate or other SET operation.

This is a fairly common warning in SQL land, so I hadn't paid any attention to it. But it is possible to suppress, using the following statement:

SET ANSI_WARNINGS OFF

Sure enough, this did the trick. The moral of this story (just like last time) is that pyodbc wants a single rowset and that's it; returning any data in any of the alternate ODBC channels seems to break it, or at least, not yield the cursor in the result set that I expected.

Speeding up Vista User Access Control (UAC)

2008-06-07T09:24:00.000-07:00

My HP laptop (tx1210) runs Vista Home Premium, and I'm constantly performing actions on it that pop up the User Access Control window. I don't have a problem with this in and of itself, as I've read Microsoft's side of the argument and I'm willing to give them the benefit of the doubt and assume that this does actually provide some additional security.

That said, it's slow as hell.

The UAC popup isn't bad on my monster desktop, which has a top-end Core 2 Duo, 4 GB of RAM, and a decent video card, but it takes FOREVER on my laptop - probably 3-5 seconds to black out the screen and pop up the prompt, and another 3-5 to black out the screen again and return me to what I was doing. This is unacceptable in 2008.

After reading a few unhelpful posts directing me to disable UAC completely, I found one that mentioned that it wasn't as slow with Aero (the pretty transparency and other visual effects package in the higher versions of Vista) disabled. So I opened the Performance Information & Tools feature in the Control Panel and selected Adjust Visual Effects. After turning off the vast majority (I left Use visual styles on windows and buttons because I just couldn't bear to look at the old grey XP-style taskbar any more), guess what? UAC is now lightning-fast and doesn't bother me at all.

I'll probably try re-enabling a few more settings and find the balance between speed and graphical sugar, but disabling most of the visual effects seems to have done the trick, and is already improving my Vista experience dramatically.

Python's subprocess doesn't like SmartFTP

2008-05-06T14:13:00.000-07:00

Well, that was a frustrating afternoon. I was trying to automate the update process described in my previous post - an executable that uses SmartFTP's COM library to connect to an FTP server and download new files - by calling it from a Python script that would check the output and re-run the updater as necessary. I wanted to use the subprocess module, as I was advised by the Python docs that this was the New Cool Thing, and I wanted to use the environment variable functionality that it provided.

However, I consistently received the following error:

[20080506 19:05:00] SmartFTP FTP Library v1.5.8.21
[20080506 19:05:00] Resolving host name "ftp.******.com"
[20080506 19:05:00] Unable to resolve host name.
[20080506 19:05:00] 1

So I replaced the hostname with the IP address and got this error instead:

The requested service provider could not be loaded or initialized.

I found this phrase in the Microsoft Winsock documentation, at which point I had a mild aneurysm and went to work on something else for a while. I came back a little later to read that documentation, but it was unhelpful.

Finally I switched over to declaring the environment variables and calling the executable in a batch file and called that using os.system instead of subprocess... and it worked fine. So it appears that there's some incompatibility between this vendor app and/or SmartFTP's FTP library and Python's subprocess module. Guess I'll be using the tried and true os.system for now, because it's definitely not worth any more debugging time.

How to break your FTP client

2008-05-02T13:17:00.001-07:00

Haven't posted in a while because I was in Russia for a few weeks checking out the smog, vodka, and beautiful women. Now I'm back, and I have a quick non-database item to relate.

Today I was working on scheduling the execution of some updater software from a small data vendor of ours. As it turns out, they have no spec or unified framework for their error handling and messages, so I had to work out what sort of errors could happen on my own. One thing the updater does is log on to the vendor's server via FTP and download new files. So naturally I wanted to prevent this to see what would happen.

The easiest way would have been to just unplug my network connection, but the app needs some files that live on the network, so that was out.

I tried to block the program with Windows Firewall, but we've got a bunch of Group Policy stuff applied there, which complicated things. Even when I unchecked my local firewall exception for the app, and then also the exception for FTP, the application connected to the vendor's FTP server without any trouble.

Next I went out and found a 3rd party app that could block ports. The one I settled on after a quick browse of some security sites was Ghost Personal Firewall. Simple, straightforward, free, quick installation - suited my needs perfectly. And it worked great. It took 2 minutes to download, install (no reboot needed), and configure a rule to block all traffic on port 21. This had the desired effect of breaking the app and producing an error log.

So consider this an unsolicited endorsement of Ghost's firewall. Served my purpose just fine, and it's going into my security toolkit for future app testing.

Returning rows from a stored procedure with pyodbc

2008-03-27T08:07:00.000-07:00

I'll start with the executive summary for those of you short on time or who don't care about the background:

When executing multiple statements within a SQL Server stored procedure, including a SELECT statement that you expect to return rows to a rowset via pyodbc, it is necessary to SET NOCOUNT ON.

Backstory:

I ran into a new issue with pyodbc yesterday - an inability to parse rows returned by a SQL Server stored procedure. The proc in question was executing a number of queries, generating logging as rows to a table variable, and then returning the log via a select statement against the table variable.

However, when I executed the statement from a pyodbc connection, it didn't work.

This was a fairly complex proc that caused changes in the database when executed and I didn't want to run it repeatedly, so I mocked up "spTest" as follows:

create proc spTest as
select 'a' as msg
union all select 'b'
go

When executed from Python:

db = pyodbc.connect(CNXNSTRING, True)
rows = db.execute("exec spTest")
for row in rows:
print row

This returned rows. So I added a table variable:

create proc spTest2 as
declare @msgs table (msg varchar(255))

insert into @msgs
select 'a' as msg
union all select 'b'

select * from @msgs
go

This, being more similar to the actual proc, did not work. I changed a few things, tried a temp table instead of a table variable, etc., but didn't get any better results. Then my esteemed colleague Pijus asked if it was possible that pyodbc was reading some other channel of information from SQL that was obscuring the results. Aha!

SQL spits out rowcounts by default. I'm not sure where these turn up in ODBC world, but I know that they do get provided to the ODBC driver, and that does constitute an additional channel of information that could be confusing pyodbc. So I SET NOCOUNT ON, and voila, the final select returns results regardless of how many other operations occur within the procedure.

Case Sensitive GROUP BY

2008-03-12T07:28:00.000-07:00

This is fairly trivial, but it took a few minutes to work out, so I figured I'd post it. Someone on my team needed to perform a case-sensitive GROUP BY operation on a table with a case-insensitive collation. I had performed case-sensitive SELECTs before by "casting" the field in question to an alternate collation, so I tried that, but couldn't get it to work at first. Of course, had I thought about it, I would have realized that the same operation needs to be applied in both the GROUP BY and the SELECT.

Ex:

create table foo
(bar varchar(20))

insert into foo select 'Banana'
insert into foo select 'banana'
insert into foo select 'Banana'

select * from foo

select
bar COLLATE SQL_Latin1_General_CP1_CS_AS
, count(*)
from foo
group by bar COLLATE SQL_Latin1_General_CP1_CS_AS

Alternately, one could use Microsoft's recommended method and convert the field to binary and then back. I haven't performance-tested the two methods, so I'm not sure which is faster. I leave that as an exercise for the reader. ;)

select convert(varchar(20),x.bar), x.ct
from
(select
convert(binary(20), bar) as bar
, count(*) as ct
from foo
group by convert(binary(20),bar)
) as x

INSERT problems via pyodbc

2008-03-10T12:31:00.000-07:00

This is really stupid, but I just wasted about an hour of dev time trying to figure out why my INSERT statements weren't working from a Python script using pyodbc even though SELECTs were fine. As it turns out, they were getting rolled back because I wasn't explicitly committing the transactions and hadn't set the AUTOCOMMIT option to true for the connection.

So if you happen to search for "pyodbc INSERT problems" (as I did), hopefully you'll stumble across this (as opposed to nothing, which is what I found) and slap yourself in the head (as I did).

SQL Server CLR Remote Debugging

2008-03-10T08:22:00.000-07:00

My CLR stored procs have matured to the point that they're being deployed, which is to say that the bugs are now insidious rather than blatant. It's a royal pain to make changes, deploy the project locally, run the post-deploy correction script to fix the decimal precision on everything (see previous post), debug any issues, and then do the whole deployment again to the dev server. It's much easier to just debug it straight on the server.

Fortunately, remote debugging is a straightforward process. Predictably, Microsoft's docs (here's one of many) on the subject are somewhat convoluted, although this msdn blog post helped.

All you really need to do is install Remote Debugging Monitor on the server and run it under the same login you use to connect to SQL. It comes with its own standalone installer on the Visual Studio CD, just look for the Remote Debugger directory and run the setup app found there. Then you can run it either manually or as a service.

Next, place a breakpoint in your code somewhere and deploy it to the server. I've had trouble with my test scripts when deploying/debugging all at once - basically, they hang without ever running - so what I've started doing is this:

1) Start without debugging (Ctrl-F5) to deploy to the remote server
2) Run my decimal parameter correction script
3) Attach to Process in Visual Studio to sqlservr.exe on the remote server
4) Execute the proc or UDF in a SQL Management Studio window

Then I can step through the code, look at locals, and generally debug to my heart's content.

SqlDecimal truncation oddity

2008-02-22T09:22:00.001-08:00

I've been working with all sorts of SQL CLR integration lately - User Defined Types (which are a bad idea), stored procs, and UDFs. In general, I'm pretty happy with the stored procedures, although I've run up against a number of irritating (but understandable) limitations, like the inability to dynamically load libraries and the requirement that only SQL data types be passed in and out of the CLR stored procs. But I haven't really broken any new ground, so I haven't written about it.

Today's issue is also not ground-breaking, but it is odd, so I thought I'd mention it. I wrote a function to convert a date into a decimal. Better that you not ask why. Here's the code:

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDouble fnTimeSeriesCalcFracYrFromDateS(SqlDateTime inputDT)
{
DateTime dt = (DateTime)inputDT;
double temp = (dt.Year + (Math.Round(((double)dt.Month * (365 / 12) + (double)dt.Day) * 1000000 / 365) / 1000000));
SqlDecimal temp2 = (SqlDecimal)temp;
return temp2;
}
};

Obviously I could have dispensed with almost all of that by casting the data types within a single line, but I broke it out for debugging purposes.

Here's the funny part: when placing a breakpoint at the return statement, here are the values of the variables:

inputDT {1/2/2008 12:00:00 AM} System.Data.SqlTypes.SqlDateTime
dt {1/2/2008 12:00:00 AM} System.DateTime
temp 2008.087671 double
temp2 {2008.0876710000000} System.Data.SqlTypes.SqlDecimal

All as they should be. But the output of the function is 2008.

Why? I have no idea. I'm guessing it has something to do with the SqlDecimal declaration of the function, because when I changed it to SqlDouble, it returns 2008.087671 as it should. Anybody know why this is happening?

UPDATE: Figured it out. Visual Studio deploys the UDF without specifying a precision and scale, which causes the scale to be set to 0. One can circumvent this by dropping and re-creating the function manually within SQL Server and declaring the decimal data type with the desired precision and scale (in this case, (10,6)).

However, this is annoying because it turns a one-button deploy/debug process into a multi-step process, since one can't use the test script functionality in Visual Studio to run a script with multiple "GOs" to drop and re-create the function and then run it. The workaround I devised was to

1) Deploy the function to SQL via Visual Studio.
2) Drop and re-create the function manually in SQL Management Studio.
3) Set a breakpoint in VS.
4) Attach VS to the sqlservr.exe process.
5) Run the function. It should hit the breakpoint, allowing you to debug when it has the correct variable type settings.

But it would be nice to get back to where one press of F5 deployed and debugged the whole thing. Maybe I can figure out a way to set up a custom deploy script from VS...

UPDATE 2: When attempting to deploy the CLR functions after manually dropping them and re-creating them with the desired precision on the decimal return types, you get a Deploy error like the following:

The assembly module 'fnTimeSeriesCalcFracYrFromDateS' cannot be re-deployed because it was created outside of Visual Studio. Drop the module from the database before deploying the assembly.

To get around this, you can write a SQL script to drop the functions if they exist, save it into your project directory, and use the Pre-build event command line on the Build Events tab of the project properties to execute that drop script before each deploy. One less step per build!

Next I have to figure out how to get the post-build to execute the script to drop and recreate the functions with the proper precision.

Arggh

2008-01-28T13:50:00.000-08:00

I wrote this nice little piece of code to eliminate tons of redundant rows from a 70-million row table, tested it in a vacuum in Dev, put it in the Integration environment for 3 weeks... and had it blow up spectacularly when applied to Production this weekend because of triggers on the table that I hadn't accounted for. Stupid legacy code!

There's no real content to this story, I'm just venting. The only moral is TEST YOUR CODE.

More fun with ranking functions

2007-12-11T13:51:00.000-08:00

I don't think I really understood why Mike F at Capital IQ was so excited about the SQL 2005 ranking functions until just recently, but I'm using them all over the place now. Minutes after kicking off the time series load I described in my last post, I came across another use for RANK.

EDIT: As I review this, I recognize that this is pretty esoteric and may not have much practical application for most people. You've been warned.

Like many financial companies, we get data from multiple vendors, and we have to decide which vendor's data to use in each case. So I've got a set of data provided by multiple Vendors, grouped by ObjectId and Date, that is ranked for each date by some business logic built into the procedure that loads the data. The best (lowest, in this case) ranked row for each ObjectId and Date gets a bit flag set; we'll call it IsBest.

However, it was uncovered that there was an extra negation in the business logic for many cases, so many of the rows had the bit flag set incorrectly. I needed to find a way to determine what portion of the data had the flag set incorrectly. This could be accomplished using any number of data warehousing techniques - Analysis Services is well-suited to it, for example, or Microstrategy, or even a complicated series of GROUP BYs. But I didn't want to go to all that trouble just to produce a single ad hoc report, so I found another way.

Some (totally made up, to prevent my employer from having me whacked) sample data, in which only objectId 4 has the correct settings for the bit flag:

I needed to find rows in which the highest-ranked VendorId had the IsBest flag set for a given ObjectId and Date, and any rows in which a lower-ranked VendorId did not have the IsBest flag set. Any other combination is incorrect. If I'm only worried about how many rows need to be fixed, I can stop there, or I can divide the sum of those two rowcounts by the total number of rows to get a percentage of data that is correct.

A query to return the rowcounts follows. I'm using DENSE_RANK rather than RANK to keep the ranking correct despite duplicate rows (as in objectId 3).

SELECT
IsBest, DRank, count(*)
FROM
(SELECT
[Date]
, ObjectId
, VendorId
, IsBest
, DENSE_RANK() OVER (PARTITION BY Date, ObjectId ORDER BY Priority) as DRank
from tbSource
) as allRows
group by IsBest, DRank
order by IsBest, DRank

Additional code could be added to this query to automatically sum the correct cases and determine the percentages, but I will leave that as an exercise for the reader. Here are the raw counts, with the correct cases highlighted:

Another cool use for RANK() - Time Series

2007-12-11T11:58:00.000-08:00

I've got a big set of time series data across many object instances that needs to be inserted into a table that uses a trigger to set the "End Time" of each segment in the series. In other words, data like this:

When a new row for a given ObjectId is inserted, the previous row in the series is given an EndTime equal to the StartTime for the new row. Usually, this is done one row at a time, with only one row coming into the table for each ObjectId at a time.

However, in this case, I need to backfill a lot of data, which will cause the trigger to not behave as expected. I have a few options to work around this:

Insert each row one at a time. Turns out there are over a million rows to be inserted and this is a heavily-used table, so this option is undesirable.
Write a script to generate the EndTimes for each row ahead of time. This could be tricky and CPU-intensive, and I'd rather avoid it if possible.
Partition the data such that the trigger will work as it's supposed to, but large batches of data can be inserted instead of inserting one row at a time.

I'm currently investigating option 3, especially because it lets me use the RANK() function. The basic idea is this: for each ObjectId, rank the StartTimes, then loop through and insert all rows where RANK = 1, then 2, 3, etc. This will insert all the ObjectIds with their initial StartTime, then the next StartTime, and so on, allowing the trigger to fire on each round and update the EndTimes en masse.

The query I'm using (generalized for mass consumption):

INSERT INTO tbTemp
SELECT
ObjectId
, Value
, StartTime
, RANK() OVER (PARTITION BY ObjectId ORDER BY StartTime) as Ord
FROM
tbSource

Then the data in tbTemp can be looped through by rank and inserted into the destination table.

And the resulting data should look like this:

Many-to-many physical table design

2007-11-29T12:10:00.000-08:00

I was creating a new set of tables (we'll say 2 for simplicity) that will contain rows that can have many-to-many relationships, and I was pondering how best to design the association table so as to optimize IO.

In this table scenario, joins can come from either direction, so it could be one row from the first table that joins to many in the second, or vice versa. I'm generally a big fan of a clustered index on some column, especially an integer ID if it's useful, but in this case, I think the best performance would be achieved by dispensing with the clustered index and ID column altogether. The basic table design would be as follows:

CREATE TABLE User
(
UserId INT IDENTITY(1,1)
, ...
)

CREATE TABLE Group
(
GroupId INT IDENTITY(1,1)
, ...
)

CREATE TABLE UserToGroup
(
UserId INT
, GroupId INT
, ?
)

A clustered index on UserId would speed up queries starting with a User and joining to Groups, but it would fragment as old users added groups, splitting pages to insert UserIds. The reverse would be true of clustering on GroupId. Unless the query pattern supported such an unbalanced design which would require frequent index defragmentation, either of these clustered indices would be less than ideal.

Adding a surrogate key identity column would be no better; it would prevent index fragmentation, but the index wouldn't be used for seeks, and the column would add 50% more space on disk between the columns that we really care about, making I/O proportionately slower.

The best design should be a heap, with nonclustered indices on UserId, GroupId and GroupId, UserId. Or more specifically, in SQL Server 2005, indices on UserId and GroupId, with the other column set up as an "included column" to make each index covering.

I haven't tested this yet, though, so I welcome any comments or contradictions.

Odd SSIS bug

2007-11-27T14:50:00.000-08:00

Here's what happened: I added a column to a source table, went into the Data Flow source, refreshed the table list, selected the table, and checked all the output columns that I wanted. Then I resolved all the naming issues with the downstream tasks.

But I'm getting an error (#1 below) when I try to build, because for some reason SSIS will not automatically add the new column to the Error Output, and it won't let me add a column to that output, even in the Advanced Editor. This is annoying. I'm probably going to need to delete and add this data source, which is going to screw up all my transformations.

Error Message #1
Error at Data Flow Task [tbCA Source [1]]: The output column "CaxSubTypeId" (1052) on the non-error output has no corresponding output column on the error output.

Error Message #2
TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [tbCA Source [1]]: The component does not allow adding columns to this input or output.

off topic again

2007-10-09T10:09:00.001-07:00

While catching up on the thousands of RSS articles that showed up in Google Reader while I was on vacation for a week, I read Scott Hanselman's post about typing speed and speech recognition and immediately had to take the test.

Number of words typed: 261
Test duration: 3 min
Speed: 87.0 words/min. (435 keystrokes/min.)
Error penalty: 10
Accuracy: 96.2%

Ha! I win! (He managed 72.6)

Actually, I'm surprised I managed 87 wpm, but I have to admit, this test was slightly stressful. I was trying hard. I probably average around 60 most of the time.

Regex for linked servers

2007-09-20T09:17:00.000-07:00

As part of the DTS migration I mentioned in my last post, I wrote a few functions to find less-than-ideal code. One thing we wanted to get rid of was linked-server joins, and the easiest way to find those was to check each Execute SQL task using a regular expression (long live Regulazy). So once again I'm using this blog as my personal source repository and re-producing the regex for this.

Regex for linked server queries:
(\S+)\.(\S+)\.dbo\.(\S+)

I tried to generate an expression to find "SELECT...INTO" where ... did not contain @ or FROM, but couldn't figure it out with the meager regex capabilities of VBScript. Seems like a lookahead or lookbehind would probably work for this purpose, but according to the 4 Guys From Rolla, VBScript regex does not support these.

UPDATE: Spent a little more time on it and figured out a suitable regular expression to find instances of SELECT INTO that excludes INSERT INTO and SELECT FROM.

Regex for SELECT INTO:
select((?!from)(?!insert).\n)*into

UPDATE 2: My first SELECT INTO regex was foiled by something like "SELECT foo, bar, admintool from ...", so I fixed it to make sure there's whitespace before and after the SELECT and the INTO.

select[\s]((?!from)(?!insert).\n)*[\s]into[\s]

Parsing the filesystem for files

2007-09-11T12:37:00.000-07:00

I'm sure this has been done many times before, but this is my implementation. I needed it to run a large DTS migration from files stored on disk, so first I needed a list of those files. They were contained in a directory tree in no particular order, and in many levels of subdirectories. I wanted a generalized solution to the parsing, so here it is.

Pasting it into Blogger butchered my formatting, but it feels like a waste of time to fix it, since it will probably not paste properly back into an editor anyway. I made the (minimal) comments green just to break it up a bit.

As usual, no warranty or even functionality of this code is expressed or implied. Use at your own risk, especially since I'm not sure the xp_subdirs extended proc is supported and xp_cmdshell can definitely be dangerous.

/*************************************************
** get list of dirs to search for files
*************************************************/

declare @currentdir varchar(1000)
declare @fullpath varchar(1000)
declare @basepath varchar(1000)

set @basepath = 'c:\stuff'
set @fullpath = @basepath

if exists (select 1 from tempdb.dbo.sysobjects where name like '#dirs___%')
drop table #dirs

create table #dirs
(
directory varchar(1000)
, done bit
)

if exists (select 1 from tempdb.dbo.sysobjects where name like '#dirtemp___%')
drop table #dirtemp

create table #dirtemp
( directory varchar(1000) )

insert into #dirtemp exec master.dbo.xp_subdirs @fullpath

insert into #dirs
select
'\' + directory as directory
, 0 as done
from #dirtemp

Loop: -- xp_subdirs spits out errors when a dir has no subdirs, so they have to be caught with this GOTO Loop construct
while exists (select 1 from #dirs where done = 0)
begin
if exists (select 1 from tempdb.dbo.sysobjects where name like '#subdirs___%')
drop table #subdirs

create table #subdirs
( directory varchar(255) )

select top 1 @currentdir = directory from #dirs where done = 0

set @fullpath = @basepath + @currentdir

exec master.dbo.xp_subdirs @fullpath
if (@@ERROR <> 0)
begin
update #dirs set done = 1 where @currentdir = directory
GOTO Loop
end

insert into #subdirs exec master.dbo.xp_subdirs @fullpath
insert into #dirs select @currentdir + '\' + s.directory, 0 as done from #subdirs s
update #dirs set done = 1 where @currentdir = directory
end

if exists (select 1 from tempdb.dbo.sysobjects where name like '#dirtemp___%')
drop table #dirtemp

if exists (select 1 from tempdb.dbo.sysobjects where name like '#subdirs___%')
drop table #subdirs

/*************************************************
** get list of files
*************************************************/

declare @command varchar(1000)
declare @fileextension varchar(5)

set @fileextension = 'dts'

if exists (select 1 from tempdb.dbo.sysobjects where name like '#files___%')
drop table #files

create table #files
(
filepath varchar(1000)
)

update #dirs set done = 0

while exists (select 1 from #dirs where done = 0)
begin
if exists (select 1 from tempdb.dbo.sysobjects where name like '#filetemp___%')
drop table #filetemp

create table #filetemp
(
filepath varchar(1000)
)

select top 1 @currentdir = directory from #dirs where done = 0

set @command = 'dir ' + @basepath + @currentdir + '\*.' + @fileextension + ' /B'

insert into #filetemp exec master.dbo.xp_cmdshell @command

insert into #files select @basepath + @currentdir + '\' + filepath from #filetemp

update #dirs set done = 1 where directory = @currentdir
end

if exists (select 1 from tempdb.dbo.sysobjects where name like '#filetemp___%')
drop table #filetemp

delete from #files where ISNULL(filepath, 'x') not like '%' + @fileextension + '%'

select * from #files

Completely off-topic

2007-08-29T06:56:00.000-07:00

My globe-trotting brother has started a blog. He's currently hanging out in Moscow doing translation work for a big Russian firm, and while I'm not sure what the typical content will be like, he's a good writer, so it should be interesting.

http://russiatranslated.blogspot.com/

SQL Management Studio Setup

2007-08-28T06:29:00.000-07:00

First off, I'm back at a desk after an excellent time in Europe - a whirlwind tour through Budapest, Bratislava, Krakow, and Vienna. I may have to move to Vienna one of these days, and I will definitely be seeking a trophy wife in Bratislava. So today is my second day at my new employer, an entity I have been informed prefers not to be named publically, so all references to it from here out will be oblique, or possibly substituted with "The Black Chamber."

In any case, I needed to start setting up my new desktop (that was already built when I got here, by the way, and with three (!) 19" monitors mounted on extremely solid articulating arms), and realized that I can never remember the miscellaneous settings I change in SMS to get it set up the way I like for maximum productivity. Generally I just stumble into them after I try to do something the way I usually do and it doesn't work. So like many other posts on this blog, this is purely for my reference purposes, and if it helps anyone else out, even better.

ProFont - Mike Forman introduced me to this, and I find it absolutely essential these days. I use it as my main text editing and printing font, at 9 point, and it's clearer and smaller at that font size than anything else I've found. I'll make it available for download as soon as I manage to get access to my own FTP server again. Until then, I'm sure it can be googled.

Environment->Keyboard - I add the following:
- Ctrl+F1: sp_helptext
- Ctrl+3: sp_who2
- Ctrl+4: sp_spaceused

Text Editor->Plaintext - I change the tab spacing to 2. Just my personal preference.

Query Results->SQL Server->Results to Grid - I check "Display results in a separate tab" and "Switch to results tab after the query executes." I prefer having as much screen real estate as possible for both query and results, and I find it quick to switch between tabs with F6.

The Change Connection button - Another one from Mike, I use the Customize menu option to display the Change Connection icon as Image and Text, and change the Name from "C&hange Connection..." to "Chan&ge Connection...". This allows quick switching of the connection via the keyboard shortcut of Alt-G. The default name needs to be changed because Alt-H brings up the Help menu.

I know there are a few others, but I can't recall them at the moment.

Change of Scenery

2007-08-07T07:53:00.000-07:00

It's about that time again. The Big Corporation atmosphere wasn't turning out to be what I wanted, so I'm going small again, moving to [redacted], a hedge fund based in Dallas. I'll be in the New York office, where I'll be working with some very bright people, who will hopefully point out to me how little I know about SQL, databases, and computer science in general, and then help to further my education in these subjects.

I have no idea what the rules are regarding any further disclosure about my current and future employers, so that's all I'm going to say for now.

Precision

2007-06-25T12:04:00.001-07:00

There's probably a better way to do this, but this is what occurred to me:

select
max(
len(
rtrim(
replace(
substring(
cast(position as varchar(50))
, charindex(
'.'
, cast(position as varchar(50)))
, 50)
, '0', '')
)
)
)
from ...

Sprawl

2007-06-05T15:00:00.000-07:00

Just looked at a refreshed copy of our server list, and we have more servers than users.

Standardize!

2007-05-21T07:36:00.000-07:00

I was trying to restore a database backup from one server onto another this morning, and despite the two having the same drive letters for data and logs, I ran into problems because the directory names were different. For a company that runs everything based on rules and standards to the point of excess, this is silly. So I'm making a quick list of SQL Server-related settings that I feel should be standardized across all servers in one's environment. It just makes things easier!

1) Directory structures
2) Default data and log locations
3) Drive letters, if possible
4) Build levels (Service Pack/hotfix)
5) Logins, although these may be different between Dev, QA, and Prod
6) Install locations - I have never found a valid reason to install SQL Server on the C: drive of a non-desktop, but I keep seeing it

I'm sure I'll add to this list as I come up with more obnoxiously un-standardized settings in our environment.

WRITELOG waits

2007-05-16T12:50:00.000-07:00

My team has been assisting another team off and on for several months with performance tuning their overnight DB processes. There are many possible bottlenecks in their process, but since the app writing into the DB is highly complex and multi-tiered, we're trying to focus on the internal DB issues first.

The first big win was dropping triggers from the tables being written to, which resulted in a 20% reduction in overall job time. Now the biggest WAITTYPE during the process is WRITELOG, with about 50-60% of the overall time in both QA and Prod. NETWORKIO is next, and I'd really like to SET NOCOUNT ON, since these writes are created as individual transactions and dropping the count should reduce the NETWORKIO substantially, but that's been problematic so far.

Digging into the WRITELOG waits, the disk queues are pretty low (0-5 range going against 80 or so physical disks on a big Hitachi SAN with a ton of cache), so the storage guys have dismissed the disk as the problem. However, I think it's still the disk, just not in the usual way.

The standard problem is that the disk subsystem runs out of write cache and has to start de-staging data to disk, and the physical disks can't write data as fast as it's coming in. That's not the case here, because the cache on the SAN is huge (70+ GB, according to the SAN guys), it can accept data faster than the logs are created, and it hasn't gotten close to the high watermark, when it starts de-staging. So what's the problem?

Well, in this case, the disks attached to the SQL Server have been attached as a single logical drive, so all the IOs get queued by Windows against that drive. Therein lies the problem, I believe. The SAN is fast, but every operation goes against that same queue, so one slow read for a piece of data not in the cache can slow down all the writelog IOs queued up behind it. This is what I believe is creating the WRITELOG waits.

The proposed solution is simple: add another logical drive dedicated to logs. Even if it's hitting the same physical disks, the addition of another disk queue should allow the log IOs to flow through without being blocked by slower data IOs or data reads. Stay tuned to see if it works.

Addendum: We tried this before at Capital IQ, splitting up sets of disks into multiple metaLUNs and comparing them against the same number of disks in one metaLUN and showed barely any performance gain (within the margin of measurement error). However, the difference between that situation and this one was the amount of cache involved - we were always past cache on the CapIQ SAN, so the disks were the bottleneck. Here I suspect it's the queue.

Cool new clustering stuff

2007-05-08T07:54:00.000-07:00

That I just learned while perusing some Longhorn clustering docs.

1) GPT cluster disks are now supported. This should mean no more 2TB limit.
2) The Supported Hardware list is gone - now anything that validates is officially supported.
3) Clustering of virtual servers is now officially supported, so no more hacking virtual disks to get them to show up in multiple virtual instances. Caveat: The Parallel-SCSI model is deprecated, so it looks like virtual iSCSI is the way to get this to work.

and my personal favorite, quoted straight from the Q&A session:

4) "In Windows Server 2003 the way disks were identified was by the Disk Signature. In Longhorn clustered disks are identified by multiple attributes, including the Disk Signature and the SCSI Inquiry data. If a disk can't be found by one attribute, it will attempted to be found in another way. If found the service will self heal and update it's records of the disk. So in Longhorn disks will self heal and should elimate support issues of disk signatures changing and disks not coming online."

Finally! No more deleting and re-adding cluster disks when cluster services loses them!

Longhorn clustering should be cool. Now I just have to find another cluster to manage.

Mysterious HTTP 400 errors caused by large Kerberos tickets

2007-04-27T13:30:00.000-07:00

First off, I know there's been a lack of DBA material lately. This is because I'm not doing anything interesting, database-wise. I can tell you all about the woes of interfacing SQL Server with an Oracle system to which I have minimal access and no table schema, but that's pretty boring.

The most interesting problem I've fixed lately was the mysterious HTTP 400 errors plaguing one of the users of our system. Briefly, the application consists of a .NET web app that performs business-specific functions and then runs Microstrategy 8.0.1 in an iFrame to run OLAP-style reports. One of the users of the app was regularly experiencing HTTP 400 errors while using the app, and several others were experiencing these errors intermittently.

After reading the links below (and a bunch of others), I checked the HTTP error logs at C:\WINNT\system32\LogFiles\HTTPERR and found that the HTTP 400 errors were associated with a RequestLength error type. According to Microsoft, the default maximum request size is 16384. I installed Ethereal packet capture software on the QA web server and captured packets while the user attempted to run reports. A check of the request packets showed that GET requests for certain pages exceeded 16384 bytes immediately before HTTP 400 errors (see image).

I verified this by capturing packets while I ran reports, and my requests were in the 2000-3000 byte range (see image).

Apparently the larger size of his packets could be caused by an excessively large Kerberos authentication ticket combined with cookies and a long request string. The Kerberos ticket can be larger because of membership in many groups – the user is a member of at least 19 that I can see in Active Directory. This could be temporarily cleared up in some cases by deleting cookies to reduce request size, which is why I believe that other users who have had this problem have had temporary success at clearing it up by deleting their cookies.

According to posts I read on this topic (see the top link for the most useful exchange), using the IP address instead of the hostname will avoid Kerberos ticketing and therefore not cause the problem. This was the case in our situation – when using the IP address of the QA server, the user had no problems running reports. He also had no problems after I added the two registry settings above and cycled the required services. The only alternative I could see was to ask the user to use the IP address of the server instead of the DNS name, but this may be against company policy and would reduce flexibility if we need to change the DNS entry in the future.

Registry keys added under HKLM\System\CurrentControlSet\Services\HTTP\Parameters

MaxFieldLength 32768
MaxRequestBytes 32768

After adding these keys, all users stopped experiencing HTTP 400 errors.

Main links used for reference:

http://www.issociate.de/board/post/314237/HTTP_400_Bad_Request.html
http://support.microsoft.com/kb/820129
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/c7d368f0-f175-4d58-b7a8-977e1d67bd07.mspx?mfr=true

Overspecialization

2007-04-12T15:18:00.000-07:00

My new pet peeve is overspecialization.

My esteemed employer emphasizes (at an institutional level) hiring top talent for very specific positions. I, for example, was hired as a SQL Server 2000 Developer. I took an extensive test that asked all kinds of very specific SQL Server 2000 questions. Fine, I know SQL Server, no problem.

Then I start work and find out that we're not supposed to touch any low-level SQL stuff because we have a DBA Team for that. Oookay, I'd rather do these things myself rather than break my train of thought to email somebody, and then wait for them to do something, but I'll give it a shot.

The first time I had to interact with the DBA team was over an issue with our backups. They weren't working. The DBAs have this dramatically complicated procedure that they install on every SQL Server that goes back and retrieves server metadata from a central DB and then dynamically creates a command to back up your databases. Great, except that it's not flexible enough to keep 3 days worth of backups, which is what we wanted. So they had a DBA Team member write a customized version of their stored proc that would add a timestamp to the files and then delete the old ones after 3 days.

Except it didn't work. I won't go into exactly why, but there were 3 different bugs within a relatively short stored proc. So I took it over, re-wrote it from scratch, and now it works fine, completely independent of the metadata repository which wasn't gaining us anything anyway.

Why did this happen? Because the guy spends all day every day installing SQL on new servers, installing SPs on old ones, setting up replication, and troubleshooting deadlocks. And he's probably been doing this for years. Even if he once was a great developer, skills (like brains) atrophy if unused. Which is not even his fault, because his job is designed to make him really good at the few things he does and not let him do anything else.

This causes problems not only for the individual, but for every team. I'm of the opinion that each team should be capable of functioning as an autonomous unit. If you need an outside expert occasionally for some piece of knowledge completely outside of your usual purview, fine. But a team with the skills to accomplish most dev tasks on its own can stay in high gear during development, instead of moving in fits and starts every time it has to beg somebody else to do something.

Getting back to the individual: of course a broad range of skills is a good thing! For me, learning new skills is one of the main things that keeps me interested in a job. Also, every time I interview somebody who has done one thing for his whole career, I have misgivings about hiring him, because

a) who knows if he can do anything else?
b) he has nothing extra to contribute to the team
c) having only specialized knowledge reduces your creativity when it comes to sticky problems

My favorite part about CapIQ was getting to work on everything, and in fact one of the reasons that I left was that I felt I was getting pigeon-holed into being just the DB hardware guy. Little did I know that other offices could be much more restrictive...

Infrastructure

2007-04-11T08:28:00.000-07:00

I was going to post a comment on Allan Leinwand's post titled Web 2.0 & Death of the Network Engineer on GigaOM, but there were too many already and I didn't feel like reading them all. So I'm writing a quick response over here instead.

The CTO cited in the article knew nothing about the infrastructure details supporting his Web 2.0 venture. I wish him luck, but I think he's on the path to trouble.

The difference between good and great software engineers (or any professional, really) is knowledge of and attention to details. Anyone can write a stored procedure or a C# app, but a great developer's version will be 10% faster and more stable. Over time this can translate into huge savings, especially if that piece of code is run 10,000 times (or in some shops, 10,000 times A DAY).

Infrastructure works similarly. Specified, set up, and maintained properly, a company can squeeze maximum performance and capacity out of its infrastructure. This may not mean much when talking about one $10,000 web server here or there, but let's look at another piece of infrastructure that can make a much bigger difference: database hardware.

Database servers and disks have been by far the most expensive pieces of hardware owned by each team I've worked on, and the most susceptible to underutilization. For example, you can buy a 4-socket quad-core Clovertown server with cycles out the wazoo for under $30k, but if you only put $10k into the disks for it, it's not going to do much. And even if you do put more money into it, you need to know where it's going - will you go with fast, cheap, inflexible SAS storage, a soon-outgrown entry-level SAN like a CX3-10, or a monster DMX3000 but with no budget left over for spindles? Then when you buy it, who's going to have the knowledge to set it up so that it performs properly? If you dump all your DBs on one set of disks, you're potentially giving up a lot of performance, which means you'll have to buy more disks constantly as you scale up your user base.

A CTO who doesn't know these things is bad enough, but one who doesn't care is potentially leading his company into a financial quagmire. If you don't know what you're doing, it's easy to spend millions on infrastructure when you could have spent thousands. And the difference is knowing what you need and knowing how to use it.

Old Thinkpad Nightmares

2007-03-29T11:15:00.000-07:00

This is barely relevant to anything, I just want to get this information out on the net.

If you are having problems installing a fresh copy of Windows XP on an IBM Thinkpad T20 because the windows installer / setup program crashes repeatedly in random places, this is the fix:

Remove the battery.

I don't know why it works, but I'm guessing something to do with either power management drivers or CPU speed-stepping. I spent probably 6 hours thrashing on this stupid thing before locating an obscure post on an MIT newsgroup. Hopefully this blog will be slightly easier to find if anyone else is ever doing this.

Too Much Information

2007-03-15T08:58:00.000-07:00

I've noticed lately that my Google Reader inbox is more full than ever. It's not because of all the new feeds I'm adding - I've actually unsubscribed from a number of them. I don't have stats to back this up, but I believe it's because overall post volume on many blogs and news sites is increasing.

Fine. I like to stay informed, who doesn't?

Except that a lot of these posts are just for the sake of posting. When I read about disaster preparedness on Seeking Alpha and entertainment news (read: gossip) on The Register, something is wrong.

My friend Kevin Ho hates mainstream media. I disagree with him on most of his reasons, but he always points out that when the average person reads a newspaper article, he accepts that the facts and general position presented therein are accurate. Until he reads an article about a topic that he knows something about, and then he says, "What are these idiots talking about? They're missing the point entirely!"

Which is one of the reasons why I love blogs - it allows an expert in a certain subject to post about topics in an area in which he/she has extensive knowledge. And then I can aggregate the topics I care about in my RSS reader. No more amateurs writing about topics in which they have little background or understanding.

Unfortunately, many of these sites seem to be getting a big head about their traffic numbers and have started to assume that their readers live on their sites and go nowhere else, or else are attempting to squeeze more pageviews to generate those all-important ad dollars. At least, those are the possible rationales I've come up with for their explosions in posting, especially about topics outside their areas of expertise. Posts which are at best misguided, and at worst spam.

Even posts within a site's usual topical areas can be annoying if they're frivolous. For example, tech news outlets The Register and The Inquirer both post about 30-40 articles per day. Occasionally I'll click through to one with an interesting title, only to find that it's one paragraph long and contains no real information. Leave it out!

Mind you, I'm okay with a breadth of posts on personal blogs - the volume is low enough that ignoring the chaff doesn't take long, and some of it may even be interesting. And not all big sites are guilty of this posting diarrhea - Pitchfork only posts about music, Tom's Hardware about computer hardware, The Real Deal about real estate. But sites like Jalopnik and GigaOM, you're on notice. Keep up the crappy posts, and I'm dropping your feed.

(Yes, I realize that by posting this I'm guilty of the offense about which I'm complaining. I'm okay with the hypocrisy.)

"Unable to perform a SETUSER to the requested username" error

2007-02-14T08:26:00.000-08:00

This was a new one on me:

Backstory: We have an application that runs a SQL job every time a set of data is checked out. (I didn't design it.) Our development server was recently demolished by the SAN team while they were trying to add a new HBA, so it had to be rebuilt from scratch. After the rebuild, SQL was at version 8.0.2039 (SP4 w/ no hotfixes) and the job wouldn't run. It would spit out the above error each time, which was breaking the app.

This error looked like a permissions issue to me, so I started out by checking the user mapping. We'll call the login that runs the application "CORP\AppUser." This user was a member of dbowner in the database in the job step, so that wasn't the problem. Tried sp_start_job from Query Analyzer using those credentials, which worked, but still produced the error. Tried starting the job using my credentials instead, which produced the same error.

Then I changed the job owner to the same account that SQLAgent runs under, and the job worked. So it was definitely a permissions issue. I decided to check out the specific sequence of events underneath firing off a job, so I ran a Profiler trace and found this...

exec sp_setuserbylogin N'CORP\AppUser', 1

Hmm, what's that? It's not in BOL, and I couldn't find it in the master DB. But clearly SQL is trying to execute it, which might explain why the error it's returning is looking for user ''. I dug around a bit more and found some code to add it back into master.

exec sp_addextendedproc N'sp_setuserbylogin', N'(server internal)'

Voila! As soon as I ran this code, the job worked like a charm. Apparently a botched SP3 or 4 install can miss this proc, which is what probably happened here. So if you come across this error, make sure you've got sp_setuserbylogin properly installed.

Stupid general-purpose connection errors

2007-01-31T15:16:00.000-08:00

Had to debug a stupid error today being thrown by the VBscript we use to deploy DTS packages. Here's the text of the error:

[DBNETLIB][ConnectionRead (WrapperRead()).]

Pretty helpful, huh?

Although it wasn't difficult to debug after I got access to the script source. Here's the line that was producing the error:

Set oPackageSQLServer = oApplication.GetPackageSQLServer(sDestServer, sUser , sPwd , 0)

I tried connecting to the server using the same credentials, which broke with another unhelpful error. Then I logged into the server directly and tried logging in there, where it informed me that the default database for that user was unavailable. Aha! That DB was in the middle of a restore. So I changed the default database and was able to transfer packages.

I'm posting this so that somebody trying to track down this error with the PackageSQLServer object might land here and see this message:

Check your login credentials!

Blocking Chain script

2007-01-30T08:09:00.000-08:00

I had a need to follow some quickly changing blocking chains today, and couldn't find any simple blocking chain scripts that I liked, so I wrote one. It's not the prettiest method, but it's simple, easy to modify, and doesn't rely on lots of hard-to-debug dynamic SQL.

The next updates I'll probably make will be to display only the top-level blockers rather than all blocking chains, and to get rid of the 0s at the end of each blocking chain line.

Here's the SQL:

if object_id('tempdb..#blockers0') is not null
drop table #blockingchain

create table #blockingchain
(
blockingchain varchar(255)
)

insert into #blockingchain
select cast(spid as varchar(5)) + ' -> ' + cast(blocked as varchar(5))
from master..sysprocesses
where blocked <> 0

while exists (select 1 from #blockingchain where cast((substring(right(blockingchain, 5), 1 + charindex('>', right(blockingchain, 5)), 5)) as smallint) <> 0)
begin

update bc set blockingchain = blockingchain + ' -> ' + cast(sp.blocked as varchar(5))
from #blockingchain bc
inner join master..sysprocesses sp
on cast((substring(right(bc.blockingchain, 5), 1 + charindex('>', right(bc.blockingchain, 5)), 5)) as smallint) = sp.spid

end

select * from #blockingchain
order by len(blockingchain) asc,
substring(blockingchain, 1, charindex('-', blockingchain)) asc

Sample output:

Changing the Domain for a SQL 2005 Cluster

2007-01-17T08:31:00.000-08:00

NOTE: It looks like my old DBAIQ blog may be down for the count due to Blogger issues, and I didn't want to lose my post on changing the domain for a SQL 2005 cluster, so I retrieved it from Google's search cache, and am reprinting it here. Ironic that Google was both the cause of its destruction (taking over Blogger and breaking our team blog) and its recovery (search cache).

We're moving our Dev environment to a new domain for some unfathomable corporate reason, and it's going to be a major headache. Among our 20+ SQL Servers in the environment, we've got a few SQL 2005 clusters. According to Microsoft, we can't move them without uninstalling SQL.

http://support.microsoft.com/kb/915846/en-us

Needless to say, this would add more pain to the migration process. It wouldn't be the end of the world, but this is weekend work, and uninstalling and re-installing a couple active/active SQL clusters is just the thing to turn 1 day of work into 2+.

However, I think they're wrong.

I built two virtual machines on VMWare Server, added them to a cluster (see Repeatable Read for instructions on creating shared disks in VMWare) on our current domain, and created some databases. Verified failover capability and connectivity.

Then I moved both nodes to the new domain using the following steps:

1) Take all cluster resources offline (except the quorum, which cannot be taken offline)
2) Stop the cluster service on both nodes
3) Change the cluster service startup type to Manual
4) Change the domain of each machine to the new domain and reboot
5) After reboot, on each machine, change the cluster and SQL service accounts to accounts in the new domain
6) Run gpedit.msc or otherwise access Local Security Policy Settings (see below), and grant the following rights:

Cluster Service Account
Act as part of the operating system
Adjust memory quotas for a process
Debug programs
Increase scheduling priority
Manage auditing and security log
Restore files and directories

SQL Service Account
Adjust memory quotas for a process
Lock pages in memory
Log on as a batch job
Log on as a service
Replace a process level token

7) Add the cluster and SQL service accounts to the local Adminstrators group. NOTE: This should not be necessary for SQL, and I will update this with the minimum required permissions as soon as I sort them out. It is necessary for the cluster account, however.
8) Start the cluster service on both machines
9) Bring cluster resources online
10) Go enjoy the rest of your weekend

If you missed some permissions, the cluster service will likely fail to start with an error 7023 or 1321, and will helpfully output an error in the system log with eventId 1234 that contains a list of the necessary user rights that still need to be assigned. Now that's error reporting!

Comprehensive testing is still pending, but the preliminary results look good. After this process, SQL Server comes online on my test cluster, as do SQL Agent and Fulltext. I don't have any machines on the new domain with SQL Management Studio installed, but I could connect to SQL using osql directly on one of the cluster nodes. If anyone out there has any different experiences or comments, I'd love to hear them.

UPDATE:
My previous post left out one small but significant detail: the domain groups under which the SQL Server service accounts run. When one installs SQL 2005 on a cluster, the setup program requires domain groups to be entered for each service account. So for example:

SQL Server service account: OLDDOMAIN\SQLService
SQL Agent service account: OLDDOMAIN\SQLAgentService
SQL Browser service account: OLDDOMAIN\SQLBrowserService

Domain groups:

OLDDOMAIN\SQLServiceGroup
OLDDOMAIN\SQLAgentServiceGroup
OLDDOMAIN\FullTextGroup

Then it comes time to move your cluster, and you've followed my steps above or done your own hacking, and you've changed the service accounts to NEWDOMAIN\SQLService and so on. But the domain groups remain the same. Your cluster will come online and fail over and operate fine, but you won't be able to change it.

This was made evident when I tried to add a node to an existing cluster after moving it to a new domain. It gave me a message like "Cannot add NEWDOMAIN\SQLService to OLDDOMAIN\SQLServiceGroup." Arrgh. Microsoft had already claimed that this was not supported, so I suppose I shouldn't have been surprised.

So I started searching for the reference to OLDDOMAIN\SQLServiceGroup. And couldn't find it. Not in a config file, or a system table (I know, that was dumb, but I was desperate), or the registry, where I expected to find it. Eventually, I started combing the registry key by key within the SQL hives and came across this in HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup...

(okay, I tried to upload another image here to illustrate, but Blogger hates me, so screw it.)

The keys AGTGroup, FTSGroup, and SQLGroup contain the SIDs for whatever OLDDOMAIN groups you set up when installing SQL. Find the SIDs for your new domain groups (the VBScript below is how I did it), enter those in place of the old ones, restart SQL, and your cluster is moved. You should now be able to add or remove nodes, install hotfixes, etc. You'll need to update the SIDs for each SQL installation (MSSQL.2, MSSQL.3, etc.)

As with any unsupported operation, your mileage may vary, but let me know if you have a different experience with this or you run into additional problems.

SQL Litespeed works better when unregistered

2007-01-17T07:56:00.000-08:00

I recently set up a new DR server that had recently been built from scratch, and the DBAs who set it up didn't have a product key for SQL Litespeed, which we use to backup all of our databases. If you don't know about Litespeed, go check it out, it's handy. So the DBAs set it up using a trial version of the software.

After that, I set up an automatic restore process on the DR servers, but it was taking forever. A backup that would restore in 4-5 hours on the hardware-inferior DR QA server would take 18-20 hours on the Production DR server. This gave us a potentially long recovery time and precluded using the DR machine for reporting or other tasks. I ran a series of diagnostics against the server, and eventually even involved the SAN team, but everything looked fine - disk queues and CPU usage were low, the drives were clearly capable of much higher IOs, etc.

Then one day, the restores sped up. Not by a little bit, but by 10x - from 18 hours to 85-90 minutes. Hey, cool. Except I didn't know what had happened. But it was working and I had other stuff to do, so I left it alone.

A week later, I attempted to take a backup of a DB on that server and got an error message informing me that SQL Litespeed's trial period had expired. I checked that the backups were still restoring, which they were, and sent another request for a Litespeed license.

The request came through a few weeks later, and on the day that Litespeed was officially registered, the backups started taking 18 hours again. Bizarre? Yeah. I'm tempted to uninstall the licensed version and run it in trial mode again unless Quest can give me a good explanation as to why this is happening and how to fix it.

Here's some sample output from Litespeed on our server, with sensitive data redacted. Emphasis is mine.

Executed as user: [user]. Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Processed 29616984 pages for database '[dbname]', file '[dbname]_Data' on file 1. Processed 26037664 pages for database '[dbname]', file '[dbname]_Data2' on file 1. Processed 23631992 pages for database '[dbname]', file '[dbname]_Data3' on file 1. Processed 16 pages for database '[dbname]', file '[dbname]_Log' on file 1. RESTORE DATABASE successfully processed 79286656 pages in 5051.204 seconds (128.586 MB/sec). CPU Seconds: 8190.55 Environment: Intel(R) Xeon(TM) MP CPU 3.00GHz CPUs: 8 logical, 8 cores, 8 physical packages. [SQLSTATE 01000] (Message 1). The step succeeded.

Executed as user: [user]. Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Processed 29616984 pages for database '[dbname]', file '[dbname]_Data' on file 1. Processed 26037664 pages for database '[dbname]', file '[dbname]_Data2' on file 1. Processed 23631992 pages for database '[dbname]', file '[dbname]_Data3' on file 1. Processed 16 pages for database '[dbname]', file '[dbname]_Log' on file 1. RESTORE DATABASE successfully processed 79286656 pages in 69992.264 seconds (9.279 MB/sec). CPU Seconds: 7422.05 Environment: Intel(R) Xeon(TM) MP CPU 3.00GHz CPUs: 8 logical, 8 cores, 8 physical packages. [SQLSTATE 01000] (Message 1). The step succeeded.

Work in Progress: Ira's Home Theater

2006-12-18T10:30:00.000-08:00

As it says in my profile, I'm an audiophile and an equipment junkie, so I had to post(/brag) about my home theater system. It's not the most expensive setup around, but it sounds pretty damn good for anything from Autechre to zydeco, and The Matrix shakes the floor.

Speakers
2x JBL LSR32s
2x Bose 301 (my roommate's old speakers, being used as surrounds)
1x B&W CC6 center channel
1x Aguilar GS212

Amps
~~1x TEAC POS receiver~~
1x Harman Kardon AVR 330
1x Crown K1
1x SWR 750x

Power
1x Monster Power HTS 3600

Interconnects
Tara Labs Prism Omni speaker cables, Canare L-2T2S interconnects, Vampire, Homegrown, and Canare connectors, all hand-terminated with Cardas or Kester solder

Display
~~1x Westinghouse LVM37-w1~~
1x Samsung LN-T5265F

Sources
1x XBox 360
1x HTPC running ~~Windows XP~~ Windows Vista Ultimate
1x Scientific Atlanta 8300 HDC

Miscellaneous
1x Logitech Harmony 890 universal remote
~~2x nameless black wood speaker stands~~
2x American Recording Technologies studio monitor stands

UPDATE (1/15/07):
Picked up a Harman Kardon AVR 330. The 645 was overkill for what I wanted, since I don't use HDMI at this point, and the 330 was a steal. I also got a pair of JBL E30s with it, which I'll either use as surrounds or give to my parents.

UPDATE 2 (2/13/07):
Found a mint B&W CC6 center channel. It's not quite as high-end as I was planning to go, but there's not enough space for a big center speaker, so this one will do nicely until I buy a warehouse. And then I'll just outfit the whole place with Turbosound.

UPDATE 3 (3/12/08):
Well, the Westinghouse TV blew up, but my roommate got his bonus and replaced it with a 52" Samsung that looks quite good on the wall of our new place. I picked up an SWR 750x and an Aguilar GS212 to play my bass through, and that stack is pulling double-duty as a massively overpowered subwoofer. Also got a Monster HTS 3600, mainly so that I could switch off the Crown using a remote. And finally, we got a new couch that sits in the way of the audio rack, so I just HAD to get a Logitech Harmony 890 to control the whole system via RF (and the one-button system configuration is nice, too). Converted all the display cables, too - the 360 now displays via VGA, the Scientific Atlanta PVR via HDMI, and the HTPC via DVI->HDMI.

Lists vs. temp tables

2006-12-18T08:02:00.000-08:00

Dynamic SQL is in heavy use at my new office, and I'm not sure how I feel about a lot of it. For example, one of my coworkers showed me a neat trick by which one can append to a string with each successive row returned in a select statement, like so:

select @list = @list + cast(foo as varchar(10)) + ',' from #test2

But I noticed that this technique was used in many pieces of code in our system, and it bothered me, because I was pretty sure that creating and joining to temp tables would be faster than assembling long strings and then using them with "in" clauses. I wasn't positive, however, so I assembled this test script:

SET NOCOUNT ON

if OBJECT_ID('tempdb..#timing') is not null
drop table #timing

if OBJECT_ID('tempdb..#test1') is not null
drop table #test1

if OBJECT_ID('tempdb..#test2') is not null
drop table #test2

declare
@i int
, @items int

set @items = 1022

create table #timing
(
testrun varchar(100)
, setupStartTime datetime
, setupEndTime datetime
, setupElapsed as datediff(ms, setupStartTime, setupEndTime)
, queryStartTime datetime
, queryEndTime datetime
, queryElapsed as datediff(ms, queryStartTime, queryEndTime)
, totalElapsed as datediff(ms, setupStartTime, setupEndTime) + datediff(ms, queryStartTime, queryEndTime)
)

select
identity(int, 10403, 96) as foo
, cast('some text' as varchar(20)) as bar
into
#test1

set @i = 0

while @i < @items begin insert into #test1 select 'cowabunga' set @i = @i + 1 end insert into #timing (testRun, setupStartTime) values ('Temp Table', getDate()) select foo into #test2 from #test1 create clustered index ix on #test2 (foo) update #timing set setupEndTime = getDate() where testRun = 'Temp Table' create clustered index ix on #test1 (foo) insert into #timing (testRun, setupStartTime) values ('List', getDate()) declare @list varchar(7000), @curID int set @list = '(' select @curID = min(foo) from #test2 --while @curID is not null --begin -- select @list = @list + cast(@curId as varchar(10)) + ',' -- select @curId = min(foo) from #test2 where foo > @curID
--end

select @list = @list + cast(foo as varchar(10)) + ',' from #test2

set @list = substring(@list, 0, len(@list) - 1) + ')'

declare @sql varchar(8000)
set @sql = 'select * from #test1 where foo in ' + @list

update #timing
set setupEndTime = getDate()
where testRun = 'List'

print(@sql)

update #timing
set queryStartTime = getDate()
where testRun = 'List'

exec (@sql)

update #timing
set queryEndTime = getDate()
where testRun = 'List'

update #timing
set queryStartTime = getDate()
where testRun = 'Temp Table'

select t1.* from #test1 t1
inner join #test2 t2
on t1.foo = t2.foo

update #timing
set queryEndTime = getDate()
where testRun = 'Temp Table'

select * from #timing

The results of this test vary somewhat, so it would be best to run the whole thing in a loop to assemble a statistically valid set of data, but the difference between the two methods is so pronounced that the SD doesn't really matter. Ready for the (approximate) difference?

Temp tables are 3x faster then strings.

Here's a typical result of this script running on our QA server, an 8-CPU 20-GB box running SQL 2000 build 2171:

Running it repeatedly yielded similar results with some higher outliers, but very few lower ones. The next problem will be how to propose changing our coding practices and re-writing a lot of stored procs. Stay tuned for that one, and possibly an entry about my subsequent de-hiring.

Shrinking log files of formerly replicated DBs

2006-12-12T12:16:00.000-08:00

To start, I should note that a) there's probably a better way to do this, and b) I'm sure this is already out on the net somewhere, possibly even on my old blog as a post from Mike Forman. But this way works, it's fast, and I couldn't Google it when I needed it, so I'm posting it again.

In our QA Static and Dev environments, we have static copies of a DB that is a replication subscriber in Production. This DB gets refreshed (restored) from Production every few weeks, or when I break everything and have to restore a fresh copy. When restored, it still has transactions marked as pending replication in the log, and so the log cannot be shrunk.

Naturally, we don't have as much space as we'd like in QA and Dev, and since the DB in question is static, it doesn't need the 50 GB transaction log that it has in Production. So I wanted to shrink the transaction log, but was prevented from doing so by this error:

Cannot shrink log file 4 (Test_Log1) because all logical log files are in use.

So how do you fix this?

My solution was to fool (read: hack) the system tables to make SQL think this DB was still a replication subscriber, then use sp_repldone to set all the transactions to done. This short script will do it, although I recommend you BE CAREFUL, because this is the master database you're messing with.

sp_configure 'allow updates', 1
reconfigure with override

update master.dbo.sysdatabases set category = 1 --
where dbid = 10 -- replace with appropriate dbid for your DB

use Test
GO

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

update master.dbo.sysdatabases set category = 0
where dbid = 10 -- ditto

sp_configure 'allow updates', 0
reconfigure with override

After the sp_repldone, you'll be able to shrink the log file to your heart's content. There may be another way to do this, such as attaching the DB without its log file, but I'm not sure that works, and besides, I like hacking system tables.

Retrieve queries when SMS crashes

2006-12-07T06:35:00.000-08:00

I can never remember where the .sql temp files are stored by SQL Management Studio so that I can retrieve queries-in-progress when it crashes. So I'm blogging this so I can find it later.

This time, at least, the files were in the following path:

C:\Documents and Settings\[user name]\My Documents\SQL Server Management Studio\Backup Files\Solution1

Start me up

2006-12-04T08:27:00.000-08:00

I'm firing up another DBA blog, one that I'll hopefully be able to hang onto this time. I used to be the primary (or at least most prolific) contributor to DBA IQ, but I've moved on from that blog for two reasons. First, Google forced me to sign up for the Blogger beta, which removed my account from that blog, and second, I've changed jobs, and DBA IQ was set up as a sounding board for the stellar DBA team led by Rob Liander at Capital IQ.

I'm now at Bank of America working on the Global Credit Products team, where I'll be working on many of the same SQL development and management challenges that I did at Capital IQ and hopefully writing about the interesting ones. I won't go into the details of why I changed positions, but I will say that I knocked 30 minutes off my daily commute! Seriously, though, CapIQ is a great company (ahem, division of McGraw-Hill) and is full of cool and very bright people. Everyone should read Dan McKinley's development-focused blog and DBA IQ, which will hopefully continue to be updated by Rob, Mike Forman, and the other senior DBAs there.

I've mostly been working on getting up to speed here so far, although I've authored and implemented a new DR strategy for my team and I've been working on performance tuning. Stay tuned for more interesting SQL Server posts soon.