3

When profiling a resource group that is known to be write-heavy, I am seeing a number of the "IOps write" counters remain at zero or increment unexpectedly. specifically, the following counters...

select 
    write_io_completed_total, 
    write_io_stall_total_ms,
    write_bytes_total
from sys.dm_resource_governor_resource_pools;

It's worth noting that there appears to be a 1:1 correlation between dm_resource_governor_resource_pools and dm_os_performance_counters as below for some of these.

+-------------------------------------+------------------------------+
| dm_resource_governor_resource_pools | dm_os_performance_counters   |
+-------------------------------------+------------------------------+
| write_io_completed_total            | Avg Disk Write IO (ms) Base  |
+-------------------------------------+------------------------------+
| write_io_stall_total_ms             | Avg Disk Write IO (ms)       |
+-------------------------------------+------------------------------+

The erratic behavior is reproducible via the below script...

  • Behavior tested on 2017 (RTM) & 2016 (SP1 CU5)
  • Repro scripts assume you are running on a sandbox instance - or at least one where you can trash the settings without worry.

Setup

use [master]
go
create resource pool demo_pool;
create workload group demo_group using demo_pool;
create login rg_login_sa with password='Plz_Halp_Me_StackExchange';
alter server role sysadmin add member rg_login_sa;
go
create or alter function dbo.GetResourceGroup()
returns sysname
with schemabinding
as
begin;
    declare @rg sysname = 'default';
    if original_login() = 'rg_login_sa' set @rg = 'demo_group'
    return @rg;
end;
go
alter resource governor with (classifier_function = dbo.GetResourceGroup);
alter resource governor reconfigure;
alter resource governor reset statistics;

Execution

Opening a session with the login rg_login_sa, execute the below.

use Sandbox
go
create table demoRGtbl (
    id int not null identity primary key,
    words char(4000) default '',
    dt datetime2(0) not null default getutcdate()
);
go
with cte as ( 
    select i=1
    union all
    select i+1
    from cte
    where i<1000000
)
insert dbo.demoRGtbl(words)
select top(1000)
    convert(varchar(100), newid())
from cte
option (maxrecursion 0);

waitfor delay '00:00:00.2';
go 100

...monitoring the performance counters in a separate session will reveal incrementing values in the internal resource pool...

select name,
       write_bytes_total,
       write_io_completed_total,
       write_io_stall_total_ms,
       read_bytes_total
from   sys.dm_resource_governor_resource_pools;

...while the demo_pool values remain unchanged from zero. Interestingly, the read_bytes_total for the demo_pool will begin to increment.

Attempting from the same session to insert rows to the table from itself show the same behavior as above...

insert dbo.demoRGtbl(words)
select words 
from dbo.demoRGtbl drg

... up until about the 100K-1MM row range - at which point the IOps counters begin incrementing to the demo_pool instance.

My Questions

  1. How do I reliable track IOps by Resource Group?
  2. Is this a bug or expected behavior? If expected, what are the parameters in which a operation will log to these counters?

In other words - what on earth am I currently tracking in my production environment by using these counters?

See also: Technet blog post announcing IO resource governance.


Repro Cleanup

alter resource governor with (classifier_function = null)
alter resource governor disable;
go
drop workload group demo_group;
drop resource pool demo_pool;
drop login rg_login_sa;
drop function if exists [master].dbo.GetResourceGroup;
drop table if exists Sandbox.dbo.demoRGtbl;
go

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.