Discussion :

[castorameur]

Bonjour,

Depuis quelques semaines les performances de notre serveur de BDD se sont effondrés.
Il s'agit d'une BDD utilisé par ERP dont le principe est de créé des enregistrement liés à des livraisons de produits et des factures.

Ma première action a été de lancer le moniteur SQL et j'ai découvert que les wait_stat Buffer i/o était très important.
En gros en les mettant a zero j'obtiens 1600 secondes d'attente après 1 heure de lancement. C 'est que je trouve énorme !

J'ai regardé du côté des traitements et même s'il ne sont pas optimisés, rien n'a changé récement.

Je soupçonne le système virtuel.
J'ai trouvé la discussion suivante intéréssante
https://www.developpez.net/forums/d1...ent-niveau-io/

Mais je voudrais pouvoir sortir des métriques.
j'ai lancé perfmon et je n'arrive pas a jouté les compteurs : quand je sélectionne base de donnée/latence écrire et que je fais OK, la courbe ne s'ajoute pas. Par contre pas de soucis pour la courbe CPU et memoire

Pouvez vous m'aider pour des pistes sur la perf SQL et la réalisation de métrique a partager avec le prestataire de l'infra

cdt

[SQLpro]

Partez aussi à la recherche des messages de type "long IO" dans le journal des événements de SQL Server :

Code :

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

exec xp_readerrorlog 0, 1, N'I/O requests taking longer than';

Et renvoyez nous le résultat.

De même avec :

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
WITH T0 AS
(
SELECT DB_NAME (vfs.database_id) AS DATABASE_NAME,
       type_desc AS FILE_NATURE,
       CASE WHEN num_of_reads = 0
               THEN 0 
			ELSE (io_stall_read_ms / num_of_reads) 
	   END READ_LATENCY,
       CASE WHEN num_of_writes = 0
               THEN 0 
			ELSE (io_stall_write_ms / num_of_writes) 
	   END AS WRITE_LATENCY,
       CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
               THEN 0 
			ELSE (io_stall / (num_of_reads + num_of_writes)) 
	   END AS LATENCY,
       CASE WHEN num_of_reads = 0
               THEN 0 
			ELSE (num_of_bytes_read / num_of_reads)  
	   END AS AVERAGE_BYTE_PER_READ,
       CASE WHEN num_of_writes = 0
               THEN 0 
		    ELSE (num_of_bytes_written / num_of_writes) 
	   END AS AVERAGE_BYTE_PER_WRITE,
        CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
                THEN 0 
			 ELSE ((num_of_bytes_read + num_of_bytes_written) /
                   (num_of_reads + num_of_writes)) END AS AVERAGE_BYTE_PER_TRANSFER,
       LEFT (mf.physical_name, 2) AS DRIVE,
       mf.physical_name AS FILE_NAME
FROM   sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs
       JOIN sys.master_files AS mf
            ON vfs.database_id = mf.database_id
            AND vfs.file_id = mf.file_id
)
SELECT *, CASE WHEN LATENCY <= 5  THEN 'GOOD' 
               WHEN LATENCY <= 15 THEN 'CORRECT' 
               ELSE 'BAD' END AS STATUS
FROM  T0     
ORDER BY LATENCY DESC;

A +

[castorameur]

les résultats des requetes confirme (pour moi) qu'il y a bien un problème d'infra

Pour la première j'ai plusieurs lignes

Code :

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

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\SAGEX3V6\data\x3_prod_data.mdf:MSSQL_DBCC7] in database [x3_prod] (7).  The OS file handle is 0x0000000000000B20.  The offset of the latest long I/O is: 0x00000c06ba0000

et j'ai 1 ligne avec ça, ce qui semble faire peur !

Code :

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

SQL Server has encountered 860 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\SAGEX3V6\data\x3_prod_data.mdf:MSSQL_DBCC7] in database [x3_prod] (7).  The OS file handle is 0x0000000000000B20.  The offset of the latest long I/O is: 0x00003565730000

pour la 2ème requetes tout est en BAD !!attentes.xlsx

[SQLpro]

Rien de catastrophique…

Vous n'avez qu'un seul "long IO" et il concerne un fichier de données (écritures asynchrones) dons, pas grave.

