Dealing with Bad RoomatesSQL Server Resource Governor

Joey D’Antoni

20 July 2015

Joey D’Antoni

• Joey has over 15 years of experience with a wide variety of data platforms, in both Fortune 50 companies as well as smaller organizations

• He is a frequent speaker on database administration, big data, and career management

• He is the co-president of the Philadelphia SQL Server User’s Group• MSCE, Business Intelligence, Data Platform• He wants you to make sure you can restore your data

Joeydantoni.com

Agenda

The Problems with sharing your SQL Servers

An Introduction to Resource Governor

How to Implement Resource Governor

Demo

Bad Roomates

Bad Roommates in SQL Server

Is this your SQL Server?

Or is this your SQL Server?

Other Applications

Poll Question

Tell us about your SQL Server environment…

Resource Management < SQL 2008

SQL Server

Memory, CPU, Threads

Resources

Introducing Resource Governor

Introduced in SQL Server 2008

Enterprise Edition Only

Controls CPU and Memory Resources (2008-2012)Controls CPU, IOPs, and Memory Resources (2014)

Why Use Resource Governor?

Prioritization between apps and

users

Limit runaway queries

Give control back to the

DBA

Resource Governor Concepts

Resource Pool

Manages server resources

Workload GroupAggregates similar session requests

Classifier FunctionClassifies connection to its workload group

Resource Governor Workloads

Split Workloads• Application Name• Login • Not Database

Per Request Limits• Max Memory %• Max CPU Time• Grant Timeout

Resource Monitoring

SQL Server

Memory, CPU, Threads

Resources

AdminWorkload

OLTPWorkload

ReportWorkload

Importance

Workloads have importance labels

• Low• Medium• High

Adjusts relative importance to workloads for resource allocation

Resource Governor Pools

Resource Pool: A subset of database resources

Controls• Minimum CPU %• Maximum CPU % • Minimum Memory %• Maximum Memory %

Workload Group Controls• Importance• Maximum Requests• CPU Time (sec)• Memory Grant %• Grant Time Out• MaxDOP• Min and Max IOPS Per Volume

Poll Question:

Have you used resource governor?

Classifier Function

User defined scalar function to classify incoming connections to resource group

One per instance

Does not apply to internal workload group

Evaluated for every new connection

Should be in master database

If no function all connections go to default group

Resource Governor Overview

Limitations

Internal Pool always wins

Default Pool is always there

No resource contention=no resource governing

Importance is only weight

Does not recognize waiting tasks

No TempDB limits

Demo

Implementing and Using Resource Governor

Monitoring Resource Governor

Metadata CVs:• sys.resource_governor_resource_pools• sys.resource_governor_workload_groups• sys.resource_governor_configuration

Running value DMVs:• sys.dm_resource_governor_resource_pools• sys.dm_resource_governor_workload_groups• sys.dm_resource_governor_resource_pool_volu

mes

Use Cases

SQL Server Consolidation

Servers with mixed OLTP and Reporting

Workloads

Using SharePoint and Dynamics with other

databases

Other Limitations

Database Engine Only—Nothing for SSAS, SSRS,

SSIS

Limit of 18 user-defined resource

pools

Many components

(Database Mail, linked server

queries, XPs) are not subject to

resource governor

No IO throttling until SQL 2014

Questions