Scriptek kategória bejegyzései

Hiányzó index megkerülése

Előfordul olyan eset, amikor kéne egy index, de nincs. Feltenni nem tudjuk, nem akarjuk, nem lehet, stb, a lényeg, hogy nem is lesz, de a query-t fel kellene gyorsítani. Ilyen esetben meg lehet próbálkozni hasonlóságokat keresni az adatbázisban, amikre támaszkodva kicsit másképp, de megírható az sql kód, ami ugyanazt az eredményhalmazt adja vissza, viszont teljesítményben nagyságrendekkel felülmúlja azt.

Kifejtem egy egyszerű példán keresztül, aztán le is demózzuk. Van egy transactions táblánk sok sorral az alábbi mezőkkel:
– id: egyedi azonosító, folyamatosan növekvő sorszámmal
– user_id: adott user-hez tartozik a tranzakció
– amount: a tranzakció összege
– create_date: a tranzakció dátuma (kb. adatbázisba kerülés időpontja)
– egyéb, számunkra most lényegtelen oszlopok

A táblán 1 index van, az id mezőn, ami primary key és clustered index is.

A feladat, hogy számoljuk meg adott időponttól a felhasználói tranzakciókat. Ebben az esetben a lekérdezés ez lenne:

select
	count(*) as cnt
from transactions
where create_date >= @datum

Index híján ebből clustered index scan lesz, ami jelen esetben egyenlő a teljes tábla beolvasásával és ez nem szokott hatékony lenni. Ilyenkor jöhet az ötletelés, az ötlet pedig itt a hasonlóságon alapul. Az id és a create_date mezők hasonlítanak egymásra abban, hogy mindketten kb. növekvő sorrendben haladnak. Nézzünk erről egy képet:

missing_index_01

Azokat a sorokat keressük, ahol a create_date >= ‘2010-01-17’ -el. A zöld vonal alatti rész a miénk és itt már látszik, hogy a 16-os id-tól kellenének a sorok. Ebben az esetben a lekérdezésünk már így nézne ki:

select
	count(*) as cnt
from transactions
where create_date >= '2010-01-17'
    and id >= 16
group by user_id

Ezzel már sikerül kizárni a tábla 3/4-ed részét ami sok sornál jelentős adatmennyiség. Mivel a gyakorlatban az adatbázisokban a dátum nem szokott monoton nőni, ezért a dátumszűrést célszerű továbbra is meghagyni. A következő feladat megtalálni a 16-os id-t. Ezt olcsón a logaritmikus kereséssel tehetjük meg.

A terv készen is lenne, de még meg kell oldani, hogy a gyakorlatban az adatbázisban a dátumok nem monoton nőnek, hanem egyszer-egyszer nagyobb időpont kerül a kisebb elé, bár kis időkülönbség van köztük. Pl: a fenti képen a 16-os és a 18-es id dátumait megcseréljük. Ilyenkor van az, hogy kicsit alá kell lőni a dátumnak és nem azt az id-t keressük, amihez a ‘2010-01-17’ tartozik, hanem a két nappal azelőtti, ‘2010-01-15’, ami a 14-es id. Ekkor a lekérdezés már így fog kinézni:

select
	count(*) as cnt
from transactions
where create_date >= '2010-01-17'
    and id >= 14
group by user_id

Ez tök jó, de mi van, ha nincs ‘2010-01-15’-ös dátum? Erre kell megadni egy tartománykeresést. Pl: Olyan id-t keresünk, ami ‘2015-01-12’ és ‘2015-01-15’ között van. Az SQL szerver pedig meg fogja találni a 11-14 id-k valamelyikét, nekünk mindegy is, hogy melyiket, a lényeg, hogy legyen egy id-nk, amivel a tábla nagy részét ki lehet zárni a where feltételben.

 

Egy millió soron fogunk dolgozni, de ennek a sokkal többszörösén is jól használható a logika, sőt, minél több a sor, valószínűleg annál nagyobb a nyereség. És akkor a demó.

Hozzuk létre az objektumokat és adatokat:

