#JSS2014
Les journées
SQL Server 2014
Un événement organisé par GUSS
Compteurs de performance et DMVs/DMFs ( mes favoris )
Christophe LAPORTE
#JSS2014
Faire un choix !• 200 DMVs et DMFs …
– 166 vues– 34 fonctions
• 449 compteurs de performance (sans compter les instances)– Répartis en 33 groupes– Et cela ne concerne que le SGBD …
• A raison de 5 secondes par élément on obtiendrait– Une session d’une heure …– Un bon mal de tête
#JSS2014
Agenda
SELECT Name, Comments FROM DMVs_and_DMFs_and_PerfCounters
TABLESAMPLE SYSTEM (My Favorites)
EXEC (SomeDemos) WITH RESULT SETS UNDEFINED GO 8
#JSS2014#JSS2014
Les évaluations des sessions,c’est important !!
…Et en plus on peut gagner des cadeaux
#JSS2014
Perfmon - CPU• User Mode and Privileged Mode
– Processor (_Total) \% Processor Time– Processor (_Total) \% Privileged Time– Process (sqlsrvr)\% Processor Time– Process (sqlsrvr)\% Privileged Time
• Eventuellement :– System\Context Switches /sec– System\Processor Queue Length
• VM Processor(_Total)– Effective VM Speed in MHz– Host processor speed in MHz CPU Ready …
#JSS2014
Perfmon – Bases de données• SQL Server:Databases
– Percent Log Used– Log Flush Wait Time– Log Flush Waits/Sec– Log Growths– Log Shrinks
#JSS2014
Compteurs de performance - Activité• SQL Server:SQL Statistics
– Batch Requests / Sec– SQL Compilations / Sec– SQL Recompilations / Sec
• SQL Server:Databases\Transactions per second• SQL Server:General Statistics\User Connections• SQL Server:Transactions
– Free space in TempDB (KB)– Version Store Size (KB)
• SQL Server:Locks– Lock Requests/sec
#JSS2014
Perfmon – Accès aux données• SQL Server:Access Methods
– Full Scans/Sec– Index Searches/Sec– Forwarded Records/Sec– (Page Splits/Sec)
• Attention à ce compteur …– Table Lock Escalation/sec– Workfiles created/sec
• hash operations– Wortables created/sec
• table spools, index spools
#JSS2014
Perfmon – Locks et latches• SQL Server:Locks
– Lock Requests/sec– Lock Wait Time (ms)– Lock Waits/sec– Lock Timeouts/sec– Number of Deadlocks/sec
• SQL Server:Latches– Latch Waits/sec– Avg Latch Wait Time (ms)– Total Latch Wait Time (ms)
#JSS2014
Perfmon – Buffer Manager• Memory
– Available MBytes
• SQL Server:Memory– Memory Grants Pending
• SQL Server:Buffer Manager– Page Life Expectancy
• 64GB BP & 300 secondes => 218.5MB/s !!! • PLE threshold : ((MAXBP(MB)/1024)/4)*300 ) => 4800 secs !
– Checkpoint Pages/sec– Free Pages– Free List Stalls/sec– Lazy Writes/sec– Page Reads/sec– Page Writes/sec
• VM Memory– Memory Active in MB– Memory Ballooned in MB
#JSS2014
Perfmon - Disque
• Logical Disk– Avg Disk sec/Read– Avg Disk sec/Write– % Idle Time– Avg Disk Bytes/Read– Avg Disk Bytes/Write– Disk Reads/sec (Disk Read Byte/sec)– Disk Writes/sec (Disk Write Byte/sec)
#JSS2014
DMV & Catalog View• La session porte sur les DMVs, pas le catalog view
!• Vous devez le maitriser, en particulier l’object
catalog view• Donc pour la suite vous devez maitriser
sys.databases, sys.master_files, sys.database_files, sys.tables, sys.indexes, sys.columns, sys.stats , sys.partitions, sys.procedures, sys.allocation_units, etc …
• Les DMVs et DMFs sont préfixées par sys.dm_*
#JSS2014
ClassificationCategory Description
sys.dm_exec_% Execution and Connection
sys.dm_os_% SQL OS related information
sys.dm_tran_% Transaction Management
sys.dm_io_% I/O related information
sys.dm_db_% Database scoped information
#JSS2014
DMV – Mémoire, CPU • sys.dm_os_scheduler• sys.dm_os_buffer_descriptors• sys.dm_os_performance_counters
#JSS2014
DMV – IO & Index & Statistiques• sys.dm_io_virtual_file_stats• sys.dm_io_pending_io_requests• sys.dm_db_index_operational_stats• sys.dm_db_index_usage_stats• sys.dm_db_index_physical_stats• sys.dm_db_xtp_index_stats• sys.dm_db_stats_properties
#JSS2014
DMV – Exécution• sys.dm_exec_requests• sys.dm_exec_connections• sys.dm_exec_sessions• sys.dm_exec_sql_text• sys.dm_exec_query_plan• sys.dm_tran_session_transactions• sys.dm_tran_active_transactions• sys.dm_tran_database_transactions • sys.dm_tran_locks• sys.dm_os_waiting_tasks
#JSS2014
DMV – Post-mortem• sys.dm_os_wait_stats• sys.dm_os_latch_stats• sys.dm_exec_procedure_stats• sys.dm_exec_query_stats• sys.dm_exec_trigger_stats• sys.dm_exec_cached_plans• sys.dm_db_missing_index_*
#JSS2014
Conclusion
• Compteurs pour une première analyse
• Vos compteurs absents : posez la question …
• Q&A