MS SQL Server – Kompendium ABD

Rozdział 01

Instalacja, architektura i zarządzanie plikami

Instancje, usługi, bazy systemowe, pliki, Filegroups, strony dyskowe i narzędzia administracyjne.

Spis treści

Typy instancji Kluczowe usługi Bazy systemowe Architektura plików Strony i Extenty Filegroups Shrink Narzędzia administracyjne

Typy instancji

Domyślna

Zależna od nazwy komputera, jedna na serwer. Łączenie: nazwa_komputera

Nazwana

Niezależne usługi i pliki, wiele na serwer. Łączenie: nazwa_komputera\nazwa_instancji

Uwierzytelnianie Windows

Konta domenowe/lokalne – silnik ufa systemowi, brak hasła

Mixed Mode

Windows + wewnętrzne konta serwera (SQL Server Authentication)

Express (darmowa)

Limit: 4 rdzenie, 1 socket, brak Agent. Do nauki.

Standard (płatna)

Najczęstsza edycja komercyjna

Enterprise

Pełnia możliwości, brak limitów sprzętowych

Developer (darmowa)

= Enterprise, ale zakaz użytku komercyjnego

Kluczowe usługi

MSSQLServer

Główny silnik bazy danych – bez niego nikt się nie połączy

SQLServer Agent

Harmonogramowanie zadań (Joby, Maintenance Plans). Brak w Express!

SSRS

Usługi raportowe

SSAS

Usługi analityczne

DTC

Distributed Transaction Coordinator – transakcje rozproszone

Bazy systemowe

Baza	Rola	Backup?
Master	Metadane, konta logowania, konfiguracja instancji	Tak – po każdej zmianie loginów/konfiguracji
Model	Szablon dla każdej nowo tworzonej bazy (CREATE DATABASE klonuje Model)	Opcjonalnie – jeśli personalizujesz
Tempdb	Obiekty tymczasowe, tabele robocze, bufor sortowania	Nigdy! – odtwarza się od zera przy starcie
Msdb	Joby, harmonogramy, historia backupów, alerty	Tak – jeśli masz automatyzację

Sztuczka z Model: Dodanie tabeli do bazy Model spowoduje, że każda nowo tworzona baza (CREATE DATABASE) odziedziczy tę tabelę. Na kolokwium: „każda nowa baza ma mieć tabelę emp" → po prostu utwórz tabelę emp w bazie Model.

Architektura plików

.mdf – plik podstawowy

Główny plik danych (jeden na bazę, obowiązkowy)

.ndf – pliki dodatkowe

Rozszerzenie danych, opcjonalne – przypisywane do Filegroups

.ldf – dziennik transakcji

Log wszystkich modyfikacji (WAL)

Zasada WAL (Write-Ahead Logging): dane najpierw trafiają do dziennika transakcji (.ldf), a dopiero stamtąd zapisywane są w pliku z danymi (.mdf). Gwarantuje spójność po awarii – po restarcie silnik czyta log i odtwarza lub wycofuje niedokończone transakcje.

Strony dyskowe (Pages) i Extenty

Strona (Page)

Jednostka 8 KB – najmniejszy blok I/O

Extent

8 stron = 64 KB – rezerwowany blok dyskowy

Limit rekordu

Jeden wiersz musi zmieścić się na jednej stronie (8 KB)

Ograniczenie 8 KB: Próba wstawienia danych przekraczających ~8060 bajtów w zwykłych typach (np. VARCHAR(10000)) zakończy się błędem. Rozwiązanie: typy LOB – VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) – przechowują dane na osobnych stronach LOB.

Filegroups – logiczne grupy plików

MS SQL nie pozwala bezpośrednio wskazać ścieżki dyskowej dla tabeli. Zamiast tego stosuje się Filegroups – logiczne kontenery łączące jeden lub więcej plików .ndf na konkretnym dysku.

⬡ SQL (T-SQL)

-- 1. Dodanie Filegroup do bazy ALTER DATABASE [MojaBaza] ADD FILEGROUP [FG_Dane]; -- 2. Dodanie pliku do Filegroup ALTER DATABASE [MojaBaza] ADD FILE ( NAME = 'MojaBaza_Dane1', FILENAME = 'D:\Data\MojaBaza_Dane1.ndf', SIZE = 100MB, FILEGROWTH = 50MB ) TO FILEGROUP [FG_Dane]; -- 3. Tworzenie tabeli w Filegroup CREATE TABLE dbo.Zamowienia ( Id INT PRIMARY KEY, DataZam DATE, Kwota DECIMAL(10,2) ) ON [FG_Dane];

◈ SSMS (interfejs graficzny)

Prawy klik na bazę → Properties
Zakładka Filegroups → Add Filegroup → podaj nazwę (np. FG_Dane)
Zakładka Files → Add → wybierz Filegroup, wskaż ścieżkę dyskową i nazwę pliku .ndf
Przy tworzeniu tabeli (New Table) → w Properties tabeli → ustaw „Filegroup" na FG_Dane
Domyślna grupa: PRIMARY – tam ląduje tabela, jeśli nie podano ON

TEXT/Image Filegroup: Duże obiekty LOB (XML, VARBINARY(MAX)) można wypchnąć na oddzielny, wolniejszy dysk, by nie obciążać głównych plików z danymi.

Shrink – zmniejszanie plików bazy

Po usunięciu milionów rekordów plik bazy nie zmniejsza się automatycznie na dysku. Wolna przestrzeń zostaje „wewnątrz" pliku. Shrink odzyskuje ją dla systemu.

⬡ SQL (T-SQL)

-- Zmniejsz całą bazę DBCC SHRINKDATABASE ('MojaBaza'); -- Zmniejsz konkretny plik DBCC SHRINKFILE ('MojaBaza_Data', 200); -- Sprawdź rozmiar bazy EXEC sp_spaceused; EXEC sp_helpdb 'MojaBaza';

◈ SSMS (interfejs graficzny)

Prawy klik na bazę → Tasks → Shrink → Database / Files
Wybierz typ pliku (Data / Log)
Ustaw docelowy rozmiar
Kliknij OK

Nie nadużywaj Shrink! Częste kurczenie + rozrastanie pliku powoduje zabójczą fragmentację dyskową, rujnując wydajność I/O. Wykonuj tylko w oknach serwisowych i w wyjątkowych sytuacjach.

Narzędzia administracyjne

SSMS

SQL Server Management Studio – główny graficzny interfejs administratora (instalowany osobno)

Configuration Manager

Włączanie usług, konfiguracja protokołów sieciowych (TCP/IP)

Database Engine Tuning Advisor

Automatyczne projektowanie indeksów na podstawie trace'a

SQL Server Profiler

Przechwytywanie i debugowanie zapytań (wykrywa problemy ORM!)

Pułapki SSMS: Otwarte zakładki projektowe (Design) cicho blokują serwer (Locks). Zmiany w jednej zakładce nie pojawią się w innej dopóki nie zapiszesz i nie klikniesz Refresh. Przy DROP DATABASE – włącz „Close Existing Connections" by wyrzucić podpięte sesje.

Rozdział 02

Backup & Restore – kopie zapasowe i odtwarzanie

Strategie backupowe, modele odzyskiwania, typy kopii, mechanika odtwarzania i pułapki SSMS.

Spis treści

Typy kopii Modele odzyskiwania Wykonanie backupu INIT vs NOINIT Odtwarzanie bazy WITH MOVE Backup baz systemowych Awaryjny backup logu

Typy kopii zapasowych

MS SQL wspiera Hot Backup – backup wykonuje się w trakcie normalnej pracy użytkowników. Silnik używa logów transakcyjnych do wyrównania stanu, gwarantując spójność. Backup zawiera wszystko: dane, triggery, procedury, uprawnienia i użytkowników.

Typ	Opis	Charakter	Kiedy stosować
Full (pełna)	Kompletna kopia całej bazy	Punkt bazowy	Podstawa każdej strategii – bez niej nic nie zadziała
Differential (różnicowa)	Zmiany od ostatniej kopii pełnej (na poziomie stron 8KB)	Różnicowa (nie przyrostowa!)	Codziennie; środowa Diff zawiera pon+wt+śr razem
Log (dziennik transakcji)	Zapis logiki zmian między kolejnymi backupami logu	Przyrostowa (incremental)	Co kilka minut – czyści plik .ldf!

Kopie dziennika transakcji są kluczowe – czyszczą log (zapobiegając niebotycznemu rozrostowi .ldf) i umożliwiają odtworzenie do dowolnego punktu w czasie (STOPAT). Przechowywać na zewnętrznych nośnikach!

Modele odzyskiwania (Recovery Model)

Model	Zachowanie logu	Backup logu?	Point-in-time restore
Full	Pełne logowanie – log rośnie aż do backupu	Tak (wymagany!)	Tak – do mikrosekundy
Bulk Logged	Jak Full, ale nie loguje masowych operacji	Tak	Częściowe (nie dla bulk ops)
Simple	Automatyczne czyszczenie logu po commit	Nie	Nie – brak historii modyfikacji

Simple = projekty studenckie/deweloperskie. Full = systemy produkcyjne (obowiązkowy!).

Wykonanie backupu

⬡ SQL (T-SQL)

