Discussion :

[mikedavem]

A moins de ne pas voir correctement mais je n'ai pas vu si tu avais dit si ton serveur était virtualisé ? Désolé par avance si cela a été précisé mais je ne trouve pas.

[chloeb]

oui j'ai bien ça ce message!

C'est un serveur dédié de chez 1&1

[mikedavem]

Il y a de forte chance que les serveurs soient virtualisés chez des providers ... à vérifier.. Tu pourrais voir avec RAMMAP justement si le "driver locked" ne prend pas plus d'importance quand tu as un souci de performance de ce genre.

En principe avec les conseils de David tu devrais arriver à ce que l'utilisation de la mémoire soit réellement verrouillé par SQL Server. A vérifier également.

La valeur du compteur de performance page life expectancy est plus que raisonnable maintenant en tout cas.

++

[dbaffaleuf]

J'avais compris une machine physique aussi, c'est pour ça que je me suis avancé avec LPIM. Merci David pour le coup d'oeil !

Par contre sans reboot et sans privilège 'Lock Pages in memory' au compte de service, pas de locked pages, donc il faudrait voir si le message Using locked pages for buffer pool est bien affiché dans l'ERRORLOG

[chloeb]

Oui j'ai bien ce message dans l'errorlog.

Par contre par curiosité, c'est quoi la valeur "correcte" à avoir pour la valeur du compteur de performance page life expectancy?

Je viens de repasser la requete et maintenant j'ai un résultat de 88032...

[dbaffaleuf]

La formule c'est 300 secondes par pas de 4Gb de max server memory ou de RAM. Dans ton cas donc 300 secondes.

[chloeb]

Ok, mais c'est mieux d'avoir un chiffre bas ou élevé?

Je viens de repasser la requête et le chiffre augmente (je suis à 148681 maintenant)

[dbaffaleuf]

C'est la durée de vie d'une page dans le buffer pool. Le but c'est qu'elle reste le plus longtemps possible...

[chloeb]

je m'excuse de vous déranger encore!

Mes sites bloquent toujours (je n'ai pas encore augmenté ma RAM). Dans l'activity monitor, je n'ai plus de problème de mémoire qui a un temps de réponse long (bonne chose de faite!!)

Par contre, maintenant, j'ai un wait time important pour BufferI/O et logging (dans Ressource wait) et la liste de requete sql expensive a beaucoup augmentée!!

Une idée?

[mikedavem]

Si tu as des temps d'attente important de type logging il est probable que l'écriture dans le journal des transactions de tes bases soit contre performante.

Peux-tu exécuter cette requête :

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
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
 
 
SELECT 
 SUM(wait_time_ms - signal_wait_time_ms) AS TotalWaitTime,
 (SUM(CAST(wait_time_ms - signal_wait_time_ms AS NUMERIC(20, 2))) 
  / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100) AS PercentageWaitsOfTotalTime,
 SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
(SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2))) 
  / SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100) AS PercentageSignalWaitsOfTotalTime
FROM sys.dm_os_wait_stats

++

[chloeb]

Voici les résultats :

WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S
SOS_SCHEDULER_YIELD 196.81 12.33 184.48 5061537 33.45 0.0000 0.0000 0.0000
WRITELOG 177.99 177.12 0.87 6387 30.25 0.0279 0.0277 0.0001
PAGEIOLATCH_UP 71.64 71.56 0.08 6069 12.18 0.0118 0.0118 0.0000
IO_COMPLETION 33.87 33.78 0.08 3639 5.76 0.0093 0.0093 0.0000
PAGEIOLATCH_SH 22.43 21.99 0.44 1516 3.81 0.0148 0.0145 0.0003
SQLTRACE_FILE_WRITE_IO_COMPLETION 14.20 14.20 0.00 471 2.41 0.0302 0.0302 0.0000
PAGELATCH_EX 10.20 2.12 8.07 259973 1.73 0.0000 0.0000 0.0000
SLEEP_DBSTARTUP 8.31 7.81 0.50 59 1.41 0.1408 0.1324 0.0085
PAGELATCH_SH 6.27 0.92 5.35 200253 1.07 0.0000 0.0000 0.0000
PREEMPTIVE_OS_AUTHENTICATIONOPS 6.03 6.03 0.00 11710 1.03 0.0005 0.0005 0.0000
PREEMPTIVE_OS_GENERICOPS 4.48 4.48 0.00 18043 0.76 0.0002 0.0002 0.0000
ASYNC_NETWORK_IO 3.86 2.78 1.07 6360 0.66 0.0006 0.0004 0.0002
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 3.51 3.51 0.00 2917 0.60 0.0012 0.0012 0.0000


