SQL Server utilise soudainement seulement une petite partie de la CPU

Nous avons un server Windows 2008 R2 exécutant SQL Server 2008. Tout à coup, le process SQLServer refuse de dépasser 20% d'utilisation du processeur. À partir de la semaine dernière, lors de l'exécution d'une lourde requête contre le db, il augmenterait à 100% d'utilisation comme je l'espérais. Nous avons eu ce server pendant un certain time et il semble étrange qu'il s'agisse tout à coup de cette limite. Cette limite amène nos requêtes à prendre beaucoup plus de time qu'elles ne le feraient normalement. Personne n'a apporté (à tout le less au début) des modifications à la configuration du server.

Après un peu d'enquête, j'ai découvert la vue sys.dm_os_sys_memory. Cela montre que "la memory physique disponible est élevée" bu en même time que la memory physique disponible est 339552kb où le total est 4193848kb. Il convient de noter qu'il s'agit d'un server virtuel fonctionnant sur vmware.

Existe-t-il un paramètre quelque part avec SQL Server qui définit l'utilisation maximale du processeur? J'ai trouvé les parameters dans le gouverneur des ressources, bien que cela soit actuellement désactivé comme toujours.

Nous avons récemment commencé à utiliser Spotlight pour SQL Server par Quest Software. C'est la database de lecture située sur ce server pour une courte période ce matin, j'ai d'abord remarqué le problème peu de time après, même si je n'avais fait aucune requête avant cela, donc je ne sais pas si c'est le point où le problème a commencé, mais la database fonctionnait comme prévu le vendredi après-midi. Le journal de Windows montre que les parameters suivants ont été appliqués à SpotlightPlaybackDatabase lors de sa création.

  • 21/02/2011 08: 45: 02, spid60, Inconnu, Paramétrage de l'option de database TORN_PAGE_DETECTION sur ON pour la database SpotlightPlaybackDatabase.
  • 21/02/2011 08: 45: 02, spid60, Inconnu, Définir l'option de la database MULTI_USER sur ON pour la database SpotlightPlaybackDatabase.
  • 21/02/2011 08: 45: 02, spid60, Inconnu, Paramétrage de la database READ_WRITE sur ON pour la database SpotlightPlaybackDatabase.
  • 21/02/2011 08: 45: 02, spid60, Inconnu, Définir l'option de database AUTO_UPDATE_STATISTICS sur ON pour la database SpotlightPlaybackDatabase.
  • 21/02/2011 08: 45: 02, spid60, Inconnu, Définir l'option de la database AUTO_CREATE_STATISTICS sur ON pour la database SpotlightPlaybackDatabase.
  • 21/02/2011 08: 45: 02, spid60, Inconnu, Paramétrage de la database ANSI_WARNINGS sur OFF pour la database SpotlightPlaybackDatabase.
  • 21/02/2011 08: 45: 02, spid60, Inconnu, Définir l'option de database CONCAT_NULL_YIELDS_NULL sur ON pour la database SpotlightPlaybackDatabase.
  • 21/02/2011 08: 45: 02, spid60, Inconnu, Paramétrage de la database RECUPERATION à SIMPLE pour la database SpotlightPlaybackDatabase.
  • 21/02/2011 08: 45: 02, spid60, Inconnu, Définir l'option de la database QUOTED_IDENTIFIER sur OFF pour la database SpotlightPlaybackDatabase.
  • 21/02/2011 08: 45: 02, spid60, Inconnu, Paramétrage de la database AUTO_CLOSE sur OFF pour la database SpotlightPlaybackDatabase.

Certains de ces parameters pourraient-ils modifier les parameters appliqués à l'set du server?

Modifier n ° 1: Géré pour résoudre ce problème en redémarrant le server sql, pas sûr de savoir quel était le problème en premier lieu. Bien que le problème soit résolu, j'ai encore quelques problèmes pour régler que je ne connaissais pas auparavant.

Édition # 2: le problème est survenu. La solution était de désactiver l'Analyse de trace sur Spotlight sur SQL Server, c'est ce qui a tout traîné.

Vérifiez les tâches sys.dm_os_waiting_ et voir quelles sont les ressources d'attente. Regardez fondamentalement le wait_type et voyez ce qu'il y a là. Exécutez cette requête et publiez les résultats.

select wait_type, sum(wait_duration_ms) sum_wait_duration_ms, avg(wait_duration_ms) avg_wait_duration_ms, count(*) waits from sys.dm_os_waiting_tasks group by wait_type 

Vous pourriez souffrir d'un problème similaire à ce que j'ai parlé ce matin sur mon blog .

Vous ne pouvez pas gérer l'utilisation du processeur, mais vous pouvez gérer l'affinité CPU . C'est-à-dire que quelqu'un a-t-il limité SQL Server à l'utilisation d'une CPU unique?

Dans le même ordre d'idées, quelqu'un a-t-il changé le paramètre global maxdop ? Cela limite toute requête à une seule CPU, mais une requête unique s'exécute sur l'une des CPU disponibles

En supposant qu'il n'y ait pas eu de changement de configuration à l'affinité CPU ou MAXDOP comme mentionné par gbn, il existe plusieurs possibilités.

La première est que le plan de requête pour votre requête a changé parce que la dissortingbution des index ou des données de table sous-jacentes ont considérablement changé. Essayez d'optimiser ou de rebuild des index sur les tables sous-jacentes.

Deuxièmement, vous pouvez maintenant être lié à l'E / O, soit en lisant des données à partir de votre file de database principal, soit en travaillant dans tempdb (où SQL stockera des parties intermédiaires de la requête si elle est trop grande pour la RAM). Utiliser perfmon et surveiller avg. longueur de queue du disque. Il devrait être inférieur à votre nombre de fuseaux de disque physique dans le server. Si elle se triggers pendant votre «requête lourde» alors que la CPU rest faible, la CPU attend simplement l'IO du disque et ne peut donc pas fonctionner à 100% en faisant un travail utile. Si tel est le cas, vous avez quelques options: plus de RAM (pour réduire le besoin d'utiliser le disque), un disque plus rapide (SSD?) Ou optimisez les requêtes, les index et le schéma pour réduire les IO du disque. La dernière option peut avoir de loin le plus grand impact (améliorant littéralement les choses d'un facteur de 100 ou plus). Mais cela peut aussi être le plus difficile, selon votre structure de données et vos requêtes. Lisez les plans d'exécution SQL; acheter des livres.

Une chose que vous pouvez faire est de voir exactement ce qui se passe au process exécutant la requête. Si vous continuez à surveiller l'activité spids et à voir quel est son type d'attente le plus courant. Vous constaterez probablement qu'il existe une ressource telle que le disque io que le spid attend pour signifier que le processeur est inactif pour la requête jusqu'à ce que le disque soit lu / écrit complet.

Ce problème a été résolu en redémarrant le server SQL, même si je ne sais pas ce qui l'a provoqué en premier lieu. Merci à tous pour vos réponses.