-- Kopia pełna BACKUP DATABASE [MojaBaza] TO DISK = 'D:\Backup\MojaBaza_Full.bak' WITH COMPRESSION, STATS = 10; -- Kopia różnicowa BACKUP DATABASE [MojaBaza] TO DISK = 'D:\Backup\MojaBaza_Diff.bak' WITH DIFFERENTIAL, COMPRESSION; -- Kopia dziennika transakcji BACKUP LOG [MojaBaza] TO DISK = 'D:\Backup\MojaBaza_Log.trn' WITH COMPRESSION;

◈ SSMS (interfejs graficzny)

Prawy klik na bazę → Tasks → Back Up…
Wybierz typ: Full / Differential / Transaction Log
Destination → Add → wskaż ścieżkę pliku .bak / .trn
UWAGA: Usuń domyślną ścieżkę! Dwa cele = podział backupu na fragmenty!
Options → sprawdź Verify backup / Compression
Kliknij OK

INIT vs NOINIT & przeglądanie pliku

WITH INIT

Nadpisuje plik – kasuje starą zawartość

WITH NOINIT (domyślne)

Dopisuje kolejny backup na koniec pliku – buduje historię

RESTORE HEADERONLY

Wyświetla listę wszystkich kopii w pliku .bak (daty, typy)

⬡ SQL (T-SQL)

-- Podgląd zawartości pliku backupowego RESTORE HEADERONLY FROM DISK = 'D:\Backup\MojaBaza.bak'; -- Podgląd plików logicznych w backupie RESTORE FILELISTONLY FROM DISK = 'D:\Backup\MojaBaza.bak';

◈ SSMS (interfejs graficzny)

Prawy klik na Databases → Restore Database…
Source: Device → „…" → Add → wybierz plik .bak
Kliknij przycisk Contents → widzisz listę kopii w pliku
Wybierz konkretną kopię z listy

Odtwarzanie bazy danych

Prawidłowa kolejność: najnowsza kopia pełna → najnowsza kopia różnicowa (jedna!) → chronologicznie KAŻDY log po różnicowej. Pominięcie jednego logu = przerwanie łańcucha LSN = nieodwracalny błąd!

⬡ SQL (T-SQL)

-- 1. Kopia pełna (nie udostępniaj jeszcze!) RESTORE DATABASE [MojaBaza] FROM DISK = 'D:\Backup\MojaBaza_Full.bak' WITH NORECOVERY, REPLACE; -- 2. Kopia różnicowa RESTORE DATABASE [MojaBaza] FROM DISK = 'D:\Backup\MojaBaza_Diff.bak' WITH NORECOVERY; -- 3. Kolejne logi transakcyjne RESTORE LOG [MojaBaza] FROM DISK = 'D:\Backup\MojaBaza_Log1.trn' WITH NORECOVERY; RESTORE LOG [MojaBaza] FROM DISK = 'D:\Backup\MojaBaza_Log2.trn' WITH NORECOVERY; -- 4. Ostatni krok – udostępnienie bazy RESTORE DATABASE [MojaBaza] WITH RECOVERY; -- Point-in-Time (opcjonalnie) RESTORE LOG [MojaBaza] FROM DISK = 'D:\Backup\MojaBaza_Log3.trn' WITH RECOVERY, STOPAT = '2024-05-01 14:30:00';

◈ SSMS (interfejs graficzny)

