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.
Sql-server – Sql Server 2014 IO Resource Governor
resource-governorsql server
Related Question
- Sql-server – SQL Server 2014, Volumes in Resource Governor
- Sql-server – Resource Governor seems to increase resource usage
- Sql-server – SQL Server Resource governor user-defined resource pools
- Sql-server – Resource Governor not limiting CPU / RAM
- Sql-server – Using Resource Governor with SQL Agent Jobs
- Sql-server – Disabling Resource Governor in SQL Server
Best Answer
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.