Indexing Foreign Keys – should SQL Server do that automatically?

I’ve been doing another performance tuning job today and it highlighted to me once again that problem that a lack of indexes on foreign key columns can bring.

By adding indexes on the foreign keys on three tables, we saw a reduction of 87% in total I/O load on the server. There are other aspects of the system that I’m now working on but it really struck me that having SQL Server do this by default would avoid a lot of apparent performance problems. It would have to be one of the most common indexing issues that I see in my work.

What do you think? Should SQL Server simply do this by default when you declare a foreign key reference?

Would you like a Visual Studio MSDN Premium License for a Year?

In the latest podcasts at www.sqldownunder.com, we’re giving away a Visual Studio MSDN Premium Online license for a year, courtesy of our friends at Microsoft. It’s a simple:

* you need to visit Quest‘s site for info on Litespeed via the simple link http://shrinkster.com/10fn,

* read the article covering 10 things DBAs often don’t know about Litespeed

* email a description of two of them to questATsqldownunder.com (replacing AT with @) before August 17th 2008

I’ll select one from those that come in correctly and give that person the license. Easy enough?

Thanks to all at Microsoft.

Regards,

Greg

Geek: New Phone -> Yes it’s an iPhone

I’ve also been looking around for a new phone. It became much more urgent last week after I dropped my iMate.

 

After trying lots of phones, I ended up opting for an iPhone 3G. And after using it for a few days, couldn’t be happier with it. Well, not quite true, if it worked as a NextG modem as well, it would be even better as the data plans are quite costly here in Australia.

 

I’ve heard people raving about the user interface and wondered if it was just all hype. It’s not. The user interface is just so much better than what I’ve used on Windows Mobile devices. I didn’t know how I’d go just using a finger instead of a stylus but I really don’t miss it at all. The setup was beyond simple. When I compare what I had to do to get it to sync with Outlook with the struggles I had with my iMate and Windows Mobile Device Center on Vista, I was left shaking my head as to how simple it was. In fact, it left me thinking that it was exactly the experience that I should have had with WMDC but didn’t and gave me faith that these things don’t have to be so hard for the end user.

 

If I’ve had a hassle with anything, it’s been my preconceptions about how the interface should work, based on a Windows Mobile background. Instead of working out how to give you a Windows-like interface in a small package, they seem to have just spent time thinking about how a really good phone would work. First and foremost, a phone needs to be a good phone.

 

In general, if you just do the most logical thing, you find that’s how the phone works. For example, I spent a short while trying to work out how to call a phone number on the screen. I was looking for selections and menu options, etc. But you simply point at something that looks like a phone number and it asks if you want to call it. It was only my right-click (or hold and tap) background that had me looking for something else.

 

I also really like the fact that the interface isn’t designed for people with super-eyesight. It’s clear, even outdoors.

 

