Discussion :

[StringBuilder]

Bonjour,

Je suis en train "d'auditer" un serveur SQL Server afin d'essayer de déterminer les potentielles causes de lenteurs aléatoires.

Évidemment, après deux clics dans SSMS je suis en train de me rouler par terre car la personne qui a fait l'installation a scrupuleusement respecté le manuel du serveur parfaitement mal installé.

J'ai quand même une question afin de savoir comment étayer mon discours face à des gens qui n'auront probablement aucun doute quant au bien-fondé de leurs (non)choix.

Par exemple, j'ai vingt bases de données sur le serveur, toutes plus ou moins grosses (quelques gigas en moyenne), toutes sont mono-fichier, tous les fichiers sont en croissance automatique entre 1 Mo et 50 Mo, et tous pleins.
Bref, l'apothéose.

Je ne peux donc que mettre en avant le désastre d'avoir des bases pleines et des accroissements incessants des fichiers.
A ça, on va probablement me répondre "nan mais c'était vrai en 1980 maintenant on a un disque SSD NVME la fragmentation ça gêne plus, et les accès disques s'ont presque aussi rapide qu'en RAM".

Par rapport aux index clustered, je voudrais être sûr de moi...

Si j'ai physiquement un disque dur comme ça :

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
 
Etape 1: deux bases vides, le reste du disque vide
[ooooo][ooooo]oooooooooo
 
Etape 2: Les deux bases se remplissement normalement.
A: données de la table A de la base 1
B: données de la table B de la base 1
1: données de la table A de la base 2
2: données de la table B de la base 2
[AAoBB][11o2o]oooooooooo
 
Etape 3: la table B base 1 arrive en fin de fichier. Il reste de la place dans le fichier entre A et B. Que se passe-t-il quand on tente d'insérer "b" dans la table B ?
- SQL Server déplace toutes les données de la table B, pour se coller à la "fin" de la table A
[AABBb][11o2o]oooooooooo
 
- Il redimensionne le fichier pour ajouter "b" dans un nouveau fragment ?
[AAoBB][11o2o][b]ooooooooo
 
- Ou s'il écrit "b" là où il trouve de la place dans le fichier, faisant qu'au final l'index clustered ne respecte plus l'ordre des données sur le disque ?
[AAbBB][11o2o]oooooooooo
 
Je doute que ce soit la solution 2, puisque la croissance automatique du fichier se fait généralement quand il ne reste réellement plus de place dans le fichier, alors que dans ce cas le fichier ne serait jamais plein.
Quant à la solution 3, à ce moment je me demande l'intérêt des index clustered si au final ils ne sont pas stockés physiquement dans l'ordre dans le fichier. Un traitement dans ce cas réorganise tout ce bordel en tâche de fond ? Lors d'un recalcul d'index ?
 
Enfin, maintenant qu'on a notre disque dans cet état :
[AABBb][11o2o]oooooooooo
 
J'ajoute des données "aa" dans A.
Que se passe-t-il ?
- [AABBb][11o2o][aa]oooooooo ?
- [AAaaB][11o2o][Bb]oooooooo ?
- [AAaab][11o2o][BB]oooooooo ?

Je ne sais pas si mes schémas sont clair...

-- Edit: pas réveillé... je voulais dire "clustered" et non "partitionné"

[SQLpro]

1) auditer les latences au niveau des disques...

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
WITH 
disk_activity AS
(SELECT LEFT(mf.physical_name, 2) AS Drive, 
        SUM(num_of_reads) AS num_of_reads,
	     SUM(io_stall_read_ms) AS io_stall_read_ms, 
        SUM(num_of_writes) AS num_of_writes,
	     SUM(io_stall_write_ms) AS io_stall_write_ms, 
        SUM(num_of_bytes_read) AS num_of_bytes_read,
	     SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
 FROM   sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
        INNER JOIN sys.master_files AS mf WITH (NOLOCK)
              ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
 GROUP  BY LEFT(mf.physical_name, 2))
SELECT (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS SINCE,
       Drive AS DISK_DRIVE,
       CASE 
		    WHEN num_of_reads = 0 THEN 0 
		    ELSE (io_stall_read_ms/num_of_reads) 
	    END AS READ_LATENCY,
	    CASE 
		    WHEN io_stall_write_ms = 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 GLOBAL_LATENCY
FROM   disk_activity AS tab
ORDER  BY GLOBAL_LATENCY 
OPTION (RECOMPILE);

2) auditer les latences au niveau des fichiers

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
WITH 
disk_activity AS
(SELECT DB_NAME(mf.database_id) AS DATABASE_NAME,
        mf.physical_name AS physical_file,
        CASE
           WHEN DB_NAME(mf.database_id) = 'tempdb' THEN 'TEMPDB:' + mf.type_desc
           WHEN  mf.type_desc <> 'LOG' THEN LOWER(mf.type_desc)
           ELSE mf.type_desc
        END AS file_type,
        LEFT(mf.physical_name, 2) AS Drive, 
        SUM(num_of_reads) AS num_of_reads,
	     SUM(io_stall_read_ms) AS io_stall_read_ms, 
        SUM(num_of_writes) AS num_of_writes,
	     SUM(io_stall_write_ms) AS io_stall_write_ms, 
        SUM(num_of_bytes_read) AS num_of_bytes_read,
	     SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
 FROM   sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
        INNER JOIN sys.master_files AS mf WITH (NOLOCK)
              ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
 GROUP  BY LEFT(mf.physical_name, 2), mf.database_id, mf.physical_name, mf.type_desc)
