Vision's Impact on TempDB

Below is a script that you can run throughout the day to understand your Deltek Vision environment's impact on the tempdb database.

//As usual, I provide no guarantees and cannot be liable for the code listed below.

select
tskUse.session_id,
tskUse.request_id,
ses.host_name,
ses.program_name,
req.cpu_time,
req.reads,
req.writes,
req.granted_query_memory*8/104 as granted_query_memoryMB,
tskUse.task_allocation_count*8/1024 as task_allocation_countMB,
tskUse.task_deallocation_count*8/1024 as task_deallocation_countMB,
(SELECT text FROM sys.dm_exec_sql_text(req.sql_handle)) AS TheQuery
from ( Select session_id, request_id,
sum( internal_objects_alloc_page_count +
user_objects_alloc_page_count) as task_allocation_count,
sum (internal_objects_dealloc_page_count +
user_objects_dealloc_page_count) as task_deallocation_count
from sys.dm_db_task_space_usage
where session_id > 50
group by session_id, request_id) as tskUse
inner join sys.dm_exec_requests as req on tskUse.session_id = req.session_id and tskUse.request_id = req.request_id
inner join sys.dm_exec_sessions as ses on req.session_id = ses.session_id
where req.database_id IN(select database_id from sys.databases where name = 'Vision')
order by tskUse.task_allocation_count DESC