2

I've have configured the resource governor for cpu, memory, and IO. I have my dbcc db checks running under a pool and workload that only allows 10% of memory and cpu, and only 100 IOPS. I have also been tracking it as it happens and it looks like it is working just fine. Limiting things the way I would expect. However, it seems to be affecting other pools and workloads at the IO level. Each db mdf and ldf sit on a netapp san, on their own volume. There doesn't seem to be any io contention on the netapp side. I wouldn't expect the IO governor to affect other things like this. What should I be looking at to fix this? The wait type on the dbcc is pagelatch_sh. I also see plenty of pagelatch_up and _ex for the other jobs that end up taking longer than normal.

Improve this question
9
  • Shouldn't you be seeing Resource Governor wait types if it really is throttling workloads? Did you do anything to cause more load?
    – usr
    Commented Dec 28, 2014 at 22:11
  • Can you define the wait types for resource governors?
    – HunterX3
    Commented Dec 28, 2014 at 22:36
  • 1
    Where does tempdb sit on storage?
    – user507
    Commented Dec 29, 2014 at 4:10
  • The tempdb mdf and ldf have seperate volumes and luns on our netapp. Basically each mdf has its own volume and lun, and each ldf has its own volume and lun.
    – HunterX3
    Commented Dec 29, 2014 at 12:24
  • From what I've seen there are no specific wait types for when the resource governors kick in. I've seen just lots more pagelatch_sh and _ex types.
    – HunterX3
    Commented Dec 29, 2014 at 14:24

1 Answer 1

Reset to default
1

Do you know what pages are getting the latches? DBCC heavily uses tempdb as @ShawnMelton was alluding. Have you considered that tempdb contention could be the source of your pagelatches? You should be able to investigate that with sp_whoisactive if you aren't already. How many data files do you have for tempdb? It sounds like you've only got one, try bumping it up to 4 and increase in intervals of 4 as needed, though 8 is often enough for most systems and many suggest starting with that number of data files. You'll also want to ensure the files are equally sized. You can also look at this post which has lots of info and links to reduce the impact of dbcc including a post from Paul Randall on running DBCC on a VLDB which might be helpful.

Improve this answer
10
  • I've split them in the past at two, but will 4 or 8 really help? Currently, the tempdb files are sitting on a san, in their own vol and lun that is spread across 55 disks. It seems to me that the workload would be split plenty already.
    – HunterX3
    Commented Dec 29, 2014 at 16:22
  • 1
    Each new file creates a new set of allocation map pages that can be used by the waiting transactions. This post explains it better. It allows the workload to be spread across the files/pages. You aren't getting PAGEIOLATCH* waits, you are getting PAGELATCH* waits so the IO subsystem isn't explicitly your bottleneck here, these pages are almost certainly cached in memory already but only one thread can update the page at a time. More data files = more pages to get cached and addressed.
    – Your_comment_is_not_funny
    Commented Dec 29, 2014 at 16:41
  • Thanks for the info. I'll bump it to 8 to start. I'll report the results in a week or so.
    – HunterX3
    Commented Dec 29, 2014 at 17:15
  • I split the tempdb mdf into 8 files. It didn't seem to help the issue in any noticeable way. Im still seeing plenty of PageIOLatch_SH when doing UpdateStatistics on our largest db. Even though this is io governed, it seems to affect the other jobs in other pools and workloads.
    – HunterX3
    Commented Jan 8, 2015 at 15:04
  • 1
    The governor is there to help prioritize your workload when resources come under contention. I think I worded the other response poorly. If you are dividing the workload on this one database into multiple pools, you are effectively prolonging the latching and contention because the throttled processes must keep their latches for longer periods of time since they are constrained. It sounds like you might want to revisit your classifier function if that is a problem. Just because you can limit IOPS doesn't mean you always should...try turning that off or bumping up your limit.
    – Your_comment_is_not_funny
    Commented Jan 8, 2015 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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