Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

SQL 2014 Learning Series # 9 – New Feature – Resource Governor to control I/O (Part 1)

Posted by blakhani on May 15, 2014


Have you ever come across a situation where you want to re-index complete database and other queries are facing slowness because of IO bottleneck? In earlier version of SQL Server, resource governor was available for CPU and memory. In SQL Server 2014, resource governor for IO was also introduced. This would enable DBA to manage and control the allocation of resources. To read more about earlier version, you can refer books online. As per documentation, below are the goals which were thought while designing RG:

  • Minimize impact run-away queries:- Run-away queries are the queries which would consume resources for long time. Think of a reporting query which consumes 100% as soon as it runs. If such query is executed, other queries would suffer from resource bottleneck.
  • Improve predictability of query executions:- During maintenance tasks (rebuild index, update stats) or ETL, there might be a heavy use of resource by those processes. This would cause unpredictable time of query executions. This is generally not a desirable situation.

Let’s first understand the basic functionality and various terms used in resource governor. Below picture shows various components of resource governor.

image

As soon as request comes from any source it is assigned to a particular resource group via user defined function known as “classifier function”. Classifier function can use various connection attributes and provide the bucket in which request should fall into. These buckets are called Resource Group. Those request which are not classified, would go to default group (orange color). Default group would use remaining resources. We should also remember that there is a internal pool (green color) which can’t be controlled via resource governor. The maximum number of pools which can be configured in SQL Server is 64.

Someone may still ask, why I/O needs to be governed? Think of a situation when you are database services provider. There are many customers who are hosting database on your server. What if one of your naughty customer runs read/write heavy workload? Someone else would be impacted. Right?

Let’s see what’s new in resource governor. Highlighted is the additional which is available in SQL 2014. This means that now we have ability to set/get/alter minimum and maximum I/O operations per second (IOPS) per volume to resource pool.

CREATE RESOURCE POOL pool_name
[ WITH
    (
        [ MIN_CPU_PERCENT = value ]
        [ [ , ] MAX_CPU_PERCENT = value ] 
        [ [ , ] CAP_CPU_PERCENT = value ] 
        [ [ , ] AFFINITY {SCHEDULER =
                  AUTO | ( <scheduler_range_spec> ) 
                | NUMANODE = ( <NUMA_node_range_spec> )} ] 
        [ [ , ] MIN_MEMORY_PERCENT = value ]
        [ [ , ] MAX_MEMORY_PERCENT = value ]
        [ [ , ] MIN_IOPS_PER_VOLUME = value ]
        [ [ , ] MAX_IOPS_PER_VOLUME = value ]
    ) 
]
[;]

 

There are various changes introduced in DMVs to monitor resource governor for IO and new DMVs are also added.

Below are existing DMVs having new columns about

  • sys.resource_governor_configuration
    • max_outstanding_io_per_volume
  • sys.dm_resource_governor_resource_pools:
    • min_iops_per_volume
    • max_iops_per_volume
    • read_io_queued_total
    • read_io_issued_total
    • read_io_completed_total
    • read_io_throttled_total
    • read_bytes_total
    • read_io_stall_total_ms
    • read_io_stall_queued_ms
    • write_io_queued_total
    • write_io_issued_total
    • write_io_completed_total
    • write_io_throttled_total
    • write_bytes_total
    • write_io_stall_total_ms
    • write_io_stall_queued_ms
    • io_issue_violations_total
    • io_issue_delay_total_ms

New DMV:

  • sys.dm_resource_governor_resource_pool_volumes
    • pool_id
    • volume_name
    • read_io_queued_total
    • read_io_issued_total
    • read_io_completed_total
    • read_io_throttled_total
    • read_bytes_total
    • read_io_stall_total_ms
    • read_io_stall_queued_ms
    • write_io_queued_total
    • write_io_issued_total
    • write_io_completed_total
    • write_io_throttled_total
    • write_bytes_total
    • write_io_stall_total_ms
    • write_io_stall_queued_ms
    • io_issue_violations_total
    • io_issue_delay_total_ms

Other than DMVs there are XEvents and Performance counters added to monitor and troubleshoot resource governor related to IO. Since my instance name is SQL2014, the counter name is MSSQL$SQL2014:Resource Pool Stats. I have highlighted 8 new counters (4 for read and 4 for write) which are newly added in SQL 2014. Note that these counters are “per pool” basis. Here is the query to get list of counters.

Select distinct object_name, 
counter_name from sys.dm_os_performance_counters 
where object_name like '%Pool%'

MSSQL$SQL2014:Resource Pool Stats  Active memory grant amount (KB) 
MSSQL$SQL2014:Resource Pool Stats  Active memory grants count
MSSQL$SQL2014:Resource Pool Stats  Avg Disk Read IO (ms) 
MSSQL$SQL2014:Resource Pool Stats  Avg Disk Write IO (ms) 
MSSQL$SQL2014:Resource Pool Stats  Cache memory target (KB) 
MSSQL$SQL2014:Resource Pool Stats  Compile memory target (KB)
MSSQL$SQL2014:Resource Pool Stats  CPU control effect %
MSSQL$SQL2014:Resource Pool Stats  CPU usage %
MSSQL$SQL2014:Resource Pool Stats  CPU usage % base 
MSSQL$SQL2014:Resource Pool Stats  CPU usage target % 
MSSQL$SQL2014:Resource Pool Stats  Disk Read Bytes/sec 
MSSQL$SQL2014:Resource Pool Stats  Disk Read IO Throttled/sec
MSSQL$SQL2014:Resource Pool Stats  Disk Read IO/sec 
MSSQL$SQL2014:Resource Pool Stats  Disk Write Bytes/sec
MSSQL$SQL2014:Resource Pool Stats  Disk Write IO Throttled/sec 
MSSQL$SQL2014:Resource Pool Stats  Disk Write IO/sec
MSSQL$SQL2014:Resource Pool Stats  Max memory (KB) 
MSSQL$SQL2014:Resource Pool Stats  Memory grant timeouts/sec 
MSSQL$SQL2014:Resource Pool Stats  Memory grants/sec
MSSQL$SQL2014:Resource Pool Stats  Pending memory grants count 
MSSQL$SQL2014:Resource Pool Stats  Query exec memory target (KB)
MSSQL$SQL2014:Resource Pool Stats  Target memory (KB) 
MSSQL$SQL2014:Resource Pool Stats  Used memory (KB) 

In Next part of this post, we would see IO resource governor in action.

  • Cheers,
  • Balmukund Lakhani
  • Twitter
  • @blakhani

  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: