Resource Governor: управление ресурсами в SQL Server 2008. Часть 3

createproc dbo.reset_rg (

      @kill_sessions          bit = 1     -- Принудительно завершать открытые сессии

)

asbegin

set nocount on

declare

      -- Буфер для динамических вызовов

      @str_buffer       varchar(500)                

      --======= Переменные для работы с сессиями =======--

      -- Текущая сессия в цикле

      , @spid                      int

      -- Максимальное время ожидания на spin-блокировке в секундах          

      , @spin_timeout         int               = 30

      -- Кол-во раундов spin-блокировки до "засыпания"    

      , @spin_count_limit     tinyint           = 5  

      -- Кол-во прошедших раундов spin-блокировки   

      , @spin_count           tinyint           = 0  

      -- Время входа в spin-блокировку  

      , @start_spining  datetime

      -- Признак того, что для тек. сессии уже была вызвана команда kill    

      , @kill_pending         bit                         

      --======= Переменные для работы с группами =======--

      -- Идентификатор текущей группы нагрузки

      , @group_id             int        

      -- Имя текущей группы нагрузки                

      , @group_name           sysname                     

      --======= Переменные для работы с пулами =======--

      -- Идентификатор текущего пула

      , @pool_id              int                         

      -- Имя текущего пула

      , @pool_name            sysname                     

/*

      Так как мы собираемся избавиться от пользовательских групп,

      то сами должны работать либо в default либо в internal.

*/

if (

      notexists(

            select *

            from sys.dm_exec_sessions

            where

                  session_id = @@SPID

                  and (group_id = 1 or group_id = 2)

            )

)begin

      raiserror (N'Процедура запущена в сессии пользовательской группы нагрузки.', 16, 1)

      goto the_end     

end

-- Выключаем Resource Governor, чтобы новые подключения уже не могли

-- попасть в пользовательские группы нагрузки

alterresourcegovernordisable

-- Если флажок @kill_sessions выставлен, отправим в кромешный Адлер

-- все открытые соединения, существующие в пользовательских группах

-- Если @kill_sessions = 0 и сессии есть, то разразимся ошибкой.

while (1 = 1) begin

      set @spid = null

      select

            @spid = s.session_id

      from sys.dm_exec_sessions s

      innerjoin sys.resource_governor_workload_groups g on s.group_id = g.group_id

      where

            g.group_id != 1 and g.group_id != 2

      if ((@spid isnotnull) and (@kill_sessions = 0)) begin

            raiserror (N'В пользовательских группах есть сессии.', 16, 1)

            goto the_end

      end

      if (@spid isnull) goto reset_metadata

      select

            @start_spining = getdate()

            , @kill_pending = 0

            , @spin_count = 0

      -- Принудительно завершаем тек. сессию и ждём на spin-блокировке, пока она удалится

      while (exists(select * from sys.dm_exec_sessions where session_id = @spid)) begin

            set @spin_count+=1

            if (@kill_pending = 0) begin

                  set @str_buffer = 'kill ' + cast(@spid as varchar)

                  exec (@str_buffer)

                  set @kill_pending = 1

                  print'[' + cast(@spid as varchar) + N'] произведён контрольный выстрел'

            end

            print'[' + cast(@spid as varchar) + N'] сессия всё ещё жива, пульс в норме'

            if (@spin_count > @spin_count_limit)

                  waitfor delay '00:00:01'

            if (DATEDIFF(ss, @start_spining, GETDATE()) > @spin_timeout) begin

                  raiserror (N'Время ожидания истекло, сессия [%d] так и не умерла', 16, 1, @spid)

                  goto the_end

            end

      end

      print'[' + cast(@spid as varchar) + N'] сессия отправилась в мир иной ' + char(134)

end

reset_metadata:

      begintran

            while (1 = 1) begin         

                  select

                        @group_id = null

                  selecttop 1

                        @group_id = group_id

                        , @group_name = name

                  from sys.resource_governor_workload_groups

                  where

                        name != 'internal'and name != 'default'

                  if (@group_id isnotnull) begin

                        set @str_buffer = 'drop workload group ' + quotename(@group_name)

                        exec (@str_buffer)

                        if@@error != 0 begin

                             rollback

                             goto the_end

                        end

                        print quotename(@group_name) + N' группа нагрузки уничтожена ' + char(134)        

                  endelse

                        break

            end  

            -- Восстанавливаем значения по умолчанию для группы default

            alterworkloadgroup [default]

            with (

                  importance = medium

                  , request_max_memory_grant_percent = 25

                  , request_max_cpu_time_sec = 0

                  , request_memory_grant_timeout_sec = 0

                  , max_dop = 0

                  , group_max_requests = 0

            )

            while (1 = 1) begin         

                  select

                        @pool_id = null

                  selecttop 1

                        @pool_id = pool_id

                        , @pool_name = name

                  from sys.resource_governor_resource_pools

                  where

                        name != 'internal'and name != 'default'

                  if (@pool_id isnotnull) begin

                        set @str_buffer = 'drop resource pool ' + quotename(@pool_name)

                        exec (@str_buffer)

                        if@@error != 0 begin

                             rollback

                             goto the_end

                        end

                        print quotename(@pool_name) + N' пул ресурсов уничтожен ' + char(134)        

                  endelse

                        break

            end        

            -- Восстанавливаем значения по умолчанию для пула default       

            alterresourcepool [default]

            with (

                  min_cpu_percent = 0

                  , max_cpu_percent = 100

                  , min_memory_percent = 0

                  , max_memory_percent = 100

            )

            -- Сбрасываем функцию классификатор

            alterresourcegovernor

            with (classifier_function = null) 

      commit

      -- Применяем настройки и включаем Resource Governor 

      alterresourcegovernorreconfigure

the_end:

      -- Парадный выход

end

go

exec dbo.reset_rg 1

alterresourcepool [default]

with (

      min_cpu_percent = 0

      , max_cpu_percent = 100

      , min_memory_percent = 0

      , max_memory_percent = 100

      , min_memory_percent = 5

      , min_memory_percent = 10

      , min_memory_percent = 20

)

go

select *

from sys.resource_governor_resource_pools