Analyse et Optimisation des Performances du moteur SQL Serveur

2. Analyse et Optimisation des Performances du moteur SQL Serveur 10 février 2011Frédéric PichautEMEA SR ESCALATION ENGINEERMicrosoft France

3. Agenda • Quelques Bases • Les indexes • Structures • Utilisation • Gestion • Les outils • Avec le produit • Exterieurs (SQLDiag, PSSDiag, SQL Nexus)

4. Quelques Bases • L’optimiseur • Son rôle est de déterminer le chemin d’accès au données • Il est basé sur la notion de coût (Cost-BasedOptimizer) • Génération de plans d’exécutions • Les indexes sont propres à chaque optimiseur • Structure sur disque associée à une table qui accélère l'extraction des lignes • Cluster • Trie et stocke les lignes de données de la table en fonction de la clé. • Non-cluster • Peuvent être définis sur une table dotée d'un indexe cluster ou non • Chaque ligne contient la clé cluster ou un localisateur de ligne (RID) • Possibilité d’indexes sur des colonnes calculées par des fonctions • CHECKSUM, fonction utilisable pour des « hash indexes ». • Possibilité d’indexes sur des colonnes incluses • 2008 - Possibilité d’indexes filtrés: • CREATE NONCLUSTERED INDEX FIProdAcc ON Produit (Nom, Prix) WHERE CatID >= 27 AND CatID <= 36; • DENALI – Indexes de type “Column Store Index (CSI)”

5. Apollo Column Store Index (CSI) • Accélération • Typiquement pour des requêtes de type Data Warehouse • Objectifs: de 100x à 1000x plus rapide • Fonctionnement en parallèle • Limitations • Pas d’Updates directe • RebuildIndex • Partition Switching • Pas de Large Data Types • Les types autres que String plus grand que 8 bytes • Numériques avec une précision au-delà 18 digits • Datetimeoffset au-delà 8 bytes • Unique Identifier • BLOBs • XML

6. Les Statistiques • Utilisées par l'optimiseur pour évaluer la sélectivité des expressions, et donc la taille des résultats intermédiaires et finaux • Sur chaque indexes, elles peuvent être: • Crées automatiquement ou manuellement • Mises à jour automatiquement ou manuellement • Mises à jour en synchrone ou en asynchrone • Basées sur un échantillonnage de valeurs ou toutes les valeurs • 2008 - Elles peuvent être filtrées: • CREATE STATISTICS FSPoids ON Produit (Poids) WHERE CatID IN (1,2,3);

7. Démo Statistiques Auto-update Filtrée Column Store Index

8. Reconstruction d’indexe • Reconstruire un indexe le supprime et crée un nouveau (online ou offline). • ALTER INDEX … REBUILD; • CREATE INDEX … DROP_EXISTING; • DésactiverNc Indexes pour économiser l’espace • FOREIGN KEY référençant l’indexes seront désactivées

9. Réorganisation d’indexe • ALTER INDEX … REORGANIZE • Toujours « Online » • Réorganise les feuilles de l’indexe en ordonnant les pages (de gauche a droite) • Les pages sont réorganisées en utilisant les pages existantes (pas d’allocation) • Le réorganisation est faite par fichier • Compacte les pages • Utilise un minimum de ressource • Les Blobs sont compactés par défaut quand un indexe Cluster est réorganisé

10. Quelles actions prendre • Utiliser la fonction : • sys.dm_db_index_physical_stats() • Regarder la valeur: • avg_fragmentation_in_percent SELECT a.index_id,name,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'Production.Product'), NULL, NULL, NULL)AS a JOIN sys.indexesAS bON a.object_id = b.object_id AND a.index_id = b.index_id; • Ne pas s’attacher aux petits indexes (mixed extents).

11. Méthodologie • L’analyse de performance est un perpétuel recommencement • Quand on pense en avoir fini, un changement survient

12. Dynamic Management Views (DMVs) Plus de 70 Toujours disponible Des rapports prédéfini Performance Dashboard (web 2008) Performance Data Colector (2008) SQL Profiler (SQL Trace) Capture des plans XML, visualisation Capture des deadlock, visualisation Export des events capturés + d’event (OLEDB, Full Text, CLR, Broker, Query Notification, Security Audit,…) + de colonnes capturées Combiné avec Perfmon DatabaseTuningAdvisor (DTA) Successeur de l’Index TuningWizard Plus robuste, moins de restrictions Possibilité de CapacityPlaning Perfmon Plus de compteurs Quelques outils Performance Data Colector Profiler + Perfmon

