Poznámky ze školení Microsoft SQL Server

Upozornění: Tyto poznámky jsou jen inspirací. Před aplikováním níže uvedených postupů je potřeba se s danou problematikou podrobně seznámit i v jiných zdrojích, např. v dokumentaci.

• SQL Server je nesložitější a nejdražší aplikace, kterou kdy v Microsoftu naprogramovali.
• Je zvykem mít k SQL Serveru tzv. „run book“ - dokumentaci.
• Před povýšením verze serveru je dobré si přečíst Upgrade White paper, popř. také použít program Upgrade Advisor. Povýšení SQL je možné provést i ve Failover nasazení, tzn. bez výpadku.
• Při migraci databází na jiný SQL server je potřeba přenést i nastavení Loginů SQL Serveru, což je potřeba provést pomocí speciálního skriptu (není součástí SQL Serveru).

Prvotní nastavení SQL Serveru:

• Service Accounts - účty, pod kterými běží SQL Server je dobré volit s rozvahou. Pokud to je administrátorský účet, pak každý, kdo je sysadminem, nebo má oprávnění k xp_cmdshell, může provést jakýkoliv příkaz na samotném Windows Serveru, popř. v síti.
• Management - SQL Server Logs - Configure: přenastavit počet ERRORLOGů minimálně na 12.
• Konfigurace serveru - buď přes Properties serveru nebo přes Facets serveru - zvolit Facet: Server Configuration. Popřípadě uložená procedura EXEC sp_configure:
  EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
• Memory: Max překonfigurovat dle doporučeného vzorce, počítá se pro každou instanci zvlášť, takže podle toho upravit výsledky + zohlednit další služby, např. Reporting services:
  
• Tato volba nedefinuje úplné maximum co může instance zabírat, podrobné info o paměti je v SELECT * FROM sys.dm_os_memory_clerks ORDER BY pages_kb
• Minimum se doporučuje, když už 50% maxima paměti. Není ale nutné.
• V (Local Security) Policy - User Rights Assignment - Lock pages in memory: nastavit účtu, pod kterým běží Database Engine. Při (re)startu služby DE je zmíněna informance, že používá locked pages. To brání Windows, aby byly procesy SQL stránkovány na disk. Toto nastavovat jedině s nastaveným maximem paměti!
• Database settings - nechat zaškrtnutou kompresi, zrychluje zálohování.
• Advanced: Miscellaneous - Optimize for Ad hoc Workloads: True. -- Zlepší (od 2008) memory management např. pro exekuční plány. Exekuční plán (execution plan) může u selectu pomoci najít cestu k optimalizaci výkonu. Obsah pro každý select se však ukládá jako XML do paměti (až do dalšího restartu), a to i pro dotazy, které již nikdy nebudou spuštěny.
• Advanced: Parallelism - Max degree of Parallelism: 0 je výchozí - využije všechny CPU, což může zpomalovat vykonávání dotazů, 1 - Sharepoint, 8 - doporučené maximum. -- Pro zajištění efektivity je nutné mít úkol dostatečně dlouhý. OPTION (MAXDOP 1) v dotazu ruší paralelismus.
• Advanced: Parallelism - Cost Threshold for Parallelism -- Každý dotaz má svůj cost. Tato hodnota i u jednoduchého dotazu může být větší než 5. Tam je dobré odladit nejčastější dotazy, jaký mají cost a podle toho nastavit toto číslo.
• (Local Security) Policy - User Rights Assignment - Perform volume maintenance tasks. Zabraňuje nulování (plnění nulami) při tvorbě databázových souborů. Urychlí restoring. Má minor security bug, který umožní (např. použitím příkazu DBCC PAGE) přečíst předchozí data, která na daném místě byla.
• tempdb - výkonově důležitá systémová DB. Je doporučeno její MDF rozdělit do více souborů (mdf a ndf), podle počtu procesorů, max. 8. Podobně i u ostatních databází se doporučuje rozdělení do více souborů, těm stačí 1/4 až 1/2 celkového počtu CPU. Jednotlivé soubory v rámci stejné Filegroupy fungují jako RAID 0.
• Transakční logy ukládat zvlášť na disky optimalizované pro zápis (SSD nebo RAID 10). V případě datového skladu, který je především určen pro čtení, nemá transakční log takovou roli...
• Latence pro zápis transakčního logu na disk se má blížit 5 ms, u datových souborů 20 ms, 30-35 ms.
• Autogrow hodnota u souborů s databází či transakčním logem se doporučuje nastavit v bajtech (absolutně), ne procentech (relativně).
• Login sa je dobré disablovat a přejmenovat. Ale přejmenování může zároveň v některých případech dělat potíže (pokud nějaká funkce s sa počítá).
• Database Mail - Configure Database Mail: nastavení profilu pošty.
• SQL Agent - Operators - New operator... - nastavení operátora, historie, alertů severity 17 a vyšší a 3 chyby úrovně 10 o paměti...
• SQL Agent -  Properties - Alert system: zaškrtnout Enable mail profile, zvolit Database mail a vybrat Mail profile.

