le support (css) waisso - sql server sur le terrain quelques bases méthodologie performance des...
TRANSCRIPT
SQL Server 2005 Tuning / Optimisation Fred Pichaut (Microsoft France)
EMEA Escalation [email protected]
Sham UNMAR (Waisso)
Directeur [email protected]
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 : [email protected]
Site : www.waisso.comPour déposer votre candidature :
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 : [email protected]
Site : www.waisso.com
Pour déposer votre candidature : [email protected]
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