Fstachecki: /* Non-Clustered Indexes ⌘ */

2022-11-14T10:15:39Z

Non-Clustered Indexes ⌘

New page

[[Category:PL]]
[[Category:SQL]]
<slideshow style="nobleprog" headingmark="⌘" incmark="…" scaled="false" font="Trebuchet MS" >
#footer-places { display: none; }
</slideshow>

==Agenda ⌘==
* SQL Server Usługi, Wersje, Edycje
* Management Studio
* Podstawowe polecenia T-SQL
* Przeglad właściwości bazy danych
* Stored Procedures, Views, Triggers
* Indeksy
* Log transakcyjny
* Recovery Models

==Agenda ⌘==
* Backup and Restore
* Praca z bazą danych( copy, shrink...)
* Jobs & Maintenance Plans
* Bezpieczeństwo
* Upgrade
* Monitoring

==Podstawowe wiadomości o bazach danych⌘==
 

* Bazy plikowe np. MS Access
* Bazy Client/Server
* Systemy zarządzania relacyjną bazą danych (RDBMS) :
# DB2
# Microsoft SQL Server
# MySQL
# Oracle
# PostgreSQL

==Edycje SQL Serwer⌘==
 

* Enterprise
* Standard
* Web
* Express
* Developer

 https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-2017

==Wersje SQL Serwer ⌘==
 

* Select @@version

* SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel')

* Configuration Manager

 https://sqlserverbuilds.blogspot.com/ 

==Narzędzia SQL Server⌘==
 

* SQL Server Management Studio
* Configuration Manager
* Visual Studio
* SQL Server Profiler
* Database Tuning Advisor

==Usługi SQL Server ⌘==
* Database Services
* Analysis Services
* Reporting Services
* Intergration Services

=== Database Services ⌘===

W skład SQL Server Database Services wchodzi:
# Data files
# Replication
# Full-Text search

=== Analysis Services ⌘===

Inaczej "Hurtownie Danych"

Zastosowanie:
# Przetwarzanie analityczne danych
# Wspomaganie decyzji
# Centralizacja danych
# Archiwizacja

=== Reporting Services ⌘===
 
Zastosowanie:
# Tworzenie
# Zarządzanie
# Przedstawianie raportów
# Report Manager

=== Intergration Services ⌘===
 

1. Zbiór narzędzi do zarządzania zbiorami w procesie ETL
ETL - Extract, Transform , Load ( wydobywanie, przetwarzanie, ładowanie )
2. Wykonywanie innych zadań

== Konta Serwisowe dla SQL Servera ⌘==

 
* '''"NT AUTHORITY\LOCAL SERVICE"'''
:Ten sam poziom uprawnień co członkowie grupy Users
:Konto usługi lokalnej nie jest przeznaczone dla SQL Server czy SQL Server Agent
 
* '''"NT AUTHORITY\NETWORK SERVICE"'''
:Wbudowana usługa, ma większy dostęp do zasobów i obiektów

== Konta Serwisowe dla SQL Servera cz.II ⌘==
 
* '''"NT AUTHORITY\SYSTEM"'''
:Wysoki poziom uprawnień w systemie lokalnym
 
* '''"Konto Domenowe"'''
:współdziałanie z usługami sieciowymi,
:dostep do zasobów domeny
:file-share
:połączenie z innymi serwerami SQL

== Protokoly Sieciowe SQL Serwera ⌘==
 
* Microsoft SQL Server może obsługiwać żądania od kilku protokołów, w tym samym czasie.
* Konfiguracja protokołów - SQL Server Configuration Manager

 
'''Rodzaje protokołów:'''
* Shared Memory
* TCP/IP
* Named Pipes
* Każdorazowa zmiana ustawień protokołu wymaga restartu usługi SQL Serwer.

== Bazy systemowe ⌘==
 
* master
* model
* msdb
* tempdb
 

* distribution*
* resource*
=== Master ⌘===

* najważniejsza baza
* zapisywana jest w niej konfiguracja SQL Servera – informacje o:
:obiektach systemowych
:loginach
:lokalizacji poszczególnych plików
:podpiętych bazach użytkownika

=== Model ⌘===

* Baza wzorcowa dla innych baz danych
* każda nowa baza danych jest kopią bazy model
* możliwa modyfikacja, która wpłynie na tworzone w przyszłości bazy

=== Msdb ⌘===