SQL Management Studio:

• spouštět vždycky jako administrator.
• obsahuje Template Explorer, kde je seznam různých T-SQL skriptů pro nejčastější operace.
• Pokud vyjde servicepack pro SQL Server, nezapomenout fixnout i Management Studio, které mívá hodně chyb a řeší se právě v servicepacku.
• od verze 2014 je nástroj společný i pro edici Express, ve verzi 2016 se má vývoj/opravy MS úplně oddělit od samotného SQL Serveru pro lepší pružnost.
• pro každé spojení je možné si nastavit barvu, takže člověk ve stavovém řádku vidí (podle barvy), na jaké instanci sedí.
• Registered Servers - je možné si definovat hierarchii (instancí) SQL Serverů. Usnadňuje přechod mezi instancemi (pamatuje si nastavení), možnost provést dotaz nad více instancemi zároveň, možnost kontroly politik, zda vyhovují danému nastavení.
• Activity Monitor (pravým tlačítkem na server nebo v nastrojove liste). Pozor! Nenechávat běžet, zbytečně zatěžuje SQL server. Pravým tlačítkem na Overview je možné nastavit refresh interval.

SQL Server Configuration Manager:

• Log On měnit jedině zde! V případě změny přes services.msc nemusí dojít ke správnému překonfigurování SQL Serveru.
• Startup Parameters - je možné přidat tzv. Trace flags, např. -T1117 (zvětšovat jednotlivé databázové soubory stejnoměrně zaráz - při rozložení DB do více souborů), -T1118 (alokovat větší prostor pro nové tabulky).

SQL Server - funkcionalita:

• Master Data Services (pouze v Enterprise) je služba, která umí agregovat data z více zdrojů a ukládat si je u sebe. Není však technologií vysoké dostupnosti.
• Data Quality Services (pouze v Enterprise) je služba, která umí kontrolovat konzistenci dat oproti tzv. Knowledge base. Např. zadaná adresa oproti databázi známých adres.
• Power View - zásuvný modul do Excelu - doplňuje funkci Reporting Services, do grafů umí přidat různé interakce.
• FILESTREAM - umožňuje binární soubory (varbinary) ukládat mimo DB na disk.
• Distributed Replay Controller and Client - sada služeb, které jsou schopny server cíleně zatěžovat.
• Extended Events - nový nástroj od SQL Serveru 2008 pro trasování, grafický nástroj je až u 2012, ale je možné stáhnout addin z codeplex.com Extended Events Addin.

SQL Server - obecné:

• Jednotlivé instance SQL Serveru mohou mít různé verze vydání, patchů a nastavení, maximální počet je 50.
• Mezi vydání Enterprise a Standard je ještě vložena varianta Business Intelligence, která méně omezuje Analysis Services a Reporting Services.
• Při vypínání serveru je doporučeno nejdříve vydat příkaz SHUTDOWN. Pokud potřebujeme ukončit hned, provádí se SHUTDOWN WITH NO_WAIT.
• Systémové databáze: master - je hlavní (potřebná pro start), obsahuje security a metadata dalších databází. msdb - konfigurace služeb a funkcí SQL Serveru, model - šablona nové databáze, tempdb - úložiště všech dočasných objektů (výsledky, mezivýsledky, proměnné), resource - neviditelná read-only databáze ležící u binárek SQL Serveru, obsahuje všechny systémové objekty.
• Obsahuje vlastní OS, k procesorům a paměti přistupuje jinak, než běžný proces. Pro každé CPU má tzv. Scheduler. Pro každé CPU si vyžádá několik vláken, které pak přiřadí jednomu Workeru (Worker Thread). Worker může mít 3 stavy - Running (právě se vykonává, maximálně však 4 ms), Suspended (čeká na možnost pokračovat - buď vyčerpalo čas, nebo nemá splněny požadavky jako data v paměti, nebo uvolněný zámek), Runnable (chystá se běžet - fronta FIFO).
• Ověření Windows skrze aplikaci musí být konkrétním způsobem (Kerberos) předány SQL serveru, což dělá málo aplikací, proto se používají SQL Loginy, které však svá credentials musí mít uložena v aplikaci a navíc při přenosu nejsou dostatečně šifrovaná. Pro použití Kerberos se musí server do domény zapsat jako SPN. Pro zápis SPN je potřeba danému účtu, pod kterým SQL běží, přiřadit oprávnění. Umí to též zařídit nástroj Microsoft Kerberos Configuration Manager for SQL Server.
• Novinkou od SQL Serveru 2012 je ověřování oproti databázi. To vynechává potřebu mít založené Loginy. Problém je v tom, že uživatel je pak uzavřený jen do dané DB.
• Login: Default language - ovlivňuje jak chybové hlášky, tak i formáty - především data. Univerzální zápis data je '20160218'. Ovlivnit to může pomocí SET LANGUAGE 'English'.

