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

Tom
  • 1,569
  • 6
  • 29
  • 43

1 Answers1

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.

mskinner
  • 856
  • 5
  • 8