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ą.

poniedziałek, 10 lutego 2014

Cachowanie planów wykonania

SQL Server domyślnie przechowuje plany wykonania dla wszystkich zapytań. Każdy plan zajmuje jednak trochę miejsca i jak tego miejsca na serwerze będzie za mało to inne procesy mogą z tego powodu działać znacznie wolniej. Poza planami wykonania zapytań server w cachu przechowuje też dane, które były zwrócone przez zapytania, tak by w przypadku ponownego uruchomienia tego samego zapytanie nie trzeba było odczytywać ponownie danych z dysku.

Dlatego, też jeżeli server ma w pamięci zbyt dużo planów wykonania, które nie podnoszą wydajności serwera mniej miejsca będzie dostepnego dla danych.

Jak już wspomniałem, SQL server domyślnie przechowuje wszystkie plany, ale w zależności od rodzaju zapytania, mogą być one w różnej postaci.
--Query 1  (ad hoc)
select * from [sys].[objects] where object_id = 3
go
 
--Query 2 (ad hoc)
declare @id int = 3
select * from [sys].[objects] where object_id = @id
go
Powyższe dwa zapytania są klasycznym przykładam zapytań ad hoc. Wykonanie tego skryptu spowoduje, że w cachu pojawią się dwa plany wykonania. Zmiana wartości predykatu wzapytaniu spowoduje wygenerowanie nowego planu. Niekonieczie musi być to zła wiadomość z punktu widzenia poszczególnych zapytań (lepsze wykorzystanie statystyk). Trochę więcej na ten temat pisałem tutaj.

Zdarza się, że tego typu zapytania znajdują się w aplikacjach. Z reguły oznacza to kiepską architekturę systemu. Choć pewnie może wynikać też z faktu, że kiedyś kawałek kodu został napisany tylko “tymczasowo” i tak już zostało. Czasami też po prostu łatwiej skorzystać z zapytania ad hoc niż tworzyć kolejną procedurę składowaną. Tak czy siak plan wykonania będzie w cachu.

Innym podejściem jest wykorzystanie sp_executesql.
--Query 3 (prepared statement)
declare @id int = 3
declare @sql nvarchar(max)
set @sql = N'select * from sys.objects where object_id = @id'
exec sp_executesql @sql, N'@id int', @id = @id
go
W tym przypadku plan wykonania będzie jeden niezależnie od wartości predykatu. W zależności od liczebności zbioru danych i jakości statystyk niewłaściwe indeksy mogą być wykorzystane dla konkretnego warunku. Dobra wiadomość jest taka, że tylko jeden plan wykonania będzie zapisany. Jest to zdecydowana poprawa w stosunku do poprzedniej opcji.

Jeżeli zapytanie jest wykonywane bardzo sporadycznie, do tego stopnia, że nie przeszkadza nam ponowne wygenerowanie planu wykonania, warto zastanowić się nad OPTION(RECOMPILE). W przypadku zastosowania tej opcji w zapytaniu, jego plan nie zostanie dodany do cachu. Skutkiem ubocznym, będzie to, że każde wykonanie zapytania będzie wymagało jego kompilacji.

Poniżej przykład tego typu zapytania
--Query 4 (recompile)
select * from sys.objects where object_id = 3 option(recompile)
go
OPTION(RECOMPILE) może być także użyte w przypadku sql_executesql.
Warto mieć powyższe na uwadze projektując aplikacje i dostęp do bazy danych. Nie każde zapytanie jest takie samo i różne sposoby ich wykonywania mogą poprawić wydejność serwera.

czwartek, 6 lutego 2014

Tabele przestawne na wielu kolumnach

Spotkałem się dzisiaj z ciekawym problemem. Otóż zadanie wymagało stworzenia tabeli przestawnej na wielu kolumnach.

Rozwiązanie problemu na pierwszy rzut oka niekoniecznie jest sprawą trywialną, ale z pomoca row_number i cte nie takie zagadki idzie rozwiązać.

Struktura tabel wygląda następująco:

Jako wynik oczekiwany jest następujący zbiór danych. Ilość osób na raporcie jest ściśle określona i wynosi 6. Jeżeli ktoś jest zainteresowany dynamicznymi tabelami przestawnymi to zapraszam do lektury mojego starszego wpisu.


Poniżej skrypt który tworzy i wypełnia tabelę. Tak na wszelki wypadek jak ktoś by sobie chciał to przetestować.
use tempdb
go
 
if not exists(select * from sys.tables where name = 'Driver')
create table Driver (
    DriverId int,
    Forename varchar(50),
    Surname varchar(50),
    DOB date
)
 
if not exists(select * from sys.tables where name = 'PolicyDriverLink')
create table PolicyDriverLink (
    DriverId int,
    PolicyId int
)
 
if not exists(select * from sys.tables where name = 'Policy')
create table Policy (
    PolicyId Int,
    PolicyNumber varchar(100)
)
 
if not exists(select * from Policy)
insert into Policy (PolicyId, PolicyNumber) values 
(1, 'POL1/2014'), 
(2, 'POL2/2014'),
(3, 'POL3/2014')
 
