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.

  • Shouldn't you be seeing Resource Governor wait types if it really is throttling workloads? Did you do anything to cause more load? – usr Dec 28 '14 at 22:11
  • Can you define the wait types for resource governors? – HunterX3 Dec 28 '14 at 22:36
  • 1
    Where does tempdb sit on storage? – Shawn Melton Dec 29 '14 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 Dec 29 '14 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 Dec 29 '14 at 14:24
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.

  • 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 Dec 29 '14 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 Dec 29 '14 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 Dec 29 '14 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 Jan 8 '15 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 Jan 8 '15 at 21:49

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.