wtorek, 11 lutego 2014

Usuwanie planów zapytań

W poprzednim poście poruszyłem temat planów wykonania zapytań i to, że mogą mieć one negatywny wpływ na wydajność bazy danych w przypadku gdy nie są ponownie wykorzystywane. Plany o niskim poziomie ponownego wykorzystania tylko zabierają miejsce na dane.

Co więc zrobić aby serwer funkcjonował wydajnie bez konieczności jego częstego restartu?

Otóż plany wykonania można usunąć z bazy danych.

Do tego calu służy dunkcja DBCC FREEPROCCACHE, która przyjmuje plan_handle jako parametr.
Poniższe zapytanie może być wykorzystane do zidentyfikowania potęcjalnych planów zapytań do usunięcia.
select
    cp.plan_handle,
    cp.usecounts,
    cp.size_in_bytes,
    cp.cacheobjtype,
    cp.objtype,
    txt.[text] 
from sys.dm_exec_cached_plans AS cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) txt
order by cp.usecounts 
Wywoływania DBCC FREEPROCCACHE manualnie raczej nie jest najbardziej optymalnym rozwiązaniem. Dlatego poniżej dwie opcje na poradzenie sobie z problemem w sposób automatyczny, poprzez użycie zapytania w zadaniu SQL Agenta.

Opcja 1. Usuwanie wszystkich zapytań typu ad hoc.
--Remove all Adhoc plans
declare @plan_handle varbinary(64)
declare objtype = 'Adhoc'
open plancleanup
fetch next from plancleanup into @plan_handle
while @@fetch_status = 0
begin
    dbcc freeproccache(@plan_handle)    
    fetch next from plancleanup into @plan_handle
end
close plancleanup
deallocate plancleanup
go
Opcja 2. Usuwanie zapytań ad hoc jak i też tych przygotowanych (sp_executesql) w przypadku gdy ich wielkość w pamięci przekracza ustalony próg .
--Remove all plans when they take more than 20 MB.
declare @cachesize decimal(10,2)
declare @threshold decimal(10,2)
set @threshold = 20
select @cachesize = (sum(size_in_bytes)*1.)/1024/1024 from sys.dm_exec_cached_plans where objtype in ('Adhoc', 'Prepared')
if(@cachesize > @threshold) 
begin
DBCC FREESYSTEMCACHE('SQL Plans');
end
go
Wywołanie DBCC FREEPROCCACHE bez parametów usunie także plany wykonania dla procedur składowanych. Nie jest to dobra opcja.

Powyższe zapytania na serwerach produkcyjnych powinny być wykonywane z rozwagą.

Prześlij komentarz