Author: way0utwest

I stumbled across this clustered index test from Hugo Kornelis recently from a post in the forums. I think I’d read it before, but it was surprising to me again this week as I read through it.

The test seems to show that if you are changing data over time with UPDATEs on a table, which is a heap, then you can not only fragment the table, but you can dramatically impact performance. His test, which seems reasonable, shows that you can end up with 10 times the rows being read.

I’m not sure that this is representative and your mileage may vary. You could end up with more or less scans, but what if you had double the number of rows read? That’s still a substantial impact to performance in your application. You might not notice if it you don’t run queries that force table scans often, but based on the quality of code I see people asking about on the forums, that’s not likely.

Instead, add a clustered index. The original post was from someone worried about fragmentation on a series of tables for a third party application and they couldn’t “change anything”. To me that’s dumb. Adding a clustered index, in the hope of improving performance, isn’t really a big change. And it can easily be undone if it worsens performance in that application.

It’s not likely, but it could happen. However a third party that doesn’t want to allow a clustered index to be added to tables in their application because it might cause issues is a seriously ignorant company on how a clustered index works.

I missed my first newsletter today. It wasn’t until I went to schedule tomorrow’s that I realized I hadn’t sent last nights. I had prepped it, done the work, gotten the editorial written, podcast done, articles scheduled. Things were ready to go, but I didn’t get the newsletter out. I think that I was just too busy with life yesterday, kids back in school and having projects due and for some reason when I knocked off work, I forgot the newsletter.

And when I get home from karate, I was exhausted, and didn’t check the senders to see if they were working. No backup from the home office either, and I suspect everyone there was busy after having two days off (Fri/Mon) in the UK.

There’s one goal I didn’t set this year, which I did last year: not missing any newsletters. I missed a couple last year, and once again, missed one this year.

I hate doing that, and now tonight I feel like a bit of a failure. Somewhere I need a check that will alert me if I don’t have one scheduled by some xx time and ping my phone.

I’ve always seen business cards with my phone and business address, which for the past 8 years has been my home. However concerns about identity stuff have prompted me to consider changing things. I’ve been meaning to get cards without the address and today I did it.

I got a mailing from VistaPrint, offering free business cards (you pay shipping) and so I updated my business card to have my Twitter and FaceBook addresses, but removed my home. I left my cell on there as it’s a business line for me. $10 in shipping, and avoiding about 30 other offers VistaPrint gives you (I like those options, just don’t need them now), I have business cards on the way.

It’s not often that I’ve had the need to trace back a client to their host computer, but there are time it’s handy. I had thought that the easy way to do this in the past was with sp_who.

However that’s not correct. I checked back in the SQL Server 2000 BOL entry for sp_who and it has hostname, but not IP. That could be the same, but not necessarily.

I saw a post where someone had listed this code

declare @IPAddress as varchar(15)
declare @data XML
set @data = EVENTDATA()
set @IPAddress = @data.value(‘(/EVENT_INSTANCE/ClientHost)[1]’, ‘nvarchar(15)’)

That’s interesting. It uses the EVENTDATA(), which tracks data about events. I presume that the IP is in the clienthost all the time, and that it likely comes from the view below, but I can’t find documentation on that. The XSD schema for EventData is beyond me.

I kept thinking this had to be stored in a system table/view somewhere, and sure enough I found sys.dm_exec_sessions. It actually contains the client IP and port, so you can trace things down at a more detailed connection level.

On my local instance it doesn’t seem to work great, but if I connect remotely, I see an IP.