CAPITOLE AVANSATE DE BAZE DE DATE

1
CAPITOLE AVANSATE DE BAZE DE DATE

• SQL-Server.
• Performanta si intretinere.
• Maintenance Plan

2
Maintenance plan / SQL-Server

• Performante bune
• Design (tabele indecsi)
• Statistici
• Partitionare
• Gestiune fisiere server
• plus monitorizare si intretinere
• Indicatori de performanta (Windows / SQL-Server)
• Performanta interogarilor
• Defragmentare indecsi
• Actualizare statistici
• Creare backup verificare backup
• Intretinere fisiere log-uri
• Verificare BD (CHECK DB)

3
Design

• Normalizare
• Alegerea tipurilor de date corespunzatoare
• Definire chei primare, chei straine
• Definire unique constraints, check constraints
• Folosire de view-uri (posibil indexate) pentru
  denormalizare
• Partitionare tabele

4
Design. Indecsi

• Creare de indecsi necesari in operatiile pe
  tabele
• Index clustered pe o cheie cat mai mica
• Cate un index pentru fiecare cheie straina (join)
• Creare de indecsi covering pentru interogari
  (WHERE, GROUP BY, ORDER BY, DISTINCT)
• Creare de indecsi compusi a.i. prima (primele)
  coloana(e) sa fie cea mai selectiva(e) (cele mai
  multe valori distincte)
• Indecsi included columns (no lookups)
• FILL FACTOR

5
Design

• Observatie view-uri indexate
• CREATE VIEW CREATE UNIQUE CLUSTERED INDEX
• Ocupa spatiu fizic
• Orice modificare pe tabelele sursa ale view-ului
  (care influenteaza si continutul view-ului)
  produce si modificarea view-ului (index-ului)
• Optimizare interogari

6
Statistici

• Query Optimizer foloseste statistici pentru
  datele din tabele sau view-uri indexate pentru a
  alege un plan de executie cat mai bun (SELECT,
  INSERT, UPDATE, DELETE)
• Arata distributia valorilor pe una sau mai multe
  coloane (cate valori distincte sunt, cu ce
  frecventa se repeta) pe intervale de valori
• Folosite pentru a estima numarul de inregistrari
  din rezultat
• Statistici create si actualizate automat sau
  manual
• DBCC SHOW_STATISTICS
• histograma
• RANGE_HI_KEY (limita superioara interval),
• RANGE_ROWS (cate inregistrari se incadreaza pe
  interval),
• EQ_ROWS (cate inregistrari au valoarea egala cu
  limita superioara),
• DISTINCT_RANGE_ROWS (cate inregistrari distincte
  sunt pe interval valori distincte pe coloana
  urmarita),
• AVG_RANGE_ROWS ( RANGE_ROWS /
  DISTINCT_RANGE_ROWS)
• CREATE STATISTICS
• UPDATE STATISTICS / EXEC sp_updatestats (all
  tables)

7
Partitionare orizontala

• SQL Server 2005 -gt
• Pas 1 Creare functie de partitionare
• Exemplu
• if exists ( select from sys.partition_functions
  
• where name 'fn_part_an')
• drop partition function fn_part_an
• go
• create partition function fn_part_an ( int )
• as range right
• for values ( 2000, 2002, 2004 )
• --min int, 2000), 2000, 2002), 2002, 2004),
  2004, max int)
• go

8
Partitionare orizontala

• Pas 2 Creare schema de partitionare
• Exemplu toate partitiile in acelasi (grup de
  fisiere) fisier
• CREATE PARTITION SCHEME sch_part_an
• AS PARTITION fn_part_an ALL TO (PRIMARY)

9
Partitionare orizontala

• Pas 2 Creare schema de partitionare
• Exemplu partitii in grupuri de fisiere diferite
• if exists ( select from sys.partition_schemes
• where name 'sch_part_an')
• drop partition scheme sch_part_an
• go
• create partition scheme sch_part_an
• as partition fn_part_an
• to (fg0, fg2000, fg2002, fg2004, fg2006)
• go
• alter partition function fn_part_an ()
• split range (2006)
• go
• alter partition scheme sch_part_an
• next used fg2006
• go

Goala, dar marcata ca next used
10
Partitionare orizontala

• Pas 3 Creare tabel partitii
• Exemplu
• CREATE TABLE Contracte
• (ContractID int NOT NULL,
• AngajatId int NOT NULL,
• AngajatorId int NOT NULL,
• DataIncheiere datetime NOT NULL,
• An int NOT NULL
• CONSTRAINT CK_An CHECK (An gt 1999 AND An lt
  2006))
