WAIT Stats

Query to check WAIT Stats





    select
            db_name(exec_re.database_id) databasename,
            wait_tsk.wait_duration_ms,
            wait_tsk.wait_type,
            exec_txt.text,
            exec_qpln.query_plan,
            wait_tsk.session_id,
            exec_ssion.cpu_time,
            exec_ssion.memory_usage,
            exec_ssion.logical_reads,
            exec_ssion.total_elapsed_time,
            exec_ssion.program_name,
            exec_ssion.status
    from
            sys.dm_os_waiting_tasks wait_tsk
           
            inner join sys.dm_exec_requests exec_re
                        on
                        wait_tsk.session_id = exec_re.session_id
           
            inner join sys.dm_exec_sessions exec_ssion
                        on
                        exec_ssion.session_id = exec_re.session_id
           
            cross apply sys.dm_exec_sql_text (exec_re.sql_handle) exec_txt
           
            cross apply sys.dm_exec_query_plan (exec_re.plan_handle) exec_qpln
           
    where
            exec_ssion.is_user_process = 1

TempDB size

Query to check Temp Db size immediately. 

\
SELECT
      [name]                  AS [Logical FILE Name],
      CASE type_desc
            WHEN 'ROWS' THEN 'Data'
            WHEN 'LOG'  THEN 'Log'
      END                     AS [FILE Type],
      physical_name           AS [FILE PATH],
      [SIZE]                  AS [FILE SIZE],
      CASE growth
            WHEN 0 THEN 'Enabled'
            ELSE 'Disabled'
      END                     AS [Auto Growth]
FROM tempdb.sys.database_files