Please enable JavaScript.
Coggle requires JavaScript to display documents.
Expert performance Indexing 2012 (1. Index Fundamentals :baby: (7. Index…
Expert performance Indexing 2012
1. Index Fundamentals :baby:
1. Why building indexes? :thinking_face:
odnajdowanie danych
łatwiejsze
efektywniejsze
dlaczego?
indeksy zapewniają
informacje o miejscu przechowywania danych
dane są upoorządkowane
efektywną ścieżkę dostępu
2. Major Index Types :person_with_blond_hair: :person_frowning::skin-tone-6:
Heap Tables
dane
nieuporządkowane
struktura danych
Clustered Indexes
dane
uporządkowane
według
kolumn/y klucza
struktura danych
max 1
Nonclustered Indexes
dane
uporządkowane
>=1
referencje do
sterty
indeksu klastrowego
"katalogowanie" danych
według wybranego klucza
Columnstore Indexes
dane
sposób organizacji
kolumny
przechowywane
osobno
z
1 more item...
zastosowanie
hurtownie danych
duże wolumeny danych
HoBT
tabela
struktura
albo B-Drzewo
Albo sterta
3. Other Index Types :hatched_chick: :dog2: :cat:
XML Indexes
Spatial Indexes
Full-text search
Hash and Range
In-memory OLTP
4. Index Variations :black_small_square: :black_square_button: :white_small_square: :black_square_for_stop:
Primary Key
identyfikuje rekord
w tabeli
unikatowy
dla rekordu
kolumny
>=1
<2016
max 12
2016+
max 32
not null
tylko 1
w tabeli
Unique Index
nullable
max 1 null
w kolumnie
kolumny
>=1
zapewnia
unikalność wiersza
w kolumnie
Included Column
tylko w
nonclustered index
dane
nieposortowane
pozwala uniknąć
lookup
na
stercie
B-drzewie
nie mogą zawwierać
kolumn klucza indeksu
Partitioned Indexes
dozwolone w indeksach
klastrowych
nieklastrowych
fizyczne podzielenie indeksu
według określonej wartości
na dysku
logicznie
całość
Filtered Indexes
WHERE
tylko w
nonclustered
zalety
mniej ważą
mniejsze koszty utrzymania
otrzymywanie danych
szybsze
5. Compression and Indexing :compression:
row-level
skompresowany
każdy wiersz
dane liczbowe
długość
ze
stałej
na
zmienną
metadane
w alternatywnym formacie
białe znaki
na końcu
warotści tekstowych
nieprzechowywane
nie ważą
wartości
nie wymagające przestrzeni
null
0
page-level
kompresja
stron
grup wierszy
podobieństwa
wartości tekstowych
kompresowane
podobne do
row-level
kompresja
zwiększa wykorzystanie
CPU
6. Index Data Definition Language :speaking_head_in_silhouette:
Creating
na
tabeli
widoku
INCLUDED
kolumny
nieposortowane
opcje
FILLFACTOR
ilość wolnego miesjca
na każdej stronie
gdy indeks jest
tworzony
przebudowywany
PAD_INDEX
FILLFACTOR
dla
stron indeksu
nie liści
SORT_IN_TEMPDB
wykorzystanie
tempdb
do budowy
indeksu
IGNORE_DUP_KEY
enabled
insert
wiersze zduplikowane
nie wejdą
disabled
wiersze zduplikowane
cały insert
anulowany
STATISTICS_NORECOMPUTE
czy statystyki
utworzone na nowo
gdy tworzony jest indeks
DROP_EXISTING
OFF
tworzenie indeksu
failed
ON
istniejący indeks
z taką samą nazwą
usuwany
ONLINE
zmiany
na indeksie
blokady zminimalizowane
Enterprise
ALLOW_ROW_LOCKS
czy
blokady wierszy
dozwolone
na indeksie
ALLOW_PAGE_LOCKS
czy
blokady stron
dozwolone
na indeksie
MAXDOP
DATA_COMPRESSION
tak
nie
>2012
STATISTICS_INCREMENTAL
statystkyki
tworzone
wybór
2 more items...
Altering
REBUILD
REORGANIZE
DISABLE
klucz
nie można zmienić
Dropping
DROP INDEX
zmiana
z B-Drzewa
na Stertę
MOVE TO
7. Index Metadata :scroll:
sys.indexes
info
o każdym indeksie
w bazie
nazwa
typ
właściwości
sys.index_columns
kolumny
dołączone
klucza
sys.xml_indexes
sys.spatial_indexes
sys.column_store_dictionaries
słownik
info
typ
struktura
dla kolumny
sys.column_store_segments
1 kolumna
wiele segmentów
1 segment
+- 1 000 000
wierszy
2. Index Storage Fundamentals
:open_file_folder: :minidisc:
:baby::skin-tone-5:
1. Storage Basics
:page_facing_up: Pages
podstawowy obszar przechowywania danych
8KB
limit
najmniejsza jednostka I/O
:hammer_and_wrench: budowa
nagłówek
96 bajtów
nr strony
id obiektu
do którego należy strona
typ strony
rekordy
8060 bajtów
tablica przesunięć
offset array
36 bajtów
wskaźniki
do początków wierszy
slotów
wiersz
dodanie
do 1 wolnej otwartej pozycji
w strefie rekordów
następnie
aktualizacja offset array
1 more item...
Extents
:black_square_button::black_square_button::black_square_button::black_square_button::black_square_button::black_square_button::black_square_button::black_square_button:
8 stron
obok
fizycznie
typy
mixed
wiele obiektów
>2016
już się nie używa
uniform
1 obiekt
wspomaga
contiguous read
nowa tabela
< 8 stron
I. mixed
> 8 stron
uniform
od 2016
od razu uniform
:female_sign: :male_sign:
2. Page Types
File Header Page
info o pliku bazy
File ID
File group ID
Current size of the file
Max file size
Sector size
LSN info
nr
0
Boot Page
metadane
o bazie
wersja
data utworzenia
nazwa bazy
id bazy
compatibility level
dbi_LastKnownGood
data
ostatniego
pomyślnego
1 more item...
1 per baza
nr
9
Page Free Space Page
nr
2
następnie
co 8088 stron
wolne miejsce
szacowane
na stronie
treść
bajt
reprezentuje
kolejne
strony
flaga
bity
0-2
ilość miejsca
strona
5 more items...
3
czy są
ghost records
4
czy część IAM
5
czy mixed
6
czy zaalokowana
7
nieużywany
obok bajta
#
Global Allocation Map Page
info
o extentach
czy
uniform
mixed
wolny
do alokacji
mapa
extentów
następujących
64000
po
GAM
nr
2
treść
mapa bitowa
1 bit
1 extent
+ bit
1
1 more item...
0
1 more item...
Shared Global Allocation Map Page
mapa bitowa
extentów
mieszanych
treść
mapa bitowa
1 bit
1 extent
+ bit
0
2 more items...
1
1 more item...
64000
extentów
kolejnych
po SGAM
nr
3
Differential Changed Map Page
info
czy extent
w GAM
zmienił się
pomiędzy
full backupami
mapa bitowa
1 bit
1 extent
+ bit
1
zmieniony
0
niezmieniony
przydatny
do
differential backup
nr
pierwszej
6
kolejne
po 1
na każdy interwał
GAM
Bulk Changed Map Page
nr
pierwszej
7
kolejne
po 1
na każdy interwał
GAM
info
czy extent
zmieniony
operacją logowaną minimalnie
mapa bitowa
1 bit
1 extent
+ bit
0
nie było operacji bulk
1
była operacja bulk
resetowanie do 0
1 more item...
wykorzystywany
w połączeniu
z BULK_LOGGED recovery model
Index Allocation Map Page
rozpoczyna
indeks
tabelę
mapuje
extent do
tabeli
indeksu
po 1
na interwał
GAM
wiąże
z
tabelą
strony typu
dane
index
LOB
small-large object
indeksem
mapą bitową
nagłówek
sequence number
strony IAM
dla
tabeli
indeksu
numer strony początkowej
interwału GAM
związanego ze stroną IAM
Data Page
przechowuje dane
z wierszy
za wyjątkiem
kolumn
LOB
liście indeksu
Index Page
info
o strukturze
indeksu
o lokalizacji
data pages
do budowania
hierarchii stron
do nawigowania po indeksie
w non-clustered
przechowują
wartość klucza
zawiera
mapowanie
klucza
posortowanych rekordów
adresu strony z klucze
nagłówek
typ strony
jednostka alokacji
partition ID
status alokacji
tablica przesunięć wierszy
row offstet array
wskaźnik do wierszy na stronie
Large Object Page
do
przechowywania wierszy
> 8KB
wiersz
na zwykłej stronie
urośnie
>8KB
wskaźnik
tam gdzie był wcześniej
1 more item...
wiersz
przeniesiony
1 more item...
database log
non page structure
:open_file_folder:
3. Organizing Pages
Heap Structure
sterta
zaczyna się
od IAM
która w tym przypadku
mapuje
strony
1 more item...
extenty
1 more item...
struktura
płaska
dane
kolejność
brak
B-Tree Structure
dane
hierarchiczna struktura
poziomy
strony danych
liście
strony indeksu
korzeń
poziomy pośrednie
zawierają
klucze
adresy stron
posortowane
początek
IAM
na każdy interwał GAM
strony
nagłówki
adres strony
następnej
wspomagają
odczyty sekwencyjne
poprzednie
pozwala na
partycjonowanie
każda partycja
osobne B-Drzewo
Column Store Structure
dane
pogrupowane
w kolumny
przechowywane na
LOB pages
kompresja
zawsze
zawarta w segmencie
początek
IAM
dla każdej kolumny
istnieje
>= 1 segment
+- 1 000 000 wierszy\
do segmentu
słownik
który
2 more items...
:male-doctor::skin-tone-2:
4. Examining Pages
DBCC EXTENTINFO
info
o alokacji extentów
w bazie
o typie użytego extentu
wynik
kolumny
file_id
numer pliku do którego extent jest zaalokowany
page_id
numer strony
pg_alloc
liczba zaalokowanych z extentu stron do obiektu
ext_size
object_id
tabeli
index_id
id indeksu związanego ze stertą lub indexem
partition_number
numer partycji indeksu lub sterty
partition_id
id partycji indeksu lub sterty
iam_chain_type
row data, overdflow data, LOB data
pfs_bytes
tablica bitowa z info o
ilości wolnego miejsca
czy są ghost records
czy to strona IAM
czy jest zaalokowana
czy extent jest
2 more items...
sprawdzenie
czy strony należą
do 1 extentu
nr strony / 8
wynik patrzeć bez reszty
po co?
sprawdzenie
jak strony są zaalokowane do tabel i indeksów
problemów z
fragmentacją
initial single page allocation
do mixed extents
usunięcie efektu
konieczne przebudowanie indeksu
9 zaalokowanych?
1 strona extra
to strona IAM
DBCC IND
lepsze
sys.dm_db_database_page_allocations()
do czego?
informacje
o stronach
indeksu
tabeli
b-drzewa
o zachowaniu indeksu
zwracane dane
PageFID
file number w którym jest strona
PagePID
nr strony
IAMFID
ID pliku w którym jest strona IAM
IAMPID
ID strony w pliku danych
ObjectID
ID obiektu związane z tabelą
IndexID
ID indeksu związanego ze stertą lub indeksiem
PartitionNumber
dla sterty lub indeksu
PartitionID
dla sterty lub indeksu
iam_chain_type
in-row data
row overflow
LOB
PageType
1
data page
2
index page
3
LOB page
4
LOB page
8
GAM page
9
SGAM page
10
IAM page
11
PFS page
13
Boot
15
File header page
16
Differential changed map page
17
Bulk change map page
IndexLevel
od 0 do N
0
najniższy
N
korzeń
NextPageFID
nr pliku
kolejnej strony
na poziomach indeksu
NextPagePID
numer strony
kolejnej
na poziomach indeksu
PrevPageFID
numer pliku
poprzedniej strony
na poziomach indeksu
PrevPagePID
numer strony
poprzedniej
na poziomach indeksu
DBCC PAGE
Page Header Only Print Option
Hex Rows Print Option
Hex Data Print Option
Row Data Print Option
5. Page Fragmentation
Forwarded Records
Page Splits
6. Index Characteristics
Heap
Clustered Index
Non-Clustered Index
Column Store Index
3. Index Statistics
1. Index Level Statistics
DBCC SHOW_STATISTICS
Stats Header
Histogram
Density Vector
Catalog Views
sys.stat_columns
sys.stats
STATS_DATE
Statistics DLL
2. Usage Statistics
Header Columns
User Columns
System Columns
3. Operational Statistics
Header Columns
DML Activity
Select Activity
Range Scan
Singleton Lookup
Forwarded Fetch
Locking Contention
Row Lock
Page Lock
Lock Escalation
Latch Contention
Page I/O Latch
Page Latch
Page Allocation Cycle
Compression
LOB Access
4. Physical Statistics
Header Columns
Row Statistics
Fragmentation Statistics
4. XML, Spatial and Full-Text Indexing
1. XML Indexing
Benefits
Categories
Creating
Effects on execution plans
Effects From a Primary Index
Effects From a Secondary Index
2. Spatial Data Indexing
How spatial data is Indexed?
Creating Spatial Indexes
Supporting methods with Indexes
Statistics, Properties and Information
The Views
The Procedures
Restrictions on Spatial Indexes
3. Full-Text Indexing
Creating a Full-Text Example
Creating a Full-Text Catalog
Creating a Full-Text Index
Syntax
Key Indexes
Population
Stop List
Full-Text Search Index Catalog Views and Properties
5. Index Myths and Best Practices
1. Myths
1: Databases don't need indexes
2: Primary Keys are always Clustered
3: Online Index operations don't block
4: Any column can be filtered in multicolumn indexes
5: Clustered Index stores records in physical order
6: Fill Factor is aplied to indexes during inserts
7: Every table should have a heap/clustered index
2. Best Practices
Use Clustered Indexes on primary keys by default
Balance index count
Fill Factor
Database level fill factor
Index Level Fill Factor
Index Foreign Key Columns
7. Indexing Tools
1. Missing Index DMOs
Explaining DMOs
sys.dm_db_missing_index_details
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_groups
sys._dm_db_missing_index_group_stats
Using DMOs
2. Database Engine Tuning Advisor
Explaining DTA
Using DTA GUI
Using the DTA Utility
8. Index Strategies
1. Heaps
Temporary Objects
Other Heap Scenarios
2. Clustered Indexes
Identity Column
Surrogate Key
Foreign Key
Multiple Column
Globally Unique Identifier
3. Nonclustered Indexes
Search Columns
Index Intersection
Multiple Columns
Covering Index
Included Columns
Filtered Indexes
Foreign Keys
4. Columnstore Index
5. Index Storage Strategies
Row Compression
Page Compression
6. Indexed Views
9. Query Strategies
1. Like Comparison
2. Concatenation
3. Computed Columns
4. Scalar Functions
5. Data Conversion
10. Index Analysis
1. Indexing Method
2. Monitor
Performance Counters
Dynamic Management Objects
Index Usage Stats
Index Operational Stats
Index Physical Stats
Wait Statistics
Data Cleanup
SQL Trace
3. Analyze
Review Of Server State
Performance Counters
Access Methods \ Forwarded Records/sec
Access Methods \ Freespace Scans/sec
Access Methods \ Full Scans/sec
Access Methods \ Index Searches/sec
Access Methods \ Page Splits/sec
Buffer Manager \ Page lookups/sec
Locks (*) \ Lock Wait Time (ms)
Locks(*) \ Lock Waits/sec
Locks(*) \ Number of Deadlocks/sec
Wait Statistics
CXPACKET
IO_COMPLETION
LCK
M
*
PAGEIOLATCH_*
Buffer Allocation
Schema Discovery
Identify Heaps
Duplicate Indexes
Overlapping Indexes
Unindexed Foreign Keys
Database Engine Tuning Advisor
Unused Indexes
Index Plan Usage
4. Implement
Communication
Impact Analysis
Status Report
Deployment Scripts
Prepare Deployment And Rollback of Schema
Save Index Changes to Source Code Repository
Peer Review with Impact Analysis
Execution
5. Repeat
6. Index Maintenance
1. Index Fragmentation
Fragmentation operations
Insert Operations
Update Operations
Delete Operations
Shrink operations
Fragmentation issues
Index I/O
Contigous Reads
Defragmentation options
Index Rebuild
Index Reorganization
Drop And Create
Defragmentation strategies
Maintenence plans
Reorganize index Task
Rebuild index task
T-SQL scripts
Preventing Fragmentation
Fill Factor
Data typing
Default Values
2. Index Statistics Maintenance
Automatically maintaining statistics
Automatic creation
automatic updating
Preventing auto update
Manually maintaining statistics
Maintenence plans
T-SQL Scripts
Stored procedure
DDL Command
