Discussion :

[agdid04]

Bonjour

Je cherche à récupérer les requêtes qui saturent la base tempdb .... ?

Par avance Merci.

A+

[janlouk]

Bonjour,

J'ai ça pour voir l'historique de la tempDB. Mais je ne sais pas vérifier maintenant, donc ce ne sera peut-être pas ce que tu recherches.

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
SELECT sys.dm_exec_sessions.session_id AS [SESSION ID] ,
DB_NAME() AS [DATABASE Name] ,
HOST_NAME AS [System Name] ,
program_name AS [Program Name] ,login_name AS [USER Name] ,status ,cpu_time AS [CPU TIME (in milisec)] ,
total_scheduled_time AS [Total Scheduled TIME (in milisec)] ,total_elapsed_time AS [Elapsed TIME (in milisec)] ,
(memory_usage * 8) AS [Memory USAGE (in KB)] ,
(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)] ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)] ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)] ,
(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)] ,CASE is_user_process WHEN 1 THEN 'user session'
WHEN 0 THEN 'system session' END AS [SESSION Type],
 row_count AS [ROW COUNT] 
FROM sys.dm_db_session_space_usage
 INNER join sys.dm_exec_sessions 
ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id
 
 
 
--Suivi
select * from sys.dm_db_file_space_usage
select * from sys.dm_db_session_space_usage
select * from sys.dm_db_task_space_usage