Les Webcasts

1. Les Webcasts Groupe des Utilisateurs SQL Server Juin 2013 – Tempdb David Baffaleuf– CAPDATA MVP SQL Server

2. David Baffaleuf http://blog.capdata.fr @dbaffaleuf • Management d’infrastructures IT hétérogènes • www.osmozium.com • Support Management • Technical Management • Data Management • Production Management • Leader SGBD reconnu en France • www.capdata.fr • Conseil • Service • Formation • DBA à distance

3. 30 ‘ chrono Toutes les questions que vous vous posez sur tempdb … Et aussi celles que vous ne vous posez pas ! Démos !

4. Qu’est-ce qu’elle a de plus que les autres ??? Worktables, jointures hash, tris, version store … #temp, ##temp, variables table, TVF, TVP… Stockage implicite Non persistance Logging Allocation Stockage explicite Partagée Caching Recovery SPOF

5. Comment est-elle créée ? sqlservr –f (minimal start) Ouverture master Recovery master Ouverture model Recovery model Création tempdev.mdf (4-5M) Import des extents de model Extension taille tempdev.mdf Création tempdb Création templog.ldf Création des autres fichiers Création tempdev.mdf (4-5M) …dans le répertoire par défaut Création journal 516096 bytes • Instant File Initialization • Pas de model, pas de tempdb • Si le fichier tempdev / templog n’existe pas ?

6. Qu’est-ce qu’elle stocke ? Stockage explicite : • Tables, indexes physiques, LOBs… (attention non persistées !!) • Tables temporaires (créées par utilisateur / par featureDbmail, UCP, DataCollector…) • Variables tables, TVF, TVP. Mais aussi stockage implicite: • Tris ORDER BY • Checkdbfacts • Workfiles (Hash Join) • Worktables (spools, features…) • Tables internes trigger • Online index operations • Version store…

7. Tables temporaires 1/2 Nommage: • #temp__________________________________________14563 • ##temp Allocation: • Extents mixtes d’abord (sauf si –T1118) • IAM toujours en extent mixte • Extents uniformes si taille > 8 pages Scope/durée de vie: • session / procédure (#) • instance (##).

8. Tables temporaires 2/2 [ Caching ] TF1106 Nouveau RB: RING_BUFFER_SPACEMGR_TRACE #temp PLAN PS SELECT INTO #temp … DEMO Mécanismes de cache particuliers: • Cachestore: CACHESTORE_MEMOBJECTS • Tables systèmes. • Seulement dans procédures, fonctions, triggers. • 2 pages par #temp < 8Mb (DATA + IAM) • Pas de DDL après la création de la table #temp(sauf DROP TABLE) • Pas de contraintes nommées (index uniques / clustered supportés mais leaf pages seulement, et 2 pages pour NCI)

9. Tables temporaires vs variables tables ? Pas stockées en mémoire mais bien sur disque !! SELECT INTO non autorisé Scope = batch ou procédure (pas session) Mise en cache / suppression différée = temp tables. Pas d’index Pas de statistiques Pas de rollback (transactions implicites sur les VT) TVF = variable table TVP = variable table

10. Worktables sys.dm_db_%%_space_usage Utilisées par certaines features(XML, Service Broker, CHECKDB,…) Utilisées par certains opérateurs : • Index / Table (Eager) Spools. • Exchange Spill. • Merge Joins Curseurs, variables type (max). Créées par le code de l’engine, donc pas de métadonnées dessus, pas d’accès aux tables systèmes. Object_id < 0 Mixed / Uniform extents Seule l’allocation est loggée !! Espace tracé dans les DMVs (internal_object_reserved_page_count)

11. Workfiles Utilisés uniquement par l’opérateur Hash Join(recursion). Pas de correspondance dans le buffer pool (BUF). Espace tracé dans les DMVs (internal_object_reserved_page_count)… …mais pas dans sys.dm_os_buffer_descriptors. Toujours en extents uniformes. Aucunejournalisation.

12. Version Store Utilisé pour RCSI / Snapshot Isolation. Online index rebuild, Tables internes des triggers MARS ?... Aucune journalisation de l’allocation dans le version store. Allocation tracée séparément de internaldans sys.dm_db_file_space_usage (version_store_reserved_page_count). Non tracée dans %session% ou %task% Trace séparée dans sys.dm_tran_version_store. Désallocation asynchrone (Garbage Collection)

