április 2014 havi bejegyzések

Page life expectancy rekord

A felügyelet product szervereim közül az egyiken a page life expectancy 4,2 millió másodperc felett van, kicsit több, mint 49 nap (most elárultam egy minimum server uptime-ot is 🙂 ), és a görbe továbbra is felfele ível. Abszolút rekordom. Kíváncsi leszek meddig bírja.

A page life expectancy az egyik legjobb memória mutatója az SQL szervernek. Ha memória problémával küzd a szerver, szinte biztos lehetsz benne, hogy ez jelezni fogja. Régebben 300 (second) volt a minimum normál érték, most már ez több ezer. Kell még róla tudni, hogy lekérdezhető DMV-ből is.

SELECT
    [counter_name],
    [cntr_value] in_second,
    convert(decimal(5,2), [cntr_value] / (60*60*24.0)) in_day
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
    AND [counter_name] = 'Page life expectancy'
    AND [instance_name] = ''

A query-ben szűrtem az instance_name = ”-re, ami azt jelenti, hogy egy “összesített” értéket szeretnék látni. Ha kivesszük ezt a szűrést és több NUMA NODE van a szerverben, akkor NUMA NODE-onként is láthatjuk az eredményt.

 

Aki jobban el akar merülni az SQL Server countereiben, szeretne leírást kapni róluk és esetleg előben is látni monitorozást, annak ajánlom a Red Gate “online” monitorját.

Végezetül a kedvenc SQL lekérdezésemet is megosztanám: SQL_query_to_find_an_ideal_girl

 

Reklámok

A Server Manager exe visszatér

Nem gondoltam volna, hogy a ServerManger.exe ennyire sokoldalú kis program. A múltkori CPU-s eset után, ma a jó kis SSD diszket hajtotta ki. Disk active time = 100%.

Ketten ültünk a szerver felett, hogy ugyan mi baja lehet, mígnem a disk response time lebuktatta a kis mocskot. 3 példányban futott (3 felhasználónál), ebből 2 példány egyenként 2500 ms válaszidőt produkált, aktív pagefile használattal. Task manager => kill process és helyre állt a béke.

Azt hiszem, hogy nem szeretem ezt az eszközt.

Teljesítményigényes tárolt eljárások – egyszerűen

Ebben a postban egy egyszerű query-t mutatnék, amivel ki tudjuk listázni a szerveren futó tárolt eljárásokat, és hogy azok eddig mennyi erőforrást használtak.

Lássuk is ezt a query-t:

 

use [master]
go

select
    db_name(database_id) as dbname,
    OBJECT_NAME(object_id, database_id) as sp_name,
    count(*) as stat_count,
    sum([total_worker_time]) sum_total_worker_time,
    sum([total_elapsed_time]) sum_total_elipsed_time,
    min([cached_time]) min_cached_time,
    max([cached_time]) max_cached_time,
    min([last_execution_time]) min_last_execution_time,
    max([last_execution_time]) max_last_execution_time,
    sum([execution_count]) sum_execution_count,
    sum(total_worker_time) / sum(execution_count) as avg_worker_time_per_count_in_microsecond,
    sum(total_logical_reads) as sum_total_logical_reads,
    sum(total_logical_writes) as sum_total_logical_writes,
    min(min_worker_time) as min_worker_time,
    max([max_worker_time]) as max_worker_time,
    max([last_worker_time]) as max_last_worker_time,
    sum(total_physical_reads) as sum_total_physical_reads
from sys.[dm_exec_procedure_stats] ps
--where database_id = 2
group by database_id, object_id
order by sum_total_worker_time desc
--order by avg_worker_time_per_count_in_microsecond desc
--order by sum_execution_count desc

Összesen 1 db nézet, elég egyszerű.

Megvan a resultset, de mit keressünk benne?
Én általában 3 dolog szerint keresek, ezeket pedig jól mutatják az order by záradékok. Akkor mindenképpen gyanút kell fogni, ha az első találatok egy vagy több helyiértékkel több költségbe kerülnek, mint az többi eljárás.

1.) sum_total_worker_time: Ez megmutatja, hogy eddig összesen mennyi CPU erőforrásba került a tárolt eljárás. A számítás kezdete a cache-be kerülés időpontja.

