One of my development teams needed a mechanism for identifying the value of a key that was part of a lock (don’t ask). I’d never tried doing that before. Obviously if you hit the DMV sys.dm_os_tran_locks you can see the hash of the key in the resource_description column. But how to pull the value back. After some research, I first found this excellent article by the late, great, Ken Henderson (I really wish he was still around). The article outlined, among other things, the use of an undocumented “virtual” column called %%lockres%%. Some more searching then uncovered this great article by James Rowland-Jones, AKA Claypole. He described how, in a very high volume system, he used %%lockres%% to identify the source of a deadlock as the internal mechanisms that SQL Server uses to manage locks, the hash of the key. Oh, and he opened an incident on Connect, which seems to be closed, but vote on it anyway, I did. %%lockres%% is also covered in Kalen Delaney’s excellent book on SQL Server 2008 Internals and even warrants a bit of discussion in Professional SQL Server 2008, but that was written by James Rowland-Jones, so I’m not sure it counts.

In the meantime, while I was investigating this stuff, evidently the development team was looking into it on their own. They came to the same set of resources and decided to use the virtual column as part of their real-time, transactional application. Yeah, an undocumented “virtual” column going into a major application. Since I would probably be unable to do anything about this, I decided to at least look into how this thing behaves so I can be aware of what types of problems I might run into.

First, a simple query:

SELECTa.City
–,%%lockres%%
FROM Person.Address AS a
WHERE a.AddressID = 432

If you run this query and take a look at the execution plan you’ll see a nice clean clustered index seek, just as you would suspect. If you take away the comment and run it again, the execution plan is identical. On the version of AdventureWorks2008 currently installed on my machine, I get two page reads, regardless of whether or not I include %%lockres%% or not. With the comments removed, it returns the hash of the primary key: (b0004e040bc2). This looks pretty painless, free even.

If we want to see %%lockres%% in action, it’s not too difficult:

BEGIN TRAN
UPDATE Person.Address
SET City = ‘dude’
WHERE AddressID = 432;
–ROLLBACK TRAN

Obviously this will put a key lock on that row in the table. If I just select against sys.dm_os_tran_locks, the data returned looks like this:

resource_type   resource_description   resource_associated_entity_id   request_mode
KEY                       (b0004e040bc2)            72057594043564032                      X 

The original request from the development team was for a way to get the key value back when you know that a table is locked, such as the case here. I wrote this simple query to make that happen:

SELECT a.AddressID
FROM person.address(NOLOCK) AS a
JOIN sys.dm_tran_locks AS dtl
ON a.%%lockres%% = dtl.resource_description
WHERE dtl.resource_type = ‘KEY’

This query works and returns our key value of 432 just as you would want. But, take a look at the execution plan:

Yes, that’s a clustered index (or table, same thing) scan followed by a Sort followed by a merge join, processing 19614 rows to return one. But hey, it was only 341 reads. To say the least, I’m not excited about seeing this in a production system. This was explicitly cautioned in Kalen Delaney’s book. While it appears that the remote scan operator, which is how the DMV is accessed in this case, is 59% of the operation, that’s the estimated cost and has been pointed out before, isn’t the best measure of real cost in the system.

The development team went off and developed their own query, they had said they were looking for the key value, but evidently they were looking for who was holding the lock on a particular key value:

SELECT s.nt_user_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s
on l.request_session_id = s.session_id
inner join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
where OBJECT_NAME(p.object_id) = ‘Address’ and
l.resource_description in (select %%lockres%%
from person.Address(NOLOCK) a WHERE a.AddressID = 432)

I actually had to adjust their query just a bit to get it to work correctly, but basically they had the right idea. Here’s the final execution plan:

This was still not terribly inspiring a thing to think about running in a production system although it only had one scan and seven reads. Whether or not putting this in a transactional system is a good idea, it certainly adds yet another tool, albeit an undocumented one, to the tool belt.

Aaron Bertrand showed up to teach us tips and tricks for SQL Server Management Studio. We had to move our meeting night because of a conflict at our wonderful host, New England Tech. But we still had 12 people show up. For SNESSUG, that was a good turnout. I gave away some swag that I had received from Microsoft and some stuff that we had purchased. Bribary works (at least that’s my theory, so feel free to bribe me, whenever).

