life saver - resource governor for applications & reporting
TRANSCRIPT
![Page 1: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/1.jpg)
Life saver - Resource Governor for Applications & Reporting
18/08/2015
Gothenburg – Sweden
Dr. SubraMani Paramasivam (MANI)
![Page 2: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/2.jpg)
Sponsors
![Page 3: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/3.jpg)
About me
Dr. SubraMANI ParamasivamPhD., MCT, MCITP, MCP, MCSACEO, Principal Consultant & Trainer @ DAGEOP (UK) Email: [email protected]
Follow Us
https://www.facebook.com/pages/YOUR-SQL-MAN-LTD/http://www.youtube.com/user/YourSQLMAN
https://twitter.com/dageop
http://www.linkedin.com/pub/yoursqlman-ltd/78/972/172
![Page 4: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/4.jpg)
Please silence your phones & other devices
Duration• 60 minutes
Any questions?
Information
You Know Where It Is
![Page 5: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/5.jpg)
QUESTIONS
Memory
CPU
I/O
Applications / Databases
Resource Governor
Driving
![Page 6: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/6.jpg)
On completion of this session, attendees will be able to better understand & have knowledge in
Clear Lanes & Discipline
Memory, CPU, I/O
Workloads
Resource Governor
Understanding Applications &
Priorities
Segregation of Users / Service
Accounts
Resource Consumption
Objectives
![Page 7: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/7.jpg)
INTRODUCTION
Quick info about Resource
Governor
Lane Discipline & Resource Governor
Architecture
Measures & Disaster Recovery
Dangerous driving
Congestion / No Congestion
Performance matters
Simple StepsSettings & Config DEMOS Q & A
Contents
![Page 8: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/8.jpg)
Applications Database Reporting
INTRODUCTION
![Page 9: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/9.jpg)
Applications Database Reporting
INTRODUCTION
Architecture
Performance
Purpose
![Page 10: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/10.jpg)
Better architecture
![Page 11: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/11.jpg)
Better architecture
![Page 12: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/12.jpg)
Application3
Reporting
Database
Typical example of poor architectureApplication1
Application2
Ad-hoc
![Page 13: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/13.jpg)
PERFORMANCE MATTERS
CPU
MEMORY
DISK I/O
NETWORK
![Page 14: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/14.jpg)
Example of how your performance can slow down in a high end servers because of the unexpected nonsense attitudes.
![Page 15: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/15.jpg)
Lane Discipline
![Page 16: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/16.jpg)
Organize Lane Discipline
Ambulance /Fire Engine
POLICE / GOV
![Page 17: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/17.jpg)
Organize Users/Accounts with Resource Governor
Service Accounts
(Applications)
Service Accounts
(Reporting)DevelopersBusiness
users
INTERNAL
DEFAULT
![Page 18: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/18.jpg)
Service Accounts
(Applications)
Service Accounts
(Reporting)DevelopersBusiness
users
INTERNAL
DEFAULT
Lamborghini Egoista
![Page 19: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/19.jpg)
BusinessUser1
BusinessUser2
BusinessUser3
BusinessUser4
BusinessUser5
Developer1
Developer2
Developer3
Developer4
ServiceAct_Rep1
ServiceAct_App1
CPU
MEM
ORY
![Page 20: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/20.jpg)
UG_Application
UG_Reporting
UG_Developers
UG_Business
BusinessUser1
BusinessUser2
BusinessUser3
BusinessUser4
BusinessUser5
Developer1
Developer2
Developer3
Developer4
ServiceAct_Rep1
ServiceAct_App1WG_Application
WG_Reporting
WG_Developers
WG_Business
Default
Internal
RP_Application
RP_Reporting
RP_Developers
RP_Business
Default
Internal
CPU
MEM
ORY
Classifier
Function
![Page 21: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/21.jpg)
Quick information - Resource Governor• Introduced in 2008• Available in Enterprise, Developer & Evaluation Editions• Ability to classify connections and route the workload to specific lanes• Limited to SQL Server Engine only• Self contained and instance specific• Applies to CPU & Memory consumption only• 3 main concepts
• Resource Pool • Represents physical resource
• Workload Group• Categorizes the sessions and routes to relevant resource pool
• Classification• A function to decide the sessions to allocate the relevant group
![Page 22: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/22.jpg)
Quick information - Resource Governor
SYSTEM VIEWS
DMV
PERFORMANCE
COUNTERS
• sys.resource_governor_configuration• sys.resource_governor_resource_pools• sys.resource_governor_workload_groups
• sys.dm_resource_governor_workload_groups• sys.dm_resource_governor_resource_pools• sys.dm_resource_governor_configuration
• SQLServer:Workload Group Stats• SQLServer:Resource Pool Stats
![Page 23: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/23.jpg)
• New trainee running a query which occupies the fast lane.• Application slows down as the resources are occupied.• Threats to other important applications and application users.
Typical scenario – Dangerous driving
![Page 24: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/24.jpg)
Proactive measures
Recovery by DBA
Reactive measures
Recovery by Developers/Users
![Page 25: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/25.jpg)
DBA’s/System Administrators fight against all disasters after the incident has happened. Responsibility of proactive measures is not only the responsibility of the DBA but also the business users.
Disaster Recovery
![Page 26: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/26.jpg)
An example of how a SQL Server should not run.• No Activities RESULT: Very expensive project but not used• Too much of activitiesRESULT: Server hang because of too much of congestion on databases by every single session produced by users/applications.
Congestion / No congestion
![Page 27: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/27.jpg)
Resource Governor - Default Settings
![Page 28: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/28.jpg)
Before and After Config
![Page 29: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/29.jpg)
A bad example of how a SQL Server should not run.
One instance creates issues on other instances on the same server.
![Page 30: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/30.jpg)
AM I A RESOURCE GOVERNOR?YES NO
Manage server resource activities and prioritize to give HIGH/MEDIUM/LOW
performance on the requested processes categorized by Users/Service
Accounts/Applications/Databases
I will not control the Disk I/O and network activities.
But started supporting Disk I/O from SQL Server 2014 onwards.
![Page 31: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/31.jpg)
Simple Steps to enable/disable RG
• Expand Management
• Right click on Resource Governor and choose Enable
ALTER RESOURCE GOVERNOR RECONFIGURE;GO
ALTER RESOURCE GOVERNOR DISABLE;GO
![Page 32: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/32.jpg)
DEMO
![Page 33: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/33.jpg)
Happy Days
![Page 34: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/34.jpg)
Q & A
![Page 35: Life saver - Resource Governor for Applications & Reporting](https://reader035.vdocuments.us/reader035/viewer/2022062904/587f19f61a28ab350c8b5937/html5/thumbnails/35.jpg)
www.dageop.com