For the car, I purchased a SuperTooth Light (BlueTooth) hands-free unit. It was $99AUD and I found it trivial to setup and use. (http://www.supertooth.net/light.html).

 

Both recommended !

 

Now the decision is whether or not I want my next notebook to be a Mac so I can dual boot and do development for the iPhone. I took a good look at what they offer developers the other day and it’s pretty slick also. I watched a number of the getting started videos and it has me wanting to try it. I’ve got such a good feeling about what could be done with this quality of interface.

Geek: New Monitor Dell 24inch Ultrasharp

I’ve been chasing around for a while for a new monitor. I find I need to work at 1900×1200 but my eyesight doesn’t want to let me work well on 19inch screens.

Fellow MVP Ken Schaefer suggested trying a Dell 24inch Ultrasharp (http://accessories.us.dell.com/sna/products/Monitors/productdetail.aspx?c=us&l=en&s=dhs&cs=19&sku=320-6272).

It arrived today. What can I say? I love it.

Recommended!

Parallel Programming In TSQL: Is It In Our Future?

I saw an interesting videopod from Steve Jones the other day where he noted that upcoming processors would have many more cores than now and hoping that DBAs won’t have to learn to write multiprocessor-style code. What was also interesting was the way he described that he learned some of this style of coding but later when he came back to it, he realised how much he thought he knew but didn’t.

For languages like T-SQL, we don’t have inherent support for multi-threading. In fact, the only trace I can see of this in T-SQL today is the ability to have multiple readers on a service broker queue. In general, we haven’t needed this because SQL Server systems constantly have many requests thrown at them concurrently anyway and there is a natural style of parallelism happening.

But if languages need to evolve, I think it’s important that wherever possible, that it’s the languages that work out how to parallelise the code, not the developer. I often mention to people that in one interview I did with the late Dr Jim Gray, he noted that people learning to write multithreaded code tend to go through three phases:

1. when it all just looks like magic and you don’t understand any of it.

2. when you think you understand it.

3. when you get wise.

That’s very insightful and has stuck with me ever since. I’ve written a bunch of multithreaded code over the years and my experience exactly parallels (pun intended) the phases he described. Getting it right is much, much harder than it looks.

Book: Pro SQL Server Disaster Recovery – James Luetkehoelter

I caught up with James Luetkehoelter at the PASS Summit in Germany a few months back. He sent me a copy of his new book from APress: Pro SQL Server Disaster Recovery.

I managed to finish reading it while heading back from CodeCampSA in Adelaide today (which was a good solid event again – excellent work Peter Griffiths!). I quite enjoyed the book and I like James’ writing style. It’s quite conversational and I could hear him talking to me as I read it.

The content is a pretty solid coverage of backup/recovery, mirroring, clustering, snapshots, disaster recovery planning, etc. I would have to say I wouldn’t agree with everything James said in the book (he mentions that a number of his colleagues wouldn’t anyway) but overall it’s pretty solid common sense.

I did find a few copy-edit problems that I don’t normally see on APress books. That puzzled me. For example, knowing how to restore a master database is an important skill. The book says “just enter the commands in figure 3.9” but figure 3.9 is a different version of the screenshot from figure 3.7 and nothing to do with the master database. Fortunately, these sorts of things were few and far between and didn’t detract much from the overall book.

Recommended! (particularly for those wanting more of a discussion on “what” to do and “why”, rather than “how”).

 

Finally – A Compelling Demonstration of WPF in a Business Application

Those that I’ve discussed WPF with over the years will know that I think that Microsoft really struggles to demonstrate *business* value for WPF. I’ve attended many sessions where I’ve been shown things like the ability to show a video in the taskbar while you flip the taskbar around the screen 🙁

Congratulations to Billy Hollis for his WPF business application presentation done on Carl Franklin’s DNR TV recently. If you’ve been wondering how WPF might add value to a business application, watch the first 24 minutes or so of this show:

http://perseus.franklins.net/dnrtvplayer/player.aspx?ShowNum=0115

Book: The Microsoft Data Warehouse Toolkit : Joy Mundy and Warren Thornthwaite

There are a number of key books that I’ve missed reading over the years, in areas that interest me. Recently, I’ve been fixing that. One that is always discussed is The Microsoft Data Warehouse Toolkit by Joy Mundy and Warren Thornthwaite from the Kimball Group.

I would have to say I enjoyed reading it. It is a large book at over 700 pages and a couple of inches thick so it took a while to get through.

I found the chapter “Designing the Business Process Dimensional Model” to be the most compelling part of the book. I can’t say I totally agreed with all the advice in there but it does touch the key topics that need to be considered.

I did find the constant references throughout the book that provided mappings from the Microsoft technologies to the Kimball Method terminology quite irritating. The assumption is that you’ve already bought into the Kimball Method and are now moving to the Microsoft BI stack. While I’m sure that’s valid for many, it’s certainly not the case for many others that would be the target audience for the book. For those who aren’t into the Kimball Method, the dual terminology adds an extra (and unnecessary) burden while reading the material.

The parts of the book I most struggled with were the areas where advice was given on relational database aspects of SQL Server and on hardware and system configuration. While I’m sure they felt it important to include information on this, it clearly isn’t an area of expertise for the authors. I suspect it would have been better to have left this material out and referred instead to more targeted books on the topics.

While the “not-totally-Microsoft-oriented” approach of the book might be seen as a benefit, it’s also a bit of a downside. I find that with quite a few books that I’m reading at present. I’m not sure if the authors would have written the same ideas and recommendations if their opinions weren’t somewhat colored by their experience with other toolsets beforehand ie: if they were coming to the Microsoft BI toolset with fresh eyes.

Regardless, it is a classic book that’s worth a look by anyone working in this area. The section on dimensional modelling and its terminology would make a good starting point for many wanting to get a handle on the most common concepts.

A SQL Server Meme

Well I was called out by Tibor Karaszi, so here goes:

How old were you when you first started programming?

I’d say I was about 19 when I started. I remember in 1976 that I was at University of Queensland. I was doing an honours degree in physics and maths and didn’t have the slightest interest in those computing people that spent their lunch hours looking at great piles of 15×11 listings. By the next year, I was one of them.

How did you get started in programming?

I was doing RPG work on some mini-computers, some work on micros (first TRS-80 model) and some mainframe work (Fujitsu X8, IBM assembler and COBOL and JCL, etc.) I was completely fascinated in what you could do with each type of machine. I loved the interactivity of the micro. By 1978, I’d bought a Cromemco multi-user Z-80 system running MPM and with the old bank-switched memory. The biggest hassle was finding a hard drive (around 5 and 10 meg at the time) that would work reliably for any length of time.

What was your first language?

RPG

What was the first real program you wrote?

Must have been some RPG code for a client of the consultant I was working with/learning from.

What languages have you used since you started programming?

I’m guessing now (and am sure I’ll miss some) but the ones I’ve used in any substantial amount would  be:

RPG, COBOL, Assembler, Pascal, Modula 2, C, C#, C++, Basic (many variations), SQL, SPL, Algol, Simula 

What was your first professional programming gig?

It’d be RPG coding for a local consultancy.

If you knew then what you know now, would you have started programming?

Yes

If there is one thing you learned along the way that you would tell new developers, what would it be?

Have something passionate that you’re working on all the time, even if it isn’t what you do for a living.

What’s the most fun you’ve ever had … programming?

As Tibor mentioned, it would have been the voyage of discovery in the early days. However, the playing around I did with operating system internals while working on MPE for HP was really fascinating. 

Who are you calling out?

Peter DeBetta, Kevin Kline, Craig Utley, Fernando Guerrero