Podešavanje MS SQL servera za veća Pantheon™ okruženja

  Ukoliko se odlučite za veća okruženja, isporuka besplatne verzije MS SQL Express nije dovoljna, zato se držimo za Standard ili Enterprise verzije. U nastavku sledi nekoliko osnovnih koraka, pri planiranju i pripremi sistema, gde će biti pokrenut Pantheon™ server.  

Diskov polja

Potrebna su sva tri odvojena raid1 polja, sastavljena iz svih šest diskova:

• prvo polje koristimo za sistem, pagefile, podešavanje SQL

• drugo polje koristimo za sistemske baze, uključujući i TEMPDB

• treće polje koristimo za produkcijske baze

Ukoliko imamo na raspolaganju 8 diskova, koristimo četvrto polje za loge.

Poželjno je, da sistemski raid polje bude poravnat. Za disk polja, gde se nalaze SQL baze su neophodne. U slučaju upotrebe operacionog sistema Windows Server 2008 je to napravljeno automatski, u slučaju Windows Server 2003 , napravimo ručno sa komandom diskpart. Donji primer pokazuje postupk pripreme aligned particije na jednom od raid polja:

• diskpart

• list disk

• select disk <x>

• create partition primary align=1024

• assign

• exit

Particije, gde se nalaze SQL baze je potrebno formatirati sa 64KB cluster size.

Ukoliko je SQL sistem već uspostavljen, alignment particije možemo proveriti sa komandom:

    wmic partition get BlockSize, StartingOffset, Name, Index

  Ispisan starting offset delimo sa blocksize. Ukoliko dobijemo neparnu vrednost (npr. 63), particije nisu pravilno pripremljene, što znači da 20% - 40% sporije radi SQL na različitim IO opterećenjima.

Možemo proveriti i prikladnost cluster veličine, sa komandom:

    fsutil fsinfo ntfsinfo c:

    Ukoliko je medju ispisanim vrednostima »Bytes Per Cluster« drugačije od 65536, particija nije pravilno formatirana, jer negativno utiče na IO mogućnosti SQL servera.   Procesorske mogućnosti i memorije Zahteva mogu biti veoma različite, te se preporučuje minimum dual core Xeon sistemi i 8GB memorije kao i 64 bitni sistem.

TempDB

• Preporučujemo da se ima jedan data file za svaki fizički CPU core u sistemu. Kao primer možemo koristiti produžen procesor Intel Xeon E5620, koji ima 4 fizičke core odnosno 8 virtuelnih (hyperthreading). U tom slučaju je optimalno imati 4 data file za tempdb. Svi data file-ovi moraju biti jednako veliki.

• data filu za tempdb se preporučuje unapred dodeljen disk prostor, da se izbegne fragmentacija.

• Primer pripreme dodatnog datafila sa 10GB prealocated prostora:

USE [master]

GO

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdata2', FILENAME = N'e:\sql\templog2.mdf' , SIZE = 10240000KB , FILEGROWTH = 10%)

GO  

Analiza brzine rada Pantheon™ baze podataka - sistem

Ob opažanju upočasnitev delovanja, je za prvi korak priporočena izvedba procesa nadgradnje Pantheon™ baze, saj se tako vzpostavi optimalno stanje podatkovne baze:

• popravijo se indexi in ključi na tabelah

• popravi se referenčna integriteta med tabelami

• dodajo se nova polja, uredijo obstoječa ter odstranijo odvečna

Pri nadgradnji moramo biti pozorni, da vklopimo možnost »Nadgradnja vseh objektov«. S tem se preveri celotna Pantheon™ struktura ter odpravi napake, katere je morebiti povzročil zunanji dejavnik (dodatne implementacije).

Priporočeno je spremljanje performance counterjev, na nivoju Microsoftovega operacijskega sistema:

• Memory Commited Bytes – v kolikor je alociranega pomnilnika več, kot ga ima sistem je potrebno razširiti pomnilnik, ker gre za t.i. swapping.

• Memory Available Mbytes – v kolikor le ta pade pod mejo 5%, je potrebno razširiti pomnilnik