use [tempdb]
go

if object_id('dbo.transactions', 'U') is not null
	drop table dbo.transactions
go

if object_id('dbo.usp_transactions_count_by_date_with_hop_search', 'P') is not null
	drop procedure dbo.usp_transactions_count_by_date_with_hop_search
go

if object_id('dbo.usp_transactions_count_by_date', 'P') is not null
	drop procedure dbo.usp_transactions_count_by_date
go

if object_id('dbo.v_proc_stat', 'V') is not null
	drop view dbo.v_proc_stat
go

-- Később ezt a view-t kérdezzük le, egyetlen szerepe, a blog hosszának csökkentése.
create view dbo.v_proc_stat
as
	select top 10
		p.[name],
		s.[execution_count],
		s.[total_elapsed_time],
		s.[total_worker_time],
		s.[total_logical_reads],
		s.[total_logical_writes],
		s.[plan_handle]
	from sys.[procedures] p
	join sys.[dm_exec_procedure_stats] s
		on p.[object_id] = s.[object_id]
	where p.[name] in ('usp_transactions_count_by_date', 'usp_transactions_count_by_date_with_hop_search')
	order by p.[name]
go

-- Tábla készítés
create table dbo.transactions (
	id int not null,
	user_id int not null,
	amount int not null constraint df_transactions_amount default 0,
	create_date datetime not null,
	char_field char(40) not null
	constraint pk_transactions primary key clustered (id)
)
go

-- Adatok betöltése
;with cte as (
	select top 1000000
		row_number() over(order by (select null)) as id
	from sys.[columns] c1
	cross join sys.[columns] c2
	cross join sys.[columns] c3
	cross join sys.[columns] c4
	cross join sys.[columns] c5
	cross join sys.[columns] c6
	cross join sys.[columns] c7

)


insert into [dbo].[transactions] with (tablock)
        ( [id] ,
          [user_id] ,
          [amount] ,
          [create_date],
		  [char_field]
        )
select
	id,
	[id] % 20000 + 1 as user_id,
	left(abs(checksum(newid())),4) as amount,
	dateadd(mi, id, '2010-01-01 00:00:00.000') as create_date,
	'' as charfield
from [cte]

go

-- Ellenőrzés
select top 20 * 
from [dbo].[transactions] 
where [id] >= 499995 
order by [id] 
go

-- Dátumok növekvő sorrendjének megbontása
;with cte as (
	select top 10 
		[id], 
		create_date,
		1000009 - id as upd_id
	from [dbo].[transactions] 
	where id >= 500000
	order by [id]
)
 
update c1
set c1.create_date = c2.create_date
from cte c1 
join cte c2 
	on c1.[id] = c2.[upd_id]

go
-- Ellenőrzés
select top 20 * 
from [dbo].[transactions] 
where [id] >= 499995 
order by [id] 
go

-- Tárolt eljárás logaritmikus kereséssel
create procedure dbo.usp_transactions_count_by_date_with_hop_search
	@search_date datetime  
as

	set nocount on;
	
	declare @search_date_from datetime = dateadd(mi, - 15, @search_date)
	declare @search_date_to datetime = dateadd(mi, - 8, @search_date)

	/*
	select 
		@search_date as search_date, 
		@search_date_from as search_date_from, 
		@search_date_to as search_date_to
	*/

	declare @hop int = 0
	declare @max_hop int = 30

	declare @min_id int 
	declare @max_id int
	declare @tmp_id int
	declare @result_id int

	declare @min_date datetime
	declare @max_date datetime
	declare @tmp_date datetime

	declare @end int = 0

	select top 1
			@min_id = [id], 
			@min_date = [create_date]
	from [dbo].[transactions]
	order by [id] asc


	select top 1
			@max_id = [id], 
			@max_date = [create_date]
	from [dbo].[transactions]
	order by [id] desc



	while (@end = 0)
	begin
		select top 1 
			@tmp_id = [id],
			@tmp_date = [create_date]
		from [dbo].[transactions]
		where [id] >= ((@max_id - @min_id) / 2) + @min_id
		order by [id] 

		if @search_date_from between @min_date and @tmp_date 
		begin
			set @max_date = @tmp_date
			set @max_id = @tmp_id
		end
		else if @search_date_from between @tmp_date and @max_date
		begin
			set @min_date = @tmp_date
			set @min_id = @tmp_id
		end

		if @min_date between @search_date_from and @search_date_to
		begin
			set @end = 1
			set @result_id = @min_id
		end
		else if @max_date between @search_date_from and @search_date_to
		begin
			set @end = 1
			set @result_id = @max_id
		end

		set @hop += 1;

		if @hop >= @max_hop
		begin
			set @end = 1;
			set @result_id = 0;
		end
	end

	/*
	select 
		@hop as hop, 
		@max_hop as max_hop,
		@result_id as result_id
	*/

	select 
		count(*) as cnt
	from [dbo].[transactions] 
	where [id] >= @result_id
		and [create_date] >= @search_date
