SQL Server Resource Governor - Boise SQL Server Users Group

Report
SQL Server Resource Governor
Introduction To The Resource
Governor
• Resource Governor was added in SQL Server
2008
• Purpose is to manage resources by specifying
limits on consumption of those resources by
the requests processes that are using them.
• Resource Governor is available in Enterprise,
Developer, and Evaluation Editions of SQL
Server 2008 and greater
What A Resource Governor Will
Address
• Intrusive queries that consume a lot of the
resources
• Unpredictable workload due to a workload
type mixture. For example OLTP & DSS
running on the same instance
• Workload priority can be set to ensure higher
priority processes get more resources
Resource Governor Provides
• The ability to classify connections and route
the workload to a specific group
• The ability to monitor resource usage for each
workload in a group
• The ability to pool resources and set pool
specific limits on CPU and memory.
• The ability to associate grouped workloads
with a specific pool of resources
• The ability to set priorities for workloads
Resource Governor Limitations
• Limited to SQL Server Engine Only
• Self contained within a SQL Server. In other
words, one instance doesn’t know about
resource usage in another instance
• Applies to only CPU and memory usage only
• OLTP workloads are typically short in duration
and can skew statistics.
High Level View Of Resource Governor
(from Books Online)
3 Important Concepts
• Resource Pools
• Workload Groups
• Classification
Resource Pools
• Represents physical resources of a database instance
• Types Of Pools
– internal pool (can not be altered, created by default)
– default pool (first predefined user pool, can not be dropped, but can
be altered, created by default)
– user defined pool (Can create up to 18)
• Two Parts
– Minimum resource reservation - Minimum guaranteed availability of
the pool
– Maximum resource reservation – Maximum size of the pools
• The sum of MIN values across all pools can not exceed 100% of the
instance resources
• The MAX values can be set anywhere between MIN and 100%
Resource Pools (continued)
Pool
name
internal
MIN %
setting
MAX %
setting
0
100
Calculated
Calculated
effective MAX
shared %
%
100
0
Comment
Effective MAX % and shared % are not
applicable to the internal pool.
default
0
100
25
The effective MAX value is calculated as:
min(100,100-(20+50+5)) = 25. The
25
calculated shared % is Effective MAX MIN = 25.
Pool 1
20
100
45
The effective MAX value is calculated as:
25 min(100,100-55) = 45. The calculated
Shared % is Effective MAX - MIN = 25.
Pool 2
50
70
70
The effective MAX value is calculated as:
20 min(70,100-25) = 70. The calculated
Shared % is effective MAX - MIN = 20.
Pool 3
5
100
30
The effective MAX value is calculated as:
25 min(100,100-70) = 30. The calculated
Shared % is effective MAX - MIN = 25.
Workload Groups
• A container for session requests that are similar to how they are classified.
This is where the policy is defined for all members of the group.
• Types Of Groups
– Internal – created by default and can not be modified
– Default – group where members are placed by default under the following
conditions
• No classification for a request
• Attempt to classify into a non-existent group
• Any generic classification failure
• What can be controlled by a workload group
–
–
–
–
–
–
Maximum amount of memory for a request
Maximum percentage of CPU for use
Resource time-out for a request
Priority
Maximum number of requests
Maximum degree of parallelism
Classification
• Based on rules that are part of a function that
classify a connection into a workload group. You
can not classify anything into the internal
workload group.
• Steps To Get Classification Working
– Write a scalar function that has the logic that assigns
the session to a workload group
– Register the function using the alter resource
governor statement
– Update the resource governor with the reconfigure
option
Classification Function Requirements
• Must be a scalar function (one and only one result)
• Evaluated for every new session
• Once workload group membership is determined, the
connection is bound to that group for the its lifetime
• Function must be defined in the master database
• Only one function can be used by the resource
governor at a time
• If the function is dropped, then all sessions are bound
to the default group.
• Any connection using the Dedicated Admin Connection
is not subject to the resource governor.
Resource Governor Monitoring
• System views
sys.resource_governor_configuration
Returns the stored Resource Governor state.
sys.resource_governor_resource_pools
Returns the stored resource pool configuration. Each row of the view
determines the configuration of a pool.
sys.resource_governor_workload_groups
Returns the stored workload group configuration.
• Dynamic Management Views
sys.dm_resource_governor_workload_groups
Returns workload group statistics and the current in-memory configuration of
the workload group.
sys.dm_resource_governor_resource_pools
Returns information about the current resource pool state, the current
configuration of resource pools, and resource pool statistics.
sys.dm_resource_governor_configuration
Returns a row that contains the current in-memory configuration state for
Resource Governor.
Resource Governor Monitoring
Continued
• Perfmon Counters
SQLServer:Workload Group Stats
Reports statistics for each active workload group, such as the number of active
requests and the number of blocked requests.
SQLServer:Resource Pool Stats
Reports statistics for each active resource pool, such as the number of memory
grants that are occurring in the resource pool per second and the amount of
memory that is used by the resource pool.
Demonstration

similar documents