Contention dans TempDB ?

[elsuket]

Bonjour,

A certains moments de la journée, j'observe que le nombre de sessions ouvertes retournées par cette requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
SELECT		SM.host_name
		, SM.login_name
		, SM.program_name
		, UPPER(SM.STATUS) AS STATUS
		, COUNT(*) AS session_count
		, MIN(SM.login_time) AS min_login_time
		, MAX(SM.login_time) AS max_login_time
		, MIN(SM.last_request_end_time) AS min_last_request_end_time
		, EC.client_net_address
FROM		sys.dm_exec_sessions AS SM
INNER JOIN	sys.dm_exec_connections AS EC 
			ON SM.session_id = EC.session_id
GROUP BY	SM.host_name
		, SM.program_name
		, SM.STATUS
		, SM.login_name
		, EC.client_net_address
HAVING		COUNT(*) > 5
ORDER		BY STATUS, COUNT(*) DESC

Oscille entre 150 et 180 par serveur (host_name) et ont le statut RUNNING, et environ le même nombre SLEEPING.
A d'autres j'ai seulement des sessions dont le statut est SLEEPING.
Dans les deux cas je n'observe jamais de RUNNABLE.

D'autre part j'ai remarqué que la DMV sys.dm_os_wait_stats montre un nombre important de PAGELATCH_UP.
J'ai donc cherché où ça bloque avec :

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
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
SELECT		D.name AS database_name		
		, WT.session_id
		, WT.wait_type
		, WT.wait_duration_ms
		, WT.blocking_session_id
		, WT.resource_description
		, BD.page_id
		, BD.page_level
		, BD.page_type
		, BD.row_count
		, BD.free_space_in_bytes
		, BD.is_modified AS is_dirty
		, MF.physical_name AS file_physical_name
		, MF.name AS file_logical_name
FROM		sys.dm_os_waiting_tasks AS WT
INNER JOIN	sys.dm_os_buffer_descriptors AS BD
			ON LEFT(WT.resource_description, CHARINDEX(':', WT.resource_description, 0) - 1) = BD.database_id
			AND SUBSTRING(WT.resource_description, CHARINDEX(':', WT.resource_description) + 1, CHARINDEX(':', WT.resource_description, CHARINDEX(':', resource_description) + 1) - (CHARINDEX(':', resource_description) + 1)) = BD.[file_id]
			AND RIGHT(WT.resource_description, LEN(WT.resource_description) - CHARINDEX(':', WT.resource_description, 3)) = BD.[page_id]
INNER JOIN	sys.DATABASES AS D
			ON D.database_id = BD.database_id
INNER JOIN	sys.master_files AS MF
			ON D.database_id = MF.database_id
			AND BD.file_id = MF.file_id
WHERE		WT.wait_type LIKE 'PAGE%LATCH_%'
AND		D.name = 'TempDB'

Ce qui m'a permis de trouver que la page PFS de TempDB (2:1:1 dans WT.resource_description) subit pas mal de contention (entre 30 et 40 sessions tentent d'y accéder en même temps).
Ceci est bien sûr du à l'utilisation de variables de type table, et je ne peux globalement rien y faire

La base de données TempDB a pour l'instant deux fichiers de données (qui sont loin d'être plein, chacun fait exactement 3GB), mais je n'ai observé de contention que sur un des deux fichiers, pas l'autre, et les deux fichiers sont sur deux volumes différents.

D'autre part j'ai remarqué qu'il y a des variables de type table qui sont sur le serveur depuis le 22 Décembre 2011.
Aucune d'entre elles n'occupe de l'espace (2 octets, zéro lignes).

Qu'en pensez-vous à part que je devrait peut-être ajouter des fichiers à TempDB ?
Je ne pense pas que le trace flag 1118 soit utile dans mon cas car les tables sont de petite taille ...

@++

[mikedavem]

Plusieurs choses :

1- Tu as vérifié si tu avais une activité IO homogène sur l'ensemble de tes fichiers tempdb ? (via sys.dm_io_virtual_file_stats par exemple).

2- Tu dis que chaque fichier est sur son propre volume ? Mais est-ce que ces volumes sont sur leurs propres axes physiques ?

3- Tu as vérifié si un de tes volumes n'avaient pas un souci de performance (latence, file d'attente des disques etc ..) ? (via les objets de compteurs SQL ou sys.dm_io_virtual_file_stats )


++

[elsuket]

Bonjour,

1. Non, mais j'avoue que c'est bête. La raison c'est que comme les fichiers font exactement la même taille, je n'ai pas pensé cela nécessaire.

2. Ben ... pas facile de communiquer avec l'administrateur SAN ...

3. Je vais voir cela

Je vais faire un bon tour avec sys.dm_io_virtual_file_stats (c'est con en plus j'ai la requête toute prête, je l'ai juste pas exécutée ), et je te tiens au jus.

Merci

[elsuket]

En pièce jointe, la comparaison des attentes entre les deux serveurs, résultat de la requête suivante :

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
SELECT		D.name AS database_name
		, MF.physical_name
		, MF.type_desc
		, VFS.io_stall_read_ms / NULLIF(VFS.num_of_reads, 0) AS avg_read_latency_ms
		, VFS.io_stall_write_ms / NULLIF(VFS.num_of_writes, 0) AS avg_write_latency_ms
		, VFS.io_stall / NULLIF(VFS.num_of_reads + VFS.num_of_writes, 0) AS avg_total_latency_ms
		, VFS.num_of_bytes_read / NULLIF(VFS.num_of_reads, 0) AS avg_bytes_per_read
		, VFS.num_of_bytes_written / NULLIF(VFS.num_of_writes, 0) AS avg_bytes_per_write
		, VFS.io_stall
		, VFS.num_of_reads
		, VFS.num_of_bytes_read
		, VFS.io_stall_read_ms
		, VFS.num_of_writes
		, VFS.num_of_bytes_written
		, VFS.io_stall_write_ms
		, CAST(VFS.size_on_disk_bytes / 1024 / 1024.0 AS decimal(16,2)) AS size_on_disk_MB
FROM		sys.dm_io_virtual_file_stats(NULL, NULL) AS VFS
INNER JOIN	sys.master_files AS MF
			ON VFS.database_id = MF.database_id
			AND VFS.file_id = MF.file_id
INNER JOIN	sys.DATABASES AS D
			ON D.database_id = VFS.database_id
WHERE		D.name = 'tempdb'
AND		MF.type_desc = 'ROWS'
ORDER BY	D.name, MF.physical_name, avg_read_latency_ms DESC

Le second subit beaucoup plus d'attentes que le premier ...

@++

[dbaffaleuf]

- Sur le second le nb d'IO n'est pas homogène entre tes deux fichiers (393M reads sur le NDF et 590M sur le MDF). S'il y a plus d'IOs sur le MDF c'est qu'il y a plus d'espace libre dedans.
- Pas de RCSI activé sur ces machines ? Pas de version store ?