Comment vérifier quelle procédure stockée prend le temps maximum dans le serveur sql

Je veux savoir quelles sont les différentes méthodes par lesquelles je peux suivre ma procédure stockée et requêtes SQL prennent plus de temps sur les différents composants(CPU cycle, temps de balayage, etc.) que déjà valeur de seuil prédéterminée.

Je veux qu'il soit connecté.
Chaque fois qu'un utilisateur utilise mon site et en appelant une procédure, je veux faire un journal de toutes les procédures de franchissement de mon seuil de tolérance.

Est-il possible de le faire avec des requêtes SQL ou des procédures. Nous avons des procédures pour cela.
Tout les outils SQL ou tout outil externe, peuvent être payés(avec essai) ou libre. Je veux les essayer sur ma base de données.

source d'informationauteur Shantanu Gupta

12

Vous devriez être en mesure de le faire à l'aide de Vues De Gestion Dynamique (Dmv), en particulier, vous allez probablement être plus intéressés à la exec_query_stats vue qui maintient les statistiques d'exécution sur toutes les requêtes (temps CPU, Physique /Logique lit etc...) regroupés par le plan d'exécution.

Voir aussi cet excellent article qui contient un exemple de requête pour visualiser le plan des statistiques, et va dans beaucoup plus de détails sur le sujet:
- http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/how-to-get-high-quality-information-about-query-performance.aspx
Enfin, si vous souhaitez effectuer le suivi /enregistrement trop des requêtes de longue durée, vous souhaitez peut-être envisager de laisser une trace SQL server profiler à toutes les heures, avec un filtre sur le temps d'exécution fixée à une valeur élevée (par exemple > 1000 ms). Vous pouvez soit utiliser le générateur de profils SQL server application windows, ou vous pouvez créer la trace à l'aide de T-SQL ont de se connecter à une table dans la base de données à la place:
- http://blogs.msdn.com/b/sqlsecurity/archive/2008/12/12/how-to-create-a-sql-trace-without-using-sql-profiler.aspx
Cela a l'avantage de vous dire exactement ce que la requête a pris exactement combien de temps, quand et ce que les paramètres de cette requête ont été (saint SQL Batman!)

Les implications sur les performances de l'exécution de cette trace sur chargé des bases de données est en fait très faible, je ne connais étonnamment critique des applications qui ont ces traces de l'exécution comme une question de routine afin d'être en mesure de diagnostiquer rapidement les problèmes de performances (et ça aide beaucoup). La clé est dans le choix d'un "grand" moment de l'exécution qui est assez grand pour ne pas anéantir le journal, mais assez petit pour ramasser assez longues requêtes pour être utile.

Un autre truc qui a été utilisé dans le passé lorsque vous rencontrez des problèmes de performance a été de laisser un non filtrée trace SQL server en cours d'exécution pour une courte période de temps (1 min), chargé de SQL server (il n'a vraiment étonnant que peu d'effet, vous obtenez juste inondé avec des journaux)

J'ai aussi cordialement recommander la Microsoft SQL Server internals livres sur ce sujet - il est très technique, mais son brillant, car il ne couvre pas seulement ces sortes d'outils de diagnostic, mais aussi de ce qu'ils signifie réellement
4

Si vous avez SQL 2005 + vous pouvez exécuter les rapports standard dans les rapports de gestion du Clic Droit sur la base de données dans management studio et sélectionnez l'Objet de statistiques d'exécution, - cela ne fonctionne que depuis le dernier redémarrage. Vous pouvez également interroger que l'utilisation du DMV sys.dm_exec_query_stats

Si vous êtes sur une version plus ancienne de l'utilisation de SQL SQL Server Profiler cela va vous montrer le temps d'exécution, lit, cpu, écrit et vous pouvez filtrer sur des seuils de l'un de ces.
2

Vous pouvez utiliser SQL Server Profiler:
1. De créer une nouvelle Trace.
2. Sélectionnez le serveur que vous voulez
3. Sélectionner le modèle 'vide'
4. Dans l'onglet Sélection des Événements, sélectionnez
  SP:Terminé (au titre du Stockées
  Les procédures)
5. Tour sur les colonnes que vous souhaitez
6. Dans les Filtres de Colonne, sélectionnez la Durée
  et le mettre en valeur pour " Plus de
  ou même " (prendre note des unités
  décrit ci-dessus; il s'agit soit d'
  millisecondes ou microsecondes). C'est votre "seuil"
7. Cliquez Sur 'Exécuter'
Notes:
- Ce qui exclut le temps de charge de la SP; si
  votre SPs sont de très grande taille, qui
  ajouter aux moments ci-dessus
- J'ai parfois eu des problèmes avec
  la valeur de Durée; si vous avez
  les problèmes avec elle, vous pouvez
  datediff fin/début des temps

