Question perf SQL 2005

**agdid04** · 09/09/2015, 12h10

Bonjour,

J’ai ces indicateurs sur un server SQL 2005 SP2 sur windows 2003R2

Code :

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

1
2
3
4
5
6
7
8
9
10
LCK_M_S			364187
PAGEIOLATCH_SH		8859
IO_COMPLETION		4687
CHKPT				1078
MSQL_XP			359
ASYNC_NETWORK_IO		218
PAGELATCH_EX			203
PAGELATCH_SH		93
LATCH_EX			15
PAGEIOLATCH_EX		15

Ou encore

Code :

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

1
2
3
4
5
6
7
8
9
10
SQLTRACE_BUFFER_FLUSH	6710	93.05
LCK_M_S	454	6.30
BROKER_TASK_STOP	31	0.44
PAGEIOLATCH_SH	8	0.12
IO_COMPLETION	4	0.06
CHKPT	1	0.01
MSQL_XP	0	0.00
ASYNC_NETWORK_IO	0	0.00
PAGELATCH_EX	0	0.00
PAGELATCH_SH	0	0.00

Code :

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

1
2
3
SELECT * FROM sys.dm_os_waiting_tasks
 
0x0089A3E8	15	0	6803547	PAGEIOLATCH_SH	0x02D91BD0	NULL	NULL	NULL	150:1:704

Alors je voudrai savoir si c’est grave « docteur »

ou il faut retoucher aux réglages de mon instance ?
Merci d’avance.

**mikedavem** · 09/09/2015, 12h27

J’ai ces indicateurs sur un server SQL 2005 SP2 sur windows 2003R2

A quoi correspond tes indicateurs exactement ? Types d'attentes et les chiffres correspondent à quoi ? Attentes cumulées, temps cumulé, temps moyen ... ?

++

**agdid04** · 09/09/2015, 13h22

Envoyé par mikedavem

A quoi correspond tes indicateurs exactement ? Types d'attentes et les chiffres correspondent à quoi ? Attentes cumulées, temps cumulé, temps moyen ... ?

++

Merci, voici la requete correspondante:

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
SELECT TOP 10
 
wait_type, wait_time_ms
 
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', 'BROKER_TASK_STOP',
 
'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' )
 
ORDER BY wait_time_ms DESC

A+

**mikedavem** · 09/09/2015, 13h38

Essaie de voir avec ceci :

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

++

**agdid04** · 09/09/2015, 14h26

Envoyé par mikedavem

Essaie de voir avec ceci :

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

++

Merci bien, voici le retour de la requete et copie ecran en pj.

Code :

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

1
2
3
LCK_M_S	848.19	848.19	0.00	434	98.20	1.9543	1.9543	0.0000
 
26805462	99.999600	107	0.000300

A+

**mikedavem** · 09/09/2015, 15h27

Envoyé par agdid04

Merci bien, voici le retour de la requete et copie ecran en pj.

A+

C'est moi ou je ne vois pas de pièces jointes ?

++

Question perf SQL 2005 [2005]

Administration SQL Server

Vue hybride

Discussions similaires

Partager

Partager