Vous êtes ici :
Ce script permet de connaître l’utilisation de tempdb par session utilisateur :
Transact-SQL
|
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 |
/*------------------------------------------------------------------- [SCRIPT] tempdb consumption by user session [DATABASE] tempdb [DESCRIPTION] tempdb consumption by user session [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ USE tempdb GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 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 ,sd.name 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 JOIN sys.dm_db_task_space_usage tsp ON ssp.session_id = tsp.session_id JOIN sys.dm_exec_sessions se ON ssp.session_id = se.session_id JOIN sys.databases sd ON se.database_id = sd.database_id GROUP BY ssp.session_id ,se.status ,se.login_name ,se.host_name ,se.program_name ,sd.name ,se.login_time ) AS ta LEFT JOIN sys.dm_exec_connections c ON ta.session_id = c.session_id OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS rq WHERE ta.session_id > 50 ORDER BY allocated_pages DESC ,total_alloc_page_count DESC |
Table of Contents