• ON sch_part_an (An)
• GO

select partitie PARTITION.fn_part_an(an),
nr_inreg count() from dbo.contracte group by
PARTITION.fn_part_an(an) order by partitie
11
Gestiune fisiere server

• Gestiune fisiere server
• Pozitionare fisiere de date pe alt disc fata de
  fisierele de log-uri
• Pozitionare tempDB pe alt disc fata de fisierele
  de date si fisierele de log-uri
• Pozitionare fisiere backup pe alt disc (alta
  masina) fata de alte fisiere ale server-ului
• Monitorizare dimensiune fisiere (vezi autogrow)
• ALTER DATABASE myDB
• MODIFY FILE (NAME myDBFileName, SIZE 20MB,
  FILEGROWTH 10MB)
• Verifica fragmentarea datelor disc (defrag Win)
• Observatie Pentru o buna performanta, fisierul
  de loguri trebuie stocat pe un suport
  performant!!!

0 no autogrowth
12
Indicatori de performanta (Windows / SQL-Server)

• Windows
• Computer management -gt System Tools -gt
  Performance Logs and Alerts (or perfmon.exe)
• SQL-Server
• Tools -gt Performance -gt SQL Server Profiler (sau
  folosind sp-uri)
• sys.dm_os_performance_counters gt object_name,
  counter_name, cntr_value, ... (locks,
  transactions, access methods, errors, statistics,
  cursor manager, memory manager, deprecated
  features, ...)

13
Indicatori de performanta (Win)
14
Performanta interogari

• SQL Profiler
• Duration
• Reads
• Writes
• CPU
• ...
• XEvents
• Plan de executie
• Hints
• Locking / isolation level hints
• Index hints
• Join hints
• Indecsi

15
Performanta interogari

• DMVs (2005 -gt)
• last_execution_time, execution_count
• min, max, last, total worker_time,
  logical_reads, logical_writes, elapsed_time
• query text, query hash
• SELECT
• FROM sys.dm_exec_query_stats AS qstats
• CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle
  ) AS qtext

16
Performanta interogari

• XEvents (2008 -gt)
• solicita putine resurse
• gestiune interfata grafica sau Transact-SQL
• colectare de informatii
• pachete
• events
• targets events consumers / event output
• file
• ring buffer
• ...
• actions
• predicates

17
Performanta interogari

• XEvents (2008) exemplu
• CREATE EVENT SESSION XESession_FindLongRunningQuer
  ies ON SERVER
