Category Archives: Everyday coding

Easy win – index candidates on SQL Server 2005

Below is my real life story telling how easy it can be to find index candidates if you use your tools.

This weekend we deployed a new version of a medium/large solution to the production environment for one of our customers. Normally this is not a big issue, as we are always prepared to roll back to the previous version – the one currently in production. The deployment and configuration processes are well-prepared and highly automated to minimize faults and late-night-bug-hunting-frenzy.

After the deployment is complete we smoke test the setup to make sure that it is configured correctly and all is sound.

This weekend after the successful deployment the smoke test failed miserably. We received TimeOutExceptions all over. It was coming from the user repository service.

We decided to investigate a little before the decision of aborting the deployment of the new version and therefore rolling back to the previous version.

The steps to resolve the issue is what I want to share with you.

A quick look in the source repository showed that almost no changes to the user repository service – none that could cause TimeOutExceptions.

With the SQL Server Profiler it was easy to spot the cause of the exception.

This simple select statement was isolated and copied into the Management Studio to view the Actual Execution Plan. The execution plan did show much, but by execution the below statement which instructs the SQL Server not to execute the Transact-SQL  statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document.


The SQL Server will include loads in information and one of these are suggested indexes which you will not see with the graphical execution plan in Management Studio. Look for the MissingIndex elements:

  <missingindexgroup Impact="96.6222">
    <missingindex Database="&#91;DOIPEI&#93;" Schema="&#91;dbo&#93;" Table="&#91;OrganisationalUnit_Stack&#93;">
      <columngroup Usage="EQUALITY">
        <column Name="&#91;org_pkid&#93;" ColumnId="2" /> 

By creating this single index on [DOIPEI].[dbo]. [OrganisationalUnit_Stack]. [org_pkid] the TimeOutExceptions disappeared, as the query no longer took minutes but milliseconds.

We were able to continue and successfully deploy the new version of the solution with only minimal disruption and a delay of only half an hour. Why the index where missing is still a mystery.

It makes all the difference in the world if you know the capabilities of your tools at hand.

.Net debugger visualizers

The .Net framework 2.0 gave some great tools for debugging – they are a great help when writing code.

Some of these features are the DebuggerDisplay and DebuggerStepThrough attributes. I seldom use any of them, as I prefer to just overriding the ToString method instead of using the DebuggerDisplay attribute. This makes it easy to instrument the code too, as I can reuse the ToString method implementation for tracing.

Another feature is the ability to develop custom debugger visualizers. They enable smart developers to build their own. I find some really useful, especially the WCF visualizer. But there a many – check out this list of custom .Net debugger visualizers.

These small enhancements make it so easy to get an overview of the current state, instead of inspecting properties.

Garbage Collection Flavors

Why should you care about Garbage Collection? Well, it may very well improve performance for your applications.

The .Net framework garbage collector can operate in different modes depending on platform and requirements. There are three modes available for .Net framework version 2.0 and 3.0. Each of these modes is tailored for specific situations.

Concurrent Workstation Garbage Collection

This mode is optimized for interactivity by frequent short garbage collects. Each collect is split up into smaller pieces and are interleaved with the managed applications threads. This improves responsiveness at the cost of CPU cycles. This is ideal for interactive desktop applications where freezing application is an annoyance for the users and ideal CPU time is abundant when waiting for user input. Concurrent workstation mode improves the usability with perceived performance.

Note that interactive GC only applies for Gen2 (full heap) collects because Gen0 and Gen1 collects are in nature very fast.

Non-concurrent Workstation Garbage Collection

Non-concurrent workstation mode works by suspending managed application threads when a GC is initiated. It provides better throughput but might appear as application hiccups where everything freezes for the users.

Server Garbage Collection

In server mode a managed heap and a dedicated garbage collector thread is created for each CPU. This means the each CPU allocates memory in its own heap therefore results in lock-free allocation. When a collect is initiated all the managed application threads are suspended and all the GC threads collect in parallel.
Another thing to note is that the size of the managed heap segments is larger in server mode than workstation mode. A segment is the unit of which the memory is allocated on the managed heap.

