SQL Server 2008 Optimization

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