• ADD EVENT sqlserver.sql_statement_completed
• (
• ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
  
• WHERE sqlserver.sql_statement_completed.duration
  gt 1000
• )
• ADD TARGET package0.asynchronous_file_target(
• SET filename 'c\Temp\XE_FindLongRunningQueries
  .xet',
• metadatafile 'c\Temp\XE_FindLongRunningQuerie
  s.xem')
• --------------------------------------------
• ALTER EVENT SESSION LongRunningQuery ON SERVER
  STATE START
• ALTER EVENT SESSION LongRunningQuery ON SERVER
  STATE STOP
• (DMV sys.fn_xe_file_target_read_file gt citeste
  async. file target / XML / one row per event)
• DROP EVENT SESSION LongRunningQuery ON SERVER

18
Defragmentare indecsi

• Tabele si indecsi colectii de extends 8
  pagini 1 pagina 8KB
• Index clustered
• B-arbore
• in nodurile interioare exista intrari care contin
  valori cheie (plus adrese ale nodurilor fiu)
• in nodurile terminale sunt stocate inregistrarile
  din tabelul indexat
• Index non-clustered
• B-arbore
• peste un tabel indexat clustered sau heap
  (neindexat)
• in nodurile interioare exista intrari care contin
  valori cheie (plus adrese ale nodurilor fiu)
• In nodurile terminale valoare cheie row
  locator
• daca e heap row locator refera inregistrare din
  tabel
• daca e tabel indexat clustered row locator este
  cheia indexului clustered (pp unique)

19
Defragmentare indecsi

• Fragmentare cand ordinea logica a paginilor nu
  corespunde cu ordinea fizica a paginilor in
  fisier fragmentare externa (fizica)
• Plus mult spatiu liber in paginile unui index
  fragmentare interna (logica)
• Determinare nivel de fragmentare
  sys.dm_db_index_physical_stats (avg_fragmentation_
  in_percent, avg_page_space_used_in_percent)
• gt Reorganizare sau Reconstruire (ALTER INDEX)
• Frag intre 10 si 30 gt REORGANIZE
• Frag gt 30 gt REBUILD
• (posibil si alte valori)
• Fill factor (in caz de REBUILD)

20
Backup

• Recovery models
• Simple
• No log backup
• Mai putin spatiu necesar
• Nu se pot recupera modificarile efectuate de la
  ultimul backup
• Full
• Log backups
• Mai mult spatiu necesar
• Se pot recupera modificari de la ultimul backup
  de log (posibil chiar coada log-ului pana la
  momentul la care a aparut un defect daca dupa
  revenire se poate crea backup pt tail-log)
• Bulk-logged
• Log backups
• Performanta mai mare pentru operatii bulk copy
  decat full (min logging)
• Se pot recupera modificari pana la ultimul backup
  (mai departe, depinde de starea log-ului si daca
  au avut loc operatii bulk)
• Importanta ca ultimele operatii sa nu se piarda
  vs. costul backup-ului si restaurarii

21
Backup

• Backup
• Full (BACKUP DATABASE)
• Intreaga baza de date
• Diferential (BACKUP DATABASE WITH DIFFERENTIAL)
• De la ultimul backup full
• (plus file / filegroup / page backup)
• Log (BACKUP LOG)

22
Backup simple recovery


Backup full recuperare simpla
Backup full diferential recuperare simpla
23
Backup full recovery


Backup full log recuperare full
Backup full diferential log recuperare full
24
Restaurare si recuperare

• Restaurare BD copiere de date din backup in BD
• Recuperare BD eventual refacerea operatiilor
  tranzactiilor inregistrate in log-uri anularea
  tranzactiilor ne-comise, dupa crearea ultimului
  backup folosit BD devine online (RESTORE
  DATABASE WITH RECOVERY)

25
Restaurare si recuperare

• Creare backup pentru tail-log portiunea din
  transaction log activa
• BACKUP LOG database TO DISK \DB.bak' WITH
  NORECOVERY
• Restaurare BD din ultimul backup full, fara
  recuperare
• RESTORE DATABASE database FROM full_database_backu
  p WITH NORECOVERY
• Daca exista backup-uri diferentiale, restaurare
  din ultimul, fara recuperare
• RESTORE DATABASE database FROM full_differential_b
  ackup WITH NORECOVERY
• Pentru fiecare backup de log care a fost creat de
  la ultimul backup folosit, restaurare log-uri pe
  rand, fara recuperare (ultimul este tail-log
  backup)
• RESTORE LOG database FROM log_backup WITH
  NORECOVERY
• Recuperare BD
• RESTORE DATABASE database_name WITH RECOVERY

26
Log

• Fisier log n fisiere virtuale
• Fisier circular
• Micsorarea fisierului de log-uri
• vezi DBCC SHRINKFILE
• BACKUP LOG

27
Stare BD

• Monitorizare integritate BD
• DBCC CHECKDB ( DBCC CHECKALLOC DBCC
  CHECKTABLE DBCC CHECKCATALOG alte validari)
• Consistenta structurilor de alocare a spatiului
  pe disc
• Integritatea paginilor si structurilor unui tabel
  sau view indexat
• Consistenta catalogului
• Monitorizare integritate date
• DBCC CHECKCONSTRAINTS
• Consistenta datelor conform constrangerilor
  existente
• Atentie
• ALTER TABLE WITH NOCHECK ADD CONSTRAINT
  ALTER TABLE CHECK CONSTRAINT ,
• ALTER TABLE NOCHECK CHECK CONSTRAINT
• gt ALTER TABLE WITH CHECK CHECK CONSTRAINT

28
Jobs / Maintenance plans

• SQL-Server Agent scheduling agent
• Jobs
• Nume, context BD (BDs), owner
• Steps (next job, success, failure) transact-SQL,
  extern exe, si altele
• (Opt) Schedule
• (Opt) Output
• (Opt) Notifications la terminarea executiei
  job-ului (email, pager, net send, si altele)
• Maintenance plan (user-friendly interface /
  wizard usual jobs limited functionality)

29
Alte monitorizari

• Profiler
• Operatiile costisitoare (timp, CPU, read, write)
• Planurile de executie table scans index scans
• Tranzactiile lungi
• Deadlock-urile
• Replay traces
• ...
• Spatiu liber / ocupat pe disc(uri) (vezi
  xp_fixeddrives)
• Marirea fisierelor daca nu sunt setate sa-si
  mareasca dimensiunea automat
• Alte view-uri sistem
• sys.dm_db_missing_index_details
• sys.dm_db_index_physical_stats
• ...