SQL Server verrouillage/problème de blocage
Je suis à l'aide de SQL Server 2008 Windows Server 2008 R2, toutes les sp d up.
Je suis occasionnelle problèmes avec SQL Server accroché avec l'utilisation du PROCESSEUR à 100% sur notre serveur live. Il semble que le temps d'attente sur SQL server lorsque cela se produit est donné à SOS_SCHEDULER_YIELD.
Voici la procédure Stockée qui provoque le coup. J'ai ajouté le "AVEC (NOLOCK)" pour tenter de résoudre ce qui semble être un problème de verrouillage.
ALTER PROCEDURE [dbo].[MostPopularRead]
AS
BEGIN
SET NOCOUNT ON;
SELECT
c.ForeignId , ct.ContentSource as ContentSource
, sum(ch.HitCount * hw.Weight) as Popularity
, (sum(ch.HitCount * hw.Weight) * 100) / @Total as Percent
, @Total as TotalHits
from
ContentHit ch WITH (NOLOCK)
join [Content] c WITH (NOLOCK) on ch.ContentId = c.ContentId
join HitWeight hw WITH (NOLOCK) on ch.HitWeightId = hw.HitWeightId
join ContentType ct WITH (NOLOCK) on c.ContentTypeId = ct.ContentTypeId
where
ch.CreatedDate between @Then and @Now
group by
c.ForeignId , ct.ContentSource
order by
sum(ch.HitCount * hw.HitWeightMultiplier) desc
END
La procédure stockée lit à partir de la table "ContentHit", qui est une table qui suit lorsque le contenu sur le site est cliqué (il se fait frapper assez fréquemment, rien de 4 à 20 hits une minute). De sorte que son assez clair que ce tableau est la source du problème. Il y a une procédure stockée qui est appelé à ajouter frappé les pistes à ContentHit table, c'est assez trivial, il a juste construit une chaîne de caractères à partir de la params passé de, ce qui implique quelques sélectionne à partir quelques tables de recherche, suivie par les principaux insérer:
BEGIN TRAN
insert into [ContentHit]
(ContentId, HitCount, HitWeightId, ContentHitComment)
values
(@ContentId, isnull(@HitCount,1), isnull(@HitWeightId,1), @ContentHitComment)
COMMIT TRAN
La ContentHit table a un index cluster sur l'ID de la colonne, et j'ai ajouté un autre indice sur CreatedDate depuis qui est utilisé dans le select.
Quand j'profil de la question, je vois la procédure Stockée s'exécute exactement 30 secondes, puis le SQL exception délai se produit. Si cela fait une différence de l'application web à l'aide de c'est ASP.NET et je suis en utilisant Subsonique (3) pour exécuter ces stockées procs.
Quelqu'un peut-veuillez indiquer la meilleure façon que je peux résoudre ce problème? Je ne m'inquiète pas à propos de la lecture de données incorrectes...
EDIT:
Le MostPopularRead procédure stockée est appelée très rarement - de son nom sur la page d'accueil du site, mais les résultats sont mis en cache pour une journée. Le modèle d'événements que je vois, c'est quand j'ai vider le cache, de multiples demandes pour la page d'accueil du site, et ils ont tous frappé la procédure stockée, car il n'a pas encore été mis en cache. SQL Server puis maxes, et ne peut être résolu en redémarrant le processus sql server. Quand je fais cela, habituellement, le proc va exécuter OK (à environ 200 ms) et de mettre les données dans le cache.
EDIT 2:
J'ai vérifié le plan d'exécution, et la requête est tout à fait bon. Comme je l'ai dit plus tôt, quand il fonctionne, il prend seulement environ 200ms à exécuter. J'ai ajouté MAXDOP 1 à l'instruction select pour le forcer à utiliser un seul cœur de PROCESSEUR, mais je vois encore la question. Quand je regarde les temps d'attente, je vois que XE_DISPATCHER_WAIT, ONDEMAND_TASK_QUEUE, BROKER_TRANSMITTER, KSOURCE_WAKEUP et BROKER_EVENTHANDLER prennent quantité massive de temps d'attente.
EDIT 3:
J'ai d'abord pensé que c'était lié à Subsonique, notre ORM, mais ayant changé de ADO.NET le erros est encore vivante.
- Sons assez étrange, avez-vous vérifier avec le profiler ce Subsonique n'? Est le SP appelé exactement de la même manière que vous le faites manuellement?
- Essayez de faire glisser les données à partir du contenu frapper dans une table temporaire, puis faire votre requête complète sur que de rejoindre les autres tables.
Vous devez vous connecter pour publier un commentaire.
Suppression de l'indicateur NOLOCK.
Ouvrir une requête dans SSMS, exécutez
SET STATISTICSIO ON
et exécutez la requête dans la procédure. Laissez-le terminer et poster ici le IO stats messages. Puis après les définitions de table et tous les index définis sur eux. Ensuite, quelqu'un sera en mesure de répondre à l'index appropriés dont vous avez besoin.Comme avec tous les SQL problème de performances, le texte de la requête est sans objet sans remplir de définition de schéma.
D'une estimation couvrant l'index serait:
Mise à jour
XE_DISPATCHER_WAIT
,ONDEMAND_TASK_QUEUE
,BROKER_TRANSMITTER
,KSOURCE_WAKEUP
etBROKER_EVENTHANDLER
: vous pouvez ignorer toutes ces attentes. Ils apparaissent parce qu'elles représentent fils de stationnement et en attente d'expédition XEvents, Service de Courtier ou interne SQL pool de threads éléments de travail. Comme ils passent la plupart de leur temps à l'arrêt et d'attente, ils se représentaient pas réaliste d'attendre le temps. Les ignorer.La question est susceptible de simultanéité, pas de verrouillage. SOS_SCHEDULER_YIELD se produit lorsqu'une tâche volontairement les rendements de l'ordonnanceur pour d'autres tâches à exécuter. Pendant cette attente, la tâche est en attente de son quantum d'être renouvelé.
Quelle est la fréquence de [MostPopularRead] SP appelé et combien de temps faut-il pour exécuter?
L'agrégation dans votre requête peut être plutôt gourmandes, surtout si il y a beaucoup de données et/ou l'inefficacité des indices. Donc, vous pourriez vous retrouver avec CPU élevée à la pression, fondamentalement, une demande pour le temps CPU est trop élevée.
Je considère le suivant:
Vérifier ce que les autres requêtes sont en cours d'exécution alors que le CPU est à 100% occupé? Regardez sys.dm_os_waiting_tasks, sys.dm_os_tasks, sys.dm_exec_requests.
Regarder le plan de requête de [MostPopularRead], d'essayer d'optimiser la requête. Assez souvent inefficace requête est à l'origine d'un problème de performance, et l'optimisation de la requête est beaucoup plus simple que les autres l'amélioration des performances techniques.
Si le plan de requête est parallèle et la requête est souvent appelée par plusieurs clients en même temps, en forçant un seul thread plan avec MAXDOP=1 indice peut aider (utilisation abondante de plans parallèles est généralement indiqué par SOS_SCHEDULER_YIELD et CXPACKET attend).
Aussi, jetez un oeil à ce document: Optimisation des performances avec les statistiques d'attente. Il donne un assez bon résumé des différents types d'attendre et de leur impact sur la performance.
P. S. Il est plus facile à utiliser SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED avant une requête au lieu d'ajouter (nolock) pour chaque table.
Si vous croyez
ContentHit
être la source de votre problème, vous pouvez ajouter un Index De CouverturePrendre un coup d'oeil à la Plan De Requête si vous voulez être certain que le goulot d'étranglement dans votre requête.
Par les paramètres par défaut de sql server utilise les core/cpu pour toutes les requêtes (max DoP configuration> avancé de la propriété, DoP= Degré de Parallélisme), ce qui peut conduire à 100% de CPU, même si un seul core est en fait en attente pour certains d'I/O.
Si vous recherchez le net ou sur ce site, vous trouverez des ressources à l'expliquer mieux que moi (comme la surveillance de votre I/o en dépit de vous voir un CPU problème).
Sur un serveur, on ne pouvait pas changer l'application par une mauvaise requête qui a verrouillé toutes les ressources (CPU), mais par la mise en DoP à la moitié du nombre de core nous avons réussi à éviter que le serveur "arrêté". L'effet sur les requêtes étant moins parallèle a été négligeable dans notre cas.
--
Dom
Merci à tous qui a posté, j'ai eu quelques grands SQL Server perf conseils de réglages.
À la fin, nous avons manqué de temps pour résoudre ce mystère, nous avons trouvé une façon plus efficace de collecter ces informations et de les mettre en cache dans la base de données, de sorte que cela a résolu le problème pour nous.