SQL Server: Plonger au cœur des problèmes de performances
Il existe plusieurs tactiques pour déterminer ce qui cause la plupart du temps attente dans SQL Server, donc vous pouvez diriger votre réglage des performances.
Extrait deSQL Server DMV Starter Pack," publié par Red Gate Books (2010).
Glenn Berry, Louis Davidson et Tim Ford
Let's get droit jusqu'au niveau de l'OS. Nous regardons les threads de travail qui exécutent les tâches requises par nos transactions. Les objets de gestion dynamique (OGD) dans cette catégorie — tous qui commencent avec le nom de sys.dm_os_—provide extrêmement détaillé d'informations sur la façon dont SQL Server interagit avec le système d'exploitation et le matériel. Vous pouvez utiliser ces OMD pour obtenir des réponses à toutes sortes de questions de performance :
- Le wait_stats DMO vous indique quels genres de choses attendent sur les threads du système d'exploitation du SQL Server .
- Performance_counters vous indique les valeurs de compteur de performance SQL Server et comment elles sont décodées.
- Ring_buffers, os_schedulers ou wait_stats vous signalera une préoccupation de l'utilisation de l'UC.
- Sys_info vous donnera les caractéristiques de la machine sur laquelle SQL Server est en cours d'exécution.
- Sys_memory ou process_memory vous dira comment est utilisé votre mémoire dans son ensemble.
- Memory_cache_counters ou buffer_descriptors vous dire comment la mémoire cache est utilisée.
Tous ces requêtes fonctionnent avec SQL Server 2005, 2008 et 2008 R2. Ils également tous requièrent l'autorisation View Server State. Remarque Les objets, collectivement, devraient être appelés DMOs. Toutefois, à l'aide de DMOs tend à provoquer une certaine confusion avec l'absolument aucun rapport avec « Distributed Management Objects, » n'est encore assez fréquent pour les administrateurs de base de données désigner DMOs collectivement comme « Les vues de gestion dynamique ou DMV. »
SQL Server attend
La DMV plus importante dans la catégorie système d'exploitation est sans doute sys.dm_os_wait_stats. Chaque fois qu'une session doit attendre pour une raison quelconque avant de poursuivront les travaux demandés, SQL Server enregistre la durée attendu et la ressource pour laquelle SQL Server est en attente. L'expose DMV sys.dm_os_wait_stats ces attendent statistiques, agrégés à travers toutes les ID de session, pour vous donner une évaluation Sommaire d'où l'attend majeur est sur une instance donnée.
Ce même DMV expose également les compteurs de performances (PerfMon), qui fournissent des mesures spécifiques-utilisation des ressources (taux de transfert de disque, temps processeur consommé et ainsi de suite). En corrélant les statistiques d'attente avec des mesures de ressource, vous pouvez rapidement localiser les ressources plus attaquées sur votre système et mettre en évidence les goulots d'étranglement potentiels.
Files d'attente et attend
À l'aide de files d'attente et l'attente est une excellente base pour l'optimisation des performances, comme expliqué dans le livre blanc,SQL Server 2005 attend et met en file d'attente," par Tom Davidson. Essentiellement, chaque requête de SQL Server entraîne un certain nombre de "tâches de travailleur" être initié. Un planificateur SQL Server assigne chaque tâche à un thread de travail. Il n'y a normalement un planificateur SQL OS par UC, et qu'une seule session par planificateur peut exécuter à tout moment.
C'est le travail du planificateur pour répartir la charge de travail entre les threads de travail disponibles. Si le thread de travail de la session est en cours d'exécution sur le processeur, le statut de la session s'exécutera, comme exposé par la colonne État de sys.dm_exec_requests DMV.
Si un thread est prêt à partir, mais le planificateur qui lui est attribué actuellement a une autre session en cours d'exécution, il sera placé dans la file d'attente « praticable ». Cela signifie tout simplement que c'est dans la file d'attente pour obtenir sur le processeur. C'est ce qu'on appelle une attente du signal.
Signal attend
Le délai d'attente de signal est exposé par la colonne signal_wait_time_ms. Il s'agit uniquement de délai d'attente de CPU. Si une session est en attente pour une autre ressource soit disponible, telle qu'une page verrouillée, ou si une session doit effectuer e/s, puis il est déplacé vers la liste d'attente. Il s'agit d'une attente de ressources et de statut de l'attente de la session sera enregistrée comme « suspendue ». La raison de l'attente est enregistrée et exposée dans la colonne wait_type de la DMV de sys.dm_os_wait_stats.
L'attente de temps total passé est exposée par la colonne wait_time_ms, donc vous pouvez calculer le temps d'attente de ressources, comme suit :
Ressource attend = Total waits – attend de Signal (ou (wait_time_ms) - (signal_wait_time_ms))
Signal attend est incontournables dans des transactions en ligne (OLTP) systèmes, de traitement, car elles sont constituées d'un grand nombre de transactions courtes. La métrique clé, en ce qui concerne la pression potentielle de CPU, est l'attente du signal en pourcentage de l'attend total.
Un pourcentage élevé de signal est un signe de la pression de la CPU. « High » se réfère souvent à plus de 25 %, mais cela dépend de votre système. Une valeur supérieure à 10 à 15 % peut aussi être un signe inquiétant. Dans l'ensemble, les statistiques d'attente sont un moyen efficace pour diagnostiquer les temps de réponse de votre système. En termes très simples, soit de travail ou vous attendre.
Si les délais d'intervention sont lents et vous trouverez pas importante attend, ou principalement des attentes de signal, vous savez que vous devez mettre l'accent sur la CPU. Si vous trouvez le temps de réponse est principalement composée de temps passé à attendre des autres ressources (telles que le réseau, e/s et ainsi de suite), alors vous savez que vous devez concentrer vos efforts de syntonisation sur ces ressources.
Profilage des performances
Notre premier script dans la catégorie OS utilise le sys.dm_os_wait_stats DMV, qui renvoie des informations sur toutes les attentes rencontrées par les threads exécutés. Vous pouvez utiliser cette vue agrégée pour diagnostiquer les problèmes de performances avec SQL Server global et avec des lots et requêtes spécifiques.
Cette requête simple calcule signal attend et attend de ressources sous forme de pourcentage de la période d'attente globale, afin de diagnostiquer la pression potentielle de CPU :
-- Total waits are wait_time_ms (high signal waits indicate CPU pressure) SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] , CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]FROM sys.dm_os_wait_stats ;
Ceci est utile pour confirmer la pression de la CPU. Parce que les attentes de signal sont temps passé à attendre pour un processeur pour un thread, si vous enregistrez le signal total attend au-dessus de service environ 10 à 15 pour cent, c'est un bon indicateur de la pression de la CPU. Ces attendent ses stats sont cumulatifs depuis SQL Server a été redémarré dernière, vous avez donc besoin de connaître votre ligne de base de la valeur en attente de signal et regarder la tendance au fil du temps.
Vous pouvez effacer manuellement sur les statistiques d'attente, sans avoir à redémarrer le serveur, en publiant une cohérence de base de données contrôle commande SQLPERF (DBCC), comme suit :
-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;
Si votre instance de SQL Server a fonctionné pour un bon moment et vous apporter un changement important, comme l'ajout d'un nouvel indice important, vous devriez considérer les statistiques d'attente vieux de compensation. Dans le cas contraire, les statistiques d'attente cumulatif vieux masquera quel que soit votre changement impact sur les temps d'attente.
Notre deuxième exemple de script (voir Figure 1) montre comment l'utilisation de la sys.dm_os_wait_stats DMV vous aidera à déterminer les ressources pour lesquelles SQL Server consacre le plus de temps à attendre.
Figure 1 ce script génère un rapport sur les principales causes de waits.
-- Isolate top waits for server instance since last restart -- or statistics clear WITH Waits AS ( SELECT wait_type , wait_time_ms / 1000. AS wait_time_s , 100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct , ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN' ) ) SELECT W1.wait_type , CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s , CAST(W1.pct AS DECIMAL(12, 2)) AS pct , CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn , W1.wait_type , W1.wait_time_s , W1.pct HAVING SUM(W2.pct) - W1.pct < 95 ; -- percentage threshold
Le script dans Figure 1 vous aide à localiser les goulots d'étranglement plus grands au niveau de l'instance. Cela vous aide à concentrer vos efforts de syntonisation sur le type de problème particulier. Par exemple, si haut cumulatif attendre types sont disque I/O-liées, alors vous pouvez enquêter sur ce problème en utilisant des disques requêtes DMV et compteurs PerfMon.
Compteurs de performance
La DMV qui expose les compteurs PerfMon est sys.dm_os_performance_counters. Cette propriété renvoie une ligne pour chaque compteur de performance géré par le serveur. Il s'agit d'un utile DMV, mais il peut être frustrant de travailler avec.
Selon la valeur de cntr_type pour une ligne donnée, vous devrez creuser profond pour obtenir des renseignements utiles dans cette DMV. Il s'agit d'un remplacement pour le vieux sys.sysperfinfo SQL Server 2000.
Le script dans Figure 2 vous aide à étudier des conditions inhabituelles, remplir votre journal des transactions. Elle retourne le mode de récupération, Journal réutilisation attente description, taille du journal des transactions, espace de journal utilisée, pourcentage de journal utilisé, niveau de compatibilité et page vérifient option pour chaque base de données sur l'instance actuelle du SQL Server .
La figure 2 Déterminez ce qui se remplit le journal des transactions à l'aide de ce script.
-- Recovery model, log reuse wait description, log file size, -- log usage size and compatibility level for all databases on instance SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] , db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] , lu.cntr_value AS [Log Used (KB)] , CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] , db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]FROM sys.databases AS db INNER JOIN sys.dm_os_performance_counters AS lu ON db. name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls ON db. name = ls.instance_nameWHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’ AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;
La requête en Figure 2 est utile pour l'évaluation d'un serveur de base de données inconnues. Il est aussi généralement plus utile dans une perspective de surveillance. Par exemple, si votre réutilisation journal attendre description est quelque chose d'inhabituel comme ACTIVE_TRANSACTION et votre journal des transactions est plein à 85 %, puis il devrait y avoir quelques sonnettes d'alarme aller au large.
En utilisant ce type de surveillance est extrêmement utile pour identifier les sources et les causes d'attentes et d'autres problèmes de performances. Ce type d'enquête peut vous aider à mieux diriger vos efforts d'optimisation des performances au bon endroit.
.png)
Glenn Berry fonctionne comme un architecte de base de données chez NewsGator Technologies Inc. à Denver, au Colorado Il est un SQL Server MVP et a toute une collection de certifications Microsoft, y compris les MCITP, MCDBA, MCSE, MCSD, MCAD et SCTM, qui prouve qu'il aime à passer des tests.
.png)
Louis Davidson a été dans le secteur informatique depuis 16 ans comme architecte et développeur de base de données. Il a été un SQL Server Microsoft MVP pour six ans et a écrit quatre livres sur la conception de base de données. Actuellement, il est l'architecte de données et parfois DBA pour le Christian Broadcasting Network, soutien des bureaux à Virginia Beach, Virginie et Nashville, au Tennessee.
.png)
Timothy Ford est un SQL Server MVP et a travaillé avec SQL Server depuis plus de 10 ans. Il est le DBA primaire et expert en la matière pour la plate-forme de SQL Server pour la santé du spectre. Il a été écrit sur la technologie depuis 2007 pour une variété de sites Web et maintient son propre blog à thesqlagentman.com, couvrant SQL comme sujets de développement ainsi que télétravail et professionnel.
En savoir plus sur «SQL Server DMV Starter Pack » à rouge-gate.com.