Analyse et optimisation des performances du moteur SQL Serveur

10 février 2011Frédéric PichautEMEA SR ESCALATION ENGINEERMicrosoft France

Quelques Bases Gestion de la mémoire avec SQL 2012ColumnStore IndexLes statistiquesGestion des indexMéthodologieLes outils Avec le produit (DTA, Xevent, XE Profiler,

SQLDiag) Extérieurs (SQLDiag, PSSDiag, SQL Nexus)

Agenda

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-Based Optimizer) 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é. Un Cluster index peut être défini non-unique mais en interne il le sera toujours.

Non-cluster Peuvent être définis sur une table dotée d'un indexe cluster ou non Chaque ligne contient la clé cluster index ou un localisateur de ligne (RID) s’il n’y a pas de

CI Possibilité d’indexes sur des colonnes calculées par des fonctions

CHECKSUM, fonction utilisable pour des « hash indexes »ALTER TABLE Product ADD cs_Pname AS CHECKSUM(Name);CREATE INDEX Pname_index ON Product (cs_Pname)

Possibilité d’indexes avec des colonnes inclusesCREATE NONCLUSTERED INDEX IncIndex ON Product (ID) INCLUDE (Prix)

Possibilité d’indexes filtrés: CREATE NONCLUSTERED INDEX FiltIndex ON Product (Name, Price) WHERE CatID >= 27 AND CatID <= 36;

SQL12 – Indexes de type “Column Store Index (CSI)”

Quelques Bases

SQL Server 2012 introduit un nouveau « page allocator » qui contrôle les demandes de « single page » et « multi page »

Le nouveau « any size page allocator » gère les demandes de 8KB et plus.

Les demandes de Multi-Page et CLR sont maintenant inclus dans l’espace mémoire contrôlé par « max server memory (MB) »max server memory

Détermine la mémoire physique max (committed memory) Le min passe de 16 MB à 64 MB (x86) ou 128 MB (X64).

L’espace « memory_to_reserve » (-g) n’inclus plus les demandes de « multi-page ». En dehors de ca, tout reste identique pour cette espace.L’option « awe enabled » n’est plus supportée.L’option « Lock Pages In Memory » est supporté pour les éditions Enterprise, Standard et Business Intelligence (pas pour les autres éditions).

Plus besoin de Hotfix, Trace Flag ou « AWE »

Gestion de la mémoire avec SQL 2012

Accélération Nouvel algorithme de l’optimiseur BATCH

Produit et interprète les batch, chacun contenant ~1000 valeurs compressées

Requêtes Data Warehouse ou jointure en étoile Utilise la compression Vertipaq Moins d’IO

Limitations Pas d’Updates directe dans la table

Rebuild Index/Partition Switching Pas de Large Data Types

Decimal > 18 digits Uniqueidentifier Binary et BLOBs

ColumnStore Index (CSI)

Traditionnellement les données sont stockées dans des pages lignes par lignesOn utilise le terme « row store » pour décrire une Heap ou un B-Tree ayant plusieurs ligne par page

Columnstore Index

…

Data Pages

Un Segment contiens les valeurs d’une colonne pour un ensemble de lignesLes segments pour un même groupe de lignes constitues un « row group »Les segments sont compressésChaque segment est stocké dans un BLOBLe segment est l’unité de transfert entre le disque et la mémoire.Estimation de la mémoire nécessaire pour créer un CSI : DOP * (nb col * 4.2M + 68.1M)+ (nb string col * 53.7M)

Columnstore Index

C3 C5

Select region, sum(sales), …

Segment

Row group

Base du TPC-DS de 1 TB 1.44 milliard de lignes dans la table de faits catalog_sales

Exemple de performance

  Total CPU time Elapsed time

No columnstore 502 sec 501 sec

Columnstore 31.0 sec 1.10 sec

Speedup 16X 455X

