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

Konstatujući usporavanje rada, za prvi korak preporučujemo izvodjenje procesa nadogradnje Pantheon™ baze, jer se tako uspostavi optimalno stanje baze podataka:

• poprave se indexi i tasteri /ključevi/ na tabelama

• popravi se referentni integritet medju tabelama

• dodaju se nova polja, urede postojeća i uklone suvišne

Pri nadogradnji moramo biti pažljivi, da uklopimo mogućnost »Nadogradnja svih objekata«. S ovim proveravamo celokupnu Pantheon™ strukturu i popravljamo greške, koje je moguće izazvati spoljnim faktorom (dodatne implementacije).

Prepuročeno je menjanje performansi counterjev, na nivou Microsoft-ovog operacionog sistema:

• Memory Commited Bytes – ukoliko je izdvojene memorije više, nego što je  sistemu potrebno proširiti memoriju, jer je to swapping /zamenjivanje/.

• Memory Available Mbytes – ukoliko padne ispod 5%, potrebno je proširit memoriju

• PhysicalDisk Idle Time – ukoliko često padne ispod 15%, znači da su disk polja preoptereećena

• PhysicalDisk Read Latency & Write Latency – ukoliko često prelazi 30ms, potrebno je pregledati strukturu disk polja, sql zahteve, u skladu sa particijom za best practice ipd.  

Analiza brzine rada Pantheon™ baze podataka – SQL

Proverimo, koliko se vremena podaci čuvaju u cachu. Vrednosti »page life expectancy« su kritične, preporučene su u rangu oko 1000. Za pravilnu sliku, donji panel se izvodi svakih 5 minuta tokom dužeg perioda, da bi uzorak bio reprezentativan. Nakon startovanja više servera kontrolnih sistema (pre svega ugradjen lokalni MS data collection), mogu imati negativan uticaj na ukupne performanse servera, jer analiza podataka se uradi 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

Proverimo najzahtevnije operacije p oCPU i sa donjim querijema. Problemi uglavnom nastupaju posle korisnikovih završenih procesa (sloppy programming, indexi, full table scani,...). Na osnovu rezultata,  zahtevne operacije se  analiziraju i po potrebi optimizuju.

-- 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

Preporučujemo da proverite integritet baze podataka sa:

USE Pantheon

GO

DBCC CHECKDB WITH NO_INFOMSGS

Pogrešna instalacija ili nedostajeći indexi su čest uzrok problema performansi. Česti problemi prouzrokuju i fragmentaciju indexa ili update statistiku tabela. Fragmentaciju indexa proverimo sa:

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

Preporučujemo i nedeljno izvodjenje rebuilda indexa kao i update statistika sa:

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