Aaron’s presentation was great. He’s just showing nothing but meat. There’s no fluff. He’s just showing a series of tips & tricks in SSMS and explains why you want to use them. First revelation, -nosplash has no effect whatsoever on load time. He called it a placebo. It just kept going from there. Aaron’s stated goal was to make everyone in the audience say “wow” or “cool” at some point during the presentation. I’m pretty sure he succeeded. The first one that got a lot of people is when he demonstrated setting the connection color so you can track different connections visually on your screen. My personal one was the Registered Servers import list so you can maintain a common list, move copies around, share registered server lists within your team… I love learning stuff at a good presentation.

Oh yeah, and everyone said “wow” or “cool” at least once.

I got a little distracted after lunch and was reading through some of the various bloggers reactions to the decision to keep the Summit in Seattle for the forseeable future. I enjoyed Brent Ozar’s take on the situation, but the thing that struck me square in the eyes and inspired me to add one more post of my own, was a comment on Brent’s post by Aaron. Scroll down and read it. Here’s the part that really made an impact:

This whole situation is making me less interested in supporting the organization. As a relative newcomer to PASS and having never attended a Summit, I’m turned off by the “come talk to Microsoft employees” stance. I’d rather them say come and talk to (or sing Karaoke with?) cool guys like Brent Ozar and others in the community who deal with real world issues day to day.

The arguments for keeping the Summit in Seattle largely boiled down to cost & access to Microsoft. Cost has been beat about the face & neck by me & others, and I can’t add anything else even marginally intelligent to the conversation, so I’ll shut up on that. Microsoft. Yeah, having access to Microsoft makes the Summit pretty cool (not to mention useful, a couple of my favorite sessions were from Microsoft presenters). PASS stands for the Professional Association of SQL Server users. The summit is produced by PASS in order to meet it’s own goals which are ”dedicated to supporting, educating, and promoting the Microsoft SQL Server community.” (Yeah, silly me, I go and read the organization’s web site).

So here’s my comment. Is the community PASS is trying to support, educate and promote better represented by a bunch of Microsoft developers, or, to quote Aaron, “Brent Ozar and others in the community who deal with real world issues day to day?”

And, lest I take an inappropriate beating, I’m neither knocking Microsoft developers, nor saying that they’re not a part of the community. My job and, to a small degree, my life, wouldn’t be the same without those people. I just want to make sure any rocks tossed my way are thrown for the right reasons.

There has been some discussion recently around the location of the PASS Summit. The debate was centered on the results from a recent survey hosted by PASS. Today’s Community Connector has an editorial by the PASS President, Rushabh Mehta, explaining why those of us on the East Coast will be flying to the other side of the continent for the next two years, and supplying the results of the survey

I get why they’re doing this. Microsoft really will commit more resources to an event that is in their back yard. I get it. I also understand, that those of us who consider the PASS Summit a big part of our “community” are actually in the minority. Most people attending the Summit aren’t involved in the community, aren’t interested in networking, and go to the conference to learn something and then go back to their hotel room. They want to see Microsoft developers, not community members. I understand. I also know that the excellent support team provided through the management company is also headquartered out there. We might see fewer of them at the conference, and those few will cost more to fly in. I get it.

But…

With the economy shrinking, and no end in sight, budgets are getting tightened. Travel expenses are being examined closely where I work and justification for a trip is more difficult than it was previously. Cutting a few corners here & there, including reducing the cost of a plane flight,  might make a difference. For example, doing a quick search on Travelocity, no details, accepting defaults, meeting half-way, in Dallas, would cost $216 instead of $399. That’s almost $200 in savings. Even if Rushabh is right and we’d have to increase the cost, let’s say $150/attendee, that’s still offset by the flight.

