(ARHIV)
Nastavitve MS SQL strežnika za večja Pantheon™ oko

Nastavitve MS SQL strežnika za večja Pantheon™ okolja

  V kolikor se odločamo o večjih okoljih, priložena prosta različica MS SQL Express ne zadostuje, zato posežemo po Standard ali Enterprise različicah. V nadaljevanju sledi nekaj osnovnih napotkov, pri načrtovanju in pripravi sistema, kjer bo potekal Pantheon™ strežnik.  

Diskovna polja

Potrebujemo vsaj tri ločena raid1 polja, sestavljena iz vsaj šestih diskov:

• prvo polje uporabimo za sistem, pagefile, namestitev SQL

• drugo polje uporabimo za sistemske baze, med drugim tudi TEMPDB

• tretje polje uporabimo za produkcijske baze

V kolikor imamo na voljo 8 diskov, uporabimo četrto polje za loge.

Zaželjeno je, da je sistemsko raid polje poravnano. Za diskovna polja, kjer se nahajajo SQL baze pa je to nujno. V primeru uporabe operacijskega sistema Windows Server 2008 je to narejeno samodejno, v primeru Windows Server 2003 pa to naredimo ročno z ukazom diskpart. Spodnji primer prikazuje postopek priprave aligned particije na enem izmed raid polj:

• diskpart

• list disk

• select disk <x>

• create partition primary align=1024

• assign

• exit

Particije, kjer se nahajajo SQL baze je potrebno formatirati s 64KB cluster size.

V kolikor SQL sistem že imamo vzpostavljen, lahko alignment particij preverimo z ukazom:

    wmic partition get BlockSize, StartingOffset, Name, Index

    Izpisan starting offset delimo z blocksize. V kolikor dobimo liho vrednost (npr. 63) particije niso pravilno pripravljene, kar pomeni 20% - 40% počasnejše delovanje SQL ob različnih IO obremenitvah.

Preverimo lahko tudi ustreznost cluster velikosti, in sicer z ukazom:

    fsutil fsinfo ntfsinfo c:

    V kolikor je med izposanimi vrednostmi »Bytes Per Cluster« različen od 65536, je particija ni pravilno formatirana, kar negativno vpliva na IO zmogljivosti SQL strežnika.   Procesorske zmogljivosti in pomnilnik Zahteve so lahko zelo različne, priporočen minimum pa so vsaj dual core Xeon sistemi ter 8GB pomnilnika ter 64 bitni sistem.

TempDB

• Priporočeno je imeti en data file za vsak fizičen CPU core v sistemu. Kot primer lahko uporabimo zelo razširjen procesor Intel Xeon E5620, ki ima 4 fizične core oziroma 8 virtualnih (hyperthreading). V tem primeru je optimalno imeti 4 data file za tempdb. Vsi data filei morajo biti enako veliki

• data filom za tempdb je pripročeno vnaprej alocirati diskovni prostor, da se izognemo fragmentaciji.

• Primer priprave dodatnega datafila z 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  

Analize hitrosti delovanja Pantheon™ podatkovne baze - 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