Optimisation Fred Pichaut Microsoft France EMEA Escalation Engineer fredepmicrosoftcom Sham UNMAR Waisso Directeur Technique shamunmarwaissocom Agenda Le support CSS ID: 648294
Download Presentation The PPT/PDF document "SQL Server 2005 Tuning /" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
SQL Server 2005 Tuning / Optimisation
Fred
Pichaut
(
Microsoft France)
EMEA Escalation Engineer
fredep@microsoft.com
Sham UNMAR
(
Waisso
)
Directeur Technique
sham.unmar@waisso.comSlide2
Agenda
Le support (CSS
)
Waisso
- SQL Server sur le terrain
Quelques
bases
Méthodologie
Performance des requêtes
Concurrence d’accès
Problématiques autour de
Tempdb
Quelques outils
Ressources
Débats et échanges Slide3
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 SupportManager Situation Critique
Ingénieur d’Escalade (CPR)
Développement de correctifs (QFE)
Client
Support
Groupe de développementSlide4
4
Nos missions couvrent :
L’administration courante des bases de données
Délégation de personnel à temps partiel ou à temps plein
Le soutien aux développements
Conception 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 terrainSlide5
5
Quelques références …
WAISSO
26 rue Pagès
92150 SURESNES
Tel: +33(0)1 71 11 30 10 Fax: +33(0)1 45 06 76 55 Mail : info@waisso.comSite : www.waisso.com
Pour déposer votre candidature : recrut@waisso.com
Stand PLA02
SQL Server sur le terrainSlide6
SQL Server sur le terrain
6
Problématiques fréquemment rencontrées
Administration :
Surveillance de la fragmentation, ré-indexation
Modèle de recouvrement, sauvegardes, antivirusArchitecture :Répartition des données sur les disquesMémoire Code applicatif, index :Amélioration du codeEtude des indexDénormalisationSlide7
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 partiel
Surveiller 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.Slide8
8
Merci pour votre attention…
26 rue Pagès
92150 SURESNES
Tel: +33(0)1 71 11 30 10 Fax: +33(0)1 45 06 76 55 Mail : info@waisso.comSite : www.waisso.com
Pour déposer votre candidature : recrut@waisso.com
Stand PLA02
SQL Server sur le terrainSlide9
Quelques bases (1/2)
L’optimiseur
Son rôle est de déterminer le chemin le moins couteux pour accéder au données
Génération de plans d’exécutions
Les indexes
Structure sur disque associée à une table qui accélère l'extraction des lignesCluster 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 colonnesCHECKSUM, fonction utilisable pour des « hash indexes ». Les indexes sont propres à chaque optimiseurSlide10
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 finaux
Elles 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 indexesSlide11
Exemple de Plan
Représentation hiérarchique
du plan après les différentes phases
Analyse de syntaxe
Algébrisations
Transformations
Simplifications
SELECT
C_CustKey
,
C_Name
,
N_Name
, Count(*)
FROM Nation INNER JOIN Customer
ON
N_Nationkey
=
C_NationKey
INNER JOIN Orders
ON
O_CustKey
=
C_Custkey
WHERE
O_OrderPriority
= ‘1-URGENT’
GROUP BY
C_CustKey
,
C_Name
,
N_Name
Group By
Filter
Join
Join
Customer
Orders
Nation
C_CustKey,
C_Name
, Aggregate[count(*)]
O_CustKey =
C_Custkey
O_OrderPriority =
1-URGENT’
N_Nationkey =
C_NationKeySlide12
PerfMon
Avg. Disc sec/Read
Avg. Disk Reads/sec
DMV’s
dm_os_wait_stats
dm_io_pendion_io_requestdm_io_virtual_file_stats
dm_exec_query_stats
Message d’erreur explicite
Page 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
bottleneck
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_info
dm_exec_query_stats
SQL Trace (Recompile)
Perfmon
DMV’s:
dm_db_file_space_usage
dm_tran_active_snapshot_database
dm_db_session_space_usage
dm_db_task_space_usage
PerfMon
:
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_stats
dm_index_usage_stats
dm_exec_*
SQL Trace/Profiler
Problème
de
performance?Slide13
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éesDatabase
Tuning Advisor (DTA) Successeur de l’Index Tuning
WizardPlus robuste, moins de restrictionsPossibilit
é de Capacity
Planing
Perfmon
Plus 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 RuntimeSlide14
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 162833SSMS Reports (en SP2 possibilité de rapports personnalisés)ReadTraceLit 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
OSTRESS
OSTRESS utilisé par CSS pour des testes et “replay”SQLIOStress\SQLIOSim
Outils de stress disque et mémoireSQLDumper, génération d’un dump à la demande
http://support.microsoft.com/kb/917825Il y a une collection de SQL Trace par default Slide15
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
DMVsMaintenant que nous l’avons identifiée…Database Tuning Advisor (DTA)Analyse du plan d’exécution (Query Plan)Aller plus loin avec SQLTraceSlide16
Performance des requêtes
DémoSlide17
Comment Influencer l’Optimiseur « HINTS »
Indicateurs sur les indexes à utiliser
Indicateurs
de jointure
“…
Ligne INNER MERGE JOIN Commande…”Ils forcent l’ordre des jointuresIndicateurs au niveau de la requêteAlgorithmes de jointure, Group By et Union.Ordre des jointuresDegrés de parallélismeRECOMPILEOPTIMIZE FORUSE PLANSlide18
Guides de Plans
Les indicateurs pour l’optimiseur sont très utiles
si l’on a accès au code des requêtes
Si non
« Plan Guides » permettent d’associer un indicateur avec le texte d’une requête.« Plan Guides » sont stockés dans la baseSlide19
Exemple de Guide de Plan
Ajout d’un indicateur de requête
Requê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_NationKey
INNER JOIN Orders
ON O_CustKey = C_Custkey
WHERE 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_NationKey
INNER JOIN Orders
ON O_CustKey = C_Custkey
WHERE O_OrderPriority = ‘1-URGENT’
GROUP BY C_CustKey, C_Name, N_Name
OPTION (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)
’Slide20
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_NationKey
INNER JOIN Orders
ON O_CustKey = C_Custkey
WHERE 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_NationKey
INNER JOIN Orders
WITH (INDEX=PK_O_ORDERKEY)
ON O_CustKey = C_Custkey
WHERE O_OrderPriority = ‘1-URGENT’
GROUP BY C_CustKey, C_Name, N_Name
OPTION (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
=…
''Slide21
Guide de Plan
DémoSlide22
Concurrence d’accès
Dé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_sessionsUtiliser « blocked process threshold » pour être averti (
sp_configure & profiler)
Autres scenarios de blocageBuffer I/O latchNon BUF latch
Article: http://support.microsoft.com/kb/822101Slide23
Concurrence d’accès
DémoSlide24
Problématiques autour de Tempdb
Tempdb
grossit
anormalement
Il y a beaucoup de chose dans TempdbUtiliser les DMV pour déterminer qui et quoiConcurrence d’accès sur TempdbAllocation de pages (Trace Flag 1118)Les tables systèmesDois-je reconfigurer?Working with tempdb in SQL Server 2005Slide25
Problématiques autour de Tempdb
DémoSlide26
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
Webcasts
SQl Days Parties 7:
Adminstration, Optimiseur & Partitionnement
TechNet
Webcast
: Performance Diagnosis in SQL Server 2005
Administrer SQL Server 2005 au quotidien - Lancement Technique Lyon
Déc
2005
TechNet
Webcast
: Troubleshooting Performance Problems in Microsoft SQL Server 2005
Et l’ultime recours
Le Support MicrosoftSlide27
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 supportSlide28
© 2007 Microsoft France
Votre potentiel, notre passion
TM