SQL Server - ladění:

• SET STATISTICS IO ON -- pro daný dotaz je možné si zapnout informace o IO operacích, vhodným návrhem indexu a dostatkem paměti je možné ovlivnit výkon T-SQL.
• SELECT * FROM msdb.dbo.suspect_pages -- zobrazí přehled chybných datových stránek. Obvykle by zde nemělo nic být.
• SELECT * FROM sys.dm_exec_request -- přehled právě běžících dotazů a jejích stav. Je možné si najít, kdo koho blokuje, nebo na co vlastně čeká.
• SELECT * FROM sys.dm_os_wait_stats -- agreguje přehled různých typů čekání. Pomůže odhalit nejproblematičtější typy čekání - časy a počty čekání na daný kód čekání. wait_time_ms = runnable + suspended. signal_wait_time = runnable, tj. čekal na procesor.
• SELECT * FROM sys.dm_exec_connections -- přehled vzdálených session
• SELECT * FROM sys.dm_exec_sessions -- přehled všech i systémových session
• SELECT * FROM sys.dm_os_memory_clerks -- přehled všech položek, které zabírají paměť.
• SELECT * FROM sys.dm_exec_query_stats -- přehled všech plánů dotazů, které byly spuštěny a ponechány v paměti.
• Jsou různé doplňky do SQL pro monitoring - např. SQL Performance dashboard.
• SQL Server - Management - Data Collection: Nástroj pro přehled nad SQL serverem.
• SELECT * FROM sys.server_permissions -- zobrazí skutečná serverová oprávnění jednotlivých Loginů.

SQL Server - postupy:

• Přemístění tempdb na jiný disk:

• Podle počtu CPU přidat další soubory v tempdb - Properties - Files - Add.
• Upravit initial size dle aktuálního stavu mdb. Po restartu SQL Serveru se takhle velké soubory vytvoří.
• Autogrowth nastavit místo procent velikost MB, určitě více než 1 MB - stejně u všech souborů.
• Změnu umístění je možné provést jen pomocí T-SQL serveru - po přidávání souborů je možné si otevřít skript akce. Přemístění souborů:
• ALTER DATABASE [tempdb]
MODIFY FILE (NAME = 'tempdev1', FILENAME = 'M:\Data\tempdev1.mdf')
  (Pro běžné DB je možnépoužít Detach a Attach.)
• Po restartu je možné starý soubor smazat. Ostatní systémové DB je třeba ručně přemístit do nového umístění. U nesystémových DB není třeba restart, ale stačí přepnout offline, přenést data a přepnout do online stavu.

•  Migrace MDF do více databázových souborů:

• Vytvořit jeden ndf stejně velký jako mdf.
• SQL příkazem SHRINK EMPTY FILE se nastaví, že vše se má přesunout do toho druhého ndf.
• Vytvořit ostatní ndf (třeba dva).
• SHRINK EMPTY FILE pro druhý ndf. Data se přesunout do mdf a ostatních ndf.
  Zrušit druhý ndf.

