Vaicājuma kompilācijas ietekme uz ātrdarbību

Vaicājuma kompilēšana ietekmē vaicājuma izpildes ātrumu. Ar to saistīta vaicājuma radītā procesora slodze un izmantotais atmiņas apjoms. Tātad esam ieinteresēti, lai kompilācijas notiktu pēc iespējas retāk. Aprakstīts neliels eksperiments ko veicu ar nolūku salīdzināt vaicājumu izpildes ātrumu veicot izpildes plāna kompilāciju un bez tās.

Kas tiek salīdzināts

Salīdzināju divus dažādus vaicājumus. Pirmajā gadījumā ievietotā vērtība tiek norādīta kā skaitlis:

insert into #t (i) values (1);
insert into #t (i) values (2);
...

Un otrā gadījumā vērtība tiek norādīta kā parametrs:

insert into #t (i) values (@i); // @i = 1
insert into #t (i) values (@i); // @i = 2
...

Lai varētu kaut ko skatīt, izpildīšu 10 000 šādu vaicājumu, kas izdara vienu un to pašu uzdevumu. Pirmajā gadījumā SQL Server tiks sūtīti 10 000 sintaktiski dažādas Insert komandas, katrā atšķirsies ievietojamais skaitlis. Otrajā gadījumā tiek izpildītas 10 000 sintaktiski identiskas komandas, mainoties parametra vērtībai.

Kāpēc interesē šis salīdzinājums

Pirmais gadījums ir "sliktais". Tādi rodas, piemēram, šādās situācijās:
* Neoptimāls klienta aplikācijas kods (.Net rīki rūpējas par pareizu komandu izsaukumu. Tomēr slikto scenāriju nav grūti iegūt. Piemēram, ja kodā sāk parādīties kas šāds: "Select x From y Where i = " + i.ToString())
* Ģenerēts skripts (SSMS: Skriptu ģenerēšana tabulas datiem)
* Izpildot komandas SSMS (Piemēram, Select x From Y where i = 6). Šī dēļ testējot vaicājuma ātrdarbību SSMS ir vērts šo vaicājumu izpildīt divreiz. pirmajā reizē tiks iekļauts arī kompilācijas laiks.
* .....citi varianti

Rezultāti

10 000 rindu Insert
Kompilējot plānus katram "insert" = 00:00:08:540 (hh:mi:ss:mmm)
Nekompilējot plānus katram "insert" = 00:00:01:070 (hh:mi:ss:mmm)

Visai jūtama atšķirība (lai arī izpildes laiks pats par sevi nav tas objektīvākais rādītājs). Lai vairāk redzētu kas notiek procesa laikā uzkrāju informāciju izmantojot permon. Uzkrāti 3 dažādi skaitītāji:
* Cache Hit Ratio- cik bieži plāns ir atrasts kešatmiņā
* Cache Object Count- plānu skaits, kas ir kešatmiņā
* SQL Compilations/sec- cik plāni tiek kompilēti sekundē

Tātad, skripta izpildes posmi:
1: iztīrīta SQL Server plānu atmiņa, nekāda aktivitāte nenotiek.
2: Izpilda 10 000 sintaktiski dažādas insert komandas. Katra insert komanda tiek kompilēta (SQL Compilations/sec), tiek ievietota atmiņā (redzam, cik daudz izpildes plānu ir atmiņā), Cache Hit Ratio tuvojas 50% robežai, tas ir tādēļ, ka tiek izpildītas divas komandas. Viena ir tā, kas nosaka ievietojamo vērtību (un tā abos gadījumos ir identiska komanda) un otra, kas izpilda insert komandu.
3: SQL Server neko nedara. Rādītāji nemainās.
4: Iztīrīta SQL Server plānu atmiņa, SQL Server neko nedara.
5: Izpilda 10 000 sintaktiski vienādas insert komadas. Izpildes laikā Cache Hit Ratio pietuvojas 100%, jo abas izpildītās komandas ir SQL Server plānu atmiņā. Notiek tikai dažas kompilācijas (dēļ statistikas izmiņām) un plānu atmiņa saglabājas maza.
6: SQL Server neko nedara. Rādītāji nemainās.
7: Iztīrīta SQL Server plānu atmiņa, SQL Server neko nedara.

Vēl interesanti ir pavērot darbību ar SQL Server Profiler, apskatot šādus notikumus:
SP:CacheHit
SP:CacheInsert
SP:CacheMiss

Izpildītais skripts

Jāņem vērā, ka skripta laišanai nepieciešamas sysadmin tiesības, jo izpildes laikā tiek iztīrīta izpildes plānu atmiņa (kas var uz laiku ietekmēt citu ātrdarbību).

/* ===================== Nekompilējot plānus =======================*/
-- Iztīra plānus no atmiņas:
DBCC FREEPROCCACHE;
Go
WaitFor Delay '00:00:05'
Go
create table #t
(
    id int primary key identity,
    i int,
    dat datetime default getdate()
)
Go
declare @i int;
declare @com nvarchar(2000);
set @i = IDENT_CURRENT('tempdb..#t')
set @com = N'insert into #t (i) values (' + cast(@i as nvarchar(20)) + N')';
exec sp_executesql @com;
Go 10000
select
    'Kompilējot plānus katram "insert" = ' +
    CONVERT(VARCHAR(30), max(dat) - min(dat),114) + ' (hh:mi:ss:mmm)'
from #t
drop table #t
Go
-- pagaida 5 sekundes:
WaitFor Delay '00:00:05'
/* ===================== Nekompilējot plānus =======================*/
-- Iztīra plānus no atmiņas:
DBCC FREEPROCCACHE;
-- pagaida vēl 5 sekundes:
WaitFor Delay '00:00:05'
Go
create table #t
(
    id int primary key identity,
    i int,
    dat datetime default getdate()
)
Go
declare @i int;
set @i = IDENT_CURRENT('tempdb..#t')
exec sp_executesql N'insert into #t(i) values (@i)', N'@i int', @i;
Go 10000
select
    'Nekompilējot plānus katram "insert" = ' +
    CONVERT(VARCHAR(30), max(dat) - min(dat),114) + ' (hh:mi:ss:mmm)'
from #t
drop table #t
Go
-- pagaida 5 sekundes:
WaitFor Delay '00:00:05'
Go
DBCC FREEPROCCACHE

Plānu atmiņu var apskatīt izpildod šo komandu (pareizajos brīžos):

SELECT
    execution_count,
    -- sql:
     st.text,
    -- izpildes plāns (klikšķinam virsū, lai grafiski parādītos)
    pl.query_plan
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
OPTION (RECOMPILE);