SQL Server 2005 Tuning / Optimisation  Fred Pichaut (Microsoft France)

EMEA Escalation Engineerfredep@microsoft.com

Sham UNMAR (Waisso)

Directeur Techniquesham.unmar@waisso.com

Agenda

Le support (CSS)Waisso - SQL Server sur le terrainQuelques bases MéthodologiePerformance des requêtes Concurrence d’accèsProblématiques autour de TempdbQuelques outilsRessourcesDébats et échanges

Le support Microsoft

CPR (Critical Problem Résolution) : Équipe mondiale d'ingénieurs dont le but est de trouver des solutions aux problèmes critiques. En étroite collaboration avec les équipes de développement.

• En Europe : Environ 80 ingénieurs• En France :

• 13 ingénieurs• 7 ingénieurs d’escalade• 1 ingénieur en astreinte tous les jours 24x7

Client

Ingénieur Support (spécialiste) Responsable Technique de Compte(TAM)

Manager Support

Manager Situation Critique

Ingénieur d’Escalade (CPR)

Développement de correctifs (QFE)

Client Support Groupe de développement

4

Nos missions couvrent :

L’administration courante des bases de donnéesDélégation de personnel à temps partiel ou à temps plein

Le soutien aux développementsConception et/ou validation de modèlesAide à l’écriture et validation du code applicatif T-SQL

Les audits et l’expertiseOptimisation et TuningValidation avant Mise En ProductionCapacity PlanningOpérations ponctuelles « pompiers »

WAISSO : Notre expertise autour de SQL Server

SQL Server sur le terrain

5

Quelques références …

WAISSO26 rue Pagès

92150 SURESNES

Tel:  +33(0)1 71 11 30 10

Fax: +33(0)1 45 06 76 55

Mail : info@waisso.com

Site : www.waisso.comPour déposer votre candidature :

recrut@waisso.com

Stand PLA02

SQL Server sur le terrain

SQL Server sur le terrain 6

Problématiques fréquemment rencontrées

Administration :Surveillance de la fragmentation, ré-indexationModèle de recouvrement, sauvegardes, antivirus

Architecture :Répartition des données sur les disquesMémoire

Code applicatif, index :Amélioration du codeEtude des indexDénormalisation

7

Bonnes pratiques

SQL Server sur le terrain

Administrer sa base SQL Server comme on le ferait pour tout autre SGBDR

Penser à faire intervenir un DBA, au moins à temps partielSurveiller le système, les compteurs de performances

Relever et historiser les compteurs Windows et les traces SQL, pour vérifier leurs évolutions

Travailler sur la qualité du code et sur la pertinence des indexIl s’agit d’un levier prépondérant pour l’amélioration des performances

Ne jamais croire qu’une opération est indiscutableToujours tester la solution sur la globalité des applications impactées (réindexation, création d’un nouvel index, Service Pack…)Il n’existe pas de solution globale systématiquement efficace, il n’y a que des cas particuliers.

8

Merci pour votre attention…

26 rue Pagès92150 SURESNES

Tel:  +33(0)1 71 11 30 10

Fax: +33(0)1 45 06 76 55

Mail : info@waisso.com

Site : www.waisso.com

Pour déposer votre candidature : recrut@waisso.com

Stand PLA02

SQL Server sur le terrain

Quelques bases (1/2)L’optimiseur

Son rôle est de déterminer le chemin le moins couteux pour accéder au donnéesGénération de plans d’exécutions

Les indexesStructure 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 index cluster ou nonChaque ligne d'un index non-cluster contient la clé et un localisateur de ligne (clé de l’index cluster ou RID si pas d’index cluster)

On peut créer des indexes sur des fonctions sur des colonnes

CHECKSUM, fonction utilisable pour des « hash indexes ». Les indexes sont propres à chaque optimiseur

Quelques bases (2/2)

Les statistiques Utilisées par l'optimiseur pour évaluer la sélectivité des expressions, et donc la taille des résultats intermédiaires et finauxElles peuvent être:

Crées automatiquement ou manuellementMises à jour automatiquement ou manuellementMises à jour en synchrone ou en asynchroneBasées sur un échantillonnage de valeurs ou toutes les valeursIl y en a sur chaque indexes

Exemple de Plan

Représentation hiérarchiquedu plan après les différentes phases

Analyse de syntaxeAlgébrisationsTransformationsSimplifications

