Comment résoudre les erreurs intermittentes de délai D'attente SQL

Les problèmes de performances se résument à la contention CPU, IO ou Lock. On dirait que vous avez exclu IO. Je suppose que CPU n'est pas un problème car il s'agit d'une base de données, pas d'un nombre cruncher. Donc, cela laisse un conflit de verrouillage.

Si vous pouvez exécuter un sp_who2 pendant que les requêtes expirent, vous pouvez utiliser la colonne BlkBy pour remonter à la tenue du verrou que tout le monde attend. Comme cela ne se produit que quelques fois par jour, vous pourriez avoir du mal à attraper suffisamment de données si vous exécutez manuellement, alors je vous suggère de monter un système automatisé pour vider cette sortie sur une base régulière, ou peut-être d'être déclenchée par l'application exceptions d'expiration. Vous pouvez également utiliser le moniteur D'activité pour surveiller la dégradation de la réactivité des requêtes en temps réel, comme suggéré par peer.

Une fois que vous avez trouvé la requête de longue durée et l'application qui l'exécute, vous pouvez immédiatement résoudre le domino des délais d'attente en réduisant le délai d'attente pour cette application unique ci-dessous tous les autres (en ce moment, il doit être plus long). Ensuite, vous devriez inspecter le code pour déterminer une meilleure solution. Vous pouvez réduire le temps pendant lequel le verrou est maintenu en validant la transaction plus tôt dans un sproc, ou réduire le verrou requis par la requête de lecture avec des astuces telles que NOLOCK ou UPDLOCK.

Voici un peu plus de lecture sur sp_who2: http://sqlserverplanet.com/dba/using-sp_who2/

Et astuces de requête: http://msdn.microsoft.com/en-us/library/ms181714.aspx http://msdn.microsoft.com/en-us/library/ms187373.aspx

Un peu long, mais sur un laboratoire il y a quelque temps, nous avons eu une situation où un serveur SQL semblait ne pas répondre, non pas parce que nous avions dopé le CPU ou tout ce que nous pouvions suivre dans SQL Server, il semblait opérationnel à tous les tests mais les connexions ont échoué sous une certaine charge.

Le problème s'est avéré être dû au volume de trafic contre le serveur signifiait que nous déclenchions la Protection intégrée contre les inondations windows Syn Attack dans Windows. Fâcheusement quand vous frappez cela, il n'y a pas connecté message dans windows server , ou dans SQL - vous ne voyez que les symtpoms qui ne parviennent pas à faire des connexions - c'est parce que windows ralentit l'acceptation des messages et laisse une file d'attente construire. Du point de vue de la connexion, le serveur semble ne pas répondre quand il le devrait (il ne reconnaît même pas le message arrivé)

Http://msdn.microsoft.com/en-us/library/ee377084 (v=bts.10).aspx

Faites défiler Jusqu'à SynAttackProtect et vous verrez la valeur par défaut dans Windows server 2003 sp1 à partir était d'activer cette fonctionnalité par défaut. C'est un mécanisme de protection DDOS en vigueur, et l'absence de journalisation qu'il déclenche rend incroyablement difficile de détecter quand votre serveur le fait.

Il a fallu 3 jours dans le laboratoire MS avant qu'il ne soit compris.

Vous avez mentionné 100 conenctions, nous avions une application qui se connectait constamment, lançait des requêtes puis déconnectait, elle ne maintenait pas les connexions ouvertes. Cela signifiait que nous avions plusieurs threads sur chaque machine connectiong faisant cela, 10 machines, plusieurs threads par machine , et il a été considéré comme suffisamment de connexions différentes constamment faites / abandonnées pour déclencher la défense.

