Discussion :

[mikedavem]

Ici ce n'est pas une baie de disque mais du stockage local visiblement.
Je pense à du RAID 5 et RAID 1 en fonction du contrôleur RAID (grappe de 3 et 2 disques). Le RAID10 nécessite au minimum 4 disques.

Tu peux éventuellement nous donner la taille des chunks pour tes grappes RAID + taille du cache en lecture / écriture si activé.

L'idée ici c'est surtout d'avoir les statistiques IO des fichiers SQL Server qui nous permettront de dire combien de temps attend un processus SQL pour le traitement d'une IO (lecture ou écriture)

++

[Kropernic]

Il faudrait avoir la correspondante logique et physique de ton stockage et des partitions.

Est-ce que tu peux nous donner un aperçu de tes attentes SQL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
use master
GO
declare @lastrestart datetime
select @lastrestart =create_date from sys.databases where database_id=2
select @lastrestart 'Date Dernier Redémarrage' ,DATEDIFF(dd, @lastrestart, GETDATE()) 'Uptime'
GO
 
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')
    )
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

++

Les deux résultats sont en pièces jointes (c'est juste pas présentable si je copie sur le forum)

EDIT : Pour le nom des pièces jointes, j'avais pas fait gaffe que la demande ne venait pas de sqlpro. Avec tous ces MVP's, je ne m'y retrouve plus ^^

[Kropernic]

Il faudrait aussi avoir la ddl de ta table + les index qui existent sur cette table + éventuellement le plan d'exécution estimé de ton DELETE.

Si je poste le ddl, vous allez tous me tomber dessus... C'est une table obèse... Elle est héritée d'un vieux système et devrait être "refactorisée" (c'est le bon terme dans le monde DB ça ???) mais il y a tellement de requêtes écrites dessus que j'ai pas encore eu le courage de migrer les données (j'ai déjà créer le nouveau modèle et tout). Malgré tout, il est en pièce jointe *se prépare à recevoir des cailloux*
Pour le plan, c'est pareil, en pièce jointe (j'ai juste changé l'extension en .txt)

et également des statistiques IO des fichiers de bases de données :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
WITH io_file_stats 
AS 
( 
    SELECT 
        d.name AS database_name, 
        f.name AS [file_name], 
        f.physical_name, 
        f.type_desc, 
        vf.num_of_reads, 
        vf.num_of_writes, 
        CAST(vf.num_of_bytes_read * 1. / (vf.num_of_reads + 1) / 1024 AS DECIMAL(18,2)) AS avg_kbytes_read, 
        CAST(vf.io_stall_read_ms * 1. / (vf.num_of_reads + 1) AS DECIMAL(18,2)) AS avg_stall_read_ms, 
        CAST(vf.num_of_bytes_written * 1. / (vf.num_of_writes + 1) / 1024  AS DECIMAL(18,2)) AS avg_kbytes_written, 
        CAST(vf.io_stall_write_ms * 1. / (vf.num_of_writes + 1) AS DECIMAL(18,2)) AS avg_stall_write_ms, 
        CAST(vf.num_of_reads * 1. / SUM(num_of_reads + 1) OVER() * 100 AS DECIMAL(5,2)) AS percent_reads, 
        CAST(vf.num_of_writes * 1. / SUM(num_of_writes + 1) OVER() *100 AS DECIMAL(5,2)) AS percent_writes 
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vf 
    INNER JOIN sys.databases AS d 
    ON d.database_id = vf.database_id 
    INNER JOIN sys.master_files AS f 
    ON f.file_id = vf.file_id 
     AND f.database_id = vf.database_id 
), 
io_file_stats_rank 
AS 
( 
SELECT 
    *, 
    RANK() OVER (ORDER BY avg_kbytes_read DESC) AS rank_avg_kbytes_reads, 
    RANK() OVER (ORDER BY avg_stall_read_ms DESC) AS rank_avg_stall_read_ms, 
    RANK() OVER (ORDER BY avg_kbytes_written DESC) AS rank_avg_kbytes_written, 
    RANK() OVER (ORDER BY avg_stall_write_ms DESC) AS rank_avg_stall_write_ms, 
    RANK() OVER (ORDER BY percent_reads DESC) AS rank_percent_reads, 
    RANK() OVER (ORDER BY percent_writes DESC) AS rank_percent_writes 
FROM io_file_stats 
) 
SELECT 
    database_name, 
    [file_name], 
    physical_name, 
    type_desc, 
    num_of_reads, 
    num_of_writes, 
    avg_kbytes_read, 
    avg_stall_read_ms, 
    avg_kbytes_written, 
    avg_stall_write_ms, 
    CAST(percent_reads AS VARCHAR(5)) + ' %' AS percent_reads, 
    CAST(percent_writes AS VARCHAR(5)) + ' %' AS percent_writes
FROM io_file_stats_rank 
-- Choix du classement = 
-- rank_avg_kbytes_reads 
-- rank_avg_stall_read_ms 
-- rank_avg_kbytes_written 
-- rank_avg_stall_write_ms 
-- rank_percent_reads 
-- rank_percent_writes 
--ORDER BY rank_percent_reads;

++

Résultat de la requête en pièce jointe.

[Kropernic]

Ici ce n'est pas une baie de disque mais du stockage local visiblement.
Je pense à du RAID 5 et RAID 1 en fonction du contrôleur RAID (grappe de 3 et 2 disques). Le RAID10 nécessite au minimum 4 disques.

Tu peux éventuellement nous donner la taille des chunks pour tes grappes RAID + taille du cache en lecture / écriture si activé.

L'idée ici c'est surtout d'avoir les statistiques IO des fichiers SQL Server qui nous permettront de dire combien de temps attend un processus SQL pour le traitement d'une IO (lecture ou écriture)

++

Ce n'est effectivement pas une baie de stockage. Les disques sont directement "sur" le serveur. Pour les restes des questions, c'est du chinois pour moi ^^. Je vais tacher de faire en sorte que mon collègue qui gère tout ça se crée un compte sur le forum et vienne vous répondre .

[mikedavem]

Bon je regarde tout cela quand j'ai un peu de temps.
Entre temps d'autres réponses seront certainement donné

EDIT : Pour le nom des pièces jointes, j'avais pas fait gaffe que la demande ne venait pas de sqlpro. Avec tous ces MVP's, je ne m'y retrouve plus ^^

Ouais je sais SQLPro a le privilège de l'âge. Je m'incline donc

++

[mikedavem]

A la vue de tes statistiques IO je pense vu de loin que ton stockage souffre de problèmes de performances (avg_stall_write_ms et avg_stall_read_ms relativement élevés pour la base Tickets et pour le fichier d'index visiblement). Les types d'attentes SQL ASYNC_IO_COMPLETION, PAGEIOLATCH_* semblent également aller dans ce sens.

Je pense qu'il va falloir creuser de ce côté en premier lieu. A voir ce que tu nous diras sur les spécificités de ton stockage.

++

[Kropernic]

En même temps, c'est un serveur en fin de vie. Le successeur est déjà là et près à recevoir les DB mais faut trouver le temps pour les migrer.

Donc ce que tu me dis ne m'étonne qu'à moitié. Va falloir que je me dépêche à tout migrer avant qu'il ne rende l'âme...

Je vais quand même demander à mon collègue qui s'occupe du hardware de venir répondre (il est apparemment dans un magasin aujourd'hui donc ça risque de n'être que demain).

[aieeeuuuuu]

Bonjour,

En plus de tout ce qui a été dis concernant le matériel sous jacent et ses contentions qu'il engendre, vous avez - d'après votre plan d’exécution - une vue indexée portant sur cette table.

Lors de votre DELETE, la vue doit également être mise à jour. ça explique également le temps anormalement long pour une suppression

[elsuket]

Bonjour,

Avant de descendre au niveau des disques, quand on voit des PAGEIOLATCH_XX, je commence d'abord par regarder d'une part la Page Life Expectancy, mais surtout le plan de requête.

C'est sur de cernier point qu'encore une fois SQL Sentry Plan Explorer se trouve être redoutable de clarté :



La mise à jour de 13 index est probablement très coûteuse.
Cela confirme que la table a de nombreuses colonnes, et que donc si le modèle avait été correct, ce ne serait tout simplement jamais arrivé.

D'autre part la maintenance de ces index doit coûter aussi en écriture dans lze fichier du journal des transactions. Il serait donc intéressant de regarder les latences d'écriture sur le volume qui héberge ce dernier, et aussi le nombre de VLF qui composent le fichier du journal des transactions ...

@++

[mikedavem]

Kropernic tu as maintenant tout ce qu'il faut pour résoudre ton problème

++

[Kropernic]

Très probablement oui. Faut-il encore que je sache quoi en faire .

Mais je vais me pencher sur le sujet.

Je pense que je peux mettre le sujet en résolu à présent.

Pour la pratique, comme on dit : "y a plus qu'à !"

[mikedavem]

Je pense que ton problème d'origine vient de ce qu'explique Elsuket. Un mauvais design.
Quand tu supprimes des lignes de table tu as une mise à jour qui doit être faite sur une série d'index (13 index non clusters). Donc pour une suppression de ligne 13 opérations de mise à jour d'index doivent encore être effectués.

Tu dois vérifier si les 13 index sont réellement utiles.

Je pense également que tu as un problème de fond au niveau de ton stockage . Les temps de latence sont vraiment important (indépendamment du fait que tu puisses manquer de mémoire sur ton serveur)

Maintenant la question de Elsuket est pertinente : as-tu assez de mémoire sur ton serveur SQL ? Le manque de mémoire peut être à l'origine d'accès disque beaucoup plus fréquent.

++

[Kropernic]

Je suis le premier à reconnaître qu'il y a un grave défaut de conception dans cette DB (et pas que dans celle-là ).

Je fais ce que je peux avec les connaissances à ma disposition et avec l'aide notamment de fmsrel et cinephil (pour ne citer qu'eux, sorry pour les autres... je ne vous oublie pas cependant) sur le forum schéma pour ne plus avoir ce genre de problème conceptuel dans les nouvelles bases de données que je mets en oeuvre.

Malheureusement, "redesigner" une DB qui existe déjà, c'est faisable oui mais personnellement, j'aurais trop peur d'oublier telle ou telle implication que cela entrainerait et que tout un pan de la gestion se retrouve par terre. J'applique donc ça et là des rustines en attendant d'avoir la formation ad-hoc pour effectuer ce genre d'opération sereinement et d'avoir le temps d'analyser les conséquences d'une refonte d'une DB au niveau applicatif pour être certain de ne rien oublier...

Quant au problème de mémoire sur le serveur, c'est une bonne question ^^. (mon collègue m'a dit qu'il tacherait de venir répondre demain au sujet du hardware )

[mikedavem]

Pas de souci. Tout le monde est là pour apprendre.

De plus tu n'es pas le seul pour qui la modification du schéma ou autre n'est pas envisageable (malheureusement). Il faut donc jouer avec les autres éléments qui peuvent l'être.

++

[Kropernic]

Ah mais ce n'est pas que ce n'est pas faisable. C'est juste que je n'ose pas

[mikedavem]

You can !

++

[aieeeuuuuu]

Ah mais ce n'est pas que ce n'est pas faisable. C'est juste que je n'ose pas

Osez ! c'est encore le meilleur moyen de connaitre les détails que vous auriez omis : les utilisateurs vous les diront très rapidement

(d'après ce que j'en ai vu, c'est malheureusement souvent comme ça qu'on procède en informatique)

[Arkhena]

J'ai un autre argument pour le changement : on n'apprend jamais aussi bien que lorsqu'on fait des erreurs

[aieeeuuuuu]

on n'apprend jamais aussi bien que lorsqu'on fait des erreurs

C'est vrai !
D'ailleurs souvent le soir je me dis

aujourd'hui j'ai encore... beaucoup appris

[Kropernic]

Car mieux vaut tard que jamais, voici la réponse de mon collègue en charge du hardware concernant les disques :

Pour répondre à ta question, à toi de voir sur quelle partition se trouve quelle DB:

- Disque C: RAID 1+0 stripe size = 128 KB (2 physical disks)
- Disque D: RAID 5 stripe size = 64 KB (3 physical disks)
- Disque F: RAID 1+0 stripe size = 64 KB (2 physical disks)

Pour rappel, l'instance (donc tempdb) se trouve sur le C, la db tickets sur le D et les logs et index sur le F.