1 / 15

SQL Server Resource Governor

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.

cybill
Download Presentation

SQL Server Resource Governor

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Resource Governor

  2. 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

  3. 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

  4. 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

  5. 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.

  6. High Level View Of Resource Governor (from Books Online)

  7. 3 Important Concepts • Resource Pools • Workload Groups • Classification

  8. 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%

  9. Resource Pools (continued)

  10. 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

  11. 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

  12. 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.

  13. Resource Governor Monitoring • System views • Dynamic Management Views

  14. Resource Governor Monitoring Continued • Perfmon Counters

  15. Demonstration

More Related