Bonjour,

Sur un serveur MSSQL 2016 j'ai un problème de tempdb dont les fichiers grossissent à un moment donné jusqu'à saturer le DD.

Lors des phases de croissances (elles ne sont pas régulières) je voudrais lister les transactions qui consomment des pages dans le tempdb.

J'ai récupéré la requête suivante :
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
52
53
54
/*-------------------------------------------------------------------
[SCRIPT] Tempdb consommateurs
[DATABASE]
[DESCRIPTION] Les plus gros consommateurs de tempdb
-------------------------------------------------------------------*/
SELECT ta.session_id
,status
,login_name
,host_name
,program_name
,db_name
,login_time
,allocated_pages
,total_alloc_page_count
,total_dealloc_page_count
,user_objects_alloc_page_count
,user_objects_dealloc_page_count
,internal_objects_alloc_page_count
,internal_objects_dealloc_page_count
,rq.text
FROM ( SELECT ssp.session_id
,se.status
,se.login_name
,se.host_name
,se.program_name
,DB_NAME(se.database_id) AS db_name
,se.login_time
,( SUM(ssp.user_objects_alloc_page_count + tsp.user_objects_alloc_page_count)
+ SUM(ssp.internal_objects_alloc_page_count + tsp.internal_objects_alloc_page_count) )
- ( SUM(ssp.user_objects_dealloc_page_count + tsp.user_objects_dealloc_page_count)
+ SUM(ssp.internal_objects_dealloc_page_count + tsp.internal_objects_dealloc_page_count) ) AS allocated_pages
,SUM(ssp.user_objects_alloc_page_count + tsp.user_objects_alloc_page_count)
+ SUM(ssp.internal_objects_alloc_page_count + tsp.internal_objects_alloc_page_count) AS total_alloc_page_count
,SUM(ssp.user_objects_dealloc_page_count + tsp.user_objects_dealloc_page_count)
+ SUM(ssp.internal_objects_dealloc_page_count + tsp.internal_objects_dealloc_page_count) AS total_dealloc_page_count
,SUM(ssp.user_objects_alloc_page_count + tsp.user_objects_alloc_page_count) AS user_objects_alloc_page_count
,SUM(ssp.user_objects_dealloc_page_count + tsp.user_objects_dealloc_page_count) AS user_objects_dealloc_page_count
,SUM(ssp.internal_objects_alloc_page_count + tsp.internal_objects_alloc_page_count) AS internal_objects_alloc_page_count
,SUM(ssp.internal_objects_dealloc_page_count + tsp.internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage ssp ( READUNCOMMITTED )
JOIN sys.dm_db_task_space_usage tsp ( READUNCOMMITTED ) ON ssp.session_id = tsp.session_id
JOIN sys.dm_exec_sessions se ( READUNCOMMITTED ) ON ssp.session_id = se.session_id
GROUP BY ssp.session_id
,se.status
,se.login_name
,se.host_name
,se.program_name
,DB_NAME(se.database_id)
,se.login_time
) AS ta
LEFT JOIN sys.dm_exec_connections c ( READUNCOMMITTED ) ON ta.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS rq
ORDER BY session_id DESC
,total_alloc_page_count DESC
Mais avec cette requête je vois des requêtes avec des pages allouées dans tempdb mais si je cumule le nb de pages je ne retrouve pas le nombre de page total indiqué pour les fichiers tempdb avec la requêtes suivante :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
SELECT name , physical_name , (size * 8)/1024 as [Size MB]
,total_page_count AS [total pages]
,( total_page_count  / 128 ) AS [total space in MB]
,unallocated_extent_page_count AS [free pages]
,( unallocated_extent_page_count  / 128 ) AS [free space in MB]
,( total_page_count  / 128 ) - ( unallocated_extent_page_count  / 128 ) AS [ use space in MB]
FROM tempdb.sys.dm_db_file_space_usage inner join tempdb.sys.database_files ON tempdb.sys.database_files.file_id = tempdb.sys.dm_db_file_space_usage.file_id ;
Avez-vous une requête permettant de lister toutes les transactions qui consomment les pages dans tempdb afin de trouver le coupable de cette croissance ?

Merci

Patrick