2

We are using Forefront Identity Manger and we are doing an initial load to which we are experiencing slow performance.

I always start with wait types for such things and can see %30 CXPACKET, %25 LATCH_EX and %15 ASYNC_NETWORK_IO.

I understand that LATCH_EX is a non buffer wait type so I looked in sys.dm_os_latch_stats and can see that %87 of the latch waits is down to ACCESS_METHODS_DATASET_PARENT.

Is this a problem? Even on Paul Randals blog I am unclear what my issue is if at all.

Thanks

  • What is the maxdop setting and cost threshold of parallelism setting on the server ? – Kin Shah Aug 14 '15 at 17:10
  • Maxdop is 8 and cost threshold is 50 – Tom Aug 17 '15 at 8:40
  • I should have said there are 8 cores on this VM – Tom Aug 17 '15 at 8:46
1

You mention Paul Randal in your question, so I assume you have seen this post where he talks about this stuff in great detail. The ACCESS_METHODS_DATASET_PARENT Waits frequently deal with Parallel queries (as do your 30% CXPACKETS) and as Paul says in the blog that I linked, don't knee-jerk and set MaxDOP to 1 to get rid of those. But I would wager based on the information provided that you do have some parallelism going on within your server, possibly for a bunch of things. The question for you is do you think this is a problem? That will depend on your situation, what the server does, how things are set, etc... Another good link that describes this wait can be found here and in this case a very specific situation is described (and sounds similar to your situation) and the resolution to this one is described in the link.

Depending on what you find to be true on your server, you may want to run some tests with MaxDOP or possible with changing the Cost Threshold For Parallelism (it defaults to 5 and in todays world that is frequently too low, but you need to see for yourself for your particular server). Cost Threshold For Parallelism is one that Jeremiah Peschka says is one of the 5 settings to change for SQL. Read through those links run some tests with those properties on your server if you have the ability to do so and hopefully that will help to get you where you want to go.

  • Our cost threshold is 50 and MAXDOP is 8 with 8 cores in the VM. In understanding this I have a query to pull out plans that have gone parallel along with costs. If this is a non-buffer wait though will I see these queries in the plan cache? – Tom Aug 17 '15 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.