Yearly Archives: 2008

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.

SET SHOWPLAN_XML ON

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:

<missingindexes>
  <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" /> 
      </columngroup>
    </missingindex>
  </missingindexgroup>
</missingindexes>

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.

Serialization in .Net 3.5 SP1

In .Net 3.5 SP1 it is now possible to serialize any object that has a default constructor without decorating it with [DataContract] or [Serializable].

Developers have these choices now when serializing objects:

  • Implicit serialization – requires public default constructor and only serialize public read/write fields
  • Use the [Serializable] attribute
  • Use [DataContract] and [DataMember] attributes
  • Implement the ISerializable interface

I must say, that I’m not keen on this new feature. I prefer explicit serialization to implicit serialization. I predict that implicit serialization of entire object graphs will cause performance problems for many .Net developers in the years to come. This could also be a good thing as I am a consultant  😉

Aaron Skonnard has a more detailed blog post about the new serialization features in .Net 3.5 SP1.

SQL Server 2008 Microsoft ISV Partner events presentations

This week I hosted two sessions at two different events for Microsoft Denmark. One at Radisson SAS Scandinavia Hotel in Århus and one in Microsoft Denmark’s headquarter in Hellerup.

It was fun at the feedback was very positive. The best part was during the breaks with great edifying technical discussions. All the slides are available at Miracle’s homepage.

.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.