go

-- Tárolt eljárás "normál" kereséssel
create procedure dbo.usp_transactions_count_by_date
	@search_date datetime
as
	set nocount on;

	select 
		count(*) as cnt
	from [dbo].[transactions] 
	where [create_date] >= @search_date
go

Futtasuk az első tesztünket és lássuk az eredményt is:

/* id = 500002: tábla közepe */
exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2010-12-14 05:27:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2010-12-14 05:27:00.000'
go 10

select * from v_proc_stat
go

03_index_search

Látszik, hogy kicsit olcsóbb volt a logaritmikus kereséses módszer.
Töröljük a planokat, aztán jöjjön a következő teszt, a tábla végéről keresünk:

-- select * from v_proc_stat plan_handle mezőjéből kell behelyettesíteni a varbinarit
DBCC FREEPROCCACHE (0x05001B00CC7C5D4380584F3A0200000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
DBCC FREEPROCCACHE (0x05001B00935869421015C7C50100000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
go

/* id = 999892: tábla vége */
exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2011-11-26 09:01:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2011-11-26 09:01:00.000'
go 10

select * from v_proc_stat
go

Nézzük az eredményt:
04_index_search
Láthatjuk, hogy sokkal, de sokkal hatékonyabb volt a logaritmikus keresés.
Nézzük tesztet a tábla eleljéről is:

DBCC FREEPROCCACHE (0x05001B00CC7C5D4380584F3A0200000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
DBCC FREEPROCCACHE (0x05001B00CC7C5D4380584F3A0200000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
go

/* id = 10: tábla eleje */
exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2010-01-01 00:10:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2010-01-01 00:10:00.000'
go 10

select * from v_proc_stat
go

Az eredmény:
05_index_search
Láthatjuk, hogy itt kicsit rosszabb volt a logaritmikus keresés.

Nézzük egy összesített tesztet is:

DBCC FREEPROCCACHE (0x05001B00CC7C5D4380584F3A0200000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
DBCC FREEPROCCACHE (0x05001B00935869421015C7C50100000001000000000000000000000000000000000000000000000000000000) WITH NO_INFOMSGS
go


exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2010-12-14 05:27:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2010-12-14 05:27:00.000'
go 10

exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2011-11-26 09:01:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2011-11-26 09:01:00.000'
go 10

exec dbo.[usp_transactions_count_by_date_with_hop_search] @search_date = '2010-01-01 00:10:00.000'
go 10
exec dbo.[usp_transactions_count_by_date] @search_date = '2010-01-01 00:10:00.000'
go 10


select * from v_proc_stat
go

Az eredmény:
06_index_search

Itt is jelentős teljesítmény növekedést értünk el.

A fenti példákból jól látszott, hogy akkor volt sokkal hatékonyabb a logaritmikus kereséses módszer a teljes táblaolvasással szemben, ha a tábla egy jelentős részét sikerült kizárni. Én pont így használom az  sok-sok millió soros táblákon és marha gyors. A scriptből többet is ki lehet hozni, aki akarja, annak hajrá! 🙂

Amikor a memóriára várunk

Talán mindenki tudja, ha adatbázis kiszolgáló a szerver, akkor legyen minél több a memória benne. Ezt én is osztom, hiszen a memória olcsó befektetés és látványos javulás érhető el vele. Mielőtt tovább mennék nézzük meg, miért is olyan fontos a sok memória.

Az adatbázis szerver mindent a memóriában csinál (elenyésző kivétellel). Tehát, ha adott művelethez nem található meg az adat a memóriában, akkor felolvassa azt a diszkről és csak ezután kezd hozzá a feladathoz. A memóriába pedig csak akkor tud adatot felolvasni, ha van szabad. Itt már lehet sejteni, hogy akármilyen gyorsak is a diszkjeink (lehet akár SSD-is), ha kevés a memória, könnyen szűk keresztmetszet lesz. Láttam már olyat, hogy többezer request/sec-et 512 MB database cache szolgált ki. Hát nem volt gyors.

Nézzük meg egy egyszerű példával is. Legyen egy SQL Server Express-ünk, ami maximum 1 GB memóriát használhat. Van egy lekérdezésünk, aminek szüksége van mind az 1 GB memóriára és 2 perc futásra a feladat elvégzéséhez. Ebben a két percben érkeznek be újabb lekérdezések, amik gyorsan lefutnának, de nem tudnak, mert nem kapnak memóriát a feladat végrehajtásához, így várakoznak. Ilyen eseteknél simán láthatjuk, hogy a CPU nincs kihajtva, a diszkolvasás elviselhető, blokk nincs és a hálózat is csendes, a lekérdezünk és persze másoké is pedig homokórázik vagy timeout-ol. Sunyi egy dög a memória probléma, az már biztos.

Erre az esetre (is) alkottak az SQL Server fejlesztői két nagyszerű nézetet, amivel ez a hiba elég jól beazonosítható:

  1. sys.dm_exec_query_resource_semaphores
  2. sys.dm_exec_query_memory_grants

 

Nézzünk a demót.

Én SQL Server Express-t használok és a max memóriát 1 GB-ra vettem vissza (ugye tud többet is használni 🙂 ), a jól demózhatóság érdekében javaslom nektek is. Jah, és teszt környeztben csináljátok.

 

use master
go

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'1024'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

 

Készítsünk teszt adatokat, most 100K sornyi adatot használunk:

use tempdb
go
if object_id('dbo.data') is not null
drop table dbo.data
go

create table dbo.data (
    id int not null identity(1,1) primary key,
    fk_1 int not null,
    fk_2 int not null,
    fk_3 int not null,
    fk_4 int not null,
    fk_5 int not null
)
go

;with cte as (
    select top (100000)
        row_number() over(order by (select null)) as rn
    from sys.[columns] c1
    cross join sys.columns c2
)

insert into dbo.data(fk_1, fk_2, fk_3, fk_4, fk_5)
select c1.rn, c1.rn, c1.rn, c1.rn, c1.rn
from cte c1
cross join cte c2
where c2.rn < 11
go

 

6 lekérdezés fog következni:
1.) 1 db query, ami alapból “gyorsan” lefut.
2.) 4 db egyforma lekérdezés, amivel leterheljük a szervert. Ha esetleg enterprise vagy developer a szerver, akkor a “merry-go-round scanning” szkennelés miatt lehet módosítani kell a lekérdezéseket. Ekkor az order by és a visszaadott oszlopok legyen eltérőek egymástól mind a 4 session-ben. Erről a szkennről az Advanced Scanning résznél olvashat az, akit érdekel.
3.) 1 db monitorozó lekérdezés, amivel beazonosítjuk, hogy memória problémával állunk szemben.

Futtassuk az 1. lekérdezést, hogy lássuk a normál futásidőt (nálam 5 mp):

use tempdb
go

;with cte as (
    select col1 from (values (1), (2), (3), (4), (5)) tbl1 (col1)
) ,

cte2 as (
    select
        c1.col1 as c1,
        c2.col1 as c2,
        c3.col1 as c3
    from cte c1
    cross join [cte] c2
    cross join [cte] c3
)

select top 100000 *
from [cte2] c1
cross join [cte2] c2
cross join [cte2] c3
cross join [cte2] c4
cross join [cte2] c5
cross join [cte2] c6
cross join [cte2] c7
order by 1,2,3,4
option (maxdop 1)

A 2. query-t indítsuk el 4 db session-ben, garantáltan sokáig fognak futni.

 
use tempdb
go

select 
	d1.[id],
	d2.[fk_2],
	d2.[id]
from [dbo].[data] d1
cross join [dbo].[data] d2
order by 
	d1.[fk_1], 
	d1.[fk_2], 
	d1.[fk_3], 
	d1.[fk_4], 
	d1.[fk_5], 
	d2.[fk_1],
	d2.[fk_2],
	d2.[fk_3],
	d2.[fk_4]
option (maxdop 1)

 

A 3. (monitorozó) lekérdezést futtassuk a 6. session-ben.

select 
    case when [granted_memory_kb] = [used_memory_kb] then 1 else 0 end as flag, 
    * 
FROM sys.dm_exec_query_resource_semaphores

SELECT * FROM sys.dm_exec_query_memory_grants

 

Ha nálatok is úgy alakul a memória használat, mint nálam, akkor a következőt láthatjátok a monitorozó session-ben:

052_wait_for_memory_1

Az összetartozó adatokat közös színnel jelöltem. A zöldeknél látjuk, hogy a felső táblázatban hárman megkapták a szükséges memórát, míg az alsó táblázatban azt is, hogy mikor és mennyit, illetve milyen session_id-kal. A pirosnál láthatjuk a felső táblázatban, hogy egy valaki várakozik memóriára, az alsóban pedig, hogy ki az (session_id). Itt grant_time mező értéke NULL. Ha esetleg nálatok nem lenne olyan, aki memóriára vár, akkor indítsatok még néhány session-ben a 2-es query-ből.

 

Most ismét indítsuk el az 1. lekérdezést, majd futtassuk a monitorozó (3.) lekérdezésünket. Ezt az eredményt láthatjuk:

052_wait_for_memory_2

Várakozik az a lekérdezésünk is, ami eddig pár másodperc alatt lefutott. Sőt, még sokáig fog várakozni. Ha kilőnénk a 2. query-ket, akkor ismét gyorsan lefutna az 1-es lekérdezésünk, de nem tesszük. Helyette inkább vizsgálódunk még.

 

Kérdezzük le, hogy milyen blocking_session-jeink vannak:

select * from sys.dm_os_waiting_tasks 
where [blocking_session_id] > 0 

Az eredmény:

052_wait_for_memory_3

Nézzük meg a counterek sql-ből lekérdezve:

SELECT [counter_name], cntr_value as value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME like '%Memory Manager%'
    AND counter_name like 'Memory Grants%'

 

Végül, ha monitorozni akarjuk ezt típusú hibát, akkor legjobban a perfmon.exe-n keresztül tehetjük meg, szerintem. Aki nem tudja hogyan kell, annak itt egy step-by-step útmutató:

1.) Írjuk a Start menü keresőjébe, hogy perfmon.exe, majd nyomjunk enter.

2.) Válasszuk a bal oldali fában a “Teljesítményfigyelő” lehetőséget, majd a jobb oldali részen a lenyíló ikonnál válasszuk a “Jelentés” menüpontot.

