none
Sql Server 2005 - Temps de traitement très variables RRS feed

  • Question

  • Bonjour,

    Je travaille sur un environnement Sql Server 2005 qui sert de datawarehouse et j'ai quelques soucis que je n'arrive pas encore a expliquer, peut-être pourrez-vous m'aider !

    Sur mon environnement d'intégration j'ai un job SQL qui tourne de nuit, j'ai une étape qui lance une procédure stockée qui a mit 50 secondes hier et plus de 5h aujourd'hui alors que l'étape de la procédure est un insert assez simple de type :

    INSERT INTO CONTRAT_NON_EXISTANT
    SELECT DISTINCT a.NUMERO_CONTRAT, a.NOM_FICHIER, getdate()
    FROM SERVICE_CONTRAT a
    LEFT JOIN CONTRAT b
     ON a.NUMERO_CONTRAT = b.NUMERO_CONTRAT
    WHERE b.NUMERO_CONTRAT is NULL

    Sachant que ma table SERVICE_CONTRAT est très petite (~800 lignes) et ma table CONTRAT est grosse (~15 millions de lignes) avec un index sur ma PK (qui n'est pas le champ de ma jointure).

    L'insert ne rentre aucune lignes puisque la jointure passe a chaque fois en ce moment, et j'envisage donc :

    - Soit un lock sur ma table CONTRAT mais je ne vois pas pourquoi sachant que je suis seul a travailler sur l'intégration en ce moment et que le script a tourné entre 1h du matin et 6h du matin...

    - Une transaction non terminé d'une étape antérieure, mais je ne vois pas pourquoi non plus, sachant que la veille tout a tourné parfaitement.

    - Un problème avec ma base tempdb, et alors là je ne sais pas trop les actions a faire...

    J'ai un problème similaire sur mon environnement de production, mais pas sur le même job. Le problème intervient sur une partie d'une procédure stockée, dont l'étape est un DELETE de type :

    DELETE FROM CONSO
    WHERE DATE_CONSO > dateadd(day, -2, getdate())

    Ma table CONSO contient une volumétrie très importante de données et possède un index (sur deux autres champs que DATE_CONSO).

    Le delete tourne généralement en 40 minutes mais il arrive (une ou deux fois par mois) qu'il mette plus de 5h.

    Si vous avez des idées... (mon espace dique insuffisant ? tempdb ? autres ? ).

    Cordialement,

    Nicolas.


    vendredi 3 octobre 2014 09:11

Toutes les réponses

  • Hello,

    Il faut voir quelles sont les attentes sur ces traitements lorsqu'ils durent plus que la moyenne habituelle.

    Deux façons:

    - La brute de fonderie: réinitialiser les statistiques d'attente avant le traitement, et faire une capture après le traitement pour voir quels sont les postes d'attente prédominants. Avantage: facile à mettre en place, inconvénient: la mesure est instance-wide, c'est à dire qu'elle ne concernera pas que le traitement en question mais tout ce qui s'est passé dans l'intervalle.

    - La subtile: créer une session extended events en traçant sqlos.wait_info et en filtrant sur le numéro de session. Avantage: on n'a que les postes d'attente du traitement en question. Inconvénient: pas simple à mettre en place.

    Déjà je pense que tu peux dégrossir avec la première méthode. Si on voit du LCK_ partout on saura d'où ça vient:

    Réinitialiser les stats d'attente:

    dbcc sqlperf('sys.dm_os_wait_stats','clear');
    GO


    Collecter les attentes:

    WITH Waits AS
        (SELECT
            wait_type,
            wait_time_ms / 1000.0 AS WaitS,
            (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
            signal_wait_time_ms / 1000.0 AS SignalS,
            waiting_tasks_count AS WaitCount,
            100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
            ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
        FROM sys.dm_os_wait_stats
        WHERE wait_type NOT IN (
            'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
            'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
            'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
            'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
            'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
            'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
            'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
            'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK','HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL','SP_SERVER_DIAGNOSTICS_SLEEP')
        )
    SELECT
        W1.wait_type AS WaitType,
        CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
        CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
        CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
        W1.WaitCount AS WaitCount,
        CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
        CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
        CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
        CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
    FROM Waits AS W1
        INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
    GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
    HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
    GO

    Autre chose, tu peux fixer une valeur de 'blocked process threshold' disons à 10 ou 15 secondes, et créer une session Profiler ou extended event pour tracer lorsqu'un blocage de plus de 10 ou 15 secondes se produit (sous Profiler: Errors and Warnings -> Blocked Process Report).

    exec sp_configure 'blocked process threshold',15;
    GO
    RECONFIGURE
    GO

    cf http://www.sqlservercentral.com/articles/Blocking/64474/


    David B.


    vendredi 3 octobre 2014 15:00
  • Bonjour

    Regardez également s'il y a eu des autogrowth sur les fichiers data ou log.

    Ensuite, y'a t'il eu des page split sur l'insertion (l'index cluster si présent est-il strictement croissant ?)

    Cdlt

    Christophe


    Christophe LAPORTE - Independent Consultant & Trainer - SQL Server MVP-MCM

    samedi 4 octobre 2014 13:16