SELECT (SELECT CAST(sqlserver_start_time AS DATE)
        FROM sys.dm_os_sys_info) AS SINCE,
       DATABASE_NAME,
       physical_file,
       file_type,
       Drive AS DISK_DRIVE,
       CASE 
		    WHEN num_of_reads = 0 THEN 0 
		    ELSE (io_stall_read_ms/num_of_reads) 
	    END AS READ_LATENCY,
	    CASE 
		    WHEN io_stall_write_ms = 0 THEN 0 
          ELSE (io_stall_write_ms/num_of_writes) 
	    END AS WRITE_LATENCY
FROM   disk_activity AS tab
ORDER  BY WRITE_LATENCY DESC
OPTION (RECOMPILE);

Aucune latence ne devrait dépasser :

• 7 ms en read
• 15 ms en write

3) voir le nombre de VLF des journaux de transaction (ne devrait pas dépasser 120) :

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
DECLARE @T_LOGINFO TABLE  (DB_NAME        NVARCHAR(128),
                           RECO           INT, 
                           FILE_ID        SMALLINT, 
                           FILE_SIZE      DECIMAL(38),  
                           START_OFFSET   DECIMAL(38), 
                           F_SEQ_NO       BIGINT, 
                           STATUS         BIT, 
                           PARITY         INT, 
                           CREATE_LSN     VARCHAR(38));
DECLARE @SQL NVARCHAR(max), 
        @DB_NAME sysname;
DECLARE C CURSOR 
   LOCAL FORWARD_ONLY STATIC READ_ONLY
   FOR   SELECT name 
         FROM   sys.databases 
         WHERE  source_database_id IS NULL 
           AND  user_access = 0 
           AND is_read_only = 0 
           AND state = 0 
           AND is_in_standby = 0;
OPEN C;
FETCH C INTO @DB_NAME;
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = N'USE [' + @DB_NAME + '];DBCC LOGINFO;';
   INSERT INTO @T_LOGINFO (RECO, 
                           FILE_ID, 
                           FILE_SIZE,  
                           START_OFFSET, 
                           F_SEQ_NO, 
                           STATUS, 
                           PARITY, 
                           CREATE_LSN)
   EXEC (@SQL);
   UPDATE @T_LOGINFO
   SET DB_NAME = @DB_NAME
   WHERE DB_NAME IS NULL;
   FETCH C INTO @DB_NAME;
END;
CLOSE C;
DEALLOCATE C;
SELECT DB_NAME, FILE_ID, FILE_SIZE, START_OFFSET, F_SEQ_NO, PARITY, CREATE_LSN
INTO   #sys_DBCC_LOGINFO_VLF
FROM   @T_LOGINFO;
 
-- voir le nombre de VLF par base 
SELECT DB_NAME, COUNT(*) NB_VLF_DB  
FROM #sys_DBCC_LOGINFO_VLF
GROUP  BY  DB_NAME
ORDER BY 2 DESC;
 
-- détail
SELECT * FROM #sys_DBCC_LOGINFO_VLF;

4) Voir les derniers épisodes de croissance et leur durée :

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
SELECT gt.DatabaseID, 
       DB_NAME(gt.DatabaseID) AS DATABASE_NAME,
       gt.FileName,
       COUNT(*) OVER(PARTITION BY DatabaseID) AS EVENT_COUNT_PER_DB,
       te.name AS EVENT_NAME,
       Duration / 1000.0 AS DURATION_MS,
       SUM(Duration) OVER(PARTITION BY gt.DatabaseID) 
          / 1000.0 AS TOTAL_DB_DURATION_MS,
       StartTime,
       EndTime,
       (IntegerData * 8) / 1024 AS FILE_SIZE_MODIF_MB
FROM   sys.fn_trace_gettable(
          (SELECT REVERSE(
                     SUBSTRING(
                        REVERSE(path), 
                        CHARINDEX('\', 
                                  REVERSE(path)
                                 ), 260
                              )
                         ) + 'log.trc'
           FROM   sys.traces
           WHERE  is_default = 1), DEFAULT) AS gt
       JOIN sys.trace_events te 
          ON gt.EventClass = te.trace_event_id
WHERE te.name in ('Data File Auto Grow', 
                  'Log File Auto Grow', 
                  'Data File Auto Shrink', 
                  'Log File Auto Shrink');