Still, those are savings at the margins, would that offset it enough to prevent people from travelling? Maybe, some people. But, there’s also the flight itself to consider. Not everyone is Gail Shaw, prepared to cross continents, oceans, raging rivers, burning deserts, and French strike lines to get to the Summit. For some people, that hike out to Seattle, ignore the cost, Microsoft, the community, is too much. Would moving it to Atlanta or Dallas or wherever guarantee a larger percentage of attendee’s? Nope, probably not, but I’ll bet you you’d see a different set of attendee’s and I’ll bet you the attendance wouldn’t drop. Because remember, it’s not just the flying time or the travel costs or the time away from work (yay). It’s time away from the family. Based on the results of the survey, 800 (51%) of the 1500 plus think a short flight is very or somewhat important, where as only about 380 (25%) thought it wasn’t. The vast majority of people responding to the survey are in the Eastern & Central time zones (585 & 458 compared to 331 in the Western zone). I could actually be wrong about the number of attendees.

And let’s just mention, Microsoft is holding some sort of get together in June. It seems to be fairly well attended by Microsoft people and, oh, look at that, it’s in New Orleans, not Seattle. I guess it is possible to get some Microsoft involvement in other places if Microsoft wants to.

I’m not on the board, so it’s easy for me to snipe from the sidelines, but based on the noise level, and the fact that 588 people thought having a conference on the east coast would make it more likely that they would attend, with only 405 making it less likely, and the fact that that number goes up to 639 more likely if the Summit was in the center of the country, I’m not alone in thinking that the PASS Community Summit should move around a bit more than we’ve been doing lately.

On another note, the release of the survey results was… poorly handled. The board, probably for good reasons, tends to play things very close to the vest. I think, at least in this case, too close. I appreciate the need to keep valuable information away from the competition. However, since this is a community organization, and one that is largely run by volunteers, I think the board really ought to err on too much communication instead of too little.

Finally, assuming anyone has made it this far, I want to thank the board and Rushabh for releasing this information. I think explaining how they made their decision and providing the basis for that in the results is absolutely the right thing to do. Did it apparently, or even evidently, require poking from people outside the board? Maybe, but they still did it and deserve the credit for taking the right action.

That’s it. End of the pointless, wandering diatribe. Go about your lives citizens. Hopefully, I’ll get another session or two accepted this year and I’ll see you all Seattle (again).

Two years old. In March of 2008 I received a whopping 96 visits. I’m up to 1900 so far this month. I’d call that a positive growth trend. Thanks for stopping by, especially if you’ve been here more than once.

Who the heck is MacGyver? Television program you say? Hang on. I need to visit imdb.com…

Oh, the late eighties… Yeah, I wasn’t watching TV in the late eighties. Actually I didn’t own a TV in the late eighties. But reading a few of the plot lines (you guys watched this?) I get the idea.

Nuts. I don’t think I’m MacGyver. Can’t we just buy something to fix the problem?

Honestly, the only thing that comes to mind was the time when I needed to get alerts when jobs failed, but I couldn’t install DBMail on the server because our admin types didn’t want mail clients on our machines. What to do? Use event forwarding. Instead of setting up the mail client on a server, I got it installed on a different machine, then forwarded events to it and let it take care of sending emails. Yeah, I know, almost as exciting as a MacGyver episode (based on reading a couple of synopsis’, synopsi, whatever).

So, I don’t have a good MacGyver story. But, what I do have is a ready set of skills, just in case I need to be MacGyver. What skills you may ask? Let me ask you, do you run backups from TSQL or from Management Studio. I don’t mean every time, but most of the time. For that matter, how much do you run CREATE or ALTER or DROP through the TSQL window and how much do you know the right-click pop-up menu of most of the database objects by heart? If you’re only using the SSMS window to administer your databases, then your MacGyver skills are going to be missing when the time comes to put them to work. I came into the database world through programming. My skills are rusty, almost to the point of immobility, but I can actually write functional (mostly) C# code and I’m working on my PowerShell skills. Is the only language you know T-SQL? Best get started on, at least, PowerShell so when your MacGyver moment comes along, you’ll be ready. Slightly harder to measure, but are you open to new ideas, especially if they seem a bit crazy or weird or MacGyverish? Assuming we’re not putting our production data at risk, I’ll try any scheme or methodology you want to take a swing at. I’ve done Agile, SCRUM, Feature-Driven Development, Test Driven Development, all on the database. I’m willing to try things. I call it being a bit of a cowboy, but you could call it being a little bit MacGyver (and I will in this case, just to keep with the theme). In addition to all this, I read, a lot, and study, a lot, to try to learn new things and keep my brain facile (as much as possible). Do you have twenty years of experience or one year of experience repeated twenty times?

