Investigating locking and deadlocking with %%lockres%%

I’ve just read a very good, very deep, and very interesting blog post by James Rowland-Jones. In the post, James investigates some locking issues using a variety of means and explains about the undocumented %%lockres%% function with you can use to figure out what the wait resource will be for individual table rows (basically the lock-hash value). He also shows something I’ve known about but never seen before – how the lock hash algorithm isn’t perfect and can actually cause lock collisions where you wouldn’t expect them – and how to mitigate the problem.

Excellent post and well worth reading. Check it out at The Curious Case of the Dubious Deadlock and the Not So Logical Lock.

We all do stupid things when we’re young

I've just been posting a few links on Facebook and began reminiscing about my days at Edinburgh University. I spent a lot of my 3rd and 4th years there running the Tardis unix cluster, a fantastic experience which fed directly into my addiction for putting things together, taking things apart, and generally dinking about with electronic stuff. But the story of how I got there is very interesting and worth recounting.

One night in 1992 (I was 20) I was in the James Clark Maxwell Building (the Computer Science building on the King's Building's campus of Edinburgh University). I was playing around with one of the SunOs workstations when I found that when it powered on, it would come up with a boot prompt. At the boot prompt, I tried single-user booting it, and it worked. Suddenly I'm root on this workstation. Cool. So I rlogin'd to one of the usual multi-user servers and to my utter astonishment I was logged in as root. So being young and stupid, I found some of my friends and logged them off remotely as a prank.

Then I tried to figure out what was going on. How could I possibly be root on this server? I look around in the workstation I was on and discovered a .rhosts file in the root directory. For those of you who've never administered a Unix box (yes, I was a Unix weenie at University, then a VMS geek while I worked for DEC, before finally succumbing to the inevitable and winding up a Windows guru when I moved to Microsoft) a .rhosts file is a file that says 'if you're user X on this server, then you can remote login as X to all the servers listed in this file without having to give a password'. They're incredibly dangerous and a massive security hole. And all the workstations had them at the time. So – that's what was going on. I tried a few more workstations and pulled a few more stunts on the servers (being late at night I figured it would be cool).

The next afternoon I come to campus and try to login. "Your account has been suspended, please contact the Head of Computer Services".

Stupid stupid stupid.

I had visions of being kicked out of University and my hopes of a cool career in computers being dashed. I didn't do anything that day. I telephoned my Director of Studies (staff-member mentor through University – don't know what the US equivalent is called) and laid out the sordid details. Of course he knew about this and was very stern. He advised me to go to campus tomorrow and fess up. Which I did.

The Head of Computing Services had that poster of a Bald Eagle on his door that says 'I *am* smiling'. I was terrified. I knocked on the door and went in and his opening words were "You can only be Paul Randal". Oh shit I thought. The only reason he knew it was me was that the security door swipe-card system log displayed on real-time on a little workstation in his office, and my swipe late at night was the last one, and just about to scroll off the monitor screen. Foiled by the old 'use your real credentials to get in the building' and then do something stupid mistake.

Stupid stupid stupid.

They thought they were under a major hacker attack (which unbeknown to be had actually happened two months previously – so they thought that was me too). Once I explained, and convinced him that I was very very sorry, he actually smiled and said it was a neat trick that no-one had thought of. Turns out he really just wanted to know how I did it. He actually became a great friend of mine, was my thesis advisor in my final year, set me up to run the Tardis cluster, and recommended me to DEC. Good results can come from very stupid actions. Very rarely.

Anyway, for all those out there who I've said your advice is bad, or you did something daft, or anything like that: I've been there too. Live and learn. 

PS And Mum & Dad, given that you read my blog, I think this is the first time you're hearing about this, 17 years later. Heh heh <sheepish grin>.

SQL Server 2008 Data Compression whitepaper published

The SQLCAT team have published another excellent whitepaper – this time the long-awaited one on the SQL Server 2008 data compression feature. Thirteen people inside and outside Microsoft (including me) provided technical reviews and the authors (Sanjay Mishra along with Marcel van der Holst, Peter Carlin, and Sunil Agarwal) did a great job. I remember leading an effort back in 2005 to see if we (the SQL team) could get some form of data compression into SQL Server 2005 RTM (no, obviously) so it's great to see data compression out there and now with top-class proscriptive guidance on when and how to use it.

Bottom line: don't just go an turn it on without analyzing whether you'll get a decent compression ratio and your workload is suited to data compression.

You can get to the whitepaper at: Data Compression: Strategy, Capacity Planning and Best Practices and I'll add it to our Whitepaper Links page.