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 |
SQL Server 2008 >