• PhysicalDisk Idle Time – v kolikor le ta večkrat pade pod 15%, pomeni da so diskovna polja preobremenjena

• PhysicalDisk Read Latency & Write Latency – v kolikor večkrat presegata 30ms, je potrebno pregledati strukturo diskovnih polj, sql zahtevke, skladnost particij z best practice ipd.  

Analize hitrosti delovanja Pantheon™ podatkovne baze – SQL

Preverimo, koliko časa podatki ostanejo v cachu. Vrednosti »page life expectancy« so kritične, priporočene pa so v rangu okoli 1000. Za pravilno sliko, se naj spodnji zahtevek izvaja vsakih 5 minut skozi dovolj dolgo obdobje, da bo vzorec reprezentativen. Ob vklopu več strežniških nadzornih sistemov (predvsem vgrajen lokalni MS data collection), lahko to negativno vpliva na celostno zmogljivost strežnika, saj analiza podatkov poteka lokalno.

select b.total - a.db as 'Plan Cache Size', c.ple as 'Page Life Expectancy'

from

( select cntr_value as db

from sys.dm_os_performance_counters

where object_name like '%Buffer Manager%' and counter_name like 'Database pages%' ) a, 

( select distinct cntr_value as total

from sys.dm_os_performance_counters

where object_name like '%Buffer Manager%' and counter_name like 'Total pages%' ) b,

( SELECT cntr_value AS ple

FROM sys.dm_os_performance_counters

WHERE object_name like '%Buffer Manager%'

AND counter_name = 'Page life expectancy' ) c

Preverimo najbolj zahtevne operacije p oCPU ter s spodnjima querijema. Večinoma težave nastopajo po uporabniških dodelavah postopkov (sloppy programming, indexi, full table scani,...). Na podlagi rezultatov, se najvolj zahtevne operacije analizira ter po potrebi optimizira.

-- top 10 CPU

SELECT TOP 10

  qs.execution_count,

  total_CPU_secs = qs.total_worker_time/1000000,

  average_CPU_secs = (qs.total_worker_time/1000000) / qs.execution_count,

  total_elapsed_time_secs = qs.total_elapsed_time/1000000,

  query = st.text,

  qp.query_plan

FROM

  sys.dm_exec_query_stats AS qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

  CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp

  ORDER BY qs.total_worker_time DESC

-- top 10 I/O

SELECT TOP 10

  qs.execution_count, qs.total_logical_reads, 

  qs.total_elapsed_time, qs.max_elapsed_time,

  qs.last_elapsed_time, qs.last_execution_time,

    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,

    ((CASE qs.statement_end_offset

    WHEN -1 THEN DATALENGTH(qt.text)

    ELSE qs.statement_end_offset

    END - qs.statement_start_offset)/2)+1) as query,

  qp.query_plan

FROM sys.dm_exec_query_stats qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

  CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

  WHERE qt.encrypted=0

  ORDER BY qs.total_logical_reads DESC

Priporočeno je preveriti integriteto podatkovne baze z:

USE Pantheon

GO

DBCC CHECKDB WITH NO_INFOMSGS

Napačna postavitev ali mankajoči indexi so velikokrat vzrok performančnih težav. Velikokrat pa težave povzroči tudi fragmentacija indexov ali update statistik tabel. Fragmentacijo indexov preverimo z:

USE Pantheon;

SELECT a.index_id, name, index_type_desc, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),

     NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE name not like 'NULL' AND avg_fragmentation_in_percent > 10

ORDER by avg_fragmentation_in_percent DESC

GO

Priporočeno je tedensko izvajanje rebuilda indexov ter update statistik z:

USE Pantheon;

GO

DECLARE @TableName VARCHAR(255)

DECLARE @sql NVARCHAR(500)

DECLARE @fillfactor INT

SET @fillfactor = 80

DECLARE TableCursor CURSOR FOR

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

EXEC (@sql)

SET @sql = 'UPDATE STATISTICS ' + @TableName + ' WITH FULLSCAN'

EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

GO