Regarde s'il n'y a pas de l'autoshrink ou du SHRINKFILE dans le plan de maintenance.

Il faut aussi que :
1) les disques soient formattés en 64 Ko
2) agrégats RAIDS de type 0, 1, 0+1 ou 10. Pas de RAID 5, 50, 6, 60, DP...
Bien entendu, pas de disques virtuels !
Guide d'architecture VMWare et SQL Server : https://www.vmware.com/content/dam/d...ices-guide.pdf

Tu peux aussi voir s'il y a des erreurs plus graves avec le script suivant :

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
CREATE TABLE #LOG
(LOG_NUM    INT,
 LOG_DATE   NVARCHAR(16),
 LOG_SIZE   INT);
 
INSERT INTO #LOG
EXEC master.sys.xp_enumerrorlogs;
 
CREATE TABLE #EVENT_LOG
(LOG_DATE      DATETIME,
 LOG_PROCESS   NVARCHAR(36), 
 LOG_EVENT     NVARCHAR(4000));
 
DECLARE @I INT = 0, 
        @SQL NVARCHAR(200) = N'', 
        @NB_LOG INT = (SELECT COUNT(*) FROM #LOG);
 
WHILE @I < @NB_LOG
BEGIN
   IF @I IN (SELECT LOG_NUM FROM #LOG)
   BEGIN
      SET @SQL = N'INSERT INTO #EVENT_LOG EXEC sp_readerrorlog ' 
               + CAST(@I AS NVARCHAR(2)) + N';';
      EXEC (@SQL);
   END
   SET @I += 1;
END
 
SELECT *
FROM   #EVENT_LOG
WHERE  LOG_EVENT LIKE '%taking longer than 15 seconds%' 
   OR  LOG_EVENT LIKE '%misaligned log IOs%'
ORDER BY LOG_DATE DESC;
 
DROP TABLE IF EXISTS #LOG;
DROP TABLE IF EXISTS #EVENT_LOG;
GO

A +

Bon Noël !

[SQLpro]

Pour tes questions.... Tout dépend de la version...
Dans les anciennes les pages étaient mélangées dans les extents. Dans les nouvelles, les pages sont uniformes. Seules les pages de point d'entrées dans les index (pages racines) sont mixées dans une même extents pour des raisons d'efficacité (dans tous les cas d'accès en seek, scan ou lookup il faut rentrer dans l'index par sa racine).

Dans les index (y compris clustered), les données indexées sont TOUJOURS ordonnées, mais les données complémentaires (partie non clé d'un index clustered ou include d'index nonclustered) peuvent être déplacées à un autre endroit que la ligne, conduisant à des lectures en zigzag... En gros la partie indexées est toujours au bon endroit, ce qui peut conduire à des splits de page en cas d'INSERT ou d'UPDATE, tandis qu'en cas d'UPDATE de données non clé, notamment agrandissement des octets d'un VARCHAR, alors il y a déplacement de la seule information nouvelle avec des pointeurs de renvoie.... Je démontre cela régulièrement dans mes cours....

Exemple : INSERT 'toto' suivi d'un UPDATE 'zigzornifle'

A +

[StringBuilder]

Merci Frédéric pour ta réponse très complète

Malheureusement, les résultats des requêtes d'audit sont moins catastrophiques que prévu.
Comme quoi l'iron forge donne parfois des résultats...

Par contre le serveur est redémarré toutes les nuits, je soupçonne que ça fausse certaines mesures... Vu la période, la base n'a pas dû être sollicité outre mesure depuis cette nuit.
Je vais essayer de les convaincre d'arrêter de rebooter le serveur toutes les nuits (ça sent le gars qui n'a pas évolué depuis Windows 95 ça...) et refaire des mesures quand des traitements de fin de mois auront tourné.

[escartefigue]

tandis qu'en cas d'UPDATE de données non clé, notamment agrandissement des octets d'un VARCHAR, alors il y a déplacement de la seule information nouvelle avec des pointeurs de renvoie.... Je démontre cela régulièrement dans mes cours....

Eh oui, ce point particulier est malheureusement bien méconnu des trop nombreux concepteurs qui choisissent systématiquement du varchar, y compris pour des toutes petites longueurs (y compris du varchar(1) plus encombrant que du char ! )

[SQLpro]

Merci Frédéric pour ta réponse très complète

Malheureusement, les résultats des requêtes d'audit sont moins catastrophiques que prévu.
Comme quoi l'iron forge donne parfois des résultats...

Par contre le serveur est redémarré toutes les nuits, je soupçonne que ça fausse certaines mesures... Vu la période, la base n'a pas dû être sollicité outre mesure depuis cette nuit.
Je vais essayer de les convaincre d'arrêter de rebooter le serveur toutes les nuits (ça sent le gars qui n'a pas évolué depuis Windows 95 ça...) et refaire des mesures quand des traitements de fin de mois auront tourné.

Sur le sujet :
http://mssqlserver.fr/restarting-ms-...-or-good-idea/

A +