SELECT w_city, w_state, d_year, SUM(cs_sales_price) AS cs_sales_priceFROM warehouse, catalog_sales, date_dimWHERE w_warehouse_sk = cs_warehouse_sk and cs_sold_date_sk = d_date_sk and w_state in ('SD','OH') and d_year in (2001,2002,2003)GROUP BY w_city, w_state, d_yearORDER BY d_year, w_state, w_city;

Utilisées par l'optimiseur pour évaluer la sélectivité des expressions, et donc la taille des résultats intermédiaires et finauxSur chaque indexes, elles peuvent être: Crées/Mises à jour automatiquement ou

manuellement Mises à jour en synchrone ou en asynchrone Basées sur un échantillonnage ou toutes les

valeursElles peuvent être filtrées:

CREATE STATISTICS FSPoids ON Produit (Poids) WHERE CatID IN (1,2,3);

Elles peuvent être sur des colonnes calculées si le résultat est déterministe.sys.dm_db_stats_properties en 2008 R2 SP2 (puis 2012)

SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter FROM sys.stats stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) sp WHERE stat.object_id = object_id('FactResellerSalesPartCopy_part')

Les Statistiques

Statistiques Table compressée Index filtré

Column Store Index Update

Démo

  ALTER INDEX REBUILD CREATE INDEX WITH DROP_EXISTING

Ajour ou suppression de colonnes changement de l’ordre ou du tri

Non Oui

Options de l’indexe a positionner Oui OuiPlus d’un indexe à reconstruire en une transaction Oui Non

un indexe partitionné peut être repartitionné  Non Ouiun indexe peut être déplacé sur un autre fichier Non Oui

Demande de l’espace temporaire supplémentaire Oui Oui

Reconstruire un indexe cluster reconstruit les indexes non cluster

Non (sauf si ALL) Non (sauf si ALL)

Single index partition à reconstruire Oui Non

Reconstruire un indexe le supprime et crée un nouveau (online ou offline). ALTER INDEX … REBUILD; CREATE INDEX … DROP_EXISTING;

Désactiver Nc Indexes pour économiser l’espaceSQL 2012: REBUILD ONLINE pour les LOB (sauf TEXT/IMAGE/FILESTREAM)FOREIGN KEY référençant l’indexes seront désactivées

Reconstruction 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é

Réorganisation d’indexe

Utiliser la fonction : sys.dm_db_index_physical_stats()

Regarder la valeur:avg_fragmentation_in_percent

SELECT a.index_id, name, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'Production.Product'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Quand?

Ne pas perdre du temps sur petits indexes (mixed extents).

avg_fragmentation_in_percent Action

> 5% et < = 30% ALTER INDEX REORGANIZE

> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)

L’analyse de performance est un perpétuel recommencementQuand on pense en avoir fini, un changement survient

Méthodologie

Capture

Analyse

Reglages

Charge du système

Comment aborder une requête longue

Server Level Component Level

dm_exec_* Execution of user code and associated connections

dm_os_* Memory, locking & scheduling

dm_tran_* Transactions & isolation

dm_io_* I/O on network and disks

dm_db_* Databases and database objects

dm_repl_* Replication

dm_broker_* SQL Service Broker

dm_fts_* Full Text Search

dm_qn_* Query Notifications

dm_clr_* Common Language Runtime

Les outils StandardDynamic Management Views (DMVs)

Plus de 70 Toujours disponible Des rapports prédéfiniPerformance Dashboard (web 2008)Performance Data Colector (2008)

SQL Profiler (SQL Trace)Grand nombre d’event capturableCombiné avec PerfmonVa disparaitre

XeventUtilise la techno ETW5% de CPU pour 20000 event/secIntégré dans le moteur

Database Tuning Advisor (DTA) Successeur de l’Index Tuning WizardPlus robuste, moins de restrictionsPossibilité de Capacity Planing

PerfmonToujours plus de compteurs

