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.
InformationsquelleAutor Matt Roberts | 2010-06-10