--- title: ACCESS_METHODS_DATASET_PARENT – Not one I’d heard of either! description: ACCESS_METHODS_DATASET_PARENT – Not one I’d heard of either! image: https://blog.coeo.com/hubfs/Imported_Blog_Media/109-2.png --- [![](https://www.coeo.com/wp-content/themes/coeo/images/logo.svg)](https://blog.coeo.com/) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) | [Client portal login](https://my.coeo.com) # ACCESS\_METHODS\_DATASET\_PARENT – Not one I’d heard of either! # The Coeo Blog ![Simon Osborne](https://blog.coeo.com/hubfs/SimonOsCircle.jpg) 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/](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! [![](https://blog.coeo.com/hubfs/Imported_Blog_Media/109-2.png)](http://feeds.wordpress.com/1.0/gocomments/simonosbornesql.wordpress.com/109/) ![](https://blog.coeo.com/hs-fs/hubfs/Imported_Blog_Media/b-79.gif?width=1&height=1&name=b-79.gif) ### Subscribe to Email Updates ## Related posts --- ### [Troubleshooting Ola Hallengren’s Maintenance Solution](https://blog.coeo.com/troubleshooting-ola-hallengrens-maintenance-solution) ### [Domain-Independent Windows Failover Cluster for SQL Server AlwaysOn Availability Group](https://blog.coeo.com/domain-independent-windows-failover-cluster-for-sql-server-alwayson-availability-group) ### [SQLBits 2022 session - Field Testing Ola Hallengren’s Maintenance Solution](https://blog.coeo.com/sqlbits-2022-session-field-testing-ola-hallengrens-maintenance-solution) ### [Windows Active Directory Detached Cluster with SQL Server Basic Availability Group](https://blog.coeo.com/advanced-sql-server-windows-cluster-options) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | info@coeo.com ## Contact Us By clicking submit below, you consent to allow Coeo to store and process the personal information submitted above to provide you the content requested. You may unsubscribe from these communications at any time. For more information on how to unsubscribe and our commitment to your privacy, please review our **[Privacy Policy](https://www.coeo.com/privacy/)**. ## Upcoming Events [See all events](https://www.coeo.com/events/) #### NOW Building, Thames Valley Park Drive, Reading, RG6 1RB [![](https://www.coeo.com/wp-content/themes/coeo/images/social-glass.png)](https://www.glassdoor.co.uk/Overview/Working-at-Coeo-EI_IE959052.11,15.htm)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-in.png)](https://www.linkedin.com/company/coeo-ltd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-twitter.png)](https://twitter.com/CoeoLtd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-fb.png)](https://www.facebook.com/coeoltd/) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-icon.png) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-close.png) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) - [Solutions](https://www.coeo.com/solutions/) - [Next Steps](https://www.coeo.com/next-steps/) - [Dedicated Support](https://www.coeo.com/dedicated-support/) - [Case studies](https://www.coeo.com/case-studies/) - [Technologies](https://www.coeo.com/solutions/technologies/) - [Industries](https://www.coeo.com/industries/) - [Finance](https://www.coeo.com/industries/finance/) - [Retail](https://www.coeo.com/industries/retail/) - [Technology](https://www.coeo.com/industries/technology/) - [The Team](https://www.coeo.com/people/) - [Join Us](https://www.coeo.com/careers/) - [Graduate Programme](https://www.coeo.com/graduate-programme/) - [About Coeo](https://www.coeo.com/about-coeo/) - [The Coeo Blog](https://www.coeo.com/blog/) - [Contact us](https://www.coeo.com/contact-us/) - [Privacy Notice](https://www.coeo.com/privacy/) - [Cookie Policy](https://www.coeo.com/privacy#Cookie_Policy) - [Events](https://www.coeo.com/events) Sign up to our newsletter ![go arrow](https://www.coeo.com/wp-content/themes/coeo/images/newsletter-go.png) Back to top