Sunday 15 November 2009

Indexes cost money

All this industry talk about cloud computing is, more than ever, placing under the spotlight the true cost of IT infrastructure. Combined with the current economic climate, this is sparkling an interesting and refreshing debate on what is the actual cost of supporting and growing an IT infrastructure. And more and more, a realistic cost breakdown of how much each piece of the infrastructure is emerging.

And one of the surprises of this exercise is the exploding costs of mass storage.  Data centre storage is under an increasing pressure. Data Warehouses keep storing more and more data points and history. ERPs continue to take on more business processes. Web front ends generate incredible amounts of detailed user activity information. Graphics and multimedia file formats are taxing file servers and document repositories at the far end of their capacity. Mail is now an accepted medium for conducting business, and mailbox size is increasing without an apparent limit.

On top of that, legal regulations demand that most everything has to be kept during five, seven, or in extreme cases I've seen, fourteen years.

Technology is also evolving to catch up with all those demands. From SAN/NAS storage, tiered data management, storage outsourcing, you name it. But, except for some notable examples (Amazon EBS, Google) the business IT environment at large is failing to meet that demand.

This is even more difficult to explain to the non-IT executive levels, since at the consumer level storage, there is an expanding and ever increasing amount of USB and firewire devices offering amazing capacities at prices that are getting lower and lower. Not to mention that the average desktop PC now comes with a 1GB hard disk as the standard size.

Usually, the point that non-IT business folks fail to realize is that it is cheap and easy to increase the raw amount of storage available.  Not so cheap is to back it up daily, keep that archive during five years and make sure that is recoverable.

My usual response in the typical elevator conversation about storage cost with business types (yes, those conversations actually happen) is to answer, "yes, you can purchase a couple of TB for a few bucks. Now, tell me what you do if those disks fail. Will you be making back ups? Will you be able to recover them in five years? Will you be able to provide a replacement in 4 hours if they fail?" It's not that they are trying to outsmart you, it's simply that all those dimensions of the support infrastructure costs are simply invisible for them.

Oh, I hear you cry, is this one of these posts of late that do not have anything to do with database tuning? No, this time my point is actually related to DB tuning. In fact, all of the above is essential to introduce the topic. The topic is "just create an index and this will be faster"

This is so wrong, and I think that I've blogged it before, that deserves to be refuted in all its possible dimensions. First, yes, there are situations where it actually makes sense to create an index.

But an index is not free. First, an extra index in your database will penalize inserts and updates on the affected table. Second, the index takes storage space, that expensive and scarce resource. Third, sometimes there are better alternatives to adding an index. Two of the typical are:

- Caching values read from the database, instead of querying for them each time they are needed. This has to do with a tendency during maintenance to prefer always adding new code rather than change existing code. The more difficult to maintain the code is, the higher the chance that maintainers simply do not want to touch it. This is safer for them, but also imposes a performance overhead as it cannot take advantage of work already done by the existing code.

- Converting procedural code to SQL data set code. Query execution plans favor working with whole tables and result sets over slowly iterating over them.

So, next time someone proposes an index on that pesky 4TB table, ask, are there any other alternatives? Tuning exercises should not provide a single answer to a problem, but a balanced cost/benefit analysis of different alternatives. It's not enough to answer "just create an index", you have to say "creating an index is the best option as opposed to...."  That puts the conversation with the paying customer in the right level.

Saturday 7 November 2009

The tragedy of tactical vs. strategic solutions

I've noticed lately an increased tendency for the so-called "Tactical Solutions" in big organizations. Those are usually focusing their IS efforts around a few key systems, termed the "Strategic" ones. Strategic is synonym of big, complex and ambitious.

I've always heard the term "tactical solution" used to denote something that was put together quickly, without paying too much attention to the strategic aspects of an organization. Its data model does not try to encompass every possible process in the world, and its functionality is a fit for purpose for the existing business process, without taking into account the expected changes introduced by the business in the future. It's something built  knowing that it is going to be replaced in the near future by the all encompassing, strategic application, but solves now today's problem.

Fact is, the all encompassing, strategic application does not always arrive. Well, in fact, it's quite likely that it will never arrive. Two are the strongest contributing factors:

Plans are never completely executed: forget about five year long plans, face it. Nobody in any organization even remotely knows what they will be doing in three years time. Plans change as the economic environment changes, as competition changes, as legislation changes , as technology changes and as boards of directors change. When I see one of those five year long plans, I always do as if I accept them and cannot but admire the lack of grounding in reality that the people that make them and those that believe them have.

Complex solutions are likely to fail: the more complex and ambitious the strategic solution is, the more it is likely to fail to deliver. This happens also outside IS and is a inevitable side effect of having to deal with a complex problem. It's a fact. Complex development fails, at a much higher rate than simpler development. So even if people sticks to the plan and do their best to execute them, there is a built-in high probability of failure in those three to five year plans.

If you think I'm exagerating, I've seen tactical solutions that were intended to last 12 months being used for five years. And did the strategic solution arrived after five years? No, the tactical solution was replaced by another tactical solution, yes, you guessed right, intended to last only 12 months.

Of course, this is an extreme example, and I'm ready to admit that there are organizations (very few) that are actually able to stick to the plan and execute it during three to five years. But rarely the tactical solution is effectively replaced. Similar to the demise of dinosaurs, the tactical solution lower size and complexity allows it to change and adapt much faster and better to the environment than the monolithic and big strategic solution. And the point is, the "tactical solution" ends up playing a key role in the application landscape, and thus becomes strategic. As with any strategic system, it ends having to comply with the same requirements as its bigger brothers. It has to be fault tolerant, it has to scale up, it has to have change control, it has to be changed to adapt to business requirements and it has to be auditable.

What? You're not able to build those attributes into your tactical solution? Didn't you think about those requirements while building it and it is now heniuosly expensive to do it? Well done, you built actually what is a tactical solution. Instead, what if your tactical solution is fault tolerant, can scale up, is under change control, is easily changed to fit new requirements and is auditable? Congratulations, you built an strategic solution much cheaper and quicker than the strategic solution is going to be built.

Whatever the result, it's time to stop using the term "tactical solution" as something quick and dirty developed for the short term and instead start describing it as what they really are. Like the Ubuntu joke (Ubuntu is an ancient African word that means "I don't know how to install Debian") "Tactical Solution" means "I don't have time for the big guys to tackle my problem and I'm solving it myself"

Just take care of making it strategic while building it.