Sur les latences de disques c'est globalement pas bon, tant sur les lectures que sur les écritures et notamment sur la base tempdb.
Il est probable que ceci soit du :

• à un mauvais provisionnement des fichiers sur les disques
• à une mauvaise configuration du stockage

d'où trois requêtes complémentaires :

1 - A lancer sur TOUTES vos bases de production

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
-- détail du stockage par groupe de fichier et fichiers
SELECT DB_NAME() AS DATABASE_NAME, COUNT(*) AS NB_OBJECTS, COUNT(distinct i.object_id) AS NB_TABLE,
       fg.name AS STORAGE, df.name AS FILE_NAME, df.size / 128 AS FILE_SIZE_MB,
       FILEPROPERTY(df.name, 'SpaceUsed') / 128 AS DATA_SIZE_MB,
       CAST(100.0 * FILEPROPERTY(df.name, 'SpaceUsed') / df.size AS DECIMAL(5, 2)) AS FILLED_SIZE_PERCENT
FROM   sys.indexes AS i
       JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
       JOIN sys.filegroups AS fg ON i.data_space_id = fg.data_space_id
       JOIN sys.objects AS o ON i.object_id = o.object_id
       JOIN sys.schemas AS s ON o.schema_id = s.schema_id
       JOIN sys.database_files AS df ON i.data_space_id = df.data_space_id
GROUP  BY fg.name, df.name, df.size;

2 - une seule fois (global) :

Code :

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

DBCC SQLPERF(LOGSPACE);

3 - une seule fois (globale)

Code :

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

1
2
3
4
5
6
7
8
WITH TRC
AS
(SELECT path AS F 
 FROM sys.traces WHERE is_default = 1)
SELECT DB_NAME(DatabaseID), T.StartTime, T.EndTime, T.Duration
FROM TRC
     CROSS APPLY sys.fn_trace_gettable(F, NULL) AS T
WHERE T.EventClass In (SELECT trace_event_id FROM sys.trace_events WHERE name LIKE '%File Auto Grow%')

A +

[SQLpro]

Pour la 3, ce script est plus exhaustif :

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
DECLARE @FILE_PATH NVARCHAR(1024), @SQL NVARCHAR(max);
SELECT @FILE_PATH = LEFT(path, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1) 
FROM sys.traces WHERE is_default = 1;
SET @SQL = N'xp_cmdshell ''DIR /B "' + @FILE_PATH + N'*.trc"''';
DECLARE @T TABLE (K INT IDENTITY, L NVARCHAR(1024))
INSERT INTO  @T
EXEC (@SQL);
DELETE FROM @T WHERE L IS NULL;
WITH TRC AS 
(SELECT L FROM @T)
SELECT DB_NAME(DatabaseID) AS DATABASENAME, 
       T.StartTime, T.EndTime, T.Duration / 1000.0 AS Duration_ms,
       CAST(SUM(T.Duration) OVER(PARTITION BY DatabaseID) / 1000.0 AS DECIMAL(16,2)) AS DB_Duration_ms
FROM TRC
     CROSS APPLY sys.fn_trace_gettable(@FILE_PATH + L, NULL) AS T
WHERE T.EventClass In (SELECT trace_event_id FROM sys.trace_events WHERE name LIKE '%File Auto Grow%');

A +

[castorameur]

Bonjour, voir les résultats






Et c'est là que je me suis rappelé que personne n'avait été modifier le paramétrage d'augmentation du fichier qui date de ...2012 et à l'époque c'était le paramétrage standard qui avait été laissé soit 5 Mo d'augmentation.
Donc aujourd'hui c est insuffisant et je viens de le passer a 100 Mo
et j'ai relancé la requete, et il n'y a plus qu'une seule extension

Pour la dernière requete, pas possible de la lancer a cause de la securité

Code :

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

SQL Server a bloqué l'accès à procédure 'sys.xp_cmdshell' du composant 'xp_cmdshell', car ce composant est désactivé dans le cadre de la configuration de la sécurité du serveur.

par contre ce matin c'est toujours aussi lent et j ai deja des pageiolatch


le fichier est il trop fragmenté ?

[SQLpro]

Un peu, mais si c'est du SSD Write Intensive pas grave….

C'est le provisionning qui n'est pas bon…

Il faut revoir globalement le stockage

A +