Select sur sys.dm_os_wait_stats

Version imprimable

Bonjour,

J'ai le resultat de cette requête:

Code:

1
2
3
4
5
6
7
select * from sys.dm_os_wait_stats WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','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', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER')
and wait_type not like 'SLEEP%'
and wait_time_ms>0

Résultat:

Code:

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
wait_type	  		waiting_tasks_count		wait_time_ms	max_wait_time_ms	signal_wait_time_ms
LCK_M_SCH_M			804		139		4		83
LCK_M_S				4		11		5		0
LCK_M_X				47		15		0		3
LATCH_SH	1			3469		3350		5		1504
LATCH_EX				803		271		5		131
PAGELATCH_SH			744071		13526		10		11293
PAGELATCH_UP			297		26		3		12
PAGELATCH_EX			1772843		53044		9		42322
PAGEIOLATCH_SH			5126		8740		60		64
PAGEIOLATCH_UP			148073		139562		336		3433
PAGEIOLATCH_EX			132		378		21		0
IO_COMPLETION			877		504		22		6
ASYNC_IO_COMPLETION		1		35		35		0
ASYNC_NETWORK_IO			206947		41372		39		11972
CHKPT				1		715		715		0
OLEDB				9592		6		0		0
THREADPOOL			6		8		3		0
SOS_SCHEDULER_YIELD		17520042		344596		32		285868
SQLTRACE_FILE_BUFFER		600		2434		991		57
WRITELOG				428947		521839		354		28684
CMEMTHREAD			1271		72		1		39
CXPACKET				320		1254		170		51
MSQL_XP				63		366		133		0
LOGBUFFER			6		2		0		0
DAC_INIT				1		3		3		0
PREEMPTIVE_OS_GENERICOPS		15		514		260		0
PREEMPTIVE_OS_AUTHENTICATIONOPS	100017		69017		20		0
PREEMPTIVE_OS_DECRYPTMESSAGE	33316		1119		1		0
PREEMPTIVE_OS_DELETESECURITYCONTEXT	33308		3750		10	0
PREEMPTIVE_OS_AUTHORIZATIONOPS	33342		3731		3		0
PREEMPTIVE_OS_LOOKUPACCOUNTSID		26		30		12	0
PREEMPTIVE_OS_REVERTTOSELF		22		1		0	0
PREEMPTIVE_OS_COMOPS			1		505		505	0
PREEMPTIVE_OS_CRYPTOPS			2		718		370	0
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT		33345		18049		6	0
PREEMPTIVE_OS_CRYPTIMPORTKEY		33317		3067		7	0
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY	33294		19922		8	0
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE	33294	2826		4	0
PREEMPTIVE_OS_DOMAINSERVICESOPS		1	179	179	0
PREEMPTIVE_OS_FILEOPS			90	286	37	0
PREEMPTIVE_OS_CREATEFILE			65	679	321	0
PREEMPTIVE_OS_DELETEFILE			27	19	1	0
PREEMPTIVE_FILESIZEGET			22	19	14	0
PREEMPTIVE_OS_GETDISKFREESPACE		6	2	0	0
PREEMPTIVE_OS_GETFILEATTRIBUTES		58	44	17	0
PREEMPTIVE_OS_SETFILEVALIDDATA		4	2	0	0
PREEMPTIVE_OS_WRITEFILEGATHER		12	41	19	0
PREEMPTIVE_OS_LIBRARYOPS	1		1124	1124	0
PREEMPTIVE_OS_GETPROCADDRESS		63	191	39	0
PREEMPTIVE_OS_LOADLIBRARY	3		171	93	0
PREEMPTIVE_OLEDBOPS			173826	21512	13	0
PREEMPTIVE_OS_PIPEOPS			1	261	261	0
PREEMPTIVE_OS_DISCONNECTNAMEDPIPE	29	2	0	0
PREEMPTIVE_OS_REPORTEVENT	51		46	13	0
PREEMPTIVE_OS_WAITFORSINGLEOBJECT	33309	3684	39	0
PREEMPTIVE_OS_QUERYREGISTRY		1386	1304	12	0
WRITE_COMPLETION	68			38	4	0
FT_IFTSHC_MUTEX				1	389	389	0
PERFORMANCE_COUNTERS_RWLOCK		17	16	13	14
SQLTRACE_FILE_WRITE_IO_COMPLETION		31	18	0	0

Alors je ne sais pas s'il ya des valeurs qui seraient anormalement très élevées ?

Merci.

Bonjour,