•  Vytváření nové databáze:

  • servisní účet, nebo sa, jinak se stává vlastníkem ten, kdo danou DB vytváří.
  • nastavit velikost Initial Size, autogrowth po bajtech,
  • přidat více datových souborů.
  • pokud chceme použít jinou filegroupu, je možné však u ní nastavit vlastnost Default. Nové tabulky se pak budou zakládat přímo v ní.
  • u velkých DB nastavit u transakčního logu na 4 nebo 8 GiB
  • Options:
  • Collation <default> nebo vlastní.
  • Recovery model: Full -, Bulk-logged - , Simple - . Určuje dobu ponechání transakce v TL.
  • Compatibility Level: Nechat, v případě restoru se CL obnovuje také. CL je možné libovolně přepínat.
  • Containment Type: Týká se možnosti ověřování v rámci databáze.
  • Nikdy nenastavovat na True: Automatic Auto Close a Auto Shrink. Auto Close automaticky po ukončení všech spojení databázi vymaže z paměti. Auto Shrink uvolňuje volné místo, ale způsobuje fragmentaci. Defragmentace však vyžaduje více místa, takže tyto dvě věci jsou proti sobě. Shrinkování se děje jedině v transakčním logu, protože v případě velkých transakcí se automaticky zvětší, ale po jejich dokončení zůstává volné místo.
  • Statistiky - informují SQL server o datech v daném sloupci. Každý index si vytváří svoje. V Options volba Auto Create Statistics ovlivňuje možnost, že v případě neexistence statistiky si ji vytvoří. Při změně dat, je třeba statistiku přepočítat. Statistika se vytváří u větších DB jen z mnohem menší části dat, takže dochází k jejím velkým nepřesnostem. Proto je potřeba je přepočítávat ručně - ruční aktualizace statistik je popsána níže.
  • Recovery - Page Verify - CHECKSUM. Starší DB mají TORN_PAGE_DETECTION. Optimálnější pro výkon a konzistenci je CHECKSUM. Možno změnit za běhu.
  • State - Restrict Access: MULTI_USER (kdokoliv, kdo má oprávnění), SINGLE_USER (jen jedno připojení) -- je možné vyvolat WITH NO_WAIT (zkusí hned, ale v případě dalších připojení skončí chybou), WITH ROLLBACK IMMEDIATE (odpojí), WITH ROLLBACK AFTER 30 (odpojí po). Jenže po odpojení a přechodu na SINGLE_USER je dobré si hned pojistit, abychom to byli my, kdo se připojí první, je dobré použít T-SQL, kde po ROLLBACK uvedeme příkaz USE [database]. RESTRICTED_USER je jen pro privilegované uživatele, ale nemusí mít efekt, pokud mají privilegia i aplikační uživatelé.

• Data Collector (od verze 2008) - konfigurace:

  • Vytvořit databázi (MDW - management dataware house), nemusí být v dané instanci.
  • Pravým tlačítkem na Data Collector - Tasks - Configure Management Data Warehouse.
  • Spustit Data Collector a nastavit mu cílovou MDW. Pravým tlačítkem na DC - Tasks - Configure Data Collection. Cache directory - sem ukládá sesbíraná data před tím, než proběhne zápis do DB (může být jednou za 15 minut).
  • Vytvoří si několik jobů, kterými vytváří přehled, který pak ukládá do DB.
  • Je poměrně velká - minimálně 300 MB/den.
  • Několik kolektorů:
  • Disk Usage - 2 letý přehled o růstu databází.
  • Query Statistics - 14 denní přehled o spuštěných dotazech (generuje hodně záznamů).
  • Server Activity - 14 denní přehled o serveru.
  • Výsledný report je možné si zobrazit pravým tlačítkem na MDW - Reports - atd.

• Extended Events

  • nový nástroj od SQL Serveru 2008 pro trasování, grafický nástroj je až u 2012, ale je možné stáhnout addin z codeplex.com Extended Events Addin.
  • Přežijí restart serveru.
  • Možné nastavit automatické spuštění.
  • Od 2008 je automaticky aktivní system_health.
  • Méně výkonově náročné.
  • New session - Events:
  • sql_statement_completed.
  • xml_deadlock_report.
  • sort_warning.
  • query_post_execution_showplan -- pozor na náročnost!
  • blocked_process_report.
  • module_end -- ukončení procedury.
  • lock_timeout.
  • wait_info/wait_completed (od 2014) -- odhalí i jiné čekání kromě zámků.
  • Event fields - ke každému eventu je možné nastavit, které informace chceme u události zjišťovat.
  • Global Fields - client_app_name, database_name, sql_text.
  • Filters - možné dát jak and tak i or, včetně závorek. Filtruje se hned na začátku vyhodnocování trasování, čím lepší filtr, tím menší dopad na výkon.
  • Data storage - event_file (nejlepší varianta, protože jde číst Management Studiem) - soubor na disku.
  • Advanced - je možné nastavit, za jakých podmínek se trasovací informace zapisují na disk, jinak jsou v paměti.

