Indexes
Resource Governor
Database Tuning Advisor
Dynamic Management Views


Resource Governor

Resource Governor

Classifier Function.
We could classify incoming request based on various criteria
Login
Database Name
Host name
Application Name
IP Address

create function dbo.UserDivision()
returns sysname with schemabinding
 as
begin
return (select case is_member('dmain\sqlserveradmin')
                when  1 then 'HighPriorityGroup'
                when 0 then 'LowpriorityGroup'
                 else 'default'
end );
end


alter resource governor reconfigure;
alter resource governor disable.

Resource Pools

Pools of system resources are defined and then a percentage
of those resource is allocated to the pool

Only two resources can be managed

Resource Pool and workload group

create resource pool adminactivities
with
(  min_cpu_percent = 0,
   max_cpu_percent=50,
   min_memory_percent=0,
   max_memory_percent=40
);

create workload group HighPriority
with  (importance=high)
using Adminactivities
go

create workload group LowPriority
with  (importance=low)
using Adminactivities
go


alter  resource governor
with (classifier_function = dbo.UserDivision)

alter resource governor reconfigure
go