Co się dzieje w tempdb?

6-Kwi-2014

Może są otwarte jakieś transkacje? Kandydat na zasobożercę:

DBCC OPENTRAN

Jeśli znajdziesz tutaj numer sesji, to może warto zobaczyć co ta sesja robi?

select t.text,* from sys.dm_exec_connections c
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) t
where c.session_id=55

A może ta lub jakaś inna sesja zablokowała inną sesję. Odpowiedź da (kolumna BlkBy):

EXEC sp_who2

Może powstały jakieś olbrzymie tabele w tempdb? Poniższe zapytanie wyświetli tabele i ich rozmiary:

use tempdb;
 
SELECT TBL.name AS ObjName
,STAT.row_count AS StatRowCount
,STAT.used_page_count * 8 AS UsedSizeKB
,STAT.reserved_page_count * 8 AS RevervedSizeKB
FROM tempdb.sys.partitions AS PART
INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT
ON PART.partition_id = STAT.partition_id
AND PART.partition_number = STAT.partition_number
INNER JOIN tempdb.sys.tables AS TBL
ON STAT.object_id = TBL.object_id
ORDER BY TBL.name;
 

To może popatrzmy, która sesja otrzymywała najwięcej przestrzeniw w tempdb? Będzie to kandydat na zasobożercę:

SELECT
sys.dm_exec_sessions.session_id AS [SESSION ID],
DB_NAME(database_id) 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

 

i znowu sprawdzenie co ta sesja robi, jak wyżej

Dodaj komentarz:

Autor: Rafał Kraik