TotalWaitTime PercentageWaitsOfTotalTime TotalSignalWaitTime PercentageSignalWaitsOfTotalTime
1830735223 80.739100 436734693 19.260800

[mikedavem]

Cela confirme bien ce que je pensais

Les types d'attente les plus importants concernent les performances du stockage :

WRITELOG --> écriture dans le journal des transactions
PAGEIOLATCH_UP / PAGEIOLATCH_SH --> délai de traitement des IO (lecture / écriture) depuis les disques .. Cela peut être dû à un manque de mémoire aussi donc il faut prendre avec parcimonie ce type d'attente surtout que tu viens de verrouiller les pages en mémoire il y a peu de temps.
IO_COMPLETION --> attente concernant la fin d'opération d'une IO

Tu as même le type d'attente SQLTRACE_FILE_WRITE_IO_COMPLETION qui met en évidence ce problème (type d'attente lié je pense ici à la trace par défaut qui tourne sur ton serveur SQL)

Maintenant il faut voir si en augmentant la mémoire cela règle ton problème car visiblement le PLE a l'air plus que correct sur ton serveur. Toutes les requêtes / transactions qui viendront écrire dans ta base ne seront par contre pas épargnés. L'écriture dans le journal des transactions est de nature synchrone qui plus est.

Est-ce que tu peux lancer une dernière requête sur ton serveur de bases de données et nous fournir le résultat

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
 
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

++

[elsuket]

Bonjour,

En sus de la requête que vous a donné Mikedavem, pouvez-vous donner la moyenne que vous observez pour le compteur de performance Buffer Cache Hit Ratio (faites plusieurs exécutions de la requête, ou visualisez-le à l'aide de PerfMon

@++

[chloeb]

J'ai mis en pièce jointe le résultat de la requête.

elsuket je reviens vers toi quand j'aurais fait une moyenne!

[mikedavem]

Les statistiques d'attente IO sur vos fichiers vont dans le même sens que les statistiques générales du serveur sql. On est largement au dessus du seuil raisonnable pour avoir des performances correctes.

En écriture sur les fichiers de transaction : en moyenne 83 ms --> problème
En écriture sur les fichiers de données : en moyenne 89 ms --> problème
En lecture sur les fichiers de données : en moyenne 20 ms --> correcte

++

[dbaffaleuf]

Il va falloir regarder la configuration des disques.
=> Les données se trouvent-elles sur les mêmes disques physiques que les journaux ?
=> Quel type de stockage (disques internes, SAN)

[filipmarchan]

Bonjour Chloé, Votre thread m'intéresse car je pense avoir un problème très similaire. Sur SQL 2K8 SR4 Entreprise j'ai une instance SQL qui héberge 2 applications dans 2 bases différentes. Après 2 ou 3 semaines de fonctionnement mon instance devient quasiment inutilisable. Je redémarre l'instance et ça marche à nouveau bien puis petit à petit c'est de plus en plus lent et je dois redémarrer mon instance après 2 semaines.
J'ai utilisé un outil qui s'appelle SQLyzer (je vous conseille d'essayer c'est facile à installer et gratuit pdt 1 mois) qui m'a permis de voir qu'au fur à mesure des attentes de types CMEMTHREAD s'accumulent et utilisent quasiment toutes les ressources SQL. Quand on redémarre ces attentes disparraissent et reviennent petit à petit. Mon instance est configurée pour utiliser 48Gb (pour une machine de 64Gb). Ces attentes ne sont pas exclusives à une requête, une application ou une base... Un consultant spécialisé dans le tuning a essayé sans succès de résoudre ce problème.

Pouvez-vous me confirmer oui ou non si les symptomes correspondent?

Merci

[filipmarchan]

Je vois que le thread a shifté d'un pb de mémoire vers un pb d'IO (désolé, j'en était resté à la première page) je suis donc hors sujet. Ceci dit l'outil dont je parlais (SQLYzer) peut sans doute vous aider surout si vous avez des baies de stockage EMC.

[chloeb]

ça commence à devenir de plus en plus technique lol!

Je vais essayer de répondre au mieux!

J'ai 2 disques durs internes :
- system de 58.5GB dont 43,6 used
- D de 872GB dont 862 free (contient le code des sites internet uniquement)

[dbaffaleuf]

- Sur quel disque se trouve la base ?
- N'y a-t-il que 2 disques physiques ou davantage de disques mais en RAID ?
- Quel modèle de machine ? (taper systeminfo sous un prompt dos)