controlling resources in sql server
DESCRIPTION
Latest presentation that was recently presented in Holland. Links to code is at http://bit.ly/10xLkgb and there is a video of this presentation at http://bit.ly/10xMubBTRANSCRIPT
![Page 1: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/1.jpg)
Controlling Resources in SQL Server
Chris Testa-O’Neill – SQL Server MVP
Principal Consultant
Microsoft SQL Server and Azure Consulting
![Page 2: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/2.jpg)
Who Am I?
Microsoft SQL Server and Azure Consulting
![Page 3: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/3.jpg)
Publications
AUTHOR
• Collection 6233AE: Implementing and Maintaining Business Intelligence in Microsoft SQL Server 2008
• Collection 2778BE: Querying and Modifying Data in Microsoft SQL Server 2008 with Transact-SQL
• Collection 6369AE: Implementing and Maintaining Microsoft SQL Server 2008
• Collection 6370AE: Microsoft SQL Server 2008 R2 Database Development
AUTHOR/TECHNICAL REVIEWER
• Course 20467A: Designing Business Intelligence Solutions with Microsoft SQL Server 2012
TECHNICAL REVIEWER
• Course 10777A: Implementing a Data Warehouse with Microsoft SQL Server 2012
• Course 10778A: Implementing Data Models and Reports with Microsoft SQL Server 2012
TECHNICAL REVIEWER
• Course 20463A: Implementing a Data Warehouse with Microsoft SQL Server 2014
• Course 20466A: Implementing Data Models and Reports with Microsoft SQL Server 2014
• Course 20467B : Designing Business Intelligence Solutions with Microsoft SQL Server 2014
• Course 10977A: Updating Your SQL Server Skills to Microsoft SQL Server 2014
Microsoft SQL Server and Azure Consulting
![Page 4: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/4.jpg)
Agenda
•Resource Governor
•Policy-Based Management
Microsoft SQL Server and Azure Consulting
![Page 5: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/5.jpg)
Resource Governor• Monitor and manage the workloads on your SQL Server
system
• Enterprise Edition feature only
• Control CPU and memory usage specifically
• Works well with applications of similar requirements
• System Monitor Counters available
• DAC connection not subject to resource governor
Microsoft SQL Server and Azure Consulting
![Page 6: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/6.jpg)
Resource Governor Components
• Resource Pools represents the physical resources of the server
Internal and default
• Workload Groups serves as a container for session requests that are
similar
• Classification Function a set of user-written criteria contained in a function
Microsoft SQL Server and Azure Consulting
![Page 7: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/7.jpg)
Demo
Microsoft SQL Server and Azure Consulting
![Page 8: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/8.jpg)
Policy Based management
• Enforce the configuration of SQL Server On all editions for single instance
Enterprise and Standard for multiple instances
• Can be used to centrally managed multiple servers
• Can export and import policies
• Can evaluate policies
Microsoft SQL Server and Azure Consulting
![Page 9: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/9.jpg)
Policy Based management componentsComponent Description
Policy A SQL object that holds the information required to enforce a policy
Facet A facet is an object within a policy that is used to represent a SQL Server component
Target A SQL Server object such as an Instance or a database to which the policy is applied to
Condition Specifies a set of allowed states of a Policy-Based Management managed target with regard to a facet.
Microsoft SQL Server and Azure Consulting
![Page 10: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/10.jpg)
How policies are enforced
• Manually
• Scheduled
• On change prevent – (DDL triggers)
• On change log only – (event notifications)
Microsoft SQL Server and Azure Consulting
![Page 11: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/11.jpg)
Demo
Microsoft SQL Server and Azure Consulting
![Page 12: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/12.jpg)
Questions?
Microsoft SQL Server and Azure Consulting
www.claribi.co.uk
@ctesta_oneill
@_claribi_
![Page 13: Controlling Resources in SQL Server](https://reader033.vdocuments.us/reader033/viewer/2022060119/558d058ad8b42a47668b45e7/html5/thumbnails/13.jpg)
Thank you!
Microsoft SQL Server and Azure Consulting