2.) avg_worker_time_per_count_in_microsecond: Ez megmutatja, átlagosan mennyi CPU erőforrásba került a tárol eljárás. Ez azért hasznos mutató, mert a sum_total_worker_time torz képet mutat, ha 1 tárolt eljárás újra fordul és a cache-be kerülés időpontja jóval későbbi, mint a többi eljárásé. Pl: “A” tárol eljárás 10 napja van a cache-ben, futott 1.000-szer és elhasznált 10.000 CPU-t, míg “B” tárolt eljárás pedig csak 1 napja van a cache-ben futott 2-szer és elhasznált 500 CPU-t. Az átlagból látszik, hogy “B” tárolt eljárás sokkal költségesebb.

3.) sum_execution_count: Ez pedig azért, hogy lássuk mit hívunk legtöbbször, ugyanis előfordulhat, hogy az alkalmazás cache-be tárolható az adat. Tipikusan ilyen adat egy felhasználó numerikus és karakteres azonosítója, mert azokra nem jellemző, hogy változnak. Ha nem minden alkalommal a db-ből kérjük le, hanem az alkalmazás cache-ből kiolvassa, máris sokat könnyítettünk az adatbázison.

Nézzünk egy kis demót. Csinálunk 1 táblát, készítünk 3 db tárolt eljárást (+ 1 függvényt) és megnézzük, hogy melyik mennyi erőforrásba került.

 

use tempdb
go

if object_id('users', 'U') is not null
	drop table users
go

if object_id('usp_get_one_user_by_id', 'P') is not null
	drop procedure usp_get_one_user_by_id
go

if object_id('usp_get_one_user_by_name', 'P') is not null
	drop procedure usp_get_one_user_by_name
go

if object_id('usp_get_one_user_by_id_bad', 'P') is not null
	drop procedure usp_get_one_user_by_id_bad
go

if object_id('udf_get_user_amount_by_name', 'FN') is not null
	drop function udf_get_user_amount_by_name
go

-- Create table
create table users (
	id int identity primary key,
	name nvarchar(255) not null,
	amount int not null,
	country_code varchar(2) not null,
	created_at datetime not null
)

go

-- Create data
;with cte as (
	select top (10000)
		row_number() over(order by (select null)) as rownumber,
		abs(checksum(newid())) as random_number
	from sys.columns c1
	join sys.columns c2
		on 1 = 1
)

insert into users
	(name, amount, country_code, created_at)
select
	concat('Name', rownumber) as name,
	left(random_number,4) as amount,
	case left(random_number,1)
		when 1 then 'HU'
		when 2 then 'US'
		when 3 then 'GB'
		when 4 then 'CH'
		when 5 then 'RU'
		when 6 then 'LU'
		when 7 then 'RO'
		when 8 then 'CZ'
		when 9 then 'SW'
		when 0 then 'DE'
		else null
	end as country_code,
	dateadd(day, -cast(left(random_number,3) as int), getdate()) as created_at
from cte
go

create index IX_name on users (name)
go

-- select * from users
-- go

-- Create stored procedures
-- SP1
create procedure usp_get_one_user_by_id
	@user_id int
as
	set nocount on;

	select
		id,
		name,
		amount,
		country_code,
		created_at
	from users
	where id = @user_id
go

-- SP2
create procedure usp_get_one_user_by_name
	@name nvarchar(255)
as
	set nocount on;

	select
		id,
		name,
		amount,
		country_code,
		created_at
	from users
	where name = @name
go

-- SP3  with function
create function udf_get_user_amount_by_name (
	@name nvarchar(255)
)
returns int
as
begin
	declare @amount int
	select @amount = amount
	from users
	where name > ''

	select @amount = amount
	from users
	where name = @name

	return @amount
end
go

create procedure usp_get_one_user_by_id_bad
	@user_id int
as
	set nocount on;

	select
		id,
		dbo.udf_get_user_amount_by_name(name) as name,
		amount,
		country_code,
		created_at
	from users
	where id = @user_id
go

Futtatjuk a fenti query-t, a where záradékot visszakommentezve, hogy csak a tempdb-ben futó sp-ket lássuk.
Nálam ez az eredmény jött ki (csak a postban tárgyalt fontosabb oszlopokat emeltem ki):

048_sp_cost_result

Tisztán látszik, hogy melyik eljárásokon kell optimalizálni. Aki akar kicsit tanulni, az a “usp_get_one_user_by_id_bad” eljárást elmezve bukkanthat új dolgokra 🙂