if not exists(select * from Driver)
insert into Driver (DriverId, Forename, Surname, DOB) values 
(1, 'John', 'Smith', '01 Jan 1980'), 
(2, 'Alan', 'Banks', '04 Aug 1960'), 
(3, 'Jacob', 'Brown', '10 Dec 1977'),
(4, 'Jessica', 'Anderson', '05 Sep 1984'),
(5, 'Monica', 'Johnson', '22 Oct 1979'),
(6, 'Samantha', 'Adler', '03 Feb 1980')
 
if not exists(select * from PolicyDriverLink)
insert into PolicyDriverLink (DriverId, PolicyId) values
(1,1), (2,1), (3, 1), (4, 1), (5, 1), (6,2), (4,3), (6,3)
Cała magia polega na skorzystaniu z row_number i cte.

Poniżej skrypt umożliwiający uzyskanie porządanego rezultatu.
;with cte as (
    select
    p.PolicyId,
    p.PolicyNumber,
    d.Forename,
    d.Surname,
    d.DOB,
    ROW_NUMBER() over(PARTITION by pdl.PolicyId order by p.PolicyNumber) as rn
    from Policy p
        inner join PolicyDriverLink pdl
            on p.PolicyId = pdl.PolicyId
         inner join Driver d
            on pdl.DriverId = d.DriverId
            
), cte2 as (
    select
        c1.PolicyNumber,
        max(case when c1.rn = 1 then c1.Forename else null end) as D1_Forename,
        max(case when c1.rn = 1 then c1.Surname else null end) as D1_Surname,
        max(case when c1.rn = 1 then c1.DOB else null end) as D1_DOB,
        
        max(case when c2.rn = 2 then c2.Forename else null end) as D2_Forename,
        max(case when c2.rn = 2 then c2.Surname else null end) as D2_Surname,
        max(case when c2.rn = 2 then c2.DOB else null end) as D2_DOB,
        
        max(case when c3.rn = 3 then c3.Forename else null end) as D3_Forename,
        max(case when c3.rn = 3 then c3.Surname else null end) as D3_Surname,
        max(case when c3.rn = 3 then c3.DOB else null end) as D3_DOB,
        
        max(case when c4.rn = 4 then c4.Forename else null end) as D4_Forename,
        max(case when c4.rn = 4 then c4.Surname else null end) as D4_Surname,
        max(case when c4.rn = 4 then c4.DOB else null end) as D4_DOB,
        
        max(case when c5.rn = 5 then c5.Forename else null end) as D5_Forename,
        max(case when c5.rn = 5 then c5.Surname else null end) as D5_Surname,
        max(case when c5.rn = 5 then c5.DOB else null end) as D5_DOB,
        
        max(case when c6.rn = 6 then c6.Forename else null end) as D6_Forename,
        max(case when c6.rn = 6 then c6.Surname else null end) as D6_Surname,
        max(case when c6.rn = 6 then c6.DOB else null end) as D6_DOB
    from cte c1
        left join cte c2
            on c1.PolicyId = c2.PolicyId and c1.rn = 2
        left join cte c3
            on c1.PolicyId = c3.PolicyId and c1.rn = 3
        left join cte c4
            on c1.PolicyId = c4.PolicyId and c1.rn = 4
        left join cte c5 
            on c1.PolicyId = c5.PolicyId and c1.rn = 5
        left join cte c6
            on c1.PolicyId = c6.PolicyId and c1.rn = 6
    group by c1.PolicyId, c1.PolicyNumber
)
 
select
    PolicyNumber, 
    D1_Forename, D1_Surname, D1_DOB,
    D2_Forename, D2_Surname, D2_DOB,
    D3_Forename, D3_Surname, D3_DOB,
    D4_Forename, D4_Surname, D4_DOB,
    D5_Forename, D5_Surname, D5_DOB,
    D6_Forename, D6_Surname, D6_DOB
from cte2

sobota, 1 lutego 2014

Cross join czy nie cross join

Tym razem kolejna zagadka z odpowiedzią. Mając poniższe tabele jak uzyskać iloczyn kartezjański.

Col1
1

Col2
a
b
c

set nocount on
 
declare @Table1 table (
    Col1 int
)
 
declare @Table2 table (
    Col2 char(1)
)
 
insert into @Table1 (Col1) values(1)
insert into @Table2 (Col2) values('a'), ('b'), ('c')
Najprostrzym rozwiązaniem jest po prostu użycie cross join.
select Col1, Col2 from @Table1, @Table2
Dużo osób zajmujących się relacyjnymi bazami danych nie bardzo ma pojęcie gdzie ten cross join można wykorzystać. Tak więc aby trochę skomplikować zadanie, chciałbym ten iloczyn kartezjański uzyskań korzystając z inner join.


Poniżej odpowiedź na tą łamigłowkę.
select Col1, Col2 from @Table1 inner join @Table2 on 1=1
Jednakże to nie wszystko. Bo ten sam wynik można uzyskać za pomocą left join i full outer join.
select Col1, Col2 from @Table1 left join @Table2 on 1=1
select Col1, Col2 from @Table1 full outer join @Table2 on 1=1
Co ciekawe pomimo tego, że zapytania zwracają ten sam wynik SQL Server wykonuje je w inny sposób. Jako ciekawostkę polecam przyjrzenie się właściwym planom wykonania i zwrócenie uwagi jaki operator złączenia jest wykorzystywany, jak także porównanie szacowanych wartości z tymi które rzeczywiście miały miejsce (Estimated vs Actual Number Of Rows oraz Estimated Number Of Executions vs Number Of Executions).