SQL Server Index Analysis

May 19, 2016 © Kramii (filed under SQL Server)

A query for the level of fragmentation of indexes in the current database, with usage since last restart of SQL.

	ps.index_id AS [Index ID], 
	b.name AS [Index Name],
	ps.avg_fragmentation_in_percent AS [Fragmentation (%)],
	ps.avg_page_space_used_in_percent AS [Page Space (%)],
	st.USER_SEEKS AS [User Seeks],
	st.USER_SCANS AS [User Scans],
	st.USER_LOOKUPS AS [User Lookups],
	st.USER_UPDATES AS [User Updates],
	ps.page_count AS [Page Count]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
	INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID 
		AND ps.index_id = b.index_id
	INNER JOIN sys.databases d ON ps.database_id = d.database_id
		AND ps.index_id = st.index_id
		AND ps.database_id = st.database_id
WHERE ps.database_id = DB_ID() 
	and ps.index_id >0
	--and b.Name = 'PK_FilterInformationGroup'
ORDER BY ps.avg_fragmentation_in_percent DESC

I can’t take credit for this code, but it has proven useful, so I’m posting it here.

Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »

Almost Implementing a Standard

October 2, 2015 © Kramii (filed under Customer Service, Humour)

Several of our suppliers claim to support standards, but in reality they support a subset of those standards under certain circumstances.

…which is another way of saying that they don’t really support them at all.

True, you get what you expect most of the time, but not quite always, and when you don’t it can be a problem.

Consider what would if other businesses worked that way. Imagine you regularly ordered a taxi, to take you to the airport or somewhere. And most time, a taxi turns up, where you want it, when you want it. But just occasionally, instead of a taxi, they send something else. Like a golden-feathered chicken, for example.

Not quite what you want when you have a plane to catch, and certainly not something you’d pay for.

Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »

When Disaster Recovery is a Disaster Waiting to Happen

October 2, 2015 © Kramii (filed under Humour)

We were performing the last minute checks prior to a Disaster Recovery excersise when my colleage and I noticed that there was a problem. We realised that there was a significant risk of data loss if we proceeded with the planned fail-over.

Some of the team wanted to continue anyway. We were, after all, trying to simulate a real disaster situation. If a real disaster were to occur, they argued, we would have to deal with similar consequences.

Debating the merits of this position, I suggested that:

There is a difference between falling off a bridge and throwing yourself off one. The first is an unfortunate accident, the second is downright recklessness.

In agreement, my colleage replid that:

Continuing with the excercise would be like throwing yourself off a bridge now just in case you fall off one later!

A deeper analysis of the risk suggested that, if we were careful, we could throw ourselves off this particular bridge relatively safely. Although there was likely to be data loss, there was a manual process available that would recover the missing records.

So, off we jumped…

Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »

The Liskov Substitution Principle (LSP)

June 22, 2015 © Kramii (filed under Design)

A simple introduction to the Liskov Substitution Principle, a design guideline that helps us with inherritance.


Despite the fancy name, it is actually quite a simple idea.

One way to explain it is this:

If A is a type of B, then everything we can say about B is also true of A.

Which means that:

If S is a subtype of T, then objects of type T in a program may be replaced with objects of type S without breaking anything.


A Snake is an Animal

If Snake is a subtype of Animal then you can replace Animal with Snake and nothing should break.

In other words, anything that is true of Animal should also be true of Snake. This means that you can take any instance of Animal in your program and substitute it with a Snake.

A Programmer is a Human

Similarly, LSP says that, if a Programmer is a type of Human , then everything we can say about a Human is also true of a Programmer .

This is unlikely to be true in reality, but we’re talking about a computer system, here! 😉

So we can take any instance of Human and replace it with a Programmer.


The challenge here is that we intuitively model like this. As a result, we tend not to think about it…. and so occasionally we make a mistake.

Code Examples

For example, imagine a program that models Square as a sub-type of Rectangle:

Bad Design