13. CHECKDB et Tempdb ? Pas de databasesnapshotdans tempdb pour CHECKDB !! CHECKDB peut utiliser tempdb pour stocker les facts: • Tracer l’allocation des objets à contrôler durant le CHECKDB • Suivre les liens précédent / suivant dans le chaînage • Suivre les pointeurs forwarding / forwarded records • Par type d’allocation: LOB, B-TREE, FILESTREAM, IAM, … Normalement stockés en mémoire, et dans des worktables si plus assez de place. 1 fact ~= 1 ligne de 5 colonnes dans une worktable. DBCC CHECKDB (bigDB) WITH ESTIMATE_ONLY

14. Comment dimensionner Tempdb ? Perfmon Volume Maintenance Tasks Ne pas laisser la taille par défaut. Initialisation instantanée !! DBCC CHECKDB WITH ESTIMATE_ONLY est un minimum. Monitorer la taille, réévaluer la taille initiale au besoin. Pas d’autogrow ! SIZE = MAXSIZE. Pour réduire: • v > =2008 : ALTER DATABASE TEMPDB MODIFY FILE … + stop / start • V < 2008: net start MSSQLSERVER /f

15. GAM SGAM Problèmes liés à l’allocation PFS Contention sur PFS/GAM/SGAM • Création / suppression massive de tables temporaires (proc / session) • Création / suppression d’objets internes plus incontrôlable encore. • Impliquent des modifications dans ces 3 pages PFS, GAM, SGAM. • Impliquent des modifications dans les tables systèmes (sysschobjs, sysallocunits, syscolpars, sysrscols…) Ces pages sont protégées par des latches • Chaque accès en lecture / écriture nécessite d’obtenir un latch (SH/EX). • On constate des attentes globales sur PAGELATCH_SH et PAGELATCH_EX • Sur les ressources types : PFS (2:1:1), GAM (2:1:2) et SGAM (2:1:3), et parfois des pages appartenant à des tables systèmes et les pages IAM. Les mécanismes de caching permettent de réduire cette contention • Mais ne fonctionnent que pour les tables temporaires dans des procédures, fonctions ou triggers.

16. Combien de fichiers de données 1/2 W W OK OK OK W OK OK W W

17. Combien de fichiers de données 2/2 Bonne pratique = 1 fichier de même taille par CPU. Observer la contention avant de multiplier les fichiers. Attentes de type PAGELATCH_SH / PAGELATCH_EX sur : • 2:1:1 PFS (=Page Free Space) • 2:1:2 GAM (=Global Allocation Map) • 2:1:3SGAM (=Shared GAM) Ajouter des fichiers additionnels au besoin Réévaluer la contention… … jusqu’à disparition de la contention. …et si le caching est insuffisant: -T1118 pour tables < 8 pages et contention SGAM.

18. ProportionnalFill / Round Robin Espace Libre Espace Libre Espace Libre Espace Libre • Proportion réévaluée: • Au restart de l’instance (lorsque les fichiers sont créés) • Lors d’un ajout / retrait de fichier • Toutes les 8192 allocations d’extents (mixtes ou uniformes) • Attention –T1117 s’applique à toutes les bases, pas seulement tempdb.

19. Combien de journaux de transactions ? 1

20. Tempdb sur des disques dédiés ? Les avantages: • Isoler les fluctuations: moins d’impacts sur les autres bases. • SSD Les inconvénients: • Moins de disques disponibles en tout. • Lissage plus difficile • Précos SQLCAT 10Tb range.

21. Logging & recovery ? -- a=1 update #t set a=2 update base.dbo.t set a=2 #t t Images après pour INSERT / UPDATE non loggées pour les heaps (tables tempo). Journal de transactions utilisé uniquement pour les ROLLBACKS. Pas de REDO. Pas de writeaheadlogging=> lazycommits Auto Checkpoint exécuté seulement lorsque le journal est plein à 70% Attention si CHECKPOINT manuel dans tempdb Internalobjects (sorts, worktables) => seule l’allocation est loggée. Parfois même rien n’est loggé !! (workfiles, version store)

22. Mesurer la contention • DMVs: • tempdb.sys.dm_db_file_space_usage • tempdb.sys.dm_db_session_space_usage • tempdb.sys.dm_db_task_space_usage • sys.dm_os_wait_stats • sys.dm_os_waiting_tasks DEMO Perfmon: • TempTables CreationRate • WorkfilesCreated/sec • WorktablesCreated/sec • Pages Allocated/sec • Free Space in tempdb (KB) • Version Store Size (KB) SQL Trace / XEvents: • Hash Warning / hash_warning • Sort Warning / sort_warning • Exchange Spill Event / exchange_spill

23. Des questions (pas dans la liste) ?

24. Les Webcasts Groupe des Utilisateurs SQL Server GUSS.fr