2

SQL Server 2008

Resource Governor

Beatrice NicoliniPremier Field EngineerMicrosoft

http://blogs.technet.com/beatriceemail: beatrice.nicolini@microsoft.com

Session Code: DAT301

3

AgendaPrior to Resource Governor

Introducing SQL Server 2008 Resource Governor Scenarios

Concepts

Monitoring

Demo 1

Limitations

CPU Considerations

Demo 2

Usage Scenarios

Get Started!

4

Prior to Resource Governor

Control memory, CPU, I\O Affinity on a per instance basis

Query Governor on a per query basis

If the estimated cost of a query exceeds the threshold, it won’t be allowed to run at all!

If statistics out of date –incorrect estimate

5

Introducing Resource GovernorScenarios

Predictable WorkloadCPU – Memory

Enterprise, Developer and Evaluation Edition

Protect from known large

resource consumers

Compete for resources as usual (SQL Server 2005

behavior)

Guarantee resources for

mission critical workloads

6

Introducing Resource Governor Concepts

Internal

Group

Default

GroupGroup 1 .... Group N

Internal Pool Default Pool Pool 1 Pool N

DAC

Classification

Incoming Sessions

7

Configuring Resource Governor

After install Resource Governor is available but is not enabled.

Internal and default Resource Pools and Workload Groups are already created

Configure Resource Governor:

Resource pools

Create a workload groups

Define and activate a classifier function

8

Introducing Resource Governor Concepts – Resource Pool

A resource pool, or pool, represents the physical resources of the server.

LIMITATIONS\WARNINGS

- Limited to 20 pools

- CPU% Applied per scheduler

9

CREATE RESOURCE POOL pool_name

WITH (

MIN_CPU_PERCENT = value,

MAX_CPU_PERCENT = value,

MIN_MEMORY_PERCENT = value,

MAX_MEMORY_PERCENT = value

)

SyntaxResource Pool

10

SyntaxGUI

11

Introducing Resource Governor Concepts – Workload Group

A workload group serves as a container for session requests that are similar according to the classification criteria that are applied to each request.

No max limitation

Internal GroupDefault Group

(All Other Applications Users)

Application 1 Users Application 2 UsersAdministrators

Group

Internal Pool Default Pool Pool 1 Pool N

12

SyntaxWorkload Group

CREATE WORKLOAD GROUP grp_name

WITH (

IMPORTANCE = {LOW | MEDIUM | HIGH},

REQUEST_MAX_MEMORY_GRANT_PERCENT = value,

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value,

REQUEST_MAX_CPU_TIME_SEC = value,

MAX_DOP = value,

GROUP_MAX_REQUESTS = value)

[ USING { pool_name | "default" } ] [ ; ]

13

What is Importance?Importance is weight (L:M:H = 1:3:9) used from tasks in

different groups when groups share a pool.

Group 1 CPU Usage: 50%Group 2 CPU Usage: 50%Pool CPU: 100%SQL Server CPU: 100%

1 Pools – 2 GroupsMAX CPU unrestrictedWithout Importance

Group 1 CPU Usage: 10%Group 2 CPU Usage: 90%Pool CPU: 100%SQL Server CPU: 100%

1 Pools – 2 GroupsMAX CPU unrestrictedImp = low – Imp=high

14

Introducing Resource Governor Concepts - Classifier

The classifier is a UDF that is fired right after the login triggers and before the T-SQL debugger is enabled during login.

BE AWARE\CLARIFICATION:

• Part of the login process logins can timeout•Classification failure or no classification criteria default group.•Cannot change group binding after initial assignment• Cannot specify in T-SQL which group to go to (aka “explicit” classification)• 1 per time

15

Syntax - UDF

HOST_NAME(), APP_NAME(), SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER(), IS_MEMBER(), LOGINPROPERTY , ORIGINAL_DB_NAME, CONNECTION PROPERTY function

EXAMPLE:

CREATE FUNCTION dbo.rgclassifier_v3 ()

RETURNS sysnameE

WITH SCHEMABINDING AS

BEGIN

DECLARE @grp_name sysname

IF suser_sname() = 'DOMAIN\username'

SET @grp_name = 'MyGroup'

RETURN @grp_name

END; GO

COMMIT TRAN; GO

16

Monitoring Resource Governor

Performance Counters

Catalog Views

DMVs

Events

17

Monitoring Resource Governor

Dedicated Administrator Connection (DAC) to troubleshoot UDF

DAC is not subject to classification

DAC can be used while Resource Governor is running and classifying incoming sessions.

If a DAC is not available restart the system in single user mode.

18

Beatrice NicoliniPremier Field EngineerMicrosoft

19

Resource Governor Limitations

• limited to the SQL Server Database Engine.

• Limit specification applies to CPU and memory

• Cannot change group binding after initial assignment

• Cannot specify in T-SQL which group to go to (aka

“explicit” classification)

• No automatic actions on various timeouts

20

CPU Considerations

Scheduling decision is made on per scheduler basis

CPU usage limited only under contention!

You need multiple tasks in different groups/pools to observe effect

Remember when troubleshooting\monitoring

21

CPU Considerations1 Pool

Min CPU 0MAX CPU 100

1 Group 1 Task

1 PoolMin CPU 0

MAX CPU 801 Group 1 Task

2 Pools A & B Min CPU 20MAX CPU 80

2 Group s 2 Task

Modify Pool AMin CPU 20MAX CPU 40

2 Groups 2 Task

22

Demo Title

Beatrice NicoliniPremier Field EngineerMicrosoft

23

Typical Scenario - Backup Compression

Before 2008: Third party vendors solution.

Backup Compression:

- Reduced Storage Space- Similar compression ratios as

vendors software (5:1)- Proprietary Algorithm

- Reduced Network Bandwidth- Reduced run-time for backup\restore

- Off by default- Enterprise Only

24

Test

on

322

Mb

Dat

abas

e

45% RunTime improvement4.2x Compression ratio

50% RunTime improvement

25

Typical ScenarioLimit CPU Usage by Backup Compression

Backup compression significantly increases CPU usage

Create a low-priority compressed backup

Setting Up a Login and User for Low-Priority Operations

Configuring Resource Governor to Limit CPU Usage

Verifying the Classification of the Current Session (Transact-SQL)

Compressing Backups Using a Session with Limited CPU

26

Typical ScenarioLimit CPU Usage by Backup Compression

27

Typical ScenarioReporting Solution

Create a high priority login for reporting applications!

28

Typical ScenarioReporting Solution

29

Typical ScenarioAdmins

Guarantee that the admin group:

• always has resources to run diagnostic queries

• these queries should not take more than N% of the server memory resources.

30

Typical ScenarioAdmins

31

Get Started!

Who’s consuming my resources?

Create workload groups to monitor resource consumption

Monitor consumption

Based on monitoring, create pools and associate your groups accordingly

32

SummaryCall-to-action

Resource Governor is a feature than enables DBAs to provide a consistent and predictable response to SQL Server workloads and system resource consumption (CPU, Memory).

Get started – create and test your workloads and tune your pools for improved control!

www.microsoft.com/techedTech·Talks Tech·Ed BloggersLive Simulcasts Virtual Labs

http://microsoft.com/technet

Evaluation licenses, pre-released products, and MORE!

Resources for IT Professionals

34

© 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,

IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.