Forelasningar 14

ATT ÖVERVAKA
INNEHÅLL
DATABASENS STORLEK
Databasens storlek kan avläsas i Enterprise Manager
• Finns tillräckligt utrymme för tillväxt?
• Kan vi ge tillbaka outnyttjat utrymme till filsystemet?
Rapporten Disk Usage
Övervakning av databasens storlek
När databasen växer
Datalagringsenheter
Krympning / defragmentering av
databasen
 Database options - Inställningar för
databasen




(Högerklicka på databasen - Reports - Standard Reports - Disk Usage)
Med sp_helpdb ser vi
bara storleken på databasfilerna.
Rapporten visar hur
mycket som är använt!
Ger tips om det finns
behov av nya filer, om vi
kan krympa filer mm.
Om det inte fungerar,
kanske det krävs att IIS är
installerad.
Tommy Löfqvist
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
1
Tommy Löfqvist
DATALAGRINGSENHETER - DATA PAGES, EXTENT
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
NÄR DATABASEN
2
VÄXER
96 byte
När behövs mer än en primär data och loggfil?
Data (ej text och image) lagras i form av
pages (sidor) 8Kbyte.
Sidorna lagras i sin tur i extent som består av
8 sidor, dvs 64KB.
max
8060
byte
I/O operationer läser data i form av datapages,
dvs 8KB i taget.
o Databasen kan vara för stor för en fil
o Prestanda kan kräva flera filer. Det går snabbare att läsa från tabeller
som ligger på var sin disk.
o Underhåll (t ex backup) kan kräva flera filer
Två alternativ när du behöver mer data eller loggutrymme
• Utöka befintliga filer
T ex utöka C:\DATA\kundData.mdf från 100MB till 300MB. Gör databasen
så stor att autogrowth undviks, vilket är mycket resurskrävande.
Från början blandas tabeller och index i samma
extent, när tabellerna växer organiseras data
så att en tabell får ett eget extent.
• Lägg till fler filer (ev på annan drive)
T ex den befintliga filen C:\DATA\kundData.mdf (100MB) kompletteras
med den nya filen D:\DATA\kundData2.ndf (200MB)
Konvention: .ndf används fr o m andra datafilen
Båda operationerna kan göras med ALTER DATABASE eller grafiskt i
Management Studio
Tommy Löfqvist
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
3
Tommy Löfqvist
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
4
1
KRYMPA DATABASER - SHRINK
DATABASE
När data raderas behåller filen sin storlek! - Slöseri med diskutrymme!
Före SHRINK:
Fragmenterad databasfil:
50 MB
Efter SHRINK
DEFAULT
Olika sätt
att krympa
filer
Krympa databaser med
Management Studio
100 MB
1. Automatic Options
EMPTYFILE
Autoclose
(False)
• Stänger databasen efter sista användaren
• Bör vara ON för databaser som accessas av
applikationer som ofta ansluter och avslutar
anslutningar till SQL Server
Auto_Create_Statistics
Man kan krympa (SHRINK) både
databasen och transaktionsloggen
Autoshrink
Auto_Update_Statistics (False)
Uppdaterar statistik när ett tröskelvärde överskridits
DBCC = Database Console Commands
DATABASE
OPTIONS
(False)
• Krymper databasen automatiskt när mer än 25% är oanvänt
• Gäller även transaktionsloggar, efter backup
• Tar mer resurser än autogrow
Krymper DBC och lämnar 5% kvar
dbc_data1 krymps nu till 5MB
DBC_data1 kan nu tas bort
med MStudio eller "ALTER DATABASE"
5
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
(True) Se bilaga sid 11
• Statistik skapas automatiskt på kolumner som
används i en WHERE-sats
• Bättre beslut av Query Optimizer
Krympa filer med TSQL
Tommy Löfqvist
Kan ändras med:
• Management Studio (inte riktigt allt)
• Kommandot: ALTER DATABASE - SET …
TRUNCATEONLY
-----
#1
Options är indelade i olika grupper
NOTRUNCATE
Flyttar data till annan fil i filgruppen.
Endast tomma filer kan tas bort.
DBCC SHRINKDATABASE (DBC, 5)
DBCC SHRINKFILE (dbc_data1, 5)
DBCC SHRINKFILE (DBC_data1, EMPTYFILE)
OPTIONS
Database options påverkar olika aspekter hos databasen.
Tommy Löfqvist
För fullständig beskrivning se
Books OnLine!
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
#2
KOMMANDON
6
FÖR STATE OPTIONS
Alla databasoptions kan sättas med TSQL
ALTER DATABASE databas
SET <optionspec> [,…n] [WITH <termination>]
2. SQL Options
Collation
Bestämmer teckenuppsättning och sorteringsordning. Finnish_Swedish_CI_AS är lämplig här
Recovery models
Bestämmer graden av återställbarhet (recovery) efter en crash Simple | Full | Bulked logged
kan väljas.
Compatibility Level
Anger kompabilitet med äldre versioner. SQL Server 2008 har 100, SQL Server 2012 har 110
3. Miscellaneous Options
Några exempel
Sätt offline
ALTER DATABASE dbc SET OFFLINE
(alt. ONLINE)
Sätt read_only
ALTER DATABASE dbc SET READ_ONLY
(alt. READ_WRITE)
Flaggor som bestämmer hur olika SQL-satser ska tolkas
4. State Options
Sätt single user mode
ALTER DATABASE dbc SET SINGLE_USER
Styr vem som kan använda databasen och vilka operationer dessa kan göra. Dessa kan
sättas i Management Studio men kommandon ger fler möjligheter.
Single user mode krävs för vissa kommandon
DataBase Read-Only
DataBase State
Encryption Enabled
Restrict Access
Tommy Löfqvist
-
(alt. RESTRICTED_USER | MULTI_USER)
True | False
ONLINE, OFFLINE or SUSPECT
True | False
MULTI_USER | SINGLE_USER | RESTRICTED USER
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
7
Tommy Löfqvist
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
8
2
KOMMANDON
FÖR STATE OPTIONS
- TERMINATION
SAMMANFATTNING
OPTIONS
Många state options kan inte sättas då databasen används av
okvalificerade användare.
Data lagras och läses i form av data pages (datasidor) om 8Kbyte
Termination options bestämmer vad som ska hända med dessa vid
ALTER DATABASE kommandot.
Databasens storlek kan övervakas med hjälp av standardrapporter i
Management Studio
ROLLBACK AFTER heltal [seconds] | ROLLBACK IMMEDIATE | ROLLBACK NO_WAIT
Exempel
Om dba behöver göra "viktiga" kommandon som kräver single user mode måste
inloggade användare kastas ut. Det kan man bara göra med kommando.
1.
Kasta "snällt" ut vanliga användare
När data raderas krymper inte den fysiska filen. Krympning av
databasfil eller databas gör vi med DBCC eller inifrån Mstudio
Ett stort antal OPTIONS kan ställas in i MStudio eller med TSQLkommandot ALTER DATABASE - SET Option
Nu är de "ur vägen" så att man kan göra "viktiga" inställningar
Optimeraren använder statistiken för att bedöma om index ska
utnyttjas
ALTER DATABASE NorthWind
SET AUTO_UPDATE_STATISTICS ON,
AUTO_CREATE_STATISTICS ON
Tommy Löfqvist
Filer kan också förstoras
Datafil måste vara tom innan den kan raderas (DBCC - EMPTYFILE)
ALTER DATABASE NorthWind
SET RESTRICTED_USER WITH ROLLBACK AFTER 60 SECONDS
2.
Man kan behöva lägga till fler filer då databasen växer, när man vill
öka prestanda eller har speciella backupönskemål
Vissa options kräver att databasen sätts i "single user mode"
Med SET … WITH ROLLBACK … kan andra användare "kastas ut”
9
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
Tommy Löfqvist
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
10
STATISTIKEN
SQL Server skapar som default statistik över innehållet i tabellerna för att kunna
besluta om index ska utnyttjas eller ej.
Fördelning av Lön i Personaltabellen
Frekvens (%)
30
OBS!
Att läsa via index kräver att man
läser i både index och data.
För att det ska löna sig ska svaret
ge mindre än 10% av posterna,
dvs man ska ha god SELEKTIVITET
0 10 20 30 40 50 60
Lön (*1000 kr)
SELECT Namn, Lön
FROM Personal
WHERE Lön > 60000
SELECT Namn, Lön
FROM Personal
WHERE Lön < 60000
Statistiken ger:
Statistiken ger:
God selektivitet (<10%)
Dålig selektivitet:
Optimeraren beslutar:
Optimeraren beslutar:
=> Det lönar sig att
använda index till frågan
 Det lönar sig inte att använda
index till frågan
”Nästan alla”
Tillbaka
Tommy Löfqvist
Institutionen
Sven
för Kommunikation
Åke
Lnu/DFM
Johansson o design
3