A programmer has defined a Rectangle class as follows:
[sourcecode language=”c#”]
class Rectangle {
protected int _height;
public virtual int Height {
get {return _height;}
set {_height = value;}
protected int _width;
public virtual int Width{
get {return _width;}
set {_width = value;}
Later, someone else adds the Square class to the application. In this new code, the Square is a sub-class of Rectangle. At first sight, this seems reasonable, because every school child knows that a Square is just a special kind of Rectangle:

[sourcecode language=”c#”]
class Square: Rectangle {
public int Height {
get {return _height;}
set {_height = value; _width = value;}
public int Width {
get {return _width;}
set {_width = value; _height = value; }
Unfortunately for the second programmer, this design violates LSP. We can see this because there are circumstances when you can’t substitute a Square for a Rectangle.

Consider a (single-threaded) client of the Rectangle class that has a method that does this:

  1. Set the value of Height to 200
  2. Set the value of Width to 400
  3. Read back the value of Height

The result is obviously 200. This is the value expected, because changing the Width of a rectangle normally has no effect on its height.

Now consider what would happen if the same method is passed a Square. This is reasonable, because we have said that a Square is a Rectangle:

  1. Set the value of Height to 200
  2. Set the value of Width to 400
  3. Read back the value of Height

Now the result is 400! Obviously, this is a different result from the same code.

Now, you could argue that it is obvious that changing the Width of a Square would obviously change its Height. But in this instance, the client has no special knowledge about Squares. It has been told that it will be passed Rectangles, and it has every right to expect that the the thing it has been passed will therefore act in the same way as any other Rectangle.

Interestingly, the issues caused by violating LSP often manifest themselves in a different part of the code from the one that has changed. In this case, the change was to add the Square class as a sub-class of Rectangle, but the issue came to light in a client of the Rectangle class. In complex systems, issues like this can easily go unnoticed, and can be very hard to debug.

Implementing LSP

It is important to think carefully about heirarchies of classes in your application. Some ways to avoid violations of LSP include:

  • Avoid inherritance altogether – favour composition over inherritance
  • Keep class heirarchies shallow

See also:


The LSP says that, if we model a Programmer as a Type of Human, then (within the domain of our program) everything we can say about Human can also be said about Programmer.

Violations of LSP:

  • Can be difficult to spot, especially because we generally don’t think about LSP because it is “obvious”
  • Can result in subtle bugs in code
  • Can cause bugs that manifest themeslves in different parts of the code than the ones that have changed

Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »

Design Topics

June 2, 2015 © Kramii (filed under Design)

A contents page for my recent articles on software design.

Previous articles have commented on:

Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »

SOLID Principles: Summary

June 2, 2015 © Kramii (filed under Design)

An extrmely simple summary of the SOLID Principles:


As systems scale, the SOLID Principles become more important.

The Single Responsibility Principle

This is to do with the design of individual models. It says that a modules (classes) should have one and only one role to play (ie. it should have only one reason to change).

The Open-Closed Principle

You should be able to extend a module, but changes to a module shouldn’t effect existing clients.

The Liskov Substitution Principle

Best explained by example:

If, in our program, we define a lion as a kind of cat, then everything that could reasonably be said about a cat can also be said about a lion. In other words, we can treat a lion as if it was a cat… because it is one.

This is rarely violated in in code that I have seen, but has been the source of subtle and unpleasant issues when it has.

The Interface Segragation Principle

This principle is about the way that modules interact with other modules. It says that a module shouldn’t tell everyone everything about itself, but only the things that they need to know.

The Dependency Inversion Principle

Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »

The Open-Closed Principle (OCP)

June 2, 2015 © Kramii (filed under Design)

A basic explaination of the Open-Closed Principle of software design (one of the five SOLID principles).

Confusion About the Open-Closed Principle

There appears to be some confusion about what it actually is, and how it should be applied:

There appear to be several different interpretation of the principle – some only subtly distinct, others seemingly almost unrelated.Jon Skeet, C# Guru

Why is this principle especially hard to understand?

The principle was devised by B. Meyer and popularized by Robert C. Martin (AKA Uncle Bob). Sadly, Meyer’s text isn’t widely available, and Martin’s explanations are less crisp as his other writing.

The principle is often summarized like this:

Software entities (classes, modules, functions, etc.) should be open for extension, but closed for modification.Bertrand Meyer, quoted on Wikipedia

But this doesn’t help unless we know exactly what is meant by “open”, “closed”, “extension” and “modification”.

The Worst Definition

Based on poor explanation of their ideas, some people seem to think it means:

A class should no change (except to fix bugs), and only inheritance can be used to extend it.

But this definition is misleading in that it appears to misrepresent what Meyer and Martin had in mind. Moreover, in practice it appears to be devastatingly restrictive and, consequentially, downright unhelpful.

A Working Definition

As far as I can tell, the basic idea of the Open Closed Principle is that:

You should be able to extend a module, but changes to a module shouldn’t effect existing clients.

In other words, you create a stable interface – a contract – between modules.

Based on this definition, we can revisit Meyer’s definition. So, by “open for extension” we mean that new functions can added. By “closed to modification”, we mean that those new features won’t modify existing functionality.

Practical Outcomes

Some of the implications of this are that:

  • No existing functions can be removed, renamed, have their signatures changed, because then all their clients will break.
  • You shouldn’t add a new abstract methods in a class, because then all its descendants will break.
  • You can extend a class using, for example, the strategy pattern, inheritance, or by adding new methods.

Reality Check

No software design can ever be completely consistent with this principle and coninue to accommodate changes. The principle is aspirational rather than prescriptive. By their nature, design decisions invariably leave some avenues of extension “closed” and others “open”. This implies that we are required to make best-guesses about future changes based on what we know today. In the words of Kent Beck, “there are no permanently closed abstractions”.

Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »

Cohesion, Coupling and Conceptual Integrity

April 24, 2015 © Kramii (filed under Design)

Good design involves High Cohesion, Low Coupling and Conceptual Integrity.

Michelangelo divided his mural into panels: separate, free-standing areas, each of which tells a story.Andrew Hunt and David Thomas, The Art in Computer Programming, 2001

High Cohesion

Each part (class, function, service, tool etc.) of a system should do one thing, and do it well.

Low Coupling

Unrelated things should remain self-contained.

Conceptual Integrity

Together, all parts of the system tell the same story.

Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »

Bad Things about Good Design

April 15, 2015 © Kramii (filed under Design)

Good design comes at a price, and it is important to understand these costs if we seek to build and maintain well designed code.

What are the Costs?

Some examples:

Up-Front Design Costs

It costs more to design code well from the outset than it does just to throw code at a problem until you have a solution. There is a tension between over-engineering in the name of good design and spaghetti coding in the name of just getting the job done as fast as possible.

Refactoring Costs

As time goes by, good designs tend to rot. It can take considerable effort to maintain good design.

The counter to this is that poorly desined  code becomes increasingly difficult to maintain, reducing its value and the value that can be added to it.

Increased Cost of Grokking a Code Base

The smaller your modules, the more there are and the higher your levels of abstraction. But more layers in your code mean that new developers will have a harder time understanding your code base.

In addition, a good design often maps onto a business domain. As a result, a developer may need deep domain knowledge in order to understand the reasoning behind many of the design descisions that have been taken. It takes time and effort (ie. investment) for developers to gain this knowledge.

Of course, developers are generally happier working on clean, well designed code, and happy are less likely to change jobs. Nevertheless, this can be a considerable cost to some organisations.

Higher Levels of Abstraction Lead to Slower Code

In general, the more modules you have, the higher the cost of those modules communicating with each other, the slower your code will run. Big balls of mud are often fast.

Of course, not all code needs to be especially fast. Moreover, well designed code is easier to optimize. Furthermore, it can be cheaper to add better hardware than reduce developer productivity: developer time is usually more expensive than CPU cycles.

Finally, good design often does involve more abstraction… but not always.

Better Design Results in More Lines of Code

Many langages require significant amounts of “boiler plate” (standard code) in the header and footer of each module, typicallly indicating such things as the module’s scope, namespace and name.

Better design often calls for more descriptive names, which are often longer than poorly thought through names.

It is arguable that “lines of code” is a poor measure of developer productivity, but even so, each additional character is a codebase has a cost associated with it.

Is Good Design Worth the Cost?

In my view, not always. I believe that the purpose of design is to maximise value delivered by a solution over the lifetime of a problem. So, if a solution is only intended to be used once, never changed, used by a single user and very short and simple then there is little to be gained by putting too much time into making its code base perfect. On the other hand, if a solution is intended to be with an organisation for any length of time, then overall costs can be reduced if the principles of good design are applied.

A common principle of good design is to avoid repetition, and this often involves increasing abstraction. I have often witnessed a pattern amongst developers, according to their experience: Novice developers apply little abstraction, more experienced tendwards over-abstraction (using, for example, all the design patterns they can think of on every project), whereas a true expert will apply appropriate, pragmatic levels of abstraction.

So, how do you know if good enough is really good enough? How can you tell if you are you guilding the lilly or adding real business value?

I don’t have all the answers, but then neither does anyone else!

Clearly, a great starting point is the recognition that this is an important question, but a hard one.

Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »

STUPID Principles

April 15, 2015 © Kramii (filed under Design)

Software design improves whe you avoid doing the wrong thing.

What are the STUPID Principles?

Poor Object Oriented design is achieved when people follow the S.T.U.P.I.D. principles of software design:

(S)tuff that is Global

For example, global variables, static methods, and singletons.

The problems here include:

  • Uncontrolled access to global entities
  • Tight coupling throughout the code base
  • Broken encapsulation
  • Extension is often hard
  • Changes can effect everything

(T)ight Coupling

In other words, high levels of interdependency between modules. Tight coupling is often caused by dependency on specifics of an implementation.

(U)nclear Naming

Often a symptom of not knowing what a module is actually for.

(P)remature Optimization

Sacrificing code clarity (not to say developer time) to making things faster… even if nobody cares if they’re slow.

(I)nvisible Dependencies

When, for example, using a class means that you have to take a whole lot of other classes along for the ride… even if you don’t actually need them.

(D)uplication and Redundancy

For example:

  • Over-generalization (I think eveyone does this)
  • Commented-out code
  • Repetition and duplication and other types of repetition
  • Functionality that the user doesn’t value


Share Freely

You are welcome to share this with your friends.
Be Sociable, Share!
Leave a comment »