(ARHIV)
Configurarea serverului MS SQL pentru medii Pantheon™ mai mari

Configurarea serverului MS SQL pentru medii Pantheon™ mai mari

  Dacă ne gândim la medii mai mari, versiunea gratuită MS SQL Express nu este suficientă, așa că apelăm la versiunile Standard sau Enterprise. În continuare, urmează câteva sfaturi de bază pentru planificarea și pregătirea sistemului în care va rula serverul Pantheon™.  

Discuri RAID

Avem nevoie de cel puțin trei volume RAID1, fiecare format din cel puțin șase discuri:

• primul volum este folosit pentru sistem, pagefile, instalarea SQL

• al doilea volum este folosit pentru bazele de date de sistem, inclusiv TEMPDB

• al treilea volum este folosit pentru bazele de date de producție

Dacă avem la dispoziție 8 discuri, folosim al patrulea volum pentru loguri.

Este de dorit ca volumul RAID de sistem să fie aliniat. Pentru volumele de disc care conțin baze de date SQL, acest lucru este esențial. În cazul utilizării sistemului de operare Windows Server 2008, acest lucru se face automat, iar în cazul Windows Server 2003, trebuie să facem manual cu comanda diskpart. Exemplul de mai jos ilustrează procesul de pregătire a unei partiții aliniate pe unul dintre volumele RAID:

• diskpart

• list disk

• select disk <x>

• create partition primary align=1024

• assign

• exit

Partițiile care conțin baze de date SQL trebuie formatate cu dimensiunea cluster-ului de 64KB.

Dacă sistemul SQL este deja configurat, putem verifica alinierea partițiilor cu comanda:

    wmic partition get BlockSize, StartingOffset, Name, Index

    Offset-ul de început afișat se împarte la dimensiunea blocului. Dacă obținem o valoare impară (de exemplu, 63), partițiile nu sunt pregătite corect, ceea ce înseamnă o performanță SQL cu 20% - 40% mai lentă în diferite sarcini de I/O.

Putem verifica și adecvarea dimensiunii cluster-ului, folosind comanda:

    fsutil fsinfo ntfsinfo c:

    Dacă printre valorile afișate „Bytes Per Cluster” este diferit de 65536, partiția nu este formatată corect, ceea ce afectează negativ performanțele I/O ale serverului SQL.   Capacitățile procesorului și memoria Cererea poate varia foarte mult, dar minimul recomandat este cel puțin sisteme dual core Xeon și 8GB de memorie, precum și un sistem pe 64 de biți.

TempDB

• Se recomandă să avem un fișier de date pentru fiecare nucleu fizic CPU din sistem. Ca exemplu, putem folosi procesorul Intel Xeon E5620, care are 4 nuclee fizice sau 8 virtuale (hyperthreading). În acest caz, este optim să avem 4 fișiere de date pentru tempdb. Toate fișierele de date trebuie să fie de aceeași dimensiune

• fișierelor de date pentru tempdb se recomandă să le alocăm din timp spațiu pe disc, pentru a evita fragmentarea.

• Exemplu de pregătire a unui fișier de date suplimentar cu 10GB de spațiu prealocat:

USE [master]

GO

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

GO  

Analiza vitezei de funcționare a bazei de date Pantheon™ - sistem

În cazul observării încetinirilor de funcționare, primul pas recomandat este efectuarea procesului de actualizare a bazei de date Pantheon™, deoarece astfel se stabilește o stare optimă a bazei de date:

• se corectează indexurile și cheile din tabele

• se repară integritatea referențială între tabele

• se adaugă câmpuri noi, se organizează cele existente și se elimină cele redundante

În timpul actualizării, trebuie să fim atenți să activăm opțiunea „Actualizare a tuturor obiectelor”. Acest lucru verifică întreaga structură Pantheon™ și elimină erorile care ar putea fi cauzate de un factor extern (implementări suplimentare).

Se recomandă monitorizarea contorilor de performanță, la nivelul sistemului de operare Microsoft:

• Memory Commited Bytes – dacă memoria alocată este mai mare decât cea disponibilă în sistem, este necesar să extindem memoria, deoarece se produce așa-numitul swapping.

• Memory Available Mbytes – dacă aceasta scade sub 5%, este necesar să extindem memoria

• PhysicalDisk Idle Time – dacă aceasta scade frecvent sub 15%, înseamnă că volumele de disc sunt suprasolicitate

• PhysicalDisk Read Latency & Write Latency – dacă acestea depășesc frecvent 30ms, este necesar să examinăm structura volumelor de disc, cererile SQL, conformitatea partițiilor cu cele mai bune practici etc.  

Analiza vitezei de funcționare a bazei de date Pantheon™ – SQL

Verificăm cât timp rămân datele în cache. Valorile „page life expectancy” sunt critice, iar cele recomandate sunt în jur de 1000. Pentru o imagine corectă, cererea de mai jos ar trebui să fie executată la fiecare 5 minute pe o perioadă suficient de lungă pentru a fi reprezentativă. Activarea mai multor sisteme de monitorizare a serverului (în special MS data collection local integrat) poate afecta negativ performanța generală a serverului, deoarece analiza datelor se desfășoară local.

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

Verificăm cele mai solicitante operațiuni pe CPU și cu interogările de mai jos. Problemele apar de obicei după alocările de utilizator ale procedurilor (programare neglijentă, indexuri, scanări complete ale tabelelor,...). Pe baza rezultatelor, cele mai solicitante operațiuni trebuie analizate și optimizate, dacă este necesar.

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

Se recomandă verificarea integrității bazei de date cu:

USE Pantheon

GO

DBCC CHECKDB WITH NO_INFOMSGS

O configurare greșită sau indexuri lipsă sunt adesea cauza problemelor de performanță. De multe ori, problemele sunt cauzate și de fragmentarea indexurilor sau actualizarea statisticilor tabelelor. Fragmentarea indexurilor se verifică cu:

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

Se recomandă efectuarea săptămânală a reconstruirii indexurilor și actualizarea statisticilor cu:

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