Je ne parviens pas à réaligner les chiffres, je crois qu'il vaut mieux que vous preniez une capture d'écran plutôt.
Voici une requête un peu plus exhaustive :

Code:

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
	WAIT AS
	(
		SELECT	wait_type
			, wait_time_ms / 1000.0 AS wait_s
			, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_time_s
			, signal_wait_time_ms / 1000.0 AS signal_wait_time_s
			, waiting_tasks_count
			, 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS percentage
			, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS n
		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', N'QDS_SHUTDOWN_QUEUE'
			)
		AND	waiting_tasks_count > 0
 )
SELECT		MAX (W1.wait_type) AS WaitType
		, CAST (MAX (W1.wait_s) AS decimal (16,2)) AS wait_s
		, CAST (MAX (W1.resource_wait_time_s) AS decimal (16,2)) AS resource_wait_time_s
		, CAST (MAX (W1.signal_wait_time_s) AS decimal (16,2)) AS signal_wait_time_s
		, MAX (W1.waiting_tasks_count) AS waiting_tasks_count
		, CAST (MAX (W1.percentage) AS decimal (5,2)) AS percentage
		, CAST ((MAX (W1.wait_s) / MAX (W1.waiting_tasks_count)) AS decimal (16,4)) AS avg_wait_time_s
		, CAST ((MAX (W1.resource_wait_time_s) / MAX (W1.waiting_tasks_count)) AS decimal (16,4)) AS avg_resource_wait_time_s
		, CAST ((MAX (W1.signal_wait_time_s) / MAX (W1.waiting_tasks_count)) AS decimal (16,4)) AS avg_signal_wait_time_s
FROM		WAIT AS W1
INNER JOIN	WAIT AS W2
			ON W2.n <= W1.n
GROUP BY	W1.n
HAVING		SUM (W2.Percentage) - MAX (W1.Percentage) < 95; -- percentage threshold
GO

Visiblement vous avez pas mal de contention d'accès aux pages en mémoire, donc il est possible que vous ayez aussi de la pression mémoire, par exemple parce que des requêtes lisent beaucoup de pages (pour simplifier drastiquement, une page stocke 8Ko de données) par manque d'index qui supporte bien la requête, ou parce que l'expression de la requête est peut-être à revoir.

La requête suivante devrait vous afficher quelques lignes aux moments où SQL Server rencontre des problèmes de performance :

Code:

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
SELECT		WT.session_id
		, WT.exec_context_id
		, T.scheduler_id
		, WT.wait_duration_ms
		, WT.wait_type
		, WT.blocking_session_id
		, WT.resource_description
		, CASE WT.wait_type
			WHEN N'CXPACKET' THEN RIGHT (WT.resource_description, CHARINDEX (N'=', REVERSE (WT.resource_description)) - 1)
			ELSE NULL
		END AS node_id
		, S.program_name
		, ST.text
		, R.database_id
		, QP.query_plan
		, R.cpu_time
FROM		sys.dm_os_waiting_tasks WT
INNER JOIN	sys.dm_os_tasks T
			ON WT.waiting_task_address = T.task_address
INNER JOIN	sys.dm_exec_sessions S
			ON WT.session_id = S.session_id
INNER JOIN	sys.dm_exec_requests R
			ON S.session_id = R.session_id
OUTER APPLY	sys.dm_exec_sql_text (R.sql_handle) ST
OUTER APPLY	sys.dm_exec_query_plan (R.plan_handle) QP
WHERE		S.is_user_process = 1
AND		WT.wait_type <> 'BROKER_RECEIVE_WAITFOR'
ORDER BY	WT.session_id, WT.exec_context_id;
GO

Je vous engage également à prendre une trace côté serveur pour capturer les caractéristiques des requêtes les plus coûteuses.
Pour aller plus loin, vous pouvez vous en remettre au livre référencé dans ma signature.

@++ ;)

28/06/2016, 10h51
agdid04

1 pièce(s) jointe(s)

Bonjour et Merci.

Ci-joint une copie d'écran de votre 2 ème requête avec son retour.

Merci.

@+
28/06/2016, 14h47
elsuket

Visiblement rien de bien méchant : vous avez des attentes sur les backups qui sont tout à fait normales.

Le type BACKUPIO matérialise le fait qu'il y a eu des attentes pour écrire sur le support de backup (probablement un fichier dans votre cas).
Il peut effectivement être causé par le fait que vous sauvegardez sur un emplacement réseau par exemple.

Mais avant tout, avez-vous des problèmes de durée de backup ?

@++ ;)
29/06/2016, 10h50
agdid04

Bonjour,

Pas de problème côté backup.

Merci bcp.

@+