SELECT C_CustKey, C_Name, N_Name, Count(*)FROM Nation INNER JOIN Customer ON N_Nationkey = C_NationKeyINNER JOIN Orders ON O_CustKey = C_CustkeyWHERE O_OrderPriority = ‘1-URGENT’GROUP BY C_CustKey, C_Name, N_Name

Group By

Filter

Join

Join

Customer OrdersNation

C_CustKey, C_Name,

Aggregate[count(*)]

O_CustKey = C_Custkey

O_OrderPriority =1-URGENT’

N_Nationkey = C_NationKey

PerfMon • Avg. Disc sec/Read• Avg. Disk Reads/secDMV’s• dm_os_wait_stats• dm_io_pendion_io_requ

est• dm_io_virtual_file_stats• dm_exec_query_stats

Message d’erreur explicitePage life expectancy basPlus d’I/O que normalement• dm_os_memory_clerks • dm_os_memory_cache_clock_hands• dm_os_memory_cache_counters • dm_os_ring_buffers

Vérifier ce qui a changé, y remédier

Un changement?Identifier

le bottlenec

k

Comparer avec les mesures antérieures

Ressources

TempDB

Requêtes

CPU

I/O

Mémoire

Espace

DDL&

Allocation

Méthodologie

Essayer des

solutions

Recommencer le processus

System Monitor (system et SQL)• dm_os_scheduler• dm_exec_query_stats• dm_exec_query_optimizer_inf

o• dm_exec_query_statsSQL Trace (Recompile)Perfmon

DMV’s:• dm_db_file_space_usage• dm_tran_active_snapshot_data

base• dm_db_session_space_usage• dm_db_task_space_usagePerfMon: • SQL Server: Transactions object

DMV’s:• dm_os_waiting_tasks PerfMon:• Access Methods object• Workfiles Created/sec• Worktables

Created/sec• Mixed page

allocations/sec• General Statistics

object

DMV’s:• dm_os_wait_stats • dm_os_waiting_tasks• dm_tran_locks• db_index_operational_s

tats• dm_index_usage_stats• dm_exec_*SQL Trace/Profiler

Problèmede

performance?

Quelques outils (1/2)Dynamic Management Views (DMVs)

Plus de 70 Toujours disponible Des rapports prédéfini

SQL Profiler (SQL Trace)Capture des plans XML, visualisationCapture des deadlock, visualisationExport des events capturés+ d’event (OLEDB, Full Text, CLR, Broker, Query Notification, Security Audit,…)+ de colonnes capturées

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

PerfmonPlus de compteurs

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

Quelques outils (2/2)SQLdiag utilitaire de collecte d’informations

Performance logs, event logs, Profiler traces, SQL Server blocking information, SQL Server configuration information Documentation en ligner ou article 162833

SSMS Reports (en SP2 possibilité de rapports personnalisés)ReadTrace

Lit les captures SQL Trace (.trc) et produit RML(Replay Markup Language) formatFournit une analyse d’exécution des requêtesCompatible SQL Server 2000 et SQL Server 2005

OSTRESSOSTRESS utilisé par CSS pour des testes et “replay”

SQLIOStress\SQLIOSimOutils de stress disque et mémoire

SQLDumper, génération d’un dump à la demandehttp://support.microsoft.com/kb/917825

Il y a une collection de SQL Trace par default

Performance des requêtes

Le point de départ:sys.dm_exec_query_stats, profiler, SSMS rapports, …Trouver si des indexes manquent grâce aux DMVs

Maintenant que nous l’avons identifiée…

Database Tuning Advisor (DTA)Analyse du plan d’exécution (Query Plan)

Aller plus loin avec SQLTrace

Performance des requêtes

Démo

Comment Influencer l’Optimiseur « HINTS »

Indicateurs sur les indexes à utiliserIndicateurs de jointure

“…Ligne INNER MERGE JOIN Commande…”Ils forcent l’ordre des jointures

Indicateurs au niveau de la requêteAlgorithmes de jointure, Group By et Union.Ordre des jointuresDegrés de parallélismeRECOMPILEOPTIMIZE FORUSE PLAN

Guides de Plans

Les indicateurs pour l’optimiseur sont très utiles

si l’on a accès au code des requêtesSi non

« Plan Guides » permettent d’associer un indicateur avec le texte d’une requête.« Plan Guides » sont stockés dans la base

Exemple de Guide de Plan

Ajout d’un indicateur de requêteRequête avec un mauvais plan Requête modifiée ayant un bon plan

