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

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

  Ukoliko se odlučujemo o većim okruženjima, priložena besplatna verzija MS SQL Express ne zadovoljava, zato posežemo po Standard ili Enterprise verzijama. U nastavku sledi nekoliko osnovnih napomena, pri planiranju i pripremi sistema, gde će se odvijati Pantheon™ server.  

Diskovna polja

Potrebujemo najmanje tri odvojena raid1 polja, sastavljena od najmanje šest diskova:

• prvo polje koristimo za sistem, pagefile, instalaciju SQL

• drugo polje koristimo za sistemske baze, između ostalog i TEMPDB

• treće polje koristimo za produkcijske baze

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

Poželjno je da je sistemsko raid polje poravnato. Za diskovna polja, gde se nalaze SQL baze, to je obavezno. U slučaju korišćenja operativnog sistema Windows Server 2008, to se radi automatski, dok u slučaju Windows Server 2003 to radimo ručno sa komandom diskpart. Donji primer prikazuje postupak 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, potrebno je formatirati sa 64KB cluster size.

Ukoliko SQL sistem već imamo uspostavljen, možemo proveriti alignment particija sa komandom:

    wmic partition get BlockSize, StartingOffset, Name, Index

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

Možemo proveriti i odgovarajuću veličinu klastera, i to sa komandom:

    fsutil fsinfo ntfsinfo c:

    Ukoliko je među ispisanim vrednostima "Bytes Per Cluster" različita od 65536, particija nije pravilno formatirana, što negativno utiče na IO performanse SQL servera.   Procesorske performanse i memorija Zahtevi mogu biti veoma različiti, preporučeni minimum su najmanje dual core Xeon sistemi i 8GB memorije, kao i 64-bitni sistem.

TempDB

• Preporučuje se imati jedan data file za svako fizičko CPU jezgro u sistemu. Kao primer možemo koristiti veoma rasprostranjen procesor Intel Xeon E5620, koji ima 4 fizička jezgra ili 8 virtuelnih (hyperthreading). U ovom slučaju je optimalno imati 4 data file za tempdb. Svi data file-ovi moraju biti jednake veličine

• data file-u za tempdb preporučuje se unapred alocirati diskovni prostor, da bismo izbegli fragmentaciju.

• Primer pripreme dodatnog datafile-a sa 10GB prealociranog prostora:

USE [master]

GO

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

GO  

Analize brzine delovanja Pantheon™ baze podataka - sistem

U slučaju zapažanja usporenja delovanja, za prvi korak preporučena je realizacija procesa nadogradnje Pantheon™ baze, jer se tako uspostavlja optimalno stanje baze podataka:

• popravljaju se indeksi i ključevi na tabelama

• popravlja se referentna integritet između tabela

• dodaju se nova polja, uređuju postojeća i uklanjaju suvišna

Pri nadogradnji moramo biti pažljivi da uključimo opciju "Nadogradnja svih objekata". Time se proverava cela Pantheon™ struktura i otklanjaju greške koje je možda izazvao spoljašnji faktor (dodatne implementacije).

Preporučuje se praćenje performansnih brojača, na nivou Microsoftovog operativnog sistema:

• Memory Commited Bytes – ukoliko je alocirane memorije više nego što sistem ima, potrebno je proširiti memoriju, jer se radi o tzv. swapping-u.

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

• PhysicalDisk Idle Time – ukoliko ova vrednost više puta padne ispod 15%, to znači da su diskovna polja preopterećena

• PhysicalDisk Read Latency & Write Latency – ukoliko više puta pređu 30ms, potrebno je pregledati strukturu diskovnih polja, sql zahteve, usklađenost particija sa najboljim praksama itd.  

Analize brzine delovanja Pantheon™ baze podataka – SQL

Proverimo koliko vremena podaci ostaju u kešu. Vrednosti "page life expectancy" su kritične, a preporučene su u rangu oko 1000. Za pravilnu sliku, donji zahtev treba da se izvršava svake 5 minuta kroz dovoljno dugo vreme, da bi uzorak bio reprezentativan. Uključivanjem više serverskih nadzornih sistema (pretežno ugrađeni lokalni MS data collection), to može negativno uticati na ukupne performanse servera, jer analiza podataka teče 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 po CPU i sa donjim upitima. Većinom problemi nastaju nakon korisničkih dodela procedura (sloppy programming, indeksi, full table skenovi,...). Na osnovu rezultata, najzahtevnije 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čuje se proveriti integritet baze podataka sa:

USE Pantheon

GO

DBCC CHECKDB WITH NO_INFOMSGS

Netačna postavka ili nedostajući indeksi su često uzrok performansnih problema. Često probleme izaziva i fragmentacija indeksa ili ažuriranje statistike tabela. Fragmentaciju indeksa proveravamo 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čuje se nedeljno izvršavanje rebuild-a indeksa i ažuriranje statistike 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