Discussion :

[StringBuilder]

Bonjour à tous,

Je suis en phase de démarrage chez un client.

Le produit est constitué :
- D'un site sous IIS
- D'une base de données sous SQL Server
- D'une série d'outils "serveurs" en mode Windows

Attention, attachez vos ceintures :
- Un seul disque : C:
- Tout tourne sur le même serveur
- Le serveur est virtualisé

C'est bon, tout le monde est encore bien là ?

Depuis un navigateur (depuis un PC distant) je lance un traitement dans l'application Web.
Un traitement qui va générer des assortiments de produits pour des clients.
1/ Je sélectionne 200 produits
2/ Je sélectionne 200 clients
3/ Je clique sur un bouton

Et c'est censé me créer pour chacun des 200 clients un listing de référencement contenant les 200 produits, soit 40 000 lignes.

Une paille pour SQL Server.
Une paille pour une application codée en C#.

Croyais-je.

Au bout de 30 minutes, timeout.

Hmpf.

Je relance, je regarde le gestionnaire des tâches :
- w3wp.exe : 11% du CPU
- sqlservr.exe : 12% du CPU

Sâchant que le CPU est un quad core, aucun coeur ne tourne à 100%, puisque même si IIS et SQL Server tournaient sur le même core (ce qui n'est pas le cas) ils ne boufferaient pas 25% à eux deux.

Bon, alors... réseau : tout est en local, rien à déclarer.

Mémoire : on est large, 2 Go de libre sur 8 Go. Rien à déclarer.

Disque dur : temps d'accès de 1 à 2 ms, pour des lectures/écritures de 30 ko/s... Hmmmm, étrange quand même que ce soit aussi lent...

Les fichiers utilisés sont :
LDF : 30 ko/s
MDF : pour ainsi dire rien du tout
"FlightPlan" (dans un dossier de SQL Server, je découvre) : 30 ko/s

Et le reste c'est epsilon.

Je change le mode de recovery de "full" à "simple", espérant limiter les écritures dans le LDF. Même combat, ça plante au bout de 30 minutes.

Je relance en divisant par deux la volumétrie à traiter.
Cette fois, pas de plantage.
Et là, j'observe quelque chose d'étrange : le LDF arrête tout d'un coup d'être sollicité (juste un peu de lecture), et le MDF patine à 300 ko/s
Soit 10 fois plus rapide que le LDF

Même si ça reste des performances dignes d'un disque des années 80, le fait que selon l'étape de la transaction, on multiplie la vitesse d'accès au disque par 10 m'amène à me poser la question suivante :
- Puis-je en déduire que le goulot d'étranglement vient de l'unité de disque ? Comment le démontrer ?

Si je fais un backup de la base ou du journal (sur le même disque), la vitesse d'écriture est de 600 ko/s

Sur le même serveur de virtualisation, et surtout, sur la même baie de disques, on retrouve d'autres SQL Server (notamment celui de l'ERP ainsi que celui du WMS), qui ne semblent pas poser de problèmes de performances...

Outre l'application qui est complètement pourrie (40 000 insertions + update au lien d'un simple merge résultant d'une produit cartésien entre produits et clients sélectionnés), quelles autres pistes puis-je suivre ?

En fait, je ne m'explique pas que le CPU se tourne les pouces pendant que le disque ne semble pas spécialement sollicité non plus... L'application fait quoi pendant tout ce temps ?

[François M.]

Bonjour

Il faudrait un peu plus de précisions sur la façon dont l'applicatif procède.

- Comment se fait l'accès à la base (PS, ORM, requêtes dans le code ?)
- Combien de transactions pour l'insertion des 40 kLignes ? Une seule ? plusieurs ?

[SQLpro]

1) Commence par dimensionner correctement tes fichiers => 10 Go pour le mdf et 2 Go pour le ldf...
2) donne à SQL Server 60% de la RAM (sp_configure max server memory)

A +

[François M.]

2) donne à SQL Server 60% de la RAM (sp_configure max server memory)

Le serveur n'est pas dédié à l'instance SQL SERVER.

[mikedavem]

Ce que tu peux faire c'est commencer par une approche globale en regardant où SQL Server attend de manière générale pendant ton traitement.


Tu réinitialises tes attentes (si possible bien sûr), tu lances ton traitement et tu récupères tes attentes une fois ton traitement fini. Si tu ne peux pas réinitialiser les attentes tu prends un snapshot avant, un snapshot après et tu fais récupères la différence.

Pour les attentes tu peux utiliser ce script:

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
59
60
61
62
63
64
65
66
67
 
 
-- A exécuter si tu peux réinitialiser les attentes sur le serveur
--DBCC SQLPERF('sys.dm_os_wait_stats', 'clear');
--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 (
        N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
        N'CHKPT',                           N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                        N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
        N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
 )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO

Edit : Désolé Benjamin, je n'avais pas vu que tu avais posté avant moi .. donc cela fait un peu doublon

++

[StringBuilder]

Merci pour toute vos réponses.

Je vais potasser tout ça

Comme premiers éléments de réponse :
- Actuellement, le MDF (qui avait une croissance automatique de 1 Mo...) fait 2,4 Go. Je lui ai passé la croissance auto à 500 Mo.
- Le LDF, lui il avait jamais été sauvegardé, et faisait 42 Go... je l'ai passé à 500 Mo de croissance après l'avoir tronqué (et backupé).

Sinon, tout se fait visiblement dans une seule transaction, puisqu'au bout d'une demi-heure (timeout), aucune donnée n'est en base.
En revanche, 60000 lignes = 60000 insert (plus certainement des update, select et autres joyeusetés)
Les requêtes sont jouées directement depuis le code.
La base de données est accédée avec le drivers OLEDB.
Après redémarrage de l'instance (TEMPDB faisait 40 Go, en croissance 1 Mo à partie de 10 Mo...) SQL Server n'occupait plus que 25% de la mémoire (2 Go). Avant le redémarrage du service, il en bouffait 75% (6 Go) mais il y avait encore pas mal de RAM libre (dans les 1 Go).

Malgré toutes ces abérations, que j'ai corrigé, rien ne change aux perfs, c'est toujours aussi lent...

Je vais ingurgiter vos pistes, et reviens vers vous

[bvesan]

L'application fait quoi pendant tout ce temps ?

On ne peut pas facilement savoir ce que fait l'appli, mais on peut assez facilement savoir si elle passe beaucoup de temps en base, et de connaître la répartition du temps passé en base.
Grâce au événements d'attente dans SQL Server, il va être possible de disposer de toutes ces infos.

Si vous êtes déjà familiarisé avec le concept d'Attentes, regardez l'évolution des attentes dans sys.dm_os_waiting_tasks et sys.dm_os_wait_stats.
Sinon, j'ai rédigé un article qui expose le sujet: Les Attentes dans SQL Server