It is possible to choose the type of GC for a managed application in the configuration file. Under the <Runtime> element add one of the below three settings and depending on the number of CPU, the garbage collector will run in the configured mode.Garbage Collection type settings

Running a standalone managed application the GC mode is by default concurrent workstation. Managed application hosts like ASP.Net and SQLCLR run with Server GC by default.

If you want to know more about how the GC works, read the blog entries “Using GC Efficiently”
by Maoni.

Favor structured exception handling

I was surprised this last week, when a discussion about error handling, whether or not to use structured exceptions in .Net, surfaced at a customer where I am helping them to complete a project. I will not name the company, but it is a Danish division of a large American IT corporation with around 80.000 employees.

The discussion was between one of their architects, a couple of others and me. The company where accustomed to used return codes and the architect argued in favor of return codes. I didn’t think in this day and age that organizations still use return codes with languages that have build-in support for structured exception handling.

An exception is an event that occurs during the execution of a program, designed to handle the occurrence of some exceptional condition which changes the normal flow of execution. Exceptions travel out of band and propagate through the call stack until an exception handler catches the exception. Structured exceptions use the Try…Catch…Finally syntax.
Below is a make believe example of a simple system calling two methods:

static void Main(string[] args)
  Order myOrder = new Order();
  decimal yield;

    yield = CalculateYield(myOrder);
  catch (Exception ex)
  //** Exception handling **//

public static decimal CalculateYield(Order order)
  // calculate yield
  return decimal.MaxValue;
public static void UpdateStatus(Order order)
  // update status

If an exception is thrown in the method CalculateYield the flow is short-circuited and the method UpdateStatus is never called. With return codes the developer has to manually check if the method failed and manually propagate the return code up the stack.

Return codes imposes on method signature by requiring the return type to be a return code and potentially use a parameter as return type. This clutters the method signature and does not make the method functionality apparent. Notice the confusing method signature of CalculateYield in the following code:

static void Main(string[] args)
  Order myOrder = new Order();
  decimal yield = 0;
  int returnCode = 0;

  returnCode = CalculateYield(myOrder, yield);

  if (returnCode < = 0)
    returnCode = UpdateStatus(myOrder);
    //** Error handling **//

  if (returnCode > 0)
    //** Error handling **//

public static int CalculateYield(Order order, decimal yield)
  // calculate yield
  return -1; // return code

public static int UpdateStatus(Order order)
  // update status
  return 0; // return code

Return codes are inferior to exceptions. Exceptions are instances of a class and can therefore carry detailed information – not like integer return codes! It is non-reputable that return codes are no good.

The return code advocate might bring up an issue like performance due to the fact that throwing an exception results in a stack walk to create the stack trace.

When to throw exceptions?

Do not throw exceptions in the normal flow of execution. If it is expected that an action might fail like a login, use the Tester-Doer Pattern (e.g. Collection.Contains) or Try-Parse Pattern (e.g. Double.TryParse).

When to catch exceptions?

Catch only an exception if detailed information about the exception can be added, the exception is handled or some sort of compensation action is required. If the flow of execution can not continue, rethrow the exception so an exception can be handled further up the stack, e.g. inform the user.

catch (ArgumentException ex)
  // some clean up or logging
  throw ex;

When rethrowing exceptions, it is important to do it the right way. By using the above pattern regarding throwing and not throwing ex, the stack walk is not performed twice, making the rethrow virtually free.

If you want to know more read the book Framework Design Guidelines. It is recommended and easily read. You can also look at the blog entry Exception Throwing by Krzysztof Cwalina – one of the authors of the Framework Design Guidelines book. There is also a small summery at Design Guidelines for Exceptions on MSDN.

On a side note: the company mentioned was as one of the first I Denmark to adopt the .Net framework – in 2000 where beta 1 was released they build their first project on the .Net framework. Go figure!