There’s a self-help saying, control the mind and the body will follow (in Kenpo, we say control the head and the body will follow, same thing, just outwardly directed). You can’t be MacGyver if you don’t have that MacGyver mind-set and the skills it takes to use it. So, while I’m waiting for my great MacGyver story, I’ll keep practicing my skills.

Tagging… I’m going with the one person that I suspect may have never seen the show before, Gail Shaw.

I’m right in the middle of moving one of our databases from Oracle to SQL Server (and I just love saying that, over & over). Evidently the most common practice in Oracle is to leave all the tables as heaps. That’s a tad bit problematic approach for SQL Server. We moved the structure from Oracle to SQL Server with almost no changes and now we’re cleaning up the mess fixing the structure iterating the design. I needed to drop & recreate 250 plus indexes. 

Remember, I’m lazy. A few minutes bouncing around the web and looking at the wonderful book “SQL Server 2008 Administration with Windows Powershell” and I put together a simple little script that generates a script for dropping and recreating all the primary keys on the tables and, as a bonus, changes them to clustered (yes, I know, all primary don’t have to be clustered and there are possibly other good candidate keys… to misquote someone, compromises were made). Here’s the script: 

param
([string] $Server, [string] $Database, [string] $filepath)
 # Connect to the server  
 [reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 
 $Scripter= New-Object("Microsoft.SqlServer.Management.Smo.Scripter") 
  

$srv =New-Object "Microsoft.SqlServer.Management.Smo.Server" "$Server"
$db = $srv.Databases["$Database"] 
$Scripter.Server = $srv 
 

# define the output

 

$filepath =$filepath+"test.sql"
 
$scrcontent  = "use [$Database]" + "`r`n"+"GO"+"`r`n" 

$Scripter
.Options.DriPrimaryKey = $true
 
$Scripter.Options.ScriptDrops = $true 
 
$scriptingOptions

= New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions  
$scriptingOptions.ScriptDrops = $true 
  
foreach ($Table in $db.Tables)
{
foreach($Index in $Table.Indexes)
{

if ($Index.IndexKeyType -eq "DriPrimaryKey" ) 

{
$scrcontent=$scrcontent + $Index.Script($scriptingOptions) +"`r`n"+"Go"+"`r`n"
$scrcontent=$scrcontent + $Index.script() +"`r`n"+"Go"+"`r`n"} } }





 

The only issue I ran into was the check to see if the index was clustered didn’t work when I tried passing the property type, so I had to use a string. No big deal, but it’s pretty cool how  what might have been a hard task was reduced to nothing. I’m digging PowerShell.

If you’re not reading Buck Woody’s blog, why not? Today he posted a helpful hint for getting performance counters directly out of PowerShell v2. I’ll add a little bit to the hint, don’t try running this on your XP boxes. It doesn’t hurt anything, but you get a helpful little message “Get-Counter : This cmdlet can only run on Vista and above.”

What the heck is wrong with you?

Still interested? Fine. I’ll tell you my take on this whole business. I’m only an expert on this if you take the adage that the expert is the guy that’s a page head of you in reading a book. To date I have published two full books and three chapters in a third. I can easily think of enough people who all have more experience than that with book writing that I’d have to take off both shoes to count them all.

Is anyone still reading? Cool. So you have the desire to write a book? Let me pop your first bubble. You will make very little money. This bears repeating. You will make very little money. If you were to figure out your hourly rate for writing this book, something I’ve never had the guts to do, you’ll cry yourself to sleep at night for being such a total fool to agree to write a book.

Still here? Let me pop your second bubble. Your home life/free time/family time/sleep cycle/excercise will suffer. Yes, that’s right. You’re getting paid pennies and you’re suffering for it.

Glutton for punishment? OK. Here’s how you do it. Do you have an idea for a book? If not, stop here and go and think of one. I’m assuming a technical readership since this is a geek blog about geek topics by a geek. Do you think you know everything there is to know about… oh, I don’t know, SQL Server 2008 hierarchy data, and you’re convinced you can fill 200+ pages talking about it? Great! You’re on your way. Pick a publisher. I’m not providing links or suggestions here. If you don’t know any book publishers that means you’re not reading books. If you don’t read them, I don’t think you should write them. Stop here and go read a technical book, preferably one of mine.

Have a publisher in mind? Go to their web site. Every one I’ve looked at has a “write for us” web page. Follow the directions there and submit your idea. You’re now on your way. I’m sure things are different for the big name authors or authors outside the technical sphere, but since you don’t have a name and you’re writing technical books, that’s pretty much all you need to do. You don’t need an agent or a lawyer. You’re going to get a non-negotiable contract from the publisher and you’re going to sign it because you want to write a book. Assuming they like your idea. Ah, but you’re not done with simply submitting the idea. You need to do two other things, and these won’t be easy. You need to define your market. Are there more than 20 people interested in reading a book on the hierarchy data type? Sound easy? It is a bit. Here’s a more challenging one for you. You also need to define how your book will stand out from the rest. If Itzik or Kalen has written 50 pages on hierarchy data types… ready for it… how will your 50 pages be better than theirs?

Stopped crying? Other options are to write articles for publication in places like SQL Server Central or Simple-Talk or SQL Server Standard (and I know the editor from SQL Server Standard most intimately, he needs articles). A few articles about the hierarchy data type and you’ll be a recognized expert. Now, if one of the publishers decides, “Hey, we could really use a book on the hiearchy data type,” and they happen to notice your article, you might get invited to write for them. Or, someone else writing a book needs a chapter on the hiearchy data type, they may contact you to help out. Or, if you’re constantly hanging out on one of the online discussion sites answering detailed questions about the hiearchy data type, the publisher or another author may find you and ask you to write a chapter.

Anyone still here? Of the two approaches, I’d suggest writing articles first. That’s going to do two things for you. First, it gets your name out there and you’ll get noticed. That’s how I did it. Second, it’ll let you decide if you like writing. The first time you get an article back that’s gone through a serious technical edit and it looks like someone has questioned every other word you wrote and the comments, while kind, bash through your arguments and ideas like a wrecking ball… you get to decide how much you like writing. A book is 50 times worse.

Want more? That’s about all there is. There are lots of details when it comes to the act of writing the book, how versions are managed, the writing schedule, promotion (if you get any), how you split the oodles of cash with your co-authors, if any (authors I mean, there will be very little cash), that sort of thing.  Networking is a useful tool. I wrote my second book because I happened to be at a publishing party for an author and I ran into his editor. A short conversation and a couple of emails later… I’m losing sleep and skipping exercise for very little money. Having friends and contacts will lead toward getting partnered up for a book. That’s how you can get tapped to write a chapter or three.

Still reading or have you all long ago stopped reading because this book writing thing is way too much of a pain?

Would I do it again? In a heart beat.

I realize I’m prejudiced, being one of those evil DBA’s & all, but I can’t help but agree with him. It’s a short post, but worth the read.

I’m getting my first look at a full-fledged nHibernate database developed by consultants for our company. I thought I’d share my initial impressions. I’ll be capturing trace events from the database over the next couple of weeks, so I’ll be following up on the behavior of nHibernate within this database as well.

The first thing I saw & thought was, “Foreign key constraints. Thank the gods.” That really is good news. I was frankly concerned that they might go with the “let the code handle it” approach. There are quite a few null columns. I’m also seeing tons & tons of nvarchar(255) which must the default string size. Lots of bit fields too. They also used bigint in a lot of places too. None of this is definitively good or bad, just observations.

There are tables that lack a primary key. That raises a bit of a concern. The primary keys I’ve looked at have all been clustered, which isn’t too problematic since that’s probably the primary access path. There are a few unique constraints on some of the tables too.

Overall though, I don’t see anything that, at first glance, makes me want to run screaming from the room (or pick up a big stick & start looking for developers).  The devil is no doubt in the details. Time to get started looking at the trace events.