augusztus 2015 havi bejegyzések

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.

 

Reklámok