Configuring Resource Governor
Resource Governor is a SQL Server 2012 feature that enables you to place limits on the consumption of CPU and memory resources within a SQL Server 2012 Database Engine instance.
It enables us to allocate session requests to different resources based on the characteristics of the session request properties
It can help you get a handle on out-of-control queries by throttling users (not literally ).
It cannot used to :
- manage system resources for Analysis Services
- manage Integration Services
- manage Reporting Services
- perform workload monitoring or workload management between SQL Server Database Engine instances
Online transaction processing (OLTP) queries are often short and do not use the CPU long enough for Resource Governor constraints to apply.
You can enable Resource Governor by either browsing in SQL Server Management Studio, expanding the Management node, editing the Resource Governor properties and then choosing “Enable Resource Governor” or we can use the Transact-SQL statement:
ALTER RESOURCE GOVERNOR RECONFIGURE; GO
Disabling is just as easy
ALTER RESOURCE GOVERNOR DISABLE; GO
As this is a big subject I have expanded this study note over additional pages, see below.