052_wait_for_memory_4

3.) Jobb oldalon alapból a processzor információ már látszik. Ezen a részen, a fehér területen jobb gomb, majd “Számláló hozzáadása…” lehetőség.

052_wait_for_memory_5

4.) Itt keressük ki, majd nyissuk le az “MSSQL$<instance név>:Memory Manager” lehetőséget, majd jelöljük ki az egér bal gombjával rákattintva a “Memory Grants Outstanding” és “Memory Grants Pending” számlálókat, alul kattintsunk a “Hozzáadás” gombra, majd “OK” gomb.

052_wait_for_memory_6

Ha mindent jól csináltunk, akkor ezt láthatjuk:

052_wait_for_memory_7

 

Arra figyeljetek, hogy tempdb nagyon hízni fog.

 

Random join

Úgy gondolom, hogy jó, ha van talonban egy olyan példa script, amivel random lehet sorokat join-olni, akár teszt adatok létrehozásához, akár más célból. Az alapfelállás az, hogy van 2 táblánk, legyen ez első mondjuk user, ahol a felhasználóinkat tároljuk, a második meg a country, amelyekkel a felhasználók valamilyen kapcsolatba kerülhetnek. Itt három féle random join-t mutatnék:

  1. 1-1 kapcsolat van a felhasználók és counrty között és minden felhasználóhoz kötelezően tartozik 1 country.
  2. 1-1 kapcsolat van a felhasználók és country között, de a felhasználókhoz nem feltétlen tartozik country.
  3. 1-több kapcsolat van a felhasználók és country között, ahol 1 felhasználóhoz 1 vagy több country tartozik.

 