* Baza pomocnicza wykorzystywana przez SQL Server Agenta
* Zawiera informacje o:
:job'ach
:backup'ach
:historię wykonywanych jobów, backup'ów i restore'ów

=== Tempdb ⌘===
 

* Baza tymczasowa
* Tworzona jest na nowo po kazdym restarcie uslugi
* Zawiera obiekty tymczasowe:
:tabele
:kursory
:rozne obiekty tworzone wewnetrznie przez SQLa
 

* Oddzielny szybki dysk
* Utworzenie tylu plikow ile jest procesorow

=== Distribution* ⌘===
 

* Wytwarzana jeżeli na Serwerze jest replikacja
* Zawiera metadata i dane historyczne dotyczące replikacji

=== Resource* ⌘===
 

* Baza w trybie READ-ONLY
* Zawiera systemowe obiekty
* Brak backup'u

==Tworzenie bazy danych==
 
=== Pliki bazy danych i ich lokalizacja⌘===
 

* .mdf
* .ndf
* .ldf
* FileGroup
* Fizyczne i logiczne pliki baz danych

=== Rozmiar plików oraz ich przyrost ⌘===
 

* rozmiar początkowy
* baza danych Model
* autogrowth

=== Partycjonowanie tabel ⌘===
 

* Partycjonowanie dostępne tylko w edycjach Enterprise i Developer SQL Server (oraz w wersjach ewaluacyjnych)
* Zwiększenie szybkości dostępu do danych
* Łatwość zarządzania
* Najczęściej wykorzystywane przy podziale danych wg "wieku"
* Wymagane uprawnienia:  sysadmin , db_owner i db_ddladmin.

==Język modyfikacji danych (DML)⌘==
 

<big>Data Manipulation Language</big> 

* SELECT - wylistowuje wiersze
* INSERT - wstawia nowy wiersz
* UPDATE - zmienia istniejący wiersz
* DELETE  -  usuwa wiersz z repozytorium

*ćwiczenie

==Język definicji danych (DDL)⌘==
 

<big>Data Definition Language</big>
 

* CREATE - tworzenie nowego obiektu
* ALTER   -  wstawianie obiektu do istniejącego systemu
* DROP - usuwanie istniejącego obiektu

*ćwiczenie

==Projektowanie tabel⌘==
 

* Kolumny i atrybuty
* Określenie typu danych
:- Tekstowe (char, varchar, nchar, ntext, nvarchar)
:- Liczbowe (int, smallint, bigint, tinyint, float, real, decimal, numeric)
:- Daty i czasu (datetime, smalldatetime)
:- Binarne (binary, varbinary)
:- Waluty (money, smallmoney )
:- Specjalne (text, image, xml, bit)
* Zmiana własności kolumn

== Indeksy ⌘==
* Indeks w bazie danych to rodzaj struktury ściśle związanej z tabelą lub widokiem, która pomaga w znaczny sposób przyspieszyć pobieranie danych z tych źródeł.
* Indeks zawiera klucze zawierające jedną, bądź kilka połączonych kolumn tabeli lub widoku.
* Klucze w indeksie przechowywane są w strukturze zwanej B-drzewem

=== Indeksy ⌘===

Indeksy
:* Clustered
:* Non-Clustered
* Klucze obce
:* Klucz główny - Primary Key
:* Klucz obcy - Foreign Key
* Dla skuteczności indeksu najistotniejsze są następujące kwestie:
* - wysoka selektywność indeksu
* - aktualność statystyk
* - niski poziom fragmentacji

=== Indeksy ⌘===
Klucz główny - Primary Key, 
:Każdy wiersz w naszej tabeli musi mieć wartość która go unikalnie zidentyfikuje. 
:Aby zakwalifikować dany atrybut jako klucz główny, musimy sprawdzić, czy posiada następujące własności:
:* musi posiadać wartość dla każdego z wierszy
:* dla każdego z tych wierszy wartość musi być unikalna
:* wartość ta nie może się zmienić, ani nie może zostać usunięta podczas całego funkcjonowania wiersza w tabeli
 
=== Indeksy ⌘===
Klucz obcy - Foreign Key, 
:Klucz obcy jest kopią klucza głównego z innej tabeli.
:Asocjacja jest utworzona pomiędzy tabelami poprzez zaznaczenie, iż wartość z jednej tabeli,
:w której jest kluczem obcym, jest powiązana z wartością z innej tabeli, gdzie jest kluczem głównym

=== Clustered Indexes ⌘===
 