SELECT C_CustKey, C_Name, N_Name, Count(*)FROM Nation INNER JOIN Customer ON N_Nationkey = C_NationKeyINNER JOIN Orders ON O_CustKey = C_CustkeyWHERE O_OrderPriority = ‘1-URGENT’GROUP BY C_CustKey, C_Name, N_Name

SELECT C_CustKey, C_Name, N_Name, Count(*)FROM Nation INNER JOIN Customer ON N_Nationkey = C_NationKeyINNER JOIN Orders ON O_CustKey = C_CustkeyWHERE O_OrderPriority = ‘1-URGENT’GROUP BY C_CustKey, C_Name, N_NameOPTION (MAXDOP 1)

Sp_create_planguide@name = N’MonGuide1’@stmt = N’SELECT C_CustKey, C_Name…’, /* Texte original */@type = N’SQL’@module_or_batch = NULL@params = NULL@hints = N’OPTION (MAXDOP 1)’

Exemple de Guide de Plan

Ajout d’un indicateur d’index

Requête avec un mauvais plan

SELECT C_CustKey, C_Name, N_Name, Count(*)FROM Nation INNER JOIN Customer ON N_Nationkey = C_NationKeyINNER JOIN Orders ON O_CustKey = C_CustkeyWHERE O_OrderPriority = ‘1-URGENT’GROUP BY C_CustKey, C_Name, N_Name

SELECT C_CustKey, C_Name, N_Name, Count(*)FROM Nation INNER JOIN Customer ON N_Nationkey = C_NationKeyINNER JOIN Orders WITH (INDEX=PK_O_ORDERKEY)ON O_CustKey = C_CustkeyWHERE O_OrderPriority = ‘1-URGENT’GROUP BY C_CustKey, C_Name, N_NameOPTION (MAXDOP 1)

SET SHOWPLAN_XML ON

SET SHOWPLAN_XML OFF

<ShowPlanXLM xmlns=‘http://…'

Sp_create_planguide@name = N’MonGuide1’@stmt = N’SELECT C_CustKey, C_Name…’, /* Texte original */@type = N’SQL’@module_or_batch = NULL@params = NULL@hints = N’OPTION (USE PLAN ''<ShowPlanXLM xmlns=…''

Guide de Plan

Démo

Concurrence d’accèsDéterminer le bottleneck avec…

sys.dm_os_wait_statssys.dm_os_waiting_taskssys.dm_io_pending_io_requestssys.dm_io_virtual_file_stats

Déterminer qui est bloquant avec…sys.dm_exec_requestssys.dm_exec_sessions

Utiliser « blocked process threshold » pour être averti (sp_configure & profiler)Autres scenarios de blocage

Buffer I/O latchNon BUF latchArticle: http://support.microsoft.com/kb/822101

Concurrence d’accès

Démo

Problématiques autour de Tempdb

Tempdb grossit anormalementIl y a beaucoup de chose dans TempdbUtiliser les DMV pour déterminer qui et quoi

Concurrence d’accès sur TempdbAllocation de pages (Trace Flag 1118)Les tables systèmesDois-je reconfigurer?

Working with tempdb in SQL Server 2005

Problématiques autour de Tempdb

Démo

Resources

L'administration et la gestion SQL Server

Livres blancsCompilation par lots, recompilation et mise en cache des plans dans SQL Server 2005Microsoft SQL Server I/O Basics Chapter 2 (2005, 2000 sp4)Statistiques utilisées par l'optimiseur de requête dans Microsoft SQL Server 2005

WebcastsSQl Days Parties 7: Adminstration, Optimiseur & Partitionnement TechNet Webcast: Performance Diagnosis in SQL Server 2005Administrer SQL Server 2005 au quotidien - Lancement Technique Lyon Déc 2005TechNet Webcast: Troubleshooting Performance Problems in Microsoft SQL Server 2005

Et l’ultime recours

La référence technique

pour les IT Pros :technet.microsoft.com

La référence technique

pour les développeurs :

msdn.microsoft.com

S’informer - Un portail d’informations, des événements, une newsletter bimensuelle personnalisée

Se former - Des webcasts, des articles techniques, des téléchargements, des forums pour échanger avec vos pairs

Bénéficier de services - Des cursus de formations et de certifications, des offres de support technique

Visual Studio 2005 +

Abonnement MSDN Premium

Abonnement TechNet Plus :

Versions d’éval + 2 incidents support

© 2007 Microsoft France

Votre potentiel, notre passion TM