Transcript DB Maint plan

CAPITOLE AVANSATE
DE BAZE DE DATE
SQL-Server.
Performanta si intretinere.
Maintenance Plan
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)
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
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
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
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)
Partitionare orizontala

SQL Server 2005 ->

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
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])
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”
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 >= 1999 AND An <= 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;
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);
0 = no autogrowth
Verifica fragmentarea datelor disc (defrag Win)
Observatie: Pentru o buna performanta, fisierul de loguri trebuie stocat
pe un suport performant!!!
Indicatori de performanta
(Windows / SQL-Server)

Windows


Computer management -> System Tools -> Performance
Logs and Alerts (or perfmon.exe)
SQL-Server


Tools -> Performance -> SQL Server Profiler (sau
folosind sp-uri)
sys.dm_os_performance_counters => object_name,
counter_name, cntr_value, ... (locks, transactions, access
methods, errors, statistics, cursor manager, memory
manager, deprecated features, ...)
Indicatori de performanta (Win)
Performanta interogari

SQL Profiler








XEvents
Plan de executie
Hints




Duration
Reads
Writes
CPU
...
Locking / isolation level hints
Index hints
Join hints
Indecsi
Performanta interogari

DMV’s (2005 ->)



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
Performanta interogari

XEvents (2008 ->)




solicita putine resurse
gestiune: interfata grafica sau Transact-SQL
colectare de informatii
pachete
• events
• targets – events consumers / event output
• file
• ring buffer
• ...
• actions
• predicates
Performanta interogari

XEvents (2008) exemplu:
CREATE EVENT SESSION XESession_FindLongRunningQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
WHERE sqlserver.sql_statement_completed.duration > 1000
)
ADD TARGET package0.asynchronous_file_target(
SET filename = 'c:\Temp\XE_FindLongRunningQueries.xet',
metadatafile = 'c:\Temp\XE_FindLongRunningQueries.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 => citeste async. file target / XML
/ one row per event)
DROP EVENT SESSION LongRunningQuery ON SERVER
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)
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)

=> Reorganizare sau Reconstruire (ALTER INDEX)




Frag intre 10% si 30% => REORGANIZE
Frag > 30% => REBUILD
(posibil si alte valori)
Fill factor (in caz de REBUILD)
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 logului si daca au avut loc operatii bulk)

Importanta ca ultimele operatii sa nu se piarda vs. costul backup-ului si
restaurarii
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)
Backup + simple recovery
Backup full +
recuperare simpla
Backup full & diferential +
recuperare simpla
Backup + full recovery
Backup full & log +
recuperare full
Backup full & diferential & log +
recuperare full
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)
Restaurare si recuperare

Creare backup pentru tail-log – portiunea din transaction log activa


Restaurare BD din ultimul backup full, fara recuperare


RESTORE DATABASE database FROM full_differential_backup 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 DATABASE database FROM full_database_backup WITH
NORECOVERY
Daca exista backup-uri diferentiale, restaurare din ultimul, fara recuperare


BACKUP LOG database TO DISK = ‘…\DB.bak' WITH NORECOVERY
RESTORE LOG database FROM log_backup WITH NORECOVERY
Recuperare BD

RESTORE DATABASE database_name WITH RECOVERY
Log

Fisier log = n fisiere virtuale

Fisier circular

Micsorarea fisierului de log-uri


vezi DBCC SHRINKFILE
BACKUP LOG
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 …
• => ALTER TABLE … WITH CHECK CHECK CONSTRAINT …
Jobs / Maintenance plans


SQL-Server Agent – scheduling agent
Jobs






Nume, context BD (BD’s), 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)
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
...