Mindhárom esetben a checksum() függvény lesz a segítségünkre, amit a join-ba fogunk tenni, a moduló osztással pedig garantáljuk, hogy olyan számokat kapjunk, ami a country tábla id-jaival egyezik vagy 0 értéket ad, feladattól függően. A feladat kitétele, hogy a country táblában a számok folytonosak legyenek, tehát ne legyenek lukak a sorszámok között.

Lássuk a demót:

IF object_id('tempdb..#user') IS NOT NULL
	DROP TABLE #user
GO

IF object_id('tempdb..#county') IS NOT NULL
	DROP TABLE #county
GO

CREATE TABLE #user (
	id INT NOT NULL PRIMARY KEY
	,NAME VARCHAR(255) NOT NULL
	)

CREATE TABLE #county (
	id INT NOT NULL PRIMARY KEY
	,country VARCHAR(255) NOT NULL
	)
GO
INSERT INTO #county ([id],[country])
VALUES
	(1,'HU'),
	(2,'RO'),
	(3,'US'),
	(4,'RU');

;WITH cte AS (
	SELECT TOP 1000 row_number() OVER (ORDER BY (SELECT NULL)) AS id
	FROM sys.[columns] c
	CROSS JOIN sys.[columns] c2
	)
INSERT INTO #user ([id],[name])
SELECT TOP 1000 id
	,CONCAT ('User ',[id]) AS NAME
