Reputation
Top tag
Next privilege 125 Rep.
Vote down
Badges
1 6
Newest
 Commentator
Impact
~3k people reached

  • 0 posts edited
  • 0 helpful flags
  • 3 votes cast
Jan
8
comment Sql Server 2014 IO Resource Governor
That makes sense. I never thought of it that way. I have however, tried multiple IOPS settings to see if it would help. I will try some more now that I have more tempdb data files.
Jan
8
comment Sql Server 2014 IO Resource Governor
Your_comment_is_not_funny - you mention contention with other workloads because of throttling. That is exactly right, I don't believe that should happen. Isn't that the purpose of the governors?
Jan
8
comment Sql Server 2014 IO Resource Governor
on this database, I run sp_updatestats 'resample'. Maybe I should just use sample x% or leave the option off and let sql figure out the sample rate. The pageiolatch is on the user database being checked.
Jan
8
comment Sql Server 2014 IO Resource Governor
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.
Dec
29
awarded  Commentator
Dec
29
comment Sql Server 2014 IO Resource Governor
wBob - I have created my own and the dbcc is running under the correct one, as well as the other jobs. Can you give more explanation as to what you mean when you say the classifier function could cause this. Ultimately the classifier has classified everything correctly.
Dec
29
comment Sql Server 2014 IO Resource Governor
Thanks for the info. I'll bump it to 8 to start. I'll report the results in a week or so.
Dec
29
comment Sql Server 2014 IO Resource Governor
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.
Dec
29
comment Sql Server 2014 IO Resource Governor
I've ran with it both on and off under multiple configs. It is 24/7 prod machine, and this in particular is the dbcc database check that runs on a 2.7 TB database. I can't really test a new config until next sunday at best.
Dec
29
comment Sql Server 2014 IO Resource Governor
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.
Dec
29
comment Sql Server 2014 IO Resource Governor
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.
Dec
28
comment Sql Server 2014 IO Resource Governor
Can you define the wait types for resource governors?
Dec
28
asked Sql Server 2014 IO Resource Governor
Jul
3
awarded  Notable Question
Jul
12
accepted Sql Server & NetApp Sql Snap Manager
Jul
9
asked Sql Server & NetApp Sql Snap Manager
Sep
6
awarded  Popular Question
Apr
17
comment Sql Server Resource Governor - track IO
I didn't think so, but thought I'd throw it out to the experts, hoping I missed something. Thanks.
Apr
17
accepted Sql Server Resource Governor - track IO
Apr
17
asked Sql Server Resource Governor - track IO