* Jeden index klastrowany na tabeli
* Najczesciej zakladany na kolumnie o wysokiej "selektywnosci"
* Klucz główny – klastrowany index

=== Non-Clustered Indexes ⌘===
 
* Do 999 indexów nie-klastrowanych na tabeli
* Moga byc oparte na indexach klastrowanych.

=== REBUILD vs. REORGANIZE ⌘===
 

* REBUILD usuwa i tworzy indeksy od nowa,
* REORGANIZE tylko sortuje strony, nie usuwa ich,
* zarówno REBUILD jak i REORGANZIE zmniejsza fragmentację niezależnie od tego ile ona wynosi 

(kolumna AVG_FRAGMENTATION_IN_PERCENT w widoku sys.dm_db_index_physical_stat)

Fragmentacja: 
* 0 - 10% - nic
* 11 - 30% - REORGANIZE
* 31 - 100% - REBUILD

=== REBUILD vs. REORGANIZE- skrypt ⌘===
 

SELECT DB_NAME(PS.database_id) AS dbName,
S.name AS SchemaName,
O.name AS TableName,
b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
INNER JOIN sys.objects O ON PS.object_id = O.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
AND PS.index_type_desc IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
AND b.is_hypothetical = 0 -- Only real indexes
AND O.type_desc = 'USER_TABLE' -- Restrict to user tables
AND PS.page_count > 8 --- ignore tables less than 64K
ORDER BY ps.avg_fragmentation_in_percent,name DESC

==Programowanie po stronie serwera⌘==
 

* Tworzenie procedur składowanych ( stored procedures )
* Widoki ( views )
* Funkcje
* Wyzwalacze ( triggers )
=== Procedury ⌘===

* Zbior instrukcji języka Transact-SQL zapisanych pod wspólną nazwą i wywoływanych jak pojedyncza instrukcja.
* Nowe procedury moga wytwarzac osoby posiadajace takie prawa:
:sysadmin
:db_owner
:db_ ddladmin
* Są najczęściej wykorzystywanym przez programistów baz danych typem obiektów.
* Do czego sie wykorzystuje procedury skladowane ?
 

*demo

=== Widoki ⌘===
 

* Jest to zdefiniowane i zapisane zapytanie po stronie serwera, ktorego wynik moze byc wielokrotnie wykonywany.
* Uproszczenie prezentacji danych dla uzytkownikow
* Ograniczneie dostepu do tabel
* Ukrycie struktury tabel

*demo

=== Wyzwalacze ⌘===
 

Z ang. "Triggers"
* Wyzwalacze są specjalnym typem procedur składowanych powiązanych z wybranymi tabelami
:i wywoływanych wykonaniem ( zarówno po - AFTER , jak i zamiast - INSTEAD OF )
:instrukcji języka Transact-SQL INSERT, UPDATE albo DELETE.
* Podstawowym zastosowaniem wyzwalaczy jest wymuszenie integralności danych, zwłaszcza ich zgodności z regułami logiki biznesowej
 

*demo

=== Funkcje ⌘===
 
* Funkcja nie tylko wykonuje pewne operacje, ale także zwraca obliczony na podstawie przekazanych parametrów wynik.
* Zwracane mogą być zarówno dane skalarne, jak i zbiory danych.

== Modele odtwarzania ⌘==
 

<big>
Z ang."Recovery Model"</big>
 

* Simple
* Full
* Bulk logged

== Modele odtwarzania ⌘==
[[File:recoverymodel.jpg]]

=Kopie bezpieczeństwa ⌘=
 
* Full
* Differential
* Transaction log

=== Pełna kopia bazy danych ⌘===
Full Database Backup 

BACKUP DATABASE master TO
DISK = 'D:\Program Files\Microsoft SQL Server\Mssql\Backup\master.bak'

Zawiera wszystkie informacje zapisane zarówno w plikach bazy danych (strukturę obiektów bazodanowych oraz dane tabel i indeksów), 

jak i w plikach dziennika transakcyjnego - tu tylko dane z aktywnej części dziennika.

=== Pełna kopia bazy danych ⌘===
* Pełna nazwa, jak sama nazwa wskazuje, tworzy kopię zapasową wszystkiego.
* To podstawa każdego rodzaju kopii zapasowej!
* Jest to kompletna kopia, która przechowuje wszystkie obiekty bazy danych: tabele, procedury, funkcje, widoki, indeksyo raz DANE
* Mając pełną kopię zapasową, będziesz mógł łatwo przywrócić bazę danych w dokładnie takiej samej formie, jak w momencie tworzenia kopii zapasowej.