Si vous êtes à ce niveau (puisque ce n'est pas un seuil clairement défini par MS) est difficile à dire.

Comme les autres affiches l'ont suggéré, il semble que vous ayez un problème de contention de verrouillage. Nous avons fait face à un problème similaire il y a quelques semaines; cependant, le nôtre était beaucoup plus intermittent, et souvent éclairci avant que nous puissions obtenir un DBA sur le serveur pour exécuter sp_who2 pour tracer le problème.

Ce que nous avons fini par faire était d'implémenter une notification par e-mail si un verrou dépassait un certain seuil. Une fois que nous avons mis cela en place, Nous avons pu identifier les processus qui étaient verrouillés, et changer le niveau d'isolement à lire non engagé le cas échéant pour résoudre le problème.

Voici un article qui donne un aperçu de la façon de configurer ce type de notification.

Si le verrouillage s'avère être le problème, et si vous ne le faites pas déjà, je suggère de regarder dans Configurer les niveaux d'isolement basés sur le versionnage de lignes .

Vous êtes sur la bonne voie avec votre traçage et votre profilage. ce que vous devez faire est de rechercher ce que les requêtes que time-out ont en commun - il est probable qu'elles frapperont toutes un petit sous-ensemble de tables ou d'index. Je soupçonne qu'une application a une mise à jour/insertion de longue durée qui affecte les requêtes sur les tables qui utilisent des index affectés par les mises à jour/insertions.

Vous devez travailler un peu en arrière-étant donné le sous-ensemble de tables que vous voyez, voir quels index sont sur ces tables. Recherchez d'autres requêtes en cours d'exécution au moment smae qui touchent ces tables / Index. Je parie que vous trouverez un petit ensemble de mises à jour/inserts faisant cela.

Ensuite, vous avez des décisions à prendre. Une option consiste à changer les astuces de verrouillage sur les requêtes qui expirent. Mais c'est une mauvaise pratique, car cela masquera le vrai problème pendant un certain temps. Pendant que vous voyez les délais d'attente disparaître pendant un certain temps, en fonction de l'indice que vous choisissez, vous pourriez vous retrouver avec des lectures sales et ensuite, les données fausses qui reviennent de ces requêtes. Cela pourrait s'avérer pire que les délais d'attente - difficile à dire.

Le meilleur pari est de savoir laquelle de vos applications soumettent la mise à jour / inserts que vous avez trouvé et creuser pour comprendre pourquoi ils prennent si longtemps.

Je vous suggère d'examiner en profondeur la fonctionnalité Dynamic Management Views de SQL Server:

Les vues et fonctions de gestion dynamiques renvoient des informations sur l'état du serveur qui peut être utilisé pour surveiller la santé d'une instance de serveur, diagnostiquer problèmes et optimiser les performances.

Cet article est un bon début avec DMVs, bien qu'il ait été écrit pour SQL 2005 (DMVs feature first appearance): dépannage des problèmes de Performance dans SQL Server 2005 , en particulier les chapitres "bloquants".

Mon expérience avec ces problèmes (pas sur SQL Server cependant) est que le multitâche excessif est souvent la cause du problème. S'il y a des données/tables similaires/connectées interrogées (presque) en même temps par de nombreuses connexions, le SGBD peut avoir du mal à garder toute l'isolation à la vérification. Ce n'est pas vraiment un problème d'utilisation du disque pour faire en sorte que certaines connexions attendent que les choses soient faites par d'autres. La synchronisation est très coûteuse en termes d'utilisation du processeur.

Les 100 connexions est beaucoup trop à mon avis. (Dans mon expérience encore) même les connexions 20 demandées par une machine peuvent être trop optimistes.

On dirait que vous avez peut-être déjà votre réponse, mais au cas où vous auriez besoin d'un autre endroit pour regarder, vous voudrez peut-être vérifier la taille et l'activité de votre temp DB. Nous avons eu un problème comme celui-ci une fois sur un site client où quelques fois par jour leur performance se dégraderait horriblement et parfois le délai d'attente. Le problème s'est avéré être une application distincte qui bousculait la base de données temp tellement qu'elle affectait les performances globales du serveur.

Bonne chance avec le dépannage continu!

J'ai vu des problèmes similaires se produire si anti-virus a été installé sur le serveur SQL. Les fonctionnalités de mise à jour automatique de L'AV synchronisaient le serveur et ne permettaient pas assez de CPU pour SQL Server.

Aussi, Avez-vous mis une petite application sur le serveur SQL lui-même qui vérifie que les connexions peuvent être faites ou exécute SQL très basique comme "SELECT GETDATE();"? Cela éliminerait les possibilités de réseau.

Puisque je fais du dépannage tous les jours dans le cadre de mon travail, voici ce que je voudrais faire:

1. Comme il S'agit de SQL Server 2008 R2, vous pouvez exécuter SQLDiag qui fait partie du produit. Vous pouvez consulter des livres en ligne pour plus de détails. En bref, capturer trace Côté Serveur et Script bloqueur.

2. Une fois la trace capturée, recherchez l'événement "Attention". Ce serait le spid qui a reçu l'erreur. Si vous filtrez par SPID, vous verrez RPC: événement terminé avant "Attention". Vérifier le temps là-bas. Est-ce temps 30 secondes? Si oui, le client a attendu 30 secondes pour obtenir la réponse de SQL et a été "temporisé" [ceci est le paramètre client car SQL ne s'arrêterait jamais et la connexion]

3. Maintenant, vérifiez si la requête qui était en cours d'exécution devrait vraiment prendre 30 secondes?

4. Si oui, réglez la requête ou augmentez le paramètre timeout du client.

5. Si non cette requête doit attendre certaines ressources (bloqué)

6. À ce stade, revenez au Script bloqueur et vérifiez le délai dans lequel" Attention " est venue

Ci-dessus suppose que le problème est avec SQL Server non lié au réseau!

Le problème est dû à une mauvaise requête le temps d'exécution de la requête prend plus de 60 secondes ou un verrou sur la Table

Le problème semble qu'un blocage se produise; nous avons des requêtes qui bloquent les requêtes à terminer à temps. Le délai d'attente par défaut pour une requête est de 60 secondes et au-delà, nous aurons L'Exception SQLException pour le délai d'attente.

Veuillez vérifier les journaux SQL Server pour les blocages. L'autre façon de résoudre le problème à augmenter le Délai d'attente sur L'objet de commande (solution temporaire).

Ces serveurs sont-ils virtualisés? Sur un autre post, j'ai lu à propos D'un serveur SQL fonctionnant parfois très lentement en raison d'un manque de mémoire suffisante. Ceci à son tour a été causé par un ballon de mémoire que le virtualizer a utilisé pour limiter la quantité de mémoire utilisée par ce serveur virtuel. C'était difficile à trouver car la pression sur la mémoire physique n'avait rien à voir avec le serveur SQL lui-même.

Une autre cause fréquente de dégradation temporaire des performances peut être un scanner de virus. Lorsque une nouvelle définition de virus est installée, tous les autres processus souffriront et fonctionneront très lentement. Découvrez tout autre processus de mise à jour automatique, cela pourrait également prendre beaucoup de ressources de façon inattendue. Bonne chance avec elle!

Nous avons expérimenté cela avec SQL Server 2012 / SP3, lors de l'exécution d'une requête via un objet SqlCommand à partir d'une application C#. La commande était une simple invocation d'une procédure stockée ayant un paramètre de table; nous passions une liste d'environ 300 entiers. La procédure à son tour appelé trois fonctions définies par l'utilisateur et passé la table en tant que paramètre à chacun d'eux. Le CommandTimeout a été réglé sur 90 secondes.

Lors de l'exécution précise du même proc stocké avec le même argument de dans SQL Server Management Studio, la requête s'est exécutée en 15 secondes. Mais lors de l'exécution de notre application en utilisant la configuration ci-dessus, le SqlCommand a expiré. Le même SqlCommand (avec des données différentes mais comparables) s'exécutait avec succès depuis des semaines, mais maintenant il a échoué avec n'importe quel argument de table contenant plus de 20 entiers. Nous avons fait une trace et découvert que lorsqu'elle est exécutée à partir de L'objet SqlCommand, la base de données a passé les 90 secondes entières à acquérir des verrous et invoquait la procédure seulement à peu près au moment de l'expiration du délai. Nous avons changé le temps CommandTimeout, et peu importe le temps que nous avons sélectionné, le proc stocké ne serait invoqué qu'à la toute fin de cette période. Nous supposons donc que SQL Server acquit indéfiniment les mêmes verrous encore et encore, et que seul le délai d'expiration de L'objet de commande a provoqué L'arrêt de SQL Server sa boucle infinie et l'exécution de la requête, moment où il était trop tard pour réussir. Une simulation de ce même processus sur un serveur similaire en utilisant des données similaires ne présentaient pas un tel problème. Notre solution était de redémarrer l'ensemble du serveur de base de données, après quoi le problème a disparu.

Il semble donc qu'il y ait un problème dans SQL Server dans lequel une ressource est cumulée et jamais libérée. Finalement, lors de la connexion via un SqlConnection et de L'exécution D'un SqlCommand impliquant un paramètre de table, SQL Server passe dans une boucle infinie en acquérant des verrous. La boucle est terminée par le délai D'attente de L'objet SqlCommand. Le la solution est de redémarrer, apparemment restaurer (temporaire?) sanity à SQL Server.

J'ai eu un problème similaire à celui-ci et j'ai découvert que c'était dû à un paramètre. NET framework par défaut

Sqlcommand.Délai d'attente

Http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=VS.100).aspx

La valeur par défaut est de 30 secondes comme indiqué dans L'url ci-dessus par Microsoft, essayez de définir ce nombre à un nombre plus élevé de secondes ou peut-être -1 avant d'ouvrir la connexion pour voir si cela résout le problème.

C'est peut-être un paramètre dans votre Web.config ou app.les fichiers de configuration ou sur vous application serveur web / fichiers de configuration.