SQL Servertempdb guide

SELECT
    df.file_id,
    df.name AS logical_name,
    df.type_desc,
    df.physical_name,
    CONVERT(decimal(18,2), df.size / 128.0) AS size_mb,
    CASE
        WHEN df.is_percent_growth = 1
            THEN CONCAT(df.growth, N'%')
        ELSE CONCAT(CONVERT(decimal(18,2), df.growth / 128.0), N' MB')
    END AS growth_setting,
    CASE
        WHEN df.max_size = -1 THEN N'Unlimited'
        WHEN df.max_size = 0 THEN N'No growth'
        ELSE CONCAT(CONVERT(decimal(18,2), df.max_size / 128.0), N' MB')
    END AS max_size,
    df.is_percent_growth
FROM tempdb.sys.database_files AS df
ORDER BY df.type_desc, df.file_id;

USE tempdb;

SELECT
    df.file_id,
    df.name AS file_name,
    CONVERT(decimal(18,2), fs.total_page_count / 128.0) AS total_mb,
    CONVERT(decimal(18,2), fs.unallocated_extent_page_count / 128.0) AS free_mb,
    CONVERT(decimal(18,2), fs.user_object_reserved_page_count / 128.0) AS user_objects_mb,
    CONVERT(decimal(18,2), fs.internal_object_reserved_page_count / 128.0) AS internal_objects_mb,
    CONVERT(decimal(18,2), fs.version_store_reserved_page_count / 128.0) AS version_store_mb,
    CONVERT(decimal(18,2), fs.mixed_extent_page_count / 128.0) AS mixed_extents_mb
FROM sys.dm_db_file_space_usage AS fs
JOIN sys.database_files AS df
    ON df.file_id = fs.file_id
ORDER BY df.file_id;

USE tempdb;

SELECT TOP (50)
    ssu.session_id,
    es.status,
    es.host_name,
    es.program_name,
    es.login_name,
    CONVERT(decimal(18,2),
        (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count) / 128.0
    ) AS user_objects_mb,
    CONVERT(decimal(18,2),
        (ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) / 128.0
    ) AS internal_objects_mb,
    es.open_transaction_count,
    es.last_request_start_time,
    es.last_request_end_time
FROM sys.dm_db_session_space_usage AS ssu
LEFT JOIN sys.dm_exec_sessions AS es
    ON es.session_id = ssu.session_id
WHERE ssu.session_id <> @@SPID
ORDER BY
    (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count)
  + (ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) DESC;

USE tempdb;

SELECT TOP (50)
    tsu.session_id,
    tsu.request_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    CONVERT(decimal(18,2),
        (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) / 128.0
    ) AS task_user_objects_mb,
    CONVERT(decimal(18,2),
        (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) / 128.0
    ) AS task_internal_objects_mb,
    SUBSTRING(
        sql_text.text,
        (r.statement_start_offset / 2) + 1,
        CASE
            WHEN r.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(max), sql_text.text))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    ) AS running_statement
FROM sys.dm_db_task_space_usage AS tsu
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = tsu.session_id
   AND r.request_id = tsu.request_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS sql_text
WHERE tsu.session_id <> @@SPID
ORDER BY
    (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)
  + (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC;

SELECT
    DB_NAME(vs.database_id) AS database_name,
    CONVERT(decimal(18,2), vs.reserved_page_count / 128.0) AS version_store_mb
FROM sys.dm_tran_version_store_space_usage AS vs
ORDER BY vs.reserved_page_count DESC;

-- Run in the database where row-versioning pressure is suspected.
SELECT
    DB_NAME() AS database_name,
    transaction_id,
    transaction_sequence_num,
    commit_sequence_num,
    elapsed_time_seconds,
    session_id,
    is_snapshot,
    first_snapshot_sequence_num,
    max_version_chain_traversed,
    average_version_chain_traversed
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

SELECT
    wt.session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.blocking_session_id,
    wt.resource_description,
    r.status,
    r.command,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_os_waiting_tasks AS wt
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = wt.session_id
LEFT JOIN sys.dm_exec_sessions AS s
    ON s.session_id = wt.session_id
WHERE wt.session_id <> @@SPID
  AND (
      wt.resource_description LIKE N'2:%'
      OR r.wait_resource LIKE N'2:%'
      OR r.database_id = 2
  )
ORDER BY wt.wait_duration_ms DESC;

SELECT
    mf.file_id,
    mf.name AS logical_name,
    mf.type_desc,
    vfs.num_of_reads,
    vfs.num_of_bytes_read,
    vfs.io_stall_read_ms,
    CONVERT(decimal(18,2),
        vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0.0)
    ) AS avg_read_latency_ms,
    vfs.num_of_writes,
    vfs.num_of_bytes_written,
    vfs.io_stall_write_ms,
    CONVERT(decimal(18,2),
        vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0.0)
    ) AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) AS vfs
JOIN tempdb.sys.database_files AS mf
    ON mf.file_id = vfs.file_id
ORDER BY avg_write_latency_ms DESC, avg_read_latency_ms DESC;

SELECT TOP (50)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_status,
    DATETIMEFROMPARTS(
        h.run_date / 10000,
        (h.run_date % 10000) / 100,
        h.run_date % 100,
        h.run_time / 10000,
        (h.run_time % 10000) / 100,
        h.run_time % 100,
        0
    ) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS run_duration_seconds,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.step_id > 0
ORDER BY run_start_time DESC;