I was doing some database tuning recently and I found a missing index that I wanted to add. This is a reasonably straightforward thing to want to do, so I scripted it up and executed the command and went to grab a coffee.

CREATE INDEX IX_Test ON dbo.Test(TestColumn);

<5 minutes passes>

So, I come back to my desk only to find that the index hasn’t finished creating yet! This was unexpected since it was a reasonably narrow index on a table that was only 2-3GB in size.

Using some queries to dig into the DMV’s and a look at the waits I see my index is waiting on LATCH_EX with a latch class of ACCESS_METHODS_DATASET_PARENT, and it had been waiting from the moment I left my desk! This was not a wait type I was familiar with so some research was required.

Reaching for my favourite search engine I soon stumbled upon this blog post from Paul Randal http://www.sqlskills.com/blogs/paul/most-common-latch-classes-and-what-they-mean/.

Basically following his advice and doing some digging I found that the MAXDOP on this server was set to 0 which is the default. This is a 24 core server and I wouldn’t normally advise setting MAXDOP to 0 on a server of this size. The cost threshold for parallelism was set to 5 (also the default) which is quite low considering the workloads performed by this box.

In order to get around the problem I discussed changing the MAXDOP of this server to 8 but the team responsible for it didn’t want to make the change at that time, and opted to change it at a later date. Great, what now? I needed this index and I needed it now…

On this occasion I opted to reach for a MAXDOP hint. For those that didn’t know, you can apply a MAXDOP hint to an index creation statement. The syntax is shown below:

CREATE INDEX IX_Test ON dbo.Test(TestColumn) WITH (MAXDOP = 4);

This time when I executed the script the index creation took only 2 minutes, and the procedures that needed it were now executing much faster than before.

Essentially I’ve written this post in the hope that it helps someone else out if they stumble across the same problem. Aside from Paul’s blog linked above I couldn’t really find any other useful troubleshooting advice for this particular issue. Happy troubleshooting!