Tuning des 1000 top Events du Plan CacheOption –n du TDA.EXE pour changer le nombre d’Event

SQL 2012 DTA

Introduit avec SQL 2008Événements (Event): points d’intérêt dans le flot d’exécution du code.Cibles (Target): Ou stoquer l’evenement capturé (Event file, Ring buffer,…)Actions: Collecte d’information, Stack dump, …Types: Définit les types de données utilisésPrédicats: mécanisme de filtrage, permet de diminuer davantage l’empreinte de XE sur les performancesMaps: table de correspondance clés / valeurs (genre d’EventSubClass plus poussé).

Xevent

SQL Trace Va disparaitre Events Portés – Inclus dans le moteur

Intégré à Management Studio (SSMS)Create, Alter, Stop, Start SessionsPossibilité de voir les données sous forme de grilleFonctions avancées Searching Aggregation (Sum, Min, Max, …) Sorting Grouping

Custom Columns (Ex: connect_options, sql_text = text)External Action Event: XEDEVPKG.DLL

XE Profiler

Create Start

StopDrop

Extended EventSession existe

Alter

Extended EventSession active

Alter

Drop

Problème de cardinalités

inaccurate_cardinality_estimate (debug channel)Démarrer la sessions XE et regarder « live data »Sur une requête en cours, nous n’avons pas le plan d’execution actuel mais en quelques secondes CEUtiliser le plan_handle et dm_exec_query_plan, pour trouver le plan et le node_id

S’appuie sur SQLDiag + Diagnostiques particuliersExemples: DMV, clone db, msdb backupContrôlé par les mêmes fichiers XML que SQLDiag, les diagnostiques particuliers sont ajoutés comme:

<CustomGroup name="msinfo" enabled="true" /> <CustomTask enabled="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 and Sqldiag Manager Disponible sur http://diagmanager.codeplex.com/

Pour SQL 7.0 jusqu’à SQL 2008 R2

PSSDiag

XEvent Interface Generate dump on error Fire external Action inaccurate_cardinality_estimate detection

Démo

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.  KB307786Ne 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

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 Showplan Statistics 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 (database name, textdata, etc) et uniquement si les filtres enlèvent >10% des évènements.

Best Practices – Capture

Evènements qui impactent la taille de la trace:      SQL:StmtCompleted et SP:StmtCompleted       Degree of Parallelism       Lock:Timeout      Show Plan Statistics       SQL:BatchStarting       RPC:Starting  

Best Practices – Capture 2

RML Utilities for SQL Server http://support.microsoft.com/kb/944837

ReadTrace Paramétrage des requêtes Agrégation des exécutions Analyse de performance (Top n)

Reporter OStress Agent de contrôle OStress relecture (ORCA)

SQL Nexus Download depuis http://sqlnexus.codeplex.com/ Importer les traces SQLDiag + PerfStats Analyser vos donnée Utilise SQL Server Reporting Services client-side report

viewer

RML & SQL Nexus

PSSDiag

Démo

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 

Troubleshooting Performance Problems in SQL Server 2008Reorganizing and Rebuilding IndexesStatistiques utilisées par l'optimiseur de requête dans Microsoft SQL Server 2005Rechercher des problèmes de délai d'E/S dans le sous-système d'E/S disqueOptimisation des performances de la base de données tempdbEt l’ultime recours

Resources

Pour aller plus loin…

Venez nous voir sur le stand SQL Server Retrouvez les experts Microsoft et MVP Assistez à des présentations des offres de nos

partenaires

Inscrivez-vous au « Virtual Launch Event » du 8 mars : http://aka.ms/vlefrance

Visitez notre nouveau site : http://www.microsoft.fr/sql

Evaluez dès aujourd’hui SQL Server 2012 En téléchargeant la RC0 : http://aka.ms/sql2012 En suivant nos « Virtual Labs » : http://aka.ms/sqllabs

palais des congrès Paris

7, 8 et 9 février 2012