Prawy klik na Databases → Restore Database…
Source: Device (nie „Database"!) → dodaj kolejno pliki
SSMS ułoży je automatycznie w kolejności
Options → zaznacz Overwrite existing database (REPLACE)
Dla point-in-time: sekcja Timeline → ustaw konkretny czas
Kliknij OK – SSMS sam ustawi NORECOVERY na pośrednich

NORECOVERY vs RECOVERY: NORECOVERY = baza w stanie „Restoring..." (żółty dopisek) – nie jest dostępna, ale przyjmuje kolejne pliki. RECOVERY = udostępnia bazę, ale zamyka możliwość dogrania kolejnych logów! Użyj RECOVERY TYLKO na ostatnim pliku.

Na kolokwium: zawsze używaj „Device" w GUI – tryb „Database" działa tylko jeśli serwer pamięta historię. W prawdziwej awarii ta historia wyparuje.

WITH MOVE – relokacja plików

Jeśli odtwarzasz bazę na innym serwerze, oryginalne ścieżki (np. D:\Data\) mogą nie istnieć. Użyj WITH MOVE:

⬡ SQL (T-SQL)

-- Relokacja plików przy Restore RESTORE DATABASE [MojaBaza] FROM DISK = 'D:\Backup\MojaBaza.bak' WITH MOVE 'MojaBaza' TO 'C:\Data\MojaBaza.mdf', MOVE 'MojaBaza_Log' TO 'C:\Logs\MojaBaza.ldf', RECOVERY;

◈ SSMS (interfejs graficzny)

Restore Database → zakładka Files
Zaznacz „Relocate all files to folder"
Wskaż nową ścieżkę docelową dla .mdf i .ldf

Backup baz systemowych

Baza	Kiedy backupować	Specjalne wymagania
Master	Po zmianach loginów, konfiguracji serwera	Restore wymaga Single User Mode (flaga z wiersza poleceń)
MSDB	Po utworzeniu Jobów, alertów, planów	Standardowy restore
Model	Po personalizacji szablonu	Opcjonalnie
TempDB	NIGDY	Odtwarza się od zera przy każdym starcie

Awaryjny backup logu (Tail-Log Backup)

Scenariusz: Ktoś zniszczył dane o godz. 12:00. Baza jest uszkodzona, ale plik .ldf wciąż działa.

Natychmiast zrób awaryjny backup logu z uszkodzonej bazy (tail-log backup).

Odtwórz wczorajszą kopię Full z NORECOVERY.

Nałóż awaryjny backup logu z STOPAT = '12:00 minus 1 minuta'.

Baza wraca do stanu sprzed katastrofy – cała poranna praca ocalona.

Rozdział 03

Bezpieczeństwo i schematy

Spis treści

1. Login (instancja)

Uwierzytelnienie do serwera. Sam login NIE daje dostępu do baz – dostaniesz „permission denied"

2. User (baza danych)

Mapowanie loginu do bazy. Dopiero User może cokolwiek robić w konkretnej bazie

⬡ SQL (T-SQL)

-- Tworzenie loginu SQL CREATE LOGIN jan_kowalski WITH PASSWORD = 'Haslo!123'; -- Tworzenie loginu Windows CREATE LOGIN [KOMPUTER\jan_kowalski] FROM WINDOWS; -- Tworzenie użytkownika w bazie USE [MojaBaza]; CREATE USER jan_kowalski FOR LOGIN jan_kowalski; -- Nadawanie uprawnień DML GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Tabela TO jan_kowalski; -- Zakaz (nadrzędny!) DENY DELETE ON dbo.Tabela TO jan_kowalski; -- Dodanie do roli wbudowanej ALTER ROLE db_datareader ADD MEMBER jan_kowalski;

◈ SSMS (interfejs graficzny)

Security → Logins → New Login…
Podaj nazwę, typ: SQL / Windows, hasło
Zakładka User Mapping → zaznacz bazę → przypisz rolę
Kliknij OK
W bazie: Security → Users → Properties
Zakładka Securables → Search → dodaj obiekt (tabelę)
W tabeli zaznacz GRANT lub DENY przy każdym uprawnieniu

Konta specjalne

SA (System Administrator)

Wbudowane konto sysadmin (Mixed Mode). Znane atakującym – używaj z silnym hasłem lub zablokuj!

dbo (database owner)

Właściciel bazy – pełne prawa, ignoruje model bezpieczeństwa. Każdy sysadmin mapowany jako dbo.

guest

Wyjątek: Login BEZ User'a w bazie może wykonywać akcje, jeśli guest ma uprawnienia

Hierarchia uprawnień (3 poziomy)

Poziom	Przykłady uprawnień	Przypisywane do
Serwer	ALTER ANY LOGIN, ALTER ANY DATABASE, SHUTDOWN	Login
Baza danych	CREATE TABLE, BACKUP DATABASE, SELECT (na wszystkich tabelach)	User
Obiekt	SELECT, INSERT, UPDATE (nawet na kolumnie!), DELETE, EXECUTE	User

GRANT / DENY / REVOKE

GRANT

Nadaje prawo. Wariant WITH GRANT OPTION pozwala oddelegować uprawnienie dalej.

REVOKE

Zdejmuje GRANT – ale user może wciąż mieć dostęp z innej roli!

DENY

Bezwzględny zakaz – nadrzędny wobec GRANT z dowolnego źródła. Specyfika MS SQL!

DENY jest zawsze nadrzędne – unieważnia GRANTy z ról, schematów, wszystkiego. Ale DENY to „środek ratunkowy" – oparcie całej architektury na DENY to porażka administratora.

Role

Serwer

sysadmin diskadmin securityadmin dbcreator

Baza danych

db_owner db_datareader db_datawriter db_ddladmin db_securityadmin

public

Każdy user należy automatycznie – uprawnienie na public = uprawnienie dla WSZYSTKICH

Role aplikacyjne

Zabezpieczone hasłem, aktywowane proceduralnie z poziomu aplikacji – tymczasowe super-uprawnienia

Schematy

Schematy grupują obiekty (tabele, procedury) – jak „foldery" (ale zawsze płaskie, 1-poziomowe). Domyślny schemat to dbo.

⬡ SQL (T-SQL)

-- Tworzenie schematu CREATE SCHEMA logistyka; -- Tworzenie tabeli w schemacie CREATE TABLE logistyka.Wysylki ( Id INT PRIMARY KEY, Data DATE ); -- Uprawnienia na CAŁY schemat GRANT SELECT ON SCHEMA::logistyka TO jan_kowalski; -- Zmiana domyślnego schematu usera ALTER USER jan_kowalski WITH DEFAULT_SCHEMA = logistyka; -- Przeniesienie obiektu między schematami ALTER SCHEMA logistyka TRANSFER dbo.Zamowienia;

◈ SSMS (interfejs graficzny)

Prawy klik na bazę → Security → Schemas → New Schema…
Podaj nazwę (np. logistyka)
W Properties usera → zmień Default Schema
W Securables schematu → nadaj GRANT/DENY

Projektuj uprawnienia na warstwach: schemat → rola → użytkownik. Nigdy nie nadawaj uprawnień „ad hoc" do pojedynczych obiektów.

Przeniesienie obiektu (ALTER SCHEMA TRANSFER) kasuje wszystkie wcześniej nadane uprawnienia obiektowe bez ostrzeżenia!

Pułapki SSMS

Search ≠ Grant: Panel Search na górze okna Object Permissions tylko pokazuje listę – uprawnienia nadajesz w dolnej tabeli (Grant/Deny checkboxy).
Owner – nie dotykaj! Zmiana Owner/Schemas Owned By This Role rujnuje model zabezpieczeń – Owner ignoruje DENY i cały system uprawnień.

Rozdział 04

Automatyzacja, monitorowanie i integracja

Maintenance Plans, Jobs, monitorowanie, alerty, Full-Text Search, Database Mail, import/eksport danych.

Spis treści

Maintenance Plans Jobs Monitorowanie Alerty i operatorzy Full-Text Search Database Mail Import/Eksport

Maintenance Plans – planowane zadania konserwacji

Graficzne narzędzie do „wyklikania" nocnych zadań. Wymaga działającego SQL Server Agent (brak w Express!). Metadane w bazie MSDB.

⬡ SQL (T-SQL)

-- Maintenance Plan generuje pod spodem Joby -- Ale można je programować ręcznie: -- Sprawdzenie integralności DBCC CHECKDB ('MojaBaza') WITH NO_INFOMSGS; -- Aktualizacja statystyk EXEC sp_updatestats; -- Przebudowa indeksów ALTER INDEX ALL ON dbo.Tabela REBUILD; -- Reorganizacja (lżejsza) ALTER INDEX ALL ON dbo.Tabela REORGANIZE;

◈ SSMS (interfejs graficzny)

Management → Maintenance Plans → prawy klik → Maintenance Plan Wizard
Wizard: wybierz zadania (Backup, Rebuild Index, Check Integrity…)
Ustaw harmonogram (Schedule) – np. codziennie o 2:00
Zaawansowany edytor: prawy klik → Modify → edytor wizualny
Łącz zadania strzałkami: zielona (sukces) / czerwona (błąd)
Subplany – oddzielne harmonogramy w jednym planie
„Execute T-SQL Statement" – blok na własny skrypt

Jobs – zadania zaplanowane (niskopoziomowe)

Maintenance Plans tworzą pod spodem Joby. Joby wykraczają poza SQL – krok może być PowerShellem, komendą OS, pakietem SSIS.

⬡ SQL (T-SQL)

-- Tworzenie joba EXEC msdb.dbo.sp_add_job @job_name = 'NocnyBackup'; -- Dodanie kroku EXEC msdb.dbo.sp_add_jobstep @job_name = 'NocnyBackup', @step_name = 'BackupFull', @subsystem = 'TSQL', @command = 'BACKUP DATABASE [MojaBaza] TO DISK = ''D:\Backup\Full.bak'' WITH INIT, COMPRESSION'; -- Dodanie harmonogramu EXEC msdb.dbo.sp_add_schedule @schedule_name = 'Codziennie_2AM', @freq_type = 4, @active_start_time = 020000; EXEC msdb.dbo.sp_attach_schedule @job_name = 'NocnyBackup', @schedule_name = 'Codziennie_2AM'; -- Włączenie joba EXEC msdb.dbo.sp_add_jobserver @job_name = 'NocnyBackup';

◈ SSMS (interfejs graficzny)

SQL Server Agent → Jobs → New Job…
General: nazwa i właściciel
Steps → New: Type = T-SQL / PowerShell / CmdExec / SSIS
Schedules → New: częstotliwość (dziennie / co godzinę)
Notifications: e-mail do Operatora po sukcesie/błędzie
OK – zadanie uruchamia się wg harmonogramu

Złota zasada nauki: Wyklikaj obiekt w SSMS, ale PRZED zapisaniem kliknij prawym → „Script as → to New Query Window". Serwer wygeneruje wzorcowy skrypt – idealny materiał do nauki!

Monitorowanie serwera

Event Viewer (Windows)

Logi aplikacji → filtruj źródło MSSQL. Informacje o Jobach, backupach, błędach krytycznych.

SQL Server Profiler

Przechwytuje KAŻDE zapytanie w czasie rzeczywistym. Trace → zapisz do tabeli → filtruj SQLem.

Performance Monitor

Liczniki MSSQL (prefiksy MSSQL:*) – CPU, I/O, połączenia, blokady. Wykresy wydajnościowe.

Activity Monitor

Prawy klik na serwer → Activity Monitor. Procesy, blokady, oczekiwania.

Alerty i operatorzy

Zdefiniuj Operatora (SQL Agent → Operators) – rekord z adresem e-mail osoby z IT.

Utwórz Alert reagujący na: SQL Server Event (numer błędu / severity) lub Performance Condition (licznik przekraczający próg).

Alert uruchamia ratunkowy Job i/lub wysyła e-mail (Database Mail) do Operatora.

Przykład praktyczny: Alert na zajętość dysku > 90% → automatycznie uruchamia Job który obcina rozpuchnięty log transakcyjny.

Full-Text Search (FTS) – wyszukiwanie pełnotekstowe

Klauzula LIKE jest wolna w ogromnych tekstach. FTS indeksuje każde słowo, rozumie koniugację/deklinację i ignoruje spójniki.

⬡ SQL (T-SQL)

-- 1. Włączenie FTS w bazie EXEC sp_fulltext_database 'enable'; -- 2. Tworzenie katalogu pełnotekstowego CREATE FULLTEXT CATALOG MojKatalog AS DEFAULT; -- 3. Tworzenie indeksu FT (tabela musi mieć PK!) CREATE FULLTEXT INDEX ON dbo.Artykuly ( Tytul, Tresc ) KEY INDEX PK_Artykuly ON MojKatalog WITH CHANGE_TRACKING AUTO; -- Wyszukiwanie słowa SELECT * FROM dbo.Artykuly WHERE CONTAINS(Tresc, 'baza danych'); -- Wyszukiwanie całego zdania (kolejność nieważna) SELECT * FROM dbo.Artykuly WHERE FREETEXT(Tresc, 'optymalizacja zapytań SQL'); -- Ranking trafności SELECT a.*, ft.RANK FROM dbo.Artykuly a INNER JOIN FREETEXTTABLE(dbo.Artykuly, Tresc, 'indeksy wydajność') ft ON a.Id = ft.[KEY] ORDER BY ft.RANK DESC;

◈ SSMS (interfejs graficzny)

Storage → Full Text Catalogs → prawy klik → New Full-Text Catalog
Podaj nazwę katalogu
Prawy klik na tabelę → Full-Text Index → Define Full-Text Index
Wybierz kolumny do indeksowania
Ustaw harmonogram odświeżania katalogu (lub AUTO)
Wymaganie: tabela musi mieć unikalny indeks (PK)

Funkcja FTS	Opis	Zwraca
CONTAINS	Szuka dokładnego słowa/frazy w kolumnie	TRUE/FALSE (WHERE)
FREETEXT	Szuka słów z całego zdania (kolejność nieważna)	TRUE/FALSE (WHERE)
CONTAINSTABLE	Jak CONTAINS, ale z rankingiem trafności	Tabela (KEY, RANK)
FREETEXTTABLE	Jak FREETEXT, ale z rankingiem trafności	Tabela (KEY, RANK)

Database Mail

MS SQL nie ma serwera pocztowego. Trzeba skonfigurować profil SMTP (serwer zewnętrzny, port, login).

⬡ SQL (T-SQL)

-- Wysłanie e-maila (po konfiguracji profilu) EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MojProfil', @recipients = 'admin@firma.pl', @subject = 'Alert: Backup FAILED', @body = 'Backup bazy MojaBaza się nie powiódł!';

◈ SSMS (interfejs graficzny)

Management → Database Mail → prawy klik → Configure
Utwórz profil i konto SMTP (serwer, port, login)
Test: prawy klik → Send Test E-Mail
Użyj sp_send_dbmail w Triggerach, Jobach, procedurach

Darmowi dostawcy (Gmail) często odrzucają połączenia od silników bazodanowych jako spam!

Import / Eksport danych

SSIS (Integration Services)

Gigantyczne środowisko ETL – pakiety graficzne i skryptowe do wymiany danych między platformami

GUI Import/Export

SSMS → prawy klik na bazę → Tasks → Import/Export Data. Kreator, mapowanie formatów. Zapisz jako pakiet SSIS.

BCP (Bulk Copy Program)

Najszybsza opcja! Wiersz poleceń: bcp tabela out plik.txt. Bajty wprost do silnika, pomija parsowanie SQL.

BULK INSERT

Komenda T-SQL do masowego ładowania z pliku. Wielokrotnie szybsza niż pętlowy INSERT INTO.

Rozdział 05

Systemy rozproszone i wysoka dostępność

Log Shipping, Mirroring, replikacja, Linked Servers, DTC, Failover Clusters.

Spis treści

Log Shipping Mirroring Replikacja Linked Servers Transakcje rozproszone (DTC) Failover Clusters

Log Shipping

Periodyczne kopiowanie logów transakcyjnych na serwer zapasowy. Model Master-Slave z interwałowym opóźnieniem. 3 automatyczne Joby:

Backup Job

Robi kopię logu na serwerze głównym

Copy Job

Kopiuje plik logu przez udział sieciowy

Restore Job

Odtwarza log na serwerze zapasowym

Tryb serwera zapasowego	Zachowanie
Standby	Odczyt możliwy, ale wgranie nowego logu brutalnie odłącza czytających
No Recovery	Baza niedostępna (Restoring) – ciągłe dopisywanie danych

Awaryjne przełączenie: RESTORE DATABASE MojaBaza WITH RECOVERY – budzi zapasową bazę.

Database Mirroring

Przesyłanie transakcji natychmiast po sieci TCP/IP. Szybsze niż Log Shipping, ale wymagające sieciowo.

Tryb	Mechanizm	Gwarancja
Synchroniczny	Serwer główny czeka na potwierdzenie z Mirror przed COMMIT	100% brak strat, ale większe opóźnienie
Asynchroniczny	Serwer główny nie czeka – wysyła i commituje natychmiast	Ryzyko utraty mikrosekund danych „w drodze"

Principal

Serwer główny – obsługuje transakcje

Mirror

Lustrzana kopia – synchronizuje z Principal

Witness (opcjonalny)

Trzecia maszyna-arbiter: nasłuchuje, a gdy Principal padnie – automatyczny Failover → Mirror staje się nowym Principal

Replikacja

Publikator

Udostępnia artykuły (tabele, widoki, procedury)

Dystrybutor

Kolejkuje i rozsyła zmiany (push/pull)

Subskrybent

Odbiera dane

Artykuły mogą być filtrowane: ucinanie kolumn, filtrowanie wierszy (np. pracownicy z Gdańska nie widzą danych z Warszawy).

Typ	Mechanizm	Zastosowanie
Snapshot	Pełne zrzuty nadpisujące tabele	Małe bazy, rzadkie zmiany, nocne transfery
Transakcyjny	Ciągły ze logu, jednokierunkowy	OLTP, real-time (nie edytuj u subskrybenta!)
Peer-to-peer	Wiele węzłów naraz, niemal real-time	Load balancing (MS zaleca edycję w jednym węźle)
Scalający (Merge)	Obustronny, wielokierunkowy, UID + priorytety	Mobilne węzły, offline sync – najkosztowniejszy

Agenci Replikacji

LogReader Agent

Czyta dziennik (model transakcyjny)

Snapshot Agent

Robi zrzuty artykułów

Distribution Agent

Rozpycha/ściąga dane (push/pull)

Merge Agent

Rozwiązuje konflikty (Merge Replication)

Konflikty Merge: Dwa węzły edytują ten sam wiersz. Mechanizm dodaje UUID do rekordów. Domyślnie wygrywa serwer z wyższym priorytetem (Publikator = 100). Istnieją resolwery: średnia arytmetyczna, konkatenacja tekstów, ręczna decyzja administratora.

Linked Servers

Połączenie MS SQL z obcymi bazami (Oracle, DB2, PostgreSQL). Po skonfigurowaniu: poczwórna adresacja w T-SQL.

⬡ SQL (T-SQL)

-- Zapytanie do obcej bazy SELECT * FROM [NazwaLinkedServer].[BazaDanych].[schemat].[tabela]; -- JOIN z lokalną tabelą SELECT l.*, r.Nazwa FROM dbo.Zamowienia l JOIN [ORACLE_SRV].[HR].[dbo].[Employees] r ON l.PracownikId = r.Id; -- Wywołanie zdalnej procedury (RPC) EXEC [ORACLE_SRV].[BazaDanych].dbo.sp_Procedura;

◈ SSMS (interfejs graficzny)

Server Objects → Linked Servers → New Linked Server…
Podaj nazwę, typ dostawcy (SQL Server / Oracle / OLE DB)
Zakładka Security → mapowanie loginów (Remote Login + Password)
Po zapisaniu: rozwijasz Linked Server jak lokalną bazę

Transakcje rozproszone (DTC) – Two-Phase Commit

Gdy skrypt dotyka jednocześnie danych lokalnych i z Linked Server, użyj BEGIN DISTRIBUTED TRANSACTION. Usługa DTC koordynuje protokołem Two-Phase Commit (2PC):

Faza Prepare: DTC pyta wszystkie serwery: „Czy jesteście gotowi zacommitować?"

Faza Commit: Jeśli WSZYSCY potwierdzili → twardy COMMIT. Jeśli choć JEDEN nie odpowiedział → absolutny ROLLBACK na WSZYSTKICH serwerach, nawet tych które zadziałały prawidłowo. Gwarancja ACID na wielu maszynach.

SQL Server Failover Clusters

Konfiguracja na poziomie Windows Server. Kilka fizycznych serwerów widocznych jako jedna maszyna. Pełna odporność awaryjna + load balancing. MS SQL rezyduje na „farmie serwerowej".

High Availability ≠ Backup! Klastry, mirroring, replikacja nie chronią przed błędem ludzkim – fatalny UPDATE sklonuje się natychmiast na serwer zapasowy. ZAWSZE rób backupy!

Rozdział 06

Metodologia strojenia (Tuning)

Plany wykonania, pomiar IO/czasu, Profiler, Database Engine Tuning Advisor, statystyki kosztowe.

Spis treści

Plan wykonania Operacje w planie Statystyki i optymalizator Profiler + Tuning Advisor

Analiza planu wykonania zapytania

⬡ SQL (T-SQL)

-- Pomiar IO (odczyty logiczne i fizyczne) SET STATISTICS IO ON; GO SELECT * FROM dbo.Tabela WHERE KolumnaId = 100; GO SET STATISTICS IO OFF; -- Pomiar czasu SET STATISTICS TIME ON; GO SELECT * FROM dbo.Tabela WHERE KolumnaId = 100; GO SET STATISTICS TIME OFF; -- Aktualizacja statystyk UPDATE STATISTICS dbo.Tabela; UPDATE STATISTICS dbo.Tabela WITH FULLSCAN;

◈ SSMS (interfejs graficzny)

Wpisz zapytanie w edytorze
Query → Include Actual Execution Plan (Ctrl+M)
Uruchom → zakładka Execution Plan
Grube strzałki = duży transfer danych
Table Scan / Index Scan = brak indeksu
Index Seek = efektywne B+ drzewo
Prawy klik → Properties (koszt, wiersze)
Żółty trójkąt = sugestia brakującego indeksu

Estimated Subtree Cost (lewy górny róg grafu, węzeł SELECT) – jednorodna punktacja niezależna od buforowania. Używaj do porównań „przed" i „po" indeksie.

Kluczowe operacje w planie zapytania

Operacja	Znaczenie	Ocena
Table Scan	Pełny odczyt tabeli (heap)	Wolno
Index Scan	Pełny odczyt indeksu (poziome przejście liści)	Średnio (ok dla sortowania)
Index Seek	Nawigacja B+ drzewem od korzenia	Szybko
Key Lookup	Skok ze strony indeksu do strony danych	Do optymalizacji (elimiuj INCLUDEm)
Nested Loops	Pętla zagnieżdżona „każdy z każdym"	Wolno na dużych zbiorach
Merge Join	Łączenie posortowanych list	Szybko
Hash Match	Hashowanie do łączenia	Średnio – zależy od rozmiaru

Statystyki kosztowe i optymalizator

Cost-based optimizer wybiera strategię na podstawie statystyk rozkładu danych. Krytyczne ustawienia serwera:

Auto create statistics

= TRUE (zawsze!)

Auto update statistics

= TRUE (zawsze!)

UPDATE STATISTICS

Ręczna przebudowa – uruchamiaj Jobem w nocy/weekendy

Wyłączenie auto-statystyk = optymalizator podejmuje decyzje „w ciemno" i omija nawet doskonałe indeksy. Nigdy nie wyłączaj!

SQL Server Profiler + Database Engine Tuning Advisor

Uruchom Profiler (Tools → Profiler) – rejestruj ruch z reprezentatywnego okresu.

Zapisz Trace do pliku .trc lub tabeli.

Otwórz Tuning Advisor (Tools → DTA) – załaduj trace + wybierz bazę.

Start Analysis – DTA przeanalizuje i wygeneruje skrypty CREATE INDEX z szacunkiem zysku (np. 40% szybciej).

Zastosuj skrypty natychmiast lub zaplanuj na okno serwisowe.

Analizuj na bazie testowej (replice), by operacja kosztowa DTA nie obciążała produkcji!

Rozdział 07

Techniki optymalizacji

Dyski, indeksy B+ tree, Fill Factor, defragmentacja, T-SQL, transakcje, izolacja, blokady, MVCC.

Spis treści

Architektura dyskowa Indeksy B+ drzewo Tworzenie indeksów Fill Factor i defragmentacja Optymalizacja T-SQL ACID i transakcje Anomalie współbieżności Poziomy izolacji Blokady (Locks) SNAPSHOT / MVCC Indexed Views

Architektura dyskowa

Separacja IO

OS, .mdf i .ldf na osobnych dyskach – eliminuje rywalizację głowic

Tabele i indeksy

Odseparuj indeksy do osobnego pliku/dysku przy ciężkim obciążeniu

RAID 10 (0+1)

Zalecany – min. 4 dyski, wydajność + bezpieczeństwo

RAID 5

Alternatywa, mniej dysków ale wolniejszy zapis

RAID 0

Brak bezpieczeństwa – tylko dla danych tymczasowych!

Indeksy – architektura B+ drzewa

Typ	Charakterystyka	Kiedy stosować
Clustered	Sortuje fizycznie dane. Jeden na tabelę. Liście = rekordy. Domyślnie na PK.	BETWEEN, ORDER BY, GROUP BY (zakresy)
Non-clustered	Wskazanie do rekordów. Do 249 na tabelę. Liście = wskaźniki.	Równości (=), klucze obce, kolumny WHERE
Wielokolumnowy	Do 16 kolumn, klucz max 900 bajtów. Kolejność kolumn kluczowa! Musi być użyta 1. kolumna.	Filtrowanie po wielu kolumnach

Kiedy indeks NIE pomoże

Małe tabele (< 10 000 rekordów) – silnik i tak je szybko skanuje
Niska selektywność – milion wpisów, ale 50 unikalnych wartości → indeks bezużyteczny
Masowo pisane, rzadko czytane tabele – koszt utrzymania indeksów przy INSERT/UPDATE przewyższa zysk

Tworzenie i zarządzanie indeksami

⬡ SQL (T-SQL)

-- Indeks niepogrupowany z INCLUDE (covering) CREATE NONCLUSTERED INDEX IX_Tabela_Kol ON dbo.Tabela (KolumnaA, KolumnaB) INCLUDE (KolumnaC, KolumnaD) WITH (FILLFACTOR = 80); -- Indeks pogrupowany (NIE na PK!) CREATE CLUSTERED INDEX CX_Tabela ON dbo.Tabela (DataTransakcji); -- Computed column (zamiennik indeksu funkcyjnego) ALTER TABLE dbo.Tabela ADD NazwaLower AS LOWER(Nazwa) PERSISTED; CREATE INDEX IX_NazwaLower ON dbo.Tabela(NazwaLower); -- Reorganizacja / Przebudowa ALTER INDEX IX_Tabela_Kol ON dbo.Tabela REORGANIZE; ALTER INDEX ALL ON dbo.Tabela REBUILD;

◈ SSMS (interfejs graficzny)

Rozwiń tabelę → Indexes
Prawy klik → New Index
Dodaj kolumny klucza + Included Columns
Options: Fill Factor (np. 80)
Przebudowa: prawy klik → Rebuild / Reorganize
Fragmentacja: Properties → Fragmentation

Strategia „tylko indeks" (Covering): Użyj INCLUDE by dodać kolumny SELECTa do liści indeksu. Eliminuje Key Lookup – silnik pobiera wszystko z indeksu, nie dotykając pliku tabeli.

Nigdy nie umieszczaj kolumny w funkcji w WHERE! WHERE YEAR(Data) = 1990 → Table Scan. Przepisz: WHERE Data BETWEEN '1990-01-01' AND '1990-12-31'. Alternatywa MS SQL: kolumna wyliczana (computed column) z indeksem.

Fill Factor i defragmentacja indeksów

Fill Factor

% wypełnienia stron. Domyślnie 0 (=100%). Dla insertowanych tabel: 70-80% → rezerwuje miejsce, eliminuje Page Splits.

Page Split

Przepełniona strona dzieli się na dwie. Kosztowna operacja I/O – spowalnia INSERTy.

Fragmentacja > 20%

Sygnał do przebudowy indeksu

REORGANIZE

Lżejsze – układa liście, online. Dla fragmentacji 10-30%.

REBUILD

Pełna przebudowa B+ drzewa od zera. Blokuje procesy! Tylko w oknach serwisowych.

Optymalizacja T-SQL

Unikaj kursorów – operacje set-based (JOIN) wielokrotnie szybsze
Problem N+1 (ORM) – Spring/Hibernate generują kaskady zapytań. Wykrywaj Profilerem, przenoś logikę do procedur składowanych.
Ograniczaj podzapytania – zastąp JOINem
DISTINCT tylko gdy potrzebny – kosztowny na dużych zbiorach
Minimalizuj wyzwalacze – niewidzialny narzut przy każdym DML
Tabele tymczasowe – opłacalne tylko przy wielokrotnym użyciu ciężkiego wyniku
Denormalizacja w raportach – nocne zrzuty do płaskich tabel raportowych

ACID i praktyka transakcji

Atomicity

„Wszystko albo nic" – transakcja wchodzi w całości lub wycofuje się

Consistency

Brak anomalii logicznych po zakończeniu

Isolation

Każdy użytkownik „jak gdyby sam"

Durability

Zacommitowane = zapisane na stałe (WAL)

⬡ SQL – transakcje

-- OBOWIĄZKOWE zabezpieczenie! SET XACT_ABORT ON; BEGIN TRANSACTION; UPDATE dbo.Konta SET Saldo -= 100 WHERE KontoId = 1; UPDATE dbo.Konta SET Saldo += 100 WHERE KontoId = 2; COMMIT TRANSACTION; -- SAVE TRANSACTION – punkt zapisu BEGIN TRANSACTION; INSERT INTO dbo.Log VALUES ('start'); SAVE TRANSACTION SP1; UPDATE dbo.Dane SET X = 5 WHERE Id = 1; -- coś poszło nie tak: ROLLBACK TRANSACTION SP1; -- cofnij tylko do SP1 INSERT INTO dbo.Log VALUES ('rollback częściowy'); COMMIT TRANSACTION; -- Wyłączenie autocommit (eksperymenty) SET IMPLICIT_TRANSACTIONS ON; -- teraz UPDATE nie commituje się automatycznie -- musisz ręcznie: COMMIT lub ROLLBACK

◈ SSMS – zarządzanie transakcjami

Każda operacja DML/DDL jest automatycznie transakcją
Autocommit jest domyślny – każdy UPDATE od razu commituje
SET IMPLICIT_TRANSACTIONS ON → „zwolnione tempo"
Activity Monitor → widok aktywnych transakcji i blokad
⚠️ Nie zostawiaj otwartej transakcji – blokujesz serwer!

SET XACT_ABORT ON jest OBOWIĄZKOWE w każdym skrypcie z BEGIN TRAN! Bez niego: drobny błąd (naruszenie klucza itp.) jest „przeskakiwany" – transakcja jedzie dalej, łamiąc atomowość. Z XACT_ABORT: dowolny błąd = natychmiastowy ROLLBACK całości.

Anomalie współbieżności

Anomalia	Opis	Przykład
Dirty Read	Odczyt danych z niezacommitowanej transakcji	User A czyta wynik UPDATE od User B, ale B robi ROLLBACK → dane fałszywe
Non-repeatable Read	Dwa odczyty tej samej komórki dają różne wyniki	SELECT saldo → 1000. Ktoś robi UPDATE. SELECT saldo → 500.
Phantom	Pojawiają się nowe rekordy w trakcie transakcji	SELECT COUNT → 10. Ktoś robi INSERT. SELECT COUNT → 11.

Poziomy izolacji

Poziom	Dirty Read	Non-repeatable	Phantom	Wydajność
READ UNCOMMITTED	TAK	TAK	TAK	Najszybszy
READ COMMITTED (domyślny)	NIE	TAK	TAK	Standardowy
REPEATABLE READ	NIE	NIE	TAK	Wolniejszy
SERIALIZABLE	NIE	NIE	NIE	Najwolniejszy (mrozi tabele!)
SNAPSHOT	NIE	NIE	NIE	Szybki (MVCC, bez blokad)

⬡ SQL – poziomy izolacji

-- Ustawianie poziomu (PRZED begin tran!) SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL SNAPSHOT; -- Aktywacja SNAPSHOT w bazie (jednorazowo) ALTER DATABASE [MojaBaza] SET ALLOW_SNAPSHOT_ISOLATION ON; -- Brudny odczyt (hint) – świadomie! SELECT * FROM dbo.Tabela WITH (NOLOCK);

◈ SSMS – izolacja

Baza → Properties → Options → Is Read Committed Snapshot On
Activity Monitor → Resource Waits → LOCK, IO
Deadlock: Trace Flag 1222 lub SQL Server Error Log

SNAPSHOT eliminuje „czytelnik blokuje pisarza" – używa wielowersyjności (MVCC). Idealna dla ciężkich raportów na produkcji. Ale: zużywa pamięć na migawki i przy konflikcie dwóch UPDATEów – wyrzuca błąd.

Złota reguła: Nakładaj NAJNIŻSZY bezpieczny poziom. Domyślny READ COMMITTED wystarcza w 90% przypadków. SERIALIZABLE „na zapas" = lawinowy przyrost blokad.

Blokady (Locks) i Deadlock

Shared Lock (S)

SELECT – tysiące czytających naraz, ale blokuje pisarzy

Exclusive Lock (X)

INSERT/UPDATE/DELETE – nikt inny nie wchodzi

ROW lock

Blokada jednego rekordu

PAGE lock

Blokada strony 8 KB

TABLE lock

Blokada całej tabeli (UPDATE bez WHERE, SERIALIZABLE)

Deadlock

Dwie transakcje czekają nawzajem na swoje zasoby → optymalizator zabija jedną po timeout

Database Snapshots (Enterprise)

Klonowanie bazy do read-only kopii z danego punktu w czasie. Lekkie (copy-on-write). Idealne do raportów: „zamroź stan z godz. 12:00 dla księgowości".

Indeksowane perspektywy (Indexed Views)

MS SQL nie ma MATERIALIZED VIEW. Zamiast tego: fizyczny indeks clustered na widoku. Ograniczenia:

Wymaga WITH SCHEMABINDING
Brak OUTER JOIN, DISTINCT, MIN/MAX, UNION
Użyj COUNT_BIG zamiast COUNT
Auto-optymalizacja: tylko Enterprise. Na Standard/Express: wymuś WITH (NOEXPAND)

Indeksy funkcyjne: MS SQL ich nie obsługuje natywnie (PostgreSQL tak). Obejście: computed column (np. AS LOWER(Nazwa) PERSISTED) + indeks na niej.

Rozdział 08

Dobre praktyki i ostrzeżenia

Pułapki indeksów, transakcje, uprawnienia, ORM, porównanie z PostgreSQL, ściągawka diagnostyczna.

Spis treści

Pułapki indeksów Transakcje Uprawnienia ORM i N+1 MS SQL vs PostgreSQL Ściągawka diagnostyczna

Pułapki indeksów i optymalizacji

Funkcja w WHERE = śmierć indeksu. WHERE sal * 12 = 12000 → Table Scan. WHERE sal = 1000 → Index Seek.

Zbyt wiele indeksów na małych tabelach. Koszt utrzymania (IO+CPU przy INSERT/UPDATE/DELETE) może przewyższać zysk SELECTa.

Clustered Index ≠ zawsze PK. Jeśli potrzebujesz zakresu (BETWEEN po dacie), zdejmij Clustered z PK i przenieś na kolumnę z zakresami.

Dobre nawyki – transakcje

Krótkie transakcje – dziel na mniejsze bloki, by nie blokować innych
SET XACT_ABORT ON – ZAWSZE przed BEGIN TRAN. Bez tego: drobny błąd nie wycofuje transakcji!
Nie używaj SERIALIZABLE „na zapas" – mrozi tabele. READ COMMITTED wystarcza w 90% przypadków.
WITH (NOLOCK) tylko świadomie – brudne odczyty z niezatwierdzonych/wycofanych transakcji
Nie zostawiaj otwartej transakcji (IMPLICIT_TRANSACTIONS ON) – blokujesz serwer

Dobre nawyki – uprawnienia

Projektuj: schemat → rola → użytkownik
Nigdy nie łącz aplikacji jako sysadmin/SA – zasada minimalnych uprawnień!
Role niestandardowe zamiast ról wbudowanych dla granularnej kontroli
Role aplikacyjne do dostępu z poziomu aplikacji

Problem N+1 (ORM)

Frameworki ORM (Spring, Hibernate, Entity Framework) generują kaskady zapytań – podręcznikowy „problem N+1": 1 zapytanie po listę + N zapytań po szczegóły każdego elementu. Wykrywaj Profilerem, przenoś logikę do procedur składowanych na serwerze.

MS SQL vs PostgreSQL

Funkcja	PostgreSQL	MS SQL Server
Indeksy funkcyjne	CREATE INDEX ON t (lower(col)) – natywnie	Brak. Computed column + indeks
Zmaterializowane widoki	MATERIALIZED VIEW – natywnie	Indexed View z SCHEMABINDING (auto-optymalizacja tylko Enterprise)
Hint wymuszający widok	Nie wymagany	WITH (NOEXPAND) na Standard/Express
Izolacja SNAPSHOT	Domyślna (MVCC)	Wymaga ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON
Indeksy haszowane	Tak – szybkie na = ale brak sortowania	Brak
Indeksy bitmapowe	Brak	Brak (Oracle ma natywnie)

Ściągawka poleceń diagnostycznych

DBCC CHECKDB

Kontrola integralności bazy danych

DBCC SHRINKDATABASE

Zmniejszenie plików (tylko serwisowo!)

SET STATISTICS IO ON

Odczyty logiczne i fizyczne

SET STATISTICS TIME ON

Czas kompilacji i wykonania

UPDATE STATISTICS

Odświeżenie statystyk (WITH FULLSCAN)

sp_helpdb / sp_spaceused

Rozmiar bazy, plików, wolne miejsce

sp_who2

Lista aktywnych połączeń / procesów

RESTORE HEADERONLY

Lista kopii w pliku .bak

Rozdział 09

🎯 Kolokwium – przewodnik i rozwiązania

6 zadań, 10 punktów. Pełne rozwiązania dla każdego typu zadania + przykładowe kolokwium.

Spis treści

Informacje organizacyjne Zad 1: Pliki i bazy (1 pkt) Zad 2: Indeksy (2 pkt) Zad 3: Transakcje (2 pkt) Zad 4: Bezpieczeństwo (2 pkt) Zad 5: Backup (2 pkt) Zad 6: Narzędzia (1 pkt) Przykładowe kolokwium

Informacje organizacyjne

✅ Dozwolone

Oficjalna dokumentacja Microsoft (learn.microsoft.com), notatki własne, materiały z Edux

❌ Zabronione

AI (ChatGPT), Google, komunikatory (Teams, poczta). Komunikator w tle = przerwanie kolokwium!

📝 Forma

Rozwiązania na kartkach (własny długopis!). Demo na komputerze – wołaj prowadzącego tylko gdy GOTOWE.

⚠️ Pliki backupowe

Każdy zestaw ma DEDYKOWANY plik na FTP. Wzięcie cudzego = złe wyniki = niezaliczenie!

Zadanie 1

Pliki i bazy danych – Filegroups / baza Model

1 punkt

Typowe polecenie: „Umieść tabelę X fizycznie w pliku na dysku D:" lub „Spraw, by każda nowo tworzona baza miała tabelę emp".

Wariant A: Filegroup

⬡ SQL

-- Filegroup + plik + tabela ALTER DATABASE [MojaBaza] ADD FILEGROUP [FG_Zadanie]; ALTER DATABASE [MojaBaza] ADD FILE ( NAME = 'Plik_Zadanie', FILENAME = 'D:\Data\Plik_Zadanie.ndf', SIZE = 10MB ) TO FILEGROUP [FG_Zadanie]; CREATE TABLE dbo.TabelaX ( Id INT PRIMARY KEY, Nazwa VARCHAR(100) ) ON [FG_Zadanie];

◈ SSMS

Baza → Properties → Filegroups → Add → „FG_Zadanie"
Zakładka Files → Add → nazwij plik, ustaw ścieżkę D:\, Filegroup = FG_Zadanie
New Table → w Properties tabeli → Filegroup = FG_Zadanie

Wariant B: Baza Model (każda nowa baza ma tabelę emp)

⬡ SQL

-- Dodaj tabelę do bazy Model USE model; CREATE TABLE dbo.emp ( Id INT PRIMARY KEY IDENTITY, Imie VARCHAR(50), Nazwisko VARCHAR(50), Stanowisko VARCHAR(100), Pensja DECIMAL(10,2) ); -- Teraz każda nowa baza będzie miała emp: CREATE DATABASE TestBaza; USE TestBaza; SELECT * FROM dbo.emp; -- tabela istnieje!

◈ SSMS

Rozwiń bazę „model" → Tables
Prawy klik → New Table… → zdefiniuj kolumny emp
Zapisz (Ctrl+S) → podaj nazwę: emp
Od teraz każda CREATE DATABASE klonuje model z tabelą emp

📖 Docs: Database Files and Filegroups

Zadanie 2

Wydajność i indeksy

2 punkty

Typowe polecenie: „Dostajesz powolne zapytanie z JOINem – zbadaj plan wykonania i zaproponuj indeks" lub „Skonfiguruj Full-Text Search na tabeli X".

Wariant A: Analiza planu i indeks

⬡ SQL

-- 1. Włącz plan i zbadaj zapytanie SET STATISTICS IO ON; -- Ctrl+M w SSMS (Include Actual Execution Plan) -- 2. Zapytanie do optymalizacji SELECT o.OrderId, o.OrderDate, c.Name FROM dbo.Orders o JOIN dbo.Customers c ON o.CustomerId = c.Id WHERE o.OrderDate > '2024-01-01' ORDER BY o.OrderDate; -- 3. Widzisz Table Scan / Index Scan → załóż: -- Clustered na zakres dat CREATE CLUSTERED INDEX CX_Orders_Date ON dbo.Orders (OrderDate); -- Non-clustered na klucz obcy (JOIN) CREATE NONCLUSTERED INDEX IX_Orders_Customer ON dbo.Orders (CustomerId) INCLUDE (OrderDate); -- 4. Uruchom ponownie – powinno być Index Seek

◈ SSMS

Wpisz zapytanie → Ctrl+M → Execute
Zakładka Execution Plan → szukaj Table Scan (czerwone!)
Żółty trójkąt = sugestia „Missing Index"
Prawy klik na sugestię → „Missing Index Details"
Kliknij → otrzymujesz gotowy CREATE INDEX
Uruchom CREATE INDEX → ponownie uruchom zapytanie
Sprawdź: teraz powinno być Index Seek (zielone)

Wariant B: Full-Text Search

Patrz: Rozdział 4 → Full-Text Search

📖 Docs: Indexes 📖 Docs: Full-Text Search

Zadanie 3

Transakcje – scenariusz dwóch sesji

2 punkty – pisemne, bez komputera

Typowe polecenie: „Dwie sesje A i B pracują jednocześnie. Odtwórz kroki i napisz jaki będzie wynik każdego SELECTa."

Jak rozwiązywać:

Sprawdź poziom izolacji każdej sesji (domyślny = READ COMMITTED).

Sprawdź tryb transakcji: autocommit (domyślny) vs explicit (BEGIN TRAN) vs implicit (SET IMPLICIT_TRANSACTIONS ON).

Dla każdego kroku określ: UPDATE/INSERT stawia X lock (wyłączny), SELECT stawia S lock (współdzielony).

S lock pozwala innym S lockom ale blokuje X. X lock blokuje wszystko. Blokada trwa do COMMIT/ROLLBACK.

Uwzględnij specyfikę poziomu izolacji: READ UNCOMMITTED → czyta nawet niezacommitowane dane. READ COMMITTED → czeka aż X lock się zwolni. SNAPSHOT → czyta migawkę sprzed transakcji.

Przykład rozwiązania scenariusza:

Stan początkowy: tabela Konta: Saldo = 1000 (KontoId=1). Obie sesje: READ COMMITTED, autocommit.

Krok	Sesja A	Sesja B	Wynik
1	BEGIN TRAN	—	A otwiera transakcję
2	UPDATE Konta SET Saldo=500 WHERE KontoId=1	—	A stawia X lock. Saldo=500 (niezacommitowane)
3	—	SELECT Saldo FROM Konta WHERE KontoId=1	B CZEKA (blokada!) – READ COMMITTED nie czyta niezacommitowanych
4	COMMIT	—	A commituje, X lock zwolniony
5	—	(odblokowany)	B zwraca: Saldo = 500

Gdyby B miał READ UNCOMMITTED → w kroku 3 zwróciłby 500 (dirty read) bez czekania.

📖 Docs: Transactions 📖 Docs: SET TRANSACTION ISOLATION LEVEL

Zadanie 4

Bezpieczeństwo – login, user, schemat, uprawnienia

2 punkty – demo na komputerze (Access Denied)

Typowe polecenie: „Utwórz login SQL, usera w bazie X, schemat, tabelę w schemacie, nadaj tylko SELECT+INSERT, wykaż brak DELETE."

⬡ SQL – pełne rozwiązanie

-- 1. Login CREATE LOGIN testowy WITH PASSWORD = 'Test!123'; -- 2. User w bazie USE [MojaBaza]; CREATE USER testowy FOR LOGIN testowy; -- 3. Schemat + tabela CREATE SCHEMA zadanie; CREATE TABLE zadanie.Dane ( Id INT PRIMARY KEY IDENTITY, Wartosc VARCHAR(100) ); INSERT INTO zadanie.Dane VALUES ('test'); -- 4. Uprawnienia (tylko DML, BEZ DELETE) GRANT SELECT, INSERT, UPDATE ON SCHEMA::zadanie TO testowy; DENY DELETE ON SCHEMA::zadanie TO testowy; -- 5. Default schema (opcjonalnie) ALTER USER testowy WITH DEFAULT_SCHEMA = zadanie; -- 6. TEST: połącz się jako testowy i sprawdź -- SELECT * FROM zadanie.Dane; → ✅ działa -- INSERT INTO zadanie.Dane VALUES ('nowy'); → ✅ -- DELETE FROM zadanie.Dane WHERE Id=1; → ❌ Access Denied!

◈ SSMS – demo

Security → Logins → New Login… → SQL Auth, hasło
User Mapping → zaznacz bazę
W bazie: New Schema → „zadanie"
New Table w schemacie → dodaj kolumny
User → Properties → Securables → Search → Schema
Zaznacz GRANT na SELECT/INSERT/UPDATE
Zaznacz DENY na DELETE
Demo: Otwórz NOWE połączenie jako testowy (File → Connect Object Explorer → SQL Auth)
Wykonaj DELETE → pokaż prowadzącemu „Access Denied"

Na kolokwium: Przygotuj testowy skrypt DELETE i pokaż prowadzącemu wynik „Access Denied" dopiero gdy WSZYSTKO jest gotowe!

📖 Docs: Getting Started with Database Engine Permissions

Zadanie 5

Backup & Restore – odtworzenie z plików

2 punkty

Typowe polecenie: „Na FTP masz pliki .bak i .trn. Odtwórz bazę do najnowszego stanu (lub do punktu w czasie). Podaj wynik SELECTa."

Procedura krok po kroku:

Ściągnij swoje pliki z FTP (pilnuj nazwy zestawu!).

RESTORE HEADERONLY FROM DISK = 'ścieżka' – sprawdź co jest w każdym pliku (typ, data).

Zidentyfikuj: najnowszy Full → najnowszy Diff PO nim → wszystkie Log PO Diff.

Restore Full WITH NORECOVERY, REPLACE → Diff WITH NORECOVERY → Logi WITH NORECOVERY → ostatni WITH RECOVERY (lub STOPAT).

Uruchom SELECT i przepisz wynik na kartkę.

⬡ SQL

-- Sprawdź zawartość plików RESTORE HEADERONLY FROM DISK = 'C:\Backup\plik1.bak'; RESTORE HEADERONLY FROM DISK = 'C:\Backup\plik2.bak'; RESTORE HEADERONLY FROM DISK = 'C:\Backup\plik3.trn'; -- Restore (ścieżki przykładowe) RESTORE DATABASE [Zad5] FROM DISK = 'C:\Backup\plik1.bak' WITH NORECOVERY, REPLACE; RESTORE DATABASE [Zad5] FROM DISK = 'C:\Backup\plik2.bak' WITH NORECOVERY; RESTORE LOG [Zad5] FROM DISK = 'C:\Backup\plik3.trn' WITH RECOVERY; -- Opcja: point-in-time -- WITH RECOVERY, STOPAT = '2024-06-15 11:59:00'; -- Wynik USE [Zad5]; SELECT * FROM dbo.Tabela;

◈ SSMS

Databases → prawy klik → Restore Database…
Source: Device → „…" → Add → dodaj WSZYSTKIE pliki
SSMS posortuje je automatycznie (zaznacz „Restore" przy każdym)
Options → Overwrite existing database
Dla point-in-time: Timeline → ustaw czas
Jeśli błąd ścieżek: zakładka Files → „Relocate all files"
OK → uruchom SELECT

📖 Docs: Restore a Database Backup Using SSMS

Zadanie 6

Narzędzia – Maintenance Plan / Job / FTS

1 punkt

Typowe polecenie: „Załóż Maintenance Plan z backupem Full codziennie o 2:00" lub „Utwórz Job z krokiem T-SQL" lub „Włącz Full-Text na tabeli X".

⬡ SQL – Job z backupem

-- Szybki Job: backup co noc EXEC msdb.dbo.sp_add_job @job_name='BackupNocny'; EXEC msdb.dbo.sp_add_jobstep @job_name='BackupNocny', @step_name='FullBackup', @subsystem='TSQL', @command='BACKUP DATABASE [MojaBaza] TO DISK=''D:\Backup\Full.bak'' WITH INIT, COMPRESSION'; EXEC msdb.dbo.sp_add_schedule @schedule_name='Codziennie2AM', @freq_type=4, @active_start_time=020000; EXEC msdb.dbo.sp_attach_schedule @job_name='BackupNocny', @schedule_name='Codziennie2AM'; EXEC msdb.dbo.sp_add_jobserver @job_name='BackupNocny';

◈ SSMS – Maintenance Plan

Management → Maintenance Plans → prawy klik → Maintenance Plan Wizard
Podaj nazwę planu
Zaznacz: „Back Up Database (Full)"
Wybierz bazę (MojaBaza)
Ustaw ścieżkę docelową (D:\Backup\)
Schedule: codziennie, 2:00 AM
Finish – plan utworzony, Agent go uruchomi o 2:00

📖 Docs: Maintenance Plans

📋 Przykładowe kolokwium z rozwiązaniem

Poniższy zestaw symuluje realistyczne kolokwium ABD (6 zadań, 10 pkt). Rozwiązania podano w SQL. Na kolokwium możesz też użyć SSMS GUI.

Zadanie 1 (1 pkt) – Baza Model

Polecenie: Spraw, aby każda nowo tworzona baza danych automatycznie posiadała tabelę pracownicy z kolumnami: Id (INT, PK, IDENTITY), Imie (VARCHAR(50)), Nazwisko (VARCHAR(50)), Dzial (VARCHAR(100)).

Rozwiązanie

USE model; CREATE TABLE dbo.pracownicy ( Id INT PRIMARY KEY IDENTITY(1,1), Imie VARCHAR(50), Nazwisko VARCHAR(50), Dzial VARCHAR(100) ); -- Weryfikacja: CREATE DATABASE TestKolokwium; USE TestKolokwium; SELECT * FROM dbo.pracownicy; -- tabela istnieje!

Zadanie 2 (2 pkt) – Indeksy

Polecenie: Poniższe zapytanie jest bardzo wolne. Zbadaj plan wykonania i zaproponuj indeks(y), które istotnie poprawią wydajność:

SELECT p.Nazwisko, z.DataZamowienia, z.Kwota FROM Zamowienia z JOIN Pracownicy p ON z.PracownikId = p.Id WHERE z.DataZamowienia > '2024-01-01' ORDER BY z.DataZamowienia;

Rozwiązanie

-- 1. Włącz plan: Ctrl+M w SSMS, uruchom zapytanie -- 2. Widzisz: Table Scan na Zamowienia, Nested Loops -- 3. Clustered na DataZamowienia (filtrowanie + ORDER BY) CREATE CLUSTERED INDEX CX_Zam_Data ON dbo.Zamowienia (DataZamowienia); -- 4. Non-clustered covering na klucz obcy CREATE NONCLUSTERED INDEX IX_Zam_Prac ON dbo.Zamowienia (PracownikId) INCLUDE (DataZamowienia, Kwota); -- 5. Ponownie uruchom → Index Seek zamiast Table Scan -- Uzasadnienie: Clustered na DataZamowienia obsługuje -- filtrowanie WHERE i ORDER BY na zakresie dat. -- INCLUDE eliminuje Key Lookup (covering strategy).

Zadanie 3 (2 pkt) – Transakcje

Polecenie: Tabela Produkty: Id=1, Cena=100. Obie sesje: READ COMMITTED, autocommit ON. Odtwórz kroki i podaj wynik każdego SELECTa.

Krok	Sesja A	Sesja B
1	BEGIN TRAN	—
2	UPDATE Produkty SET Cena=200 WHERE Id=1	—
3	—	SELECT Cena FROM Produkty WHERE Id=1
4	ROLLBACK	—
5	—	SELECT Cena FROM Produkty WHERE Id=1

Rozwiązanie

-- Krok 1: A otwiera transakcję -- Krok 2: A stawia X lock na wiersz Id=1, Cena=200 (niezacommitowane) -- Krok 3: B próbuje SELECT → READ COMMITTED nie pozwala czytać niezacommitowanych → B CZEKA (blokada!) -- Krok 4: A robi ROLLBACK → Cena wraca do 100, X lock zwolniony -- Krok 5: B się odblokował → B zwraca: Cena = 100 B ponownie SELECT → zwraca: Cena = 100 Odpowiedź: Krok 3 = BLOKADA (czeka). Krok 5 = 100.

Zadanie 4 (2 pkt) – Bezpieczeństwo

Polecenie: W bazie Northwind: utwórz login SQL „student_test" z hasłem „Abc!1234", zmapuj go do usera, utwórz schemat „raport", przenieś tabelę Employees do schematu raport, nadaj userowi SELECT na schemacie, zablokuj UPDATE. Wykaż Access Denied przy próbie UPDATE.

Rozwiązanie

CREATE LOGIN student_test WITH PASSWORD = 'Abc!1234'; USE Northwind; CREATE USER student_test FOR LOGIN student_test; CREATE SCHEMA raport; ALTER SCHEMA raport TRANSFER dbo.Employees; GRANT SELECT ON SCHEMA::raport TO student_test; DENY UPDATE ON SCHEMA::raport TO student_test; -- TEST (połącz się jako student_test): -- SELECT * FROM raport.Employees; → ✅ -- UPDATE raport.Employees SET LastName='X' WHERE EmployeeId=1; → ❌

Zadanie 5 (2 pkt) – Backup & Restore

Polecenie: Na FTP masz 4 pliki: Full_niedzielny.bak, Diff_sroda.bak, Log_czwartek.trn, Log_piatek.trn. Odtwórz bazę do najnowszego stanu. Podaj wynik: SELECT COUNT(*) FROM dbo.Zamowienia;

Rozwiązanie

-- Kolejność: Full → Diff → Log1 → Log2 (ostatni z RECOVERY) RESTORE DATABASE [Zad5] FROM DISK = 'C:\FTP\Full_niedzielny.bak' WITH NORECOVERY, REPLACE; RESTORE DATABASE [Zad5] FROM DISK = 'C:\FTP\Diff_sroda.bak' WITH NORECOVERY; RESTORE LOG [Zad5] FROM DISK = 'C:\FTP\Log_czwartek.trn' WITH NORECOVERY; RESTORE LOG [Zad5] FROM DISK = 'C:\FTP\Log_piatek.trn' WITH RECOVERY; USE [Zad5]; SELECT COUNT(*) FROM dbo.Zamowienia; -- Przepisz wynik na kartkę!

Zadanie 6 (1 pkt) – Maintenance Plan

Polecenie: Utwórz plan konserwacji, który codziennie o godz. 3:00 wykona pełny backup bazy Northwind do katalogu D:\Backup\.

Rozwiązanie (SSMS jest prostsze – użyj Wizarda!)

-- SSMS: Management → Maintenance Plans → Wizard -- 1. Nazwa: „NocnyBackupNorthwind" -- 2. Schedule: codziennie, 3:00 AM -- 3. Zadanie: Back Up Database (Full) -- 4. Baza: Northwind -- 5. Folder: D:\Backup\ -- 6. Finish -- Alternatywnie jako Job (T-SQL): EXEC msdb.dbo.sp_add_job @job_name='MP_Northwind'; EXEC msdb.dbo.sp_add_jobstep @job_name='MP_Northwind', @step_name='FullBackup', @subsystem='TSQL', @command='BACKUP DATABASE [Northwind] TO DISK=''D:\Backup\Northwind_Full.bak'' WITH INIT, COMPRESSION'; EXEC msdb.dbo.sp_add_schedule @schedule_name='Daily3AM', @freq_type=4, @active_start_time=030000; EXEC msdb.dbo.sp_attach_schedule @job_name='MP_Northwind', @schedule_name='Daily3AM'; EXEC msdb.dbo.sp_add_jobserver @job_name='MP_Northwind';

Instalacja, architektura i zarządzanie plikami

Typy instancji

Kluczowe usługi

Bazy systemowe

Architektura plików

Strony dyskowe (Pages) i Extenty

Filegroups – logiczne grupy plików

Shrink – zmniejszanie plików bazy

Narzędzia administracyjne

Backup & Restore – kopie zapasowe i odtwarzanie

Typy kopii zapasowych

Modele odzyskiwania (Recovery Model)

Wykonanie backupu

INIT vs NOINIT & przeglądanie pliku

Odtwarzanie bazy danych

WITH MOVE – relokacja plików

Backup baz systemowych

Awaryjny backup logu (Tail-Log Backup)

Bezpieczeństwo i schematy

Login vs User – dwuetapowy dostęp

Konta specjalne

Hierarchia uprawnień (3 poziomy)

GRANT / DENY / REVOKE

Role

Schematy

Pułapki SSMS

Automatyzacja, monitorowanie i integracja

Maintenance Plans – planowane zadania konserwacji

Jobs – zadania zaplanowane (niskopoziomowe)

Monitorowanie serwera

Alerty i operatorzy

Full-Text Search (FTS) – wyszukiwanie pełnotekstowe

Database Mail

Import / Eksport danych

Systemy rozproszone i wysoka dostępność

Log Shipping

Database Mirroring

Replikacja

Agenci Replikacji

Linked Servers

Transakcje rozproszone (DTC) – Two-Phase Commit

SQL Server Failover Clusters

Metodologia strojenia (Tuning)

Analiza planu wykonania zapytania

Kluczowe operacje w planie zapytania

Statystyki kosztowe i optymalizator

SQL Server Profiler + Database Engine Tuning Advisor

Techniki optymalizacji

Architektura dyskowa

Indeksy – architektura B+ drzewa

Kiedy indeks NIE pomoże

Tworzenie i zarządzanie indeksami

Fill Factor i defragmentacja indeksów

Optymalizacja T-SQL

ACID i praktyka transakcji

Anomalie współbieżności

Poziomy izolacji

Blokady (Locks) i Deadlock

Database Snapshots (Enterprise)

Indeksowane perspektywy (Indexed Views)

Dobre praktyki i ostrzeżenia

Pułapki indeksów i optymalizacji

Dobre nawyki – transakcje

Dobre nawyki – uprawnienia

Problem N+1 (ORM)

MS SQL vs PostgreSQL

Ściągawka poleceń diagnostycznych

🎯 Kolokwium – przewodnik i rozwiązania

Informacje organizacyjne

Wariant A: Filegroup

Wariant B: Baza Model (każda nowa baza ma tabelę emp)

Wariant A: Analiza planu i indeks

Wariant B: Full-Text Search

Jak rozwiązywać:

Przykład rozwiązania scenariusza:

Procedura krok po kroku:

📋 Przykładowe kolokwium z rozwiązaniem

Zadanie 1 (1 pkt) – Baza Model

Zadanie 2 (2 pkt) – Indeksy

Zadanie 3 (2 pkt) – Transakcje