Contention dans TempDB ?

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

@++ ;)

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 )


++

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 :aie:), et je te tiens au jus.

Merci ;)

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

@++ ;)

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