Znajdź, która sesja przechowuje który stół tymczasowy

Mamy bazę danych SQL Server 2005, baza danych temp została zapełniona. Wchodząc do SQL Server Management Studio widzę wszystkie tabele tymczasowe w tempdb. Czy można stwierdzić, która sesja przechowuje tabelę temp? Idealnie byłoby zapytanie, które wyświetlałoby tabele tymczasowe używane przez każdą sesję.

Dzięki,

Poprosiłem o zbudowanie czegoś w 2007 roku na Connect. Zostało to odrzucone w wersji 2008, a następnie zignorowane, dopóki Connect nie zmarł kilka lat temu. Próbowałem znaleźć go na nowej stronie z opiniami dla SQL Server , ale to wyszukiwanie jest absolutnym pożarem śmietnika. Tytuł mojego żądania brzmiał: „dmv odwzorować tabelę temp na id_sesji” - ponieważ wyszukiwanie może wykonać tylko operację LUB, „mapa temp tabeli” zwraca 118 stron wyników. Wydaje się, że Google sugeruje, że przedmiot nie zrobił cięcia, gdy zabili Connect .

W międzyczasie dla SQL Server 2005 i 2008 powinieneś być w stanie pobrać te informacje z domyślnego śledzenia:

DECLARE @FileName VARCHAR(MAX)  

SELECT @FileName = SUBSTRING(path, 0,
   LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT   
     o.name,   
     o.OBJECT_ID,  
     o.create_date, 
     gt.NTUserName,  
     gt.HostName,  
     gt.SPID,  
     gt.DatabaseName,  
     gt.TEXTData 
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
JOIN tempdb.sys.objects AS o   
     ON gt.ObjectID = o.OBJECT_ID  
WHERE gt.DatabaseID = 2 
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)   
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

_{Bezwstydnie zdjęty z tego posta na blogu Jonathana Kehayiasa .}

Aby określić wykorzystanie miejsca, możesz to jeszcze bardziej ulepszyć, aby dołączyć dane z widoków takich jak sys.db_db_partition_stats- np .:

DECLARE @FileName VARCHAR(MAX)  

SELECT @FileName = SUBSTRING(path, 0,
   LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT   
     o.name,   
     o.OBJECT_ID,  
     o.create_date, 
     gt.NTUserName,  
     gt.HostName,  
     gt.SPID,  
     gt.DatabaseName,  
     gt.TEXTData,
     row_count = x.rc,
     used_page_count = x.upc
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
JOIN tempdb.sys.objects AS o   
     ON gt.ObjectID = o.OBJECT_ID
INNER JOIN
(
 SELECT [object_id], SUM(row_count), SUM(used_page_count)
   FROM tempdb.sys.dm_db_partition_stats
   WHERE index_id IN (0,1)
   GROUP BY [object_id]
) AS x(id, rc, upc)
ON x.id = o.[object_id]
WHERE gt.DatabaseID = 2 
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)   
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)

Problemem jest tutaj próba skorelowania nazwy tabeli z tekstem zapytania; to po prostu nie jest praktyczne, ponieważ przez większość czasu użytkownik nadal nie wykonuje zapytania względem tej tabeli (nie wspominając o tym, że nadal uruchamia ten, który ją utworzył / zapełnił).

Jednak dotyczy to innych czytelników (lub Ciebie podczas aktualizacji), domyślny ślad w 2012+ nie śledzi już tworzenia obiektu tabeli temp , jeśli tabela #temp jest stertą. Nie jestem pewien, czy jest to zbieg okoliczności, czy bezpośrednio związany z faktem, że od 2012 r. Wszystkie tabele temp mają teraz wartość ujemnąobject_id . Możesz oczywiście przejść do Rozszerzonych wydarzeń, aby pomóc Ci zebrać i śledzić te informacje, ale to prawdopodobnie dużo pracy ręcznej (a ja tylko zweryfikowałem, że nie są one już śledzone - możesz nie być w stanie ich wybrać w Wydarzeniach rozszerzonych). Domyślny ślad to podnieś tabele temp. utworzone za pomocą PK lub innego ograniczenia albo z ograniczeniami lub indeksami dodanymi po zdarzeniu tworzenia, ale wtedy będziesz musiał poluzować ograniczenia czasowe powyżej (indeks można utworzyć znacznie później niż 100 ms po kreacja).

Kilka innych odpowiedzi na tej stronie, które mogą być przydatne:

• Jak rozpoznać, które zapytanie wypełnia dziennik transakcji tempdb?

• Coraz więcej problemów z plikiem mdf TempDB

• Znajdź transakcje wypełniające sklep z wersjami

Pisałem także o tym na blogu, korzystając ze niestandardowej sesji Extended Events, aby śledzić te informacje w programie SQL Server 2012 i nowszych:

• Koleś, kto jest właścicielem tego #temp table?

A Paul White pisał na blogu o bezpośrednim czytaniu stron (nie do końca dla osób o słabym sercu, ani łatwych do zautomatyzowania w jakikolwiek sposób):

• Wyświetlanie tymczasowego stołu innej sesji

Oto zapytanie, które powinno zacząć od znalezienia poszukiwanych informacji:

select top 10
    tsu.session_id,
    tsu.request_id,
    r.command,
    s.login_name,
    s.host_name,
    s.program_name,
    total_objects_alloc_page_count = 
        tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count,
    tsu.user_objects_alloc_page_count,
    tsu.user_objects_dealloc_page_count,
    tsu.internal_objects_alloc_page_count,
    tsu.internal_objects_dealloc_page_count,
    st.text
from sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests r
on tsu.session_id = r.session_id
and tsu.request_id = r.request_id
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
where tsu.user_objects_alloc_page_count > 0
or tsu.internal_objects_alloc_page_count > 0
order by total_objects_alloc_page_count desc;

To zapytanie pobiera przydatne informacje dla 10 najważniejszych zadań, takich jak strony przydzielone / cofnięte, tekst SQL zadań (jeśli są dostępne) itp.

Te DMV są pełne świetnych informacji, więc jeśli potrzebujesz więcej danych, możesz mieszać i dopasowywać do tego, co wyciągasz. Ale powinien to być punkt wyjścia do rozwiązywania problemów z bieżącymi zadaniami zużycia tempdb.