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.