13. Outils à venir: XEProfiler • SQL Trace • Va disparaitre • Events Ported – EngineOnly • Intégré à Management Studio (SSMS) • Create, Alter, Stop, Start Sessions • Possibilité de voir les données sous forme de grille • Fonctions avancées • Searching • Aggregation (Sum, Min, Max, …) • Sorting • Grouping • Custom Columns (Ex: connect_options, sql_text = text) • Save and ApplyYour Favorite Display View • Export dans Excel

14. Collecte basique Utile pour comparer les performances avant et après des changements (logiciels, code, indexes, hardware,… Livré avec SQL Server /X pour uniquement un snapshot: evt/err logs, configurations Sinon, collecte snapshot + PerfMon + Profiler Peut être utilisé comme un service ou unitairement Controlé par fichiers XML<EventlogCollectorenabled="false" startup="false" shutdown="true" /> SQLDiag

15. S’appuie sur SQLDiag + Diagnostiques particuliers Exemples: DMV, clone db, msdb backup Contrôlé par les mêmes fichiers XML que SQLDiag, les Diagnostiques particuliers sont ajoutés comme: <CustomGroupname="msinfo" enabled="true" /> <CustomTaskenabled="true" groupname="MsInfo" taskname="Get MSINFO32" type="Utility" point="Startup" wait="OnlyOnShutdown" cmd="start /B /WAIT MSInfo32.exe /computer %server% /report "%output_path%%server%_MSINFO32.TXT" /categories PSSDiag

16. Démo SQL Diag & PSSDiag

17. Une capture à la fois. Eviter GUI, SQLDiag est non graphique (SQLTrace). Utiliser un ficher de sortie local avec un bon cache d’écriture, Pas de share sur le réseau.  KB307786 Ne pas utiliser un path UNC, même s’il pointe en local. Utiliser le disque disponible le plus rapide pas utilisé par SQL (ou autre). Eviter RAID-5.  Tester sur un environnement autre que production ou pas pendant heures de production. PSSDiag Best Practices I/O

18. Eviter les évènements trop fréquents comme : Object:Opened, Lock:Acquired/Released, etc... Par défaut Batch-level, pas Statement-level. Show Plan Statistics consomme beaucoup de CPU, généralement capturer ShowplanStatistics Profile et/ou Showplan XML Statistics Profile. Showplan All est utile dans le cas de requête ne se finissant pas. Les filtres réduisent la taille des fichiers et les I/O mais augmentent la consommation CPU. Filtrer sur des colonnes de type Integer (dbid, duration, etc) plutôt que sur des textes (databasename, textdata, etc) et uniquement si les filtres enlèvent >10% des évènements. Best Practices – Capture

19. Evènements qui impactent la taille de la trace:SQL:StmtCompleted and SP:StmtCompleted      Degree of Parallelism Lock:Timeout      Show Plan Statistics SQL:BatchStartingRPC:Starting Best Practices – Capture 2

20. Download depuis Codeplex.com Importer les traces SQLDiag + PerfStats Analyser vos donnée Construire ses propre rapport SQL Nexus

21. Démo SQL Diagsuite et analyse

22. Logical disk Avg disk sec/read < 10-20ms, sec/write < 3-5ms Disk reads/sec, Disk writes/sec Memory - Available MBytes MSSQL Buffer Manager - Page Life Expectancy MSSQL Databases - Active Transactions, Backup/Restore Throughput/sec, Repl. Pending Xacts, Repl. Trans. Rate, Transactions/sec MSSQL Memory Manager - Total Server Memory, Target Server Memory MSSQL Plan Cache - Cache Hit Ratio (all instances) MSSQL Wait Statistics - (all) Process - % Processor Time (all instances) Processor - % Processor Time (all instances) PerfMon – Principaux compteurs 

23. Resources • Reorganizing and Rebuilding Indexes • Compilation par lots, recompilation et mise en cache des plans dans SQL Server 2005 • Statistiques utilisées par l'optimiseur de requête dans Microsoft SQL Server 2005 • Rechercher des problèmes de délai d'E/S dans le sous-système d'E/S disque • Optimisation des performances de la base de données tempdb • Et l’ultime recours Le Support Microsoft

24. MSDN et TechNet: l’essentiel des ressources techniques à portée de clic • Portail administration et infrastructure pour informaticiens • Portail de ressources technique pour développeurs http://technet.com http://msdn.com