Transcript Managing resources

Balmukund Lakhani
Technical Lead – SQL Support Team
http://blogs.msdn.com/SQLServerFAQ
http://blogs.msdn.com/BLakhani
Currently
Technical Lead – SQL Support Team.
Owner http://blogs.msdn.com/SQLServerFAQ
Active Participant in MSDN Forum for SQL.
In past
Sr. Support Engineer - Microsoft
Premier Field Engineer – Microsoft
Support Engineer - Microsoft
ERP Consultant – Ramco Systems
Sr. Systems Analyst – Ramco Systems
SQL Developer / DBA – Ramco Systems
Managing Resources
Key Concepts of Resource Governor
Configuration
Usage Scenarios
Why there is a need of Resource Governor
There have been asks for “limits”
Control Run-away queries.
Give control to DBA.
SQL Server
Backup
Admin Tasks
OLTP
Activity
Ad-hoc
Reports
Executive
Reports
Memory, CPU,
Threads
Single resource pool
Database engine doesn’t
differentiate workloads
Best effort resource
sharing
Tell me more…
Classifier Function
• Classify connection to workload group
Workload Group
• Aggregate similar session requests
Resource Pool
• Manage server’s physical resources
SQL Server
Backup
Admin Tasks
Admin
Workload
OLTP
Activity
Executive
Reports
Ad-hoc
Reports
OLTP
Workload
Memory, CPU,
Threads
Report
Workload
Ability to differentiate
workloads
e.g. app_name, login
Per-request limits
Max memory %
Max CPU time
Grant timeout
Max Requests
Resource monitoring
SQL Server
Backup
Admin Tasks
Admin
Workload
OLTP
Activity
Executive
Reports
Ad-hoc
Reports
High
OLTP
Workload
Memory, CPU,
Threads
Report
Workload
A workload can have an
importance label
Low
Medium
High
Gives resource allocation
preference to workloads
based on importance
SQL Server
Backup
OLTP
Activity
Admin Tasks
Admin
Workload
Executive
Reports
Ad-hoc
Reports
High
OLTP
Workload
Report
Workload
Min Memory 10%
Max Memory 20%
Max CPU 20%
Max CPU 90%
Admin Pool
Application Pool
Resource pool: A virtual
subset of physical
database engine
resources
Provides controls to
specify
Min Memory %
Max Memory %
Min CPU %
Max CPU %
Max DOP
Resource monitoring
Up to 20 resource pools
SQL Server
Backup
OLTP
Activity
Admin Tasks
Admin
Workload
Executive
Reports
Ad-hoc
Reports
High
OLTP
Workload
Report
Workload
Workloads are mapped
to Resource Pools (n : 1)
Online changes of
groups/pools
SQL Server 2005 =
default group + default
pool
Main Benefit
Prevent run-away queries
Min Memory 10%
Max Memory 20%
Max CPU 20%
Max CPU 90%
Admin Pool
Application Pool
host_name ()
app_name ()
suser_name ()
suser_sname ()
is_srvrolemember ()
is_member ()
original_db_name ()
loginproperty
connectionproperty
UDF rules apply:
No data modification
No DCL
No transactions
No stored procedures
No XPs with rowset
No EXEC
No linked servers
Show me the demo…
Let’s take a look at runaway query.
Why didn’t SQL Server put a limit on the
CPU that the query was using?
If there are no other cars on the road, why
should the governor slow down the solo driver?
Why don’t we try that again, but this time we
will add one more session
Let’s take a look at that runaway query
again.
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
Where can you use?
Problem:
Solution:
My users won’t let me put
Payroll and GL on the same
server.
Configure RG by application
or database to minimise
interference.
3rd party packages have
unknown, uncontrollable
queries.
Offer enhanced performance
for key business cycles, such
as pay-day or end-of-quarter.
Problem:
Solution:
Excel and Access users use
production databases for
workgroup applications.
Create workload groups by
application, such as
‘%excel%’ and ‘%access%’
Particularly for reporting, we
can’t control their impact on
transaction processing .
Assign those workload
groups to a low-priority
resource pool.
Use workload connection
limits to restrict concurrent
usage.
Database Engine only – no SSAS, SSRS, SSIS
Many components are not subject to RG, for
example sp_OA, linked server queries, XPs,
database mail
Limit of 18 user-defined resource pools (20
total)
Cannot constrain internal pools / groups
No throttling of I/O yet.
Enterprise Edition only!
Home work…
This is just a quick start to the resource
governor, we can do much more with this.
Classifiers based on other criteria
Time of day
Application connecting to certain DB
Using the DMVs to determine issues and
modify workload groups or resource pools
on the fly
Much more!
Books Online
www.SQLCommunity.com
http://blogs.msdn.com/b/psssql
http://blogs.technet.com/b/sqlos
© 2009 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.