Elmélet kategória bejegyzései

LIKE és az ESCAPE

A LIKE-ot szerintem mindenki ismeri, de az ESCAPE záradékát már kevesebben, pedig nagyon hasznos tud lenni. Erről írok most egy rövid postot.

Az ESCAPE kiegészítés akkor hasznos, amikor egy foglalt karakter is szerepelne a keresésben. Ilyen karakterek jelenleg: [,],%,_,^

Tehát, ha van egy ilyen változónk: @a = ‘ertek%ertek’ és azt akarjuk tudni, hogy @a változóban található e % jel, akkor escape-elni kell. Az escape karakter bármi lehet, a lényeg, hogy az escape záradékban adjuk meg, hogy mi volt az. A továbbiakban minden szónál többet fognak érni a példák, úgyhogy jöjjön a demó.

Készítünk egy táblát, teszünk bele % jeles értékeket és szűrünk többféle variációban (Százalékjel helyett lehetne más foglalt karakter is, én most ezzel demózok).

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO

CREATE TABLE #tmp (name VARCHAR(16) NULL)

INSERT INTO #tmp (name)
VALUES
(NULL),
('ertek%ertek'),
('ertekertek'),
('%'),
('%ertek'),
('ertek%'),
('e%rtek'),
('erte%k')

 

Próbáljuk keresni a hagyományos módszerrel a % jelet:

-- 1.) Elso teszt
SELECT [name]
FROM #tmp
WHERE name LIKE '%%%'

01_elso_teszt

Azt látjuk, hogy visszakaptuk a teljes tábla tartalmát a NULL kivételével. De nem ezt akartuk.

Használjuk ez escape-elést.

-- 3.) Szazalekjel van valahol az ertekben
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%%' ESCAPE '!'

03_szalaekjel_valahol
Egész jó, itt már azt kaptuk, amit akartunk. Bonyolítsuk néhány dologgal, a további példák már csak ráadások.

-- 4.) Szazalekjel van valahol az ertekben, de a szazalekjel elott van legalabb egy karakter
SELECT [name]
FROM #tmp
WHERE name LIKE '%_!%%' ESCAPE '!'

04_szazalekjel_valahol_plusz_elotte_1_karakter

 

-- 5.) Szazalekjel van valahol az ertekben, de a szazalekjel utan van legalabb egy karakter
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%_%' ESCAPE '!'

05_szazalekjel_valahol_plusz_utana_1_karakter

 

-- 6.) Szazalekjellel kezdodik az ertek
SELECT [name]
FROM #tmp
WHERE name LIKE '!%%' ESCAPE '!'

06_szazalekjellel_kezdodik

 

-- 7.) Szazalekjelre vegzodik az ertek
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%' ESCAPE '!'

07_szazalekjelre_vegzodik

 

-- 8.) Szazalekjel elott csak 1 db karakter all
SELECT [name]
FROM #tmp
WHERE name LIKE '_!%%' ESCAPE '!'

08_szazalekjel_elott_1_karakter_utana_barmennyi

 

-- 9.) Szazalekjel utan csak 1 db karakter all
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%_' ESCAPE '!'

09_szazalekjel_utan_1_karakter_elotte_barmennyi

 

-- 10.) Szazalekjel utan 'e' vagy 'r' vagy 'k' aztan barmi
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%[erk]%' ESCAPE '!'

10_szazalekjel_utan_erk_aztan_barmi

-- 11.) Szazalekjel utan 'e' vagy 'r' vagy 'k' aztan vege
SELECT [name]
FROM #tmp
WHERE name LIKE '%!%[erk]' ESCAPE '!'

11_szazalekjel_utan_erk_aztan_vege

 

Van még a LIKE-nak jó tulajdonsága, bővebben BOL-ban lehet utána olvasni.

Ha valakinek van kedve még ezzel játszani, akkor itt egy feladat: A meglévő táblába szúrjunk be további 2 értéket:

INSERT INTO [#tmp] ( [name] ) VALUES  ( 'ertek%[[ertek]]')
INSERT INTO [#tmp] ( [name] ) VALUES  ( 'ertek%[ertek]')

És most keressük ki azokat az értékeket, ahol:

  1. A ‘%’ jel után ‘[‘ jel áll. (Tehát ‘%[‘)
  2. A ‘%’ jel után ‘[‘ jel áll, utána pedig NEM ‘[‘ jel. (Tehát ‘%[‘ majd nem ‘[‘). Ez utóbbit a cikk alapján nem tudod megoldani, el kell olvasni a BOL-t, ha nem tudod a megoldást.

Végezetül pedig, ha nagyon bonyolult regex-es kifejezésekre van szükségünk, akkor használjunk hozzá CLR-t.

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.

 

A lapozás – tegyük helyre

Triviális dologról lesz szó, ám mégis azt látom, hogy sok ember fejében nincs rendben, hogy mi is a lapozás.

Alább szemléltetem, hogy mi él a fejekben a lapozásról, és itt most vegyük a hagyományos offset-es lapozást, ami az order by része (a felhasználókat listázzuk utolsó belépés alapján, a legfrissebb legelöl):

first

A fejlesztő azt gondolja, hogy a fenti result set-et 5-ös limitekben úgy fogja visszakapni, ahogy a piros vonallal jelöltem. Ez igaz, ha egy adott időpillanatban kérdeznénk le az adatokat.

A valóságban viszont nem feltétlen lesz ez így, hiszen az adatbázisainkban az adatok “mozgásban vannak”. Lehet, hogy a második szeletet az első után x órával kérdezem csak le és addigra egy utolsó belépés sorrend rendesen átrendeződhet. Nézzünk erről is egy képet:

second

Zölddel jelöltem a módosult sort. Ha az első szeletnél az első kép szerint néz ki a táblánk, a második szeletnél a második kép szerint, akkor láthatjuk, hogy a “User 15” egyszer sem fog megjelenni a találati listában, mert a lapozással átugorjuk, míg a “User 16” kétszer is (első és második szelet). Nincs mit tenni, a lapozás ilyen, ezzel együtt kell élni.

Azért választottam a last logint-t példának, mert ez egész valósághű lehet, viszont ugyanez igaz lehet minden olyan sorra, ami változik, tehát nem read only a táblánk, amiből lekérdezünk.

Végezetül még felhívnám a figyelmet, hogy a hagyományos lapozás és az, amit helyette ajánlottam (lapozzunk okosan), egymástól különböző logika alapján működnek, ezért nem feltétlen ugyanazt a találati listát fogják visszaadni.

Az általam javasolt lapozás így adna vissza az eredményeket:

third

Ahol a “User 15” szintén nem lenne listázva, viszont duplán senki. A probléma ott jelentkezne, amikor azt írjuk, hogy 20 találatunk van, közben meg csak 19-et jelenítünk meg, mert a “User 15” kimaradt. A hagyományos lapozásnál pedig azt mutatjuk a felhasználóknak, hogy 20 találatunk van, 20-at jelenítünk meg, de a “User 16”-os kétszer, a “User 15”-öt meg egyszer sem. Nem mondanám egyik logikát sem jónak vagy rossznak, a lényeg, hogy fedje le az üzleti igényeket. A lapozásnál pedig emlékezzünk erre, hiszen nap mint nap használjuk.