=== Przyrostowa kopia bazy danych ⌘===
Differential Database Backup 

BACKUP DATABASE Northwind TO
disk='D:\Program Files\Microsoft SQL Server\Mssql\Backup\Northwind_diff.BAK' WITH DIFFERENTIAL

Do pliku przyrostowej kopii bazy danych zostaną zapisane wszystkie dane, 
które zostały zmodyfikowane od czasu wykonania ostatniej pełnej kopii bazy danych.

=== Kopia dziennika transakcyjnego ⌘===
Transaction Log Backup 

BACKUP LOG Northwind TO
disk='D:\Program Files\Microsoft SQL Server\Mssql\Backup\Northwind_log.TRN'

SQL Server do pliku kopii dziennika transakcyjnego zapisuje wszystkie wpisy z dziennika danej bazy danych. 
Plik kopii dziennika transakcyjnego jest bezużyteczny bez aktualnego pliku pełnej kopii tej bazy danych.

*ćwiczenie

= Strategie kopii zapasowych ⌘=
 
* Pełna kopia
* Pełna kopia + kopia dziennika transakcyjnego
* Przyrostowa kopia
* Kopia plików

=== Strategia pełnych kopii bazy danych ⌘===
 

Wykorzystywana w przypadku niewielkich lub rzadko modyfikowanych baz danych.
 

Zalety:
:Mała liczba backupow
:Krótki proces odtwarzania bazy
 

Wady:
:Ryzyko utraty danych od ostatniego pełnego backupu
:Częste wykonywanie kopii bazy

=== Strategia pełnych kopii i dziennika transakcyjnego ⌘===
 
Strategia zalecana w przypadku niewielkich i często modyfikowanych baz danych.
 

Zalety:
:Możliwość odtworzenia danych z dowolnego momentu sprzed awarii.
:Prosty proces odtwarzania.
:Mniejsza liczba potrzebnych do przechowywania kopii nośników — Ostatnia pełna kopia bazy danych i codzienne kopie dziennika transakcyjnego.

Wady:
:Częste (co najmniej codziennego) wykonywanie pełnej kopii.
:Dłuższy czas odtwarzania bazy z powodu wiekszej lpiczby kopii dziennika transakcyjnego.

=== Strategia przyrostowych kopii bazy danych ⌘===
 
Strategia zalecana w przypadku średniej wielkości baz danych.
 

Zalety:
:Krótszy czas wykonania kopii zapasowych.
:Skrócenie, w porównaniu do poprzedniej strategii, czasu potrzebnego na odtworzenie bazy danych.
:Możliwośc powortu do dowolengo pounktu w czasie.

=== Strategia przyrostowych kopii bazy danych ⌘===
Wady:
:Trudniejszy proces odtwarzania bazy danych.
:Konieczność przechowywania:
:* plików pełnej kopii
:* przyrostowej kopii zapasowej
:* wszystkich kopii dziennika transakcyjnego od czasu ostatniej przyrostowej kopii bazy

===Strategia kopii plików bazy danych ⌘===
 
Strategia zalecana w przypadku średniej wielkości baz danych.
 

Zaley:
:Skrócenie czasu potrzebnego na wykonanie kopii zapasowych.
:Ograniczenie liczby potrzebnych do przechowywania kopii nośników.

Wady:
:Skomplikowany proces odtwarzania bazy danych.
 
''*cwiczenia''

= Odtwarzanie Bazy Danych ⌘=
[[File:Restore.JPG]]

= Odtwarzanie Bazy Danych ⌘=
[[File:Restore bazy.JPG]]

= "Shrinking" ⌘=
* DBCC SHRINKDATABASE
* DBCC SHRINKFILE
* Po pierwsze - NIE RÓB TO !!!!
* "Shrink" powinno być rzadką operacją i nie powinno być częścią regularnej konserwacji, którą wykonujesz. Zmniejszanie plików danych powinno być wykonywane jeszcze rzadziej, jeśli w ogóle. Nie jest zalecane włączanie automatycznego zmniejszania bazy danych. 

*cwiczenie

= Kopiowanie Bazy Danych ⌘=
 

* Backup / Restore
* Detach / Attach
* Kreator

*cwiczenie

=Automatyzacja⌘=
 

* SQL Agent
* Wytwarzanie i planowanie zadań automatycznych - SQL Jobs
* Powiadamianie o wynikach zadań

