Jak znaleźć instrukcje SQL, które spowodowały wzrost tempdb?

26

Tempdb serwera (SQL Server 2008) wzrasta do 500 GB + kilka razy w miesiącu. Czy można dowiedzieć się, które instrukcje SQL spowodowały ten problem? Problem zwykle nie jest spowodowany złożonymi połączeniami create table #temp...; insert into #temp...lub select ... into #temp...tylko nimi.

Początkowe Wielkość niektórych plików tempdb jest również automatycznie ustawione na znacznie większe wartości za każdym razem. Jak temu zapobiec?

Czasami plany buforowane uniemożliwiają zmianę rozmiaru / zmniejszenie plików. Jak znaleźć, który posiada tempdb?

sql-server sql-server-2008 tempdb

— u23432534
źródło

1

Przepraszam, że jest prawie druga w nocy i brakuje mi paliwa, aby całkowicie odpowiedzieć na to pytanie, jednak te adresy URL mogą się przydać podczas oczekiwania na inne odpowiedzi - mssqltips.com/sqlservertip/1432/… i google.com/search?q= które + zapytania + są + przy użyciu + tempdb

— Aaron Bertrand

27

Istnieją trzy DMV, których możesz użyć do śledzenia użycia tempdb:

Pierwsze dwa pozwalają śledzić przydziały na poziomie zapytania i sesji. Trzeci śledzi przydziały dla magazynu wersji, użytkownika i obiektów wewnętrznych.

Poniższe przykładowe zapytanie da przydziały na sesję:

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

Jeśli chcesz śledzić użycie przez pewien czas, rozważ zbieranie danych za pomocą sp_whoisactive , jak wykazała Kendra Little .

— Mark Storey-Smith
źródło

3

Dzięki. Czy różnica [SPACE Allocated FOR USER Objects (in KB)]i [SPACE Deallocated FOR USER Objects (in KB)]faktyczne zajęte miejsce sesji?

— u23432534

4

Mogą istnieć różne źródła problemu:

użycie zmiennych tabel lub tabel tymczasowych
serwer sql utworzył pośrednie zestawy wyników jako tabele robocze w tempdb - zwykle do celów sortowania (zwykle jest to znak nieobecnych indeksów / nieaktualnych statystyk)
serwer sql postanowił wstępnie ocenić zestaw wyników funkcji wycenianej przez tabelę iw tym przypadku przechowuje dane w tempdb
odtwarzanie indeksów z opcją SORT_IN_TEMPDB = ON

— Oleg Dok
źródło