Ce sont ceux que j'utilise habituellement. Je ne me souviens pas où je l'ai eu, mais ils travaillent un régal. Celui-ci est pour la plus longue d'exécution des requêtes:

SELECT TOP 100
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

...et celui-ci donne une liste de requêtes à l'aide de la plupart des I/O:

SELECT TOP 100
    (total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
    (total_logical_reads + total_logical_writes) AS total_IO,
    qs.execution_count AS execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_IO DESC;

Espérons que ces sont de peu d'aide!

1

Sur l'outil de côté, je vous conseille de S'enflammer pour SQL Server de Confio Logiciel. Vous pouvez télécharger une version d'essai gratuite et le prendre pour un spin. Nous avons pu l'utiliser pendant environ 2 ans maintenant et nous sommes très heureux avec les résultats. Dans notre cas, nous avons choisi plusieurs "low hanging fruits" procédures stockées qui nous optimisé pour les victoires rapides seulement dans le temps, nous avons couru la démo. C'en était assez pour nous convaincre qu'il vaut la peine d'investissement.
1

Également noter que dans SQL 2008 il y a un nouveau DMV spécifiquement pour les procédures: sys.dm_exec_procedure_stats. Ce point de vue vous donnera cumulatif de statistiques pour l'ensemble de la procédure, y compris le temps et l'UC passé à faire non de la requête travaux connexes tels que WAITFOR, les appels de fonction et logique conditionnelle.

S'il vous plaît ne soyez prudent de l'exécution de profiler des traces sur vos serveurs de production. Traçage introduit de surcharge sur le serveur, même un minimum de suivi. En fonction de la disponibilité de votre serveur, ce qui peut être perceptible. L'ajout d'un filtre pour la définition de la trace n'est pas de réduire ces coûts, SQL Server doit encore traiter chaque événement lancé pour déterminer s'il répond ou non aux critères de filtre. Le filtrage ne sert qu'à réduire la taille du fichier de trace.

Un autre outil que vous pouvez regarder dans si vous faites affaire avec SQL 2008 est le Collecteur de Données et de Gestion de l'Entrepôt de Données. C'est un outil qui est fourni avec SQL Server (sans coût supplémentaire) qui vous permettra de recueillir des statistiques sur les requêtes sur une base régulière et de les charger dans un entrepôt de données. Il vient avec construit-dans les collectionneurs et les rapports qui sont assez complètes pour la plupart des besoins de surveillance.

http://msdn.microsoft.com/en-us/library/dd939169(SQL.100).aspx

De toutes les choses mentionnées ci-dessus, je pense que votre meilleur pari est sys.dm_exec_query_stats comme il est faible surcharge, gratuit, et disponible sur SQL 2005 et SQL 2008.

De la réponse gagnante de une récente DONC, la questionce qui vous donnera le top 50 plus utilisé procs et les états dans le procs. Vous pouvez changer le TOP 50 de HAUT 1 ou tout autre numéro que vous aimeriez voir.

SELECT TOP 50 *
FROM ( SELECT COALESCE(OBJECT_NAME(s2.objectid), 'Ad-Hoc') AS ProcName
           ,execution_count
           ,s2.objectid
           ,( SELECT TOP 1 SUBSTRING(s2.text,
                                     statement_start_offset / 2 + 1,
                                     ( ( CASE WHEN statement_end_offset = -1
                                              THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))
                                                     * 2 )
                                              ELSE statement_end_offset
                                         END ) - statement_start_offset )
                                     / 2 + 1)
            ) AS sql_statement
           ,last_execution_time
        FROM sys.dm_exec_query_stats AS s1
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
     ) x
WHERE sql_statement NOT LIKE 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
    AND OBJECTPROPERTYEX(x.objectid, 'IsProcedure') = 1
    AND EXISTS ( SELECT 1
                    FROM sys.procedures s
                    WHERE s.is_ms_shipped = 0
                        AND s.name = x.ProcName )
ORDER BY execution_count DESC

Vous pourriez exécuter régulièrement pour voir les changements au fil du temps. Le mettre dans une procédure stockée et insérer les résultats dans une table de journal. Programmé un travail à exécuter la procédure stockée à intervalles fixes.

0

Si vous étiez à l'aide de SQL Server 2008, vous pouvez expérimenter avec le Gouverneur de Ressources qui permet de limiter et de surveiller CPU et de la mémoire de la charge de travail.

À des fins de journalisation, vous pouvez utiliser La Collecte De Données. Vous pouvez créer un jeu d'éléments de collecte de recueillir des instantanés à partir de toutes les Vues et les télécharger vers un entrepôt de données de performance.

Idera a des outils de cartographie que l'affichage de la consommation de ressources par requête. Je pense qu'il permet à l'utilisateur de définir le seuil et les alertes par courriel.

Vous devez vous connecter pour publier un commentaire.