•  Databázové přihlášení - Contained Database:

  • Server Properties - Enable Contained Databases.
  • Na dané databázi Properties - Options - Containment type - Partial.
  • New user - SQL user with password nebo Windows user.
  • Pro přihlášení je potřeba uvést databázi.
  • Pokud jsme zakázali oprávnění Connect roli Public, tak to nefunguje.

•  SQL Server Audit:

  • až od verze 2008.
  • U instance - na úrovni serveru - určíme v Security - Audit - cíl, kam se audituje.
  • V edici Standard je možné auditovat jen na úrovni serveru. V Enterprise je k dispozici Auditování i u databáze.
  • Auditovací definice jsou ale jen po skupinách. DATABASE_CHANGE_GROUP - veškeré úpravové operace databáze. DATABASE_OBJECT_GROUP - události jen databázových objektu (např. schéma). SCHEMA_OBJECT_ACCESS_GROUP - všechny přístupy k objektům schématu - tabulky, pohledy, programové prvky atd.
  • Serverová úroveň - vhodné skupiny: AUDIT_CHANGE_GROUP, FAILED_LOGIN_GROUP, SUCCESSFUL_LOGIN_GROUP, SERVER_PERMISSION_GROUP.
  • Databázová úroveň - SELECT atd..
  • Uživatelská událost pro audit EXEC sp_audit_write 50, 'TRUE', N'Nastala chyba.'.

•  Důležité úkoly údržby:

  • Test integrity databází - DBCC CHECKDB (totéž, co dělá WITH CHECKSUM pro zálohu, ale jen fyzicky), provede i kontrolu logické integrity. Minimálně 1x týdně. Online pracuje v tempdb. Ta se zvětší podle potřeby, pravděpodobně na velikost největší DB.
  • DBCC CHECKDB('database') [WITH NO_INFOMSGS| WITH ESTIMATEONLY -- velikost tempdb, kterou bude potřebovat].
  • Oprava integrity jedině restorem.
  • Údržba indexů - B list. Každá stránka indexu obsahuje ukazatel na předchozí i následující lístek indexu. Nad indexovými lístky jsou nadřazeny mezilehlé stránky, které dělají přehled nad rozsahy jednotlivých lístků. Na vrcholu je root stránka, která rozřazuje dotaz na index do mezilehlých stránek.
  • Fragmentace externí - fragmentace na disku, teoreticky minimalizována SSD a dostatkem paměti.
  • Fragmentace interní - po několika page splitech (dojde k němu při vkládání řádku do plného lístku indexu). Ve skutečnosti jsou indexem i tabulky samotné. Defragmentace je offlinová (pro daný objekt), pokud nejde o Enterprise edici.
  • SELECT object_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), NULL, NULL, NULL, 'LIMITED') -- zjistí stav fragmentace objektů
  • fragmentace 0-5 -- nic, 6-30 -- REORGANIZE (online), 31-100 -- REBUILD (náročné na výkon a čas, bulková operace).
  • ALTER INDEX [PK_tabulka]
ON [tabulka]
REBUILD
WITH (MAXDOP = 4 -- počet vláken u Enterprise, FILLFACTOR = 80 -- procentuální zaplnění lístku).
  • Aktualizace statistik:
  • SELECT * FROM sys.stats -- přehled statistik
  • UPDATE STATISTICS [tabulka]
(IX_tabulka_sloupecID)
WITH FULLSCAN. -- možnost ručně aktualizovat
  • Zálohování.
  • Maintenance Plans:
  • Méně vhodná varianta (do SQL 2016).
  • Check Database integrity.
  • Reorganize, Rebuild -- nekontroluje potřebnost provést, Update.
  • Backup (Full).
  • Maintenance Cleanup Task.
  • Ola Hallengren ola.hallengren.com - má připravené skripty pro výše uvedené práce údržby:
  • je možné stáhnout skripty s procedurami, které si můžu nainstalovat a nahradit v Maintenance Planu.

Tipy na stránky:

• Brent Ozar - SQL rádce a nástroje.
• SqlSentry - placené nástroje pro SQL.
• Ola Hallengren - předpřipravené skripty s procedurami pro údržbu SQL Serveru.
• SQLskills.com - záložka resources - whitepapers. Zde je možné si přečíst Performance tuning using wait statistics.