FROM [cte] c

--select * from #county
--select * from #user
-- Első eset: mindig legyen 1 sor az userhez
SELECT u.*,c.*
FROM #user u
JOIN #county c ON ABS(CHECKSUM(NEWID(), u.id)) % 4 + 1 = c.[id]

-- Második eset, amikor 1 userhez 1 vagy 0 country tartozik
SELECT u.*,c.*
FROM #user u
JOIN #county c ON CHECKSUM(NEWID(), u.id) % 4 = c.[id]

-- Harmadik eset, a kapcsoló táblás, amikor 1 userhez több country tartozhat
SELECT u.*,c.*
FROM #user u
JOIN #county c ON ABS(CHECKSUM(NEWID(), u.id)) % 4 + 1 >= c.[id]
ORDER BY 1,2,3

Ha esetleg nem folytonos sorszámozású a country táblánk, arra is van megoldás, amit a CTE és a row_number páros nyújt:

;with cte as (
    select *, row_number() over(order by (select null)) as rn
    from #county
)

 

Majd a country tábla helyett, ezt a CTE-t használjuk a join-ban, illetve a rn oszloppal végezzük a  mezőkapcsolást.

 

Végezetül, akit érdekel eltöprenghet, hogy fenti példákban, a táblák nagyságától függően az SQL Server melyik fizikai join-t (LOOP, HASH, MERGE) fogja választani a végrehajtási tervben, illetve melyiket biztosan nem. Aztán tesztelje le, tanuljon és profitáljon belőle! 🙂