*ćwiczenie

==Maintenance Plan ⌘==
 
* Tworzenie i edycja
* Troubleshooting
* Sprawdzanie spójności bazy - DBCC CHECKDB
* Statystyki
* Sprawdzenie wskaźników indeksów -  skrypt Rebuild vs Reorganize

*ćwiczenie

=Podstawy bezpieczeństwa i administracji SQL Server⌘=
 

* Tworzenie kont: Active Directory , SQL Login
* Schematy
* Tworzenie ról
* Konto SA
* Role Serwerowe
* Role bazodanowe

== Konto SA ⌘==

* Konto „sa” jest dobrze znanym kontem SQL Server i służy jako domyślne konto administracyjne do SQL Server.
* Hasło SA jest używane tylko podczas instalacji i migracji.
* Wg zaleceń ( best practise ) nazwa powinna być zmieniona ponieważ to konto jest często "atakowane".

== Role Serwerowe ⌘==
* bulkadmin -> może używać polecenia BULK INSERT.
* dbcreator -> może tworzyć, zmieniać, usuwać i przywracać dowolną bazę danych.
* diskadmin -> rola służy do zarządzania plikami dyskowymi
* processadmin -> może zakończyć procesy uruchomione w instancji SQL Server.
* securityadmin -> zarządza loginami i ich właściwościami, może resetować hasło.
* serveradmin -> może zmienić konfigurację SQL serwera jak i zatrzymać usługę.
* setupadmin -> może dodawać i usuwać połączone serwery za pomocą instrukcji T-SQL
* sysadmin -> może wykonywać dowolne czynności na serwerze.
* public -> Każde logoin SQL Server należy do roli public.
 

== Role bazodanowe ⌘==
W przeciwieństwie do ról serwerowych,
możliwe jest tworzenie własnych ról bazodanowych.
 

* db_accessadmin -> może dodawać lub usuwać dostęp do bazy danych dla loginów Windows, grup Windows i loginów SQL Server
* db_backupoperator -> może wykonać kopię zapasową bazy danych.
* db_datareader -> może odczytać wszystkie dane ze wszystkich tabel użytkowników.
* db_ddladmin -> może uruchomić dowolną komendę DDL (Data Definition Language) w bazie danych.
* db_datawriter -> może dodawać, usuwać lub zmieniać dane we wszystkich tabelach użytkowników.

== Role bazodanowe ⌘==
 
* db_denydatareader -> nie może odczytać żadnych danych w tabelach użytkowników w bazie danych.
* db_denydatawriter -> nie może dodawać, modyfikować ani usuwać żadnych danych w tabelach użytkowników w bazie danych.
* db_owner -> może wykonywać wszystkie czynności związane z konfiguracją i konserwacją bazy danych, a także usuwać bazę danych
* db_securityadmin -> może modyfikować członkostwo w rolach i zarządzać uprawnieniami
* public -> użytkownik dziedziczy uprawnienia przyznane publicznie na danym obiekcie. Użytkownicy bazy danych nie mogą zostać usunięci z roli publicznej

== Schematy ⌘==
 
* Obiekty, które zawierają obiekty innych typów ( np.tabele,procedury)
* Uproszczenie czynności administracyjnych
* Wlaściciel Schematu
* Transfer uprawnień

*security - ćwiczenie

=Monitorowanie aktywności SQL Server ⌘=
* Activity Monitor
* DMVs
* Performance counters + PAL
* Extended events
* Performance Dashboard Reports
* Data Collector
 http://www.sqlcoffee.com/SQLServer2008_0009.htm
* SQL Server Profiler

= Upgrade ⌘=
 
* 'InPlace' vs 'Side by side' ( migracja )
* Service-Pack
* Cluster

= TEST :) ⌘=

=Best Practices⌘=
 

* Oddzielne dyski dla baz systemowych, aplikacyjnych i TEMP DB
* Regularne kopie bezpieczeństwa
* Ograniczony dostep do Serwera baz danych
* Monitorowanie przyrostu baz
* "Porządek" w tworzeniu procedur, widkow, tabel itd
* Procedury systemowe w administracji
* Dokumentacja
* "każdy ma własne..."

=Linki⌘=
 
* www.youtube.com
* www.sqlservercentral.com
* www.udemy.com
 

=Ankieta i Certyfikaty⌘=

Administracja baza danych Microsoft SQL Server - Revision history

Fstachecki: /* Non-Clustered Indexes ⌘ */