SQL SERVER queries to gather information about the workload

To get the top queries that are taking the longest time to execute:

SELECT TOP 50
[rs].[sql_handle],
[rs].[execution_count],
[rs].[total_worker_time] AS [Total CPU Time],
[rs].[total_elapsed_time] AS [Total Elapsed Time],
[rs].[total_logical_reads] AS [Total Logical Reads],
[rs].[total_logical_writes] AS [Total Logical Writes],
[st].[text] AS [Query Text]
FROM
sys.dm_exec_query_stats AS [rs]
CROSS APPLY sys.dm_exec_sql_text([rs].[sql_handle]) AS [st]
WHERE
[rs].[total_elapsed_time] >= 1000 -- Show only queries that have taken more than 1 second to execute
ORDER BY
[rs].[total_elapsed_time] DESC;

To get the top resource-intensive queries:

SELECT TOP 50
[rs].[sql_handle],
[rs].[execution_count],
[total_worker_time] AS [Total CPU Time],
[total_logical_reads] AS [Total Logical Reads],
[total_logical_writes] AS [Total Logical Writes],
[total_physical_reads] AS [Total Physical Reads],
[total_elapsed_time] AS [Total Elapsed Time],
[query_plan],
[st].[text] AS [Query Text]
FROM
sys.dm_exec_query_stats [rs]
CROSS APPLY
sys.dm_exec_sql_text([rs].[sql_handle]) [st]
CROSS APPLY
sys.dm_exec_query_plan([rs].[plan_handle]) [qp]
WHERE
[rs].[total_elapsed_time] >= 1000 -- Show only queries that have taken more than 1 second to execute
ORDER BY
[total_worker_time] DESC;

To get the top wait types:

SELECT TOP 10
wait_type,
wait_time_ms / 1000.0 AS wait_time_seconds,
wait_time_ms / (1000.0 * 60.0) AS wait_time_minutes,
wait_time_ms / (1000.0 * 60.0 * 60.0) AS wait_time_hours,
(wait_time_ms / 1000.0) / (wait_time_ms / 1000.0 + signal_wait_time_ms / 1000.0) AS wait_time_percent,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms
FROM
sys.dm_os_wait_stats
WHERE
wait_type NOT LIKE '%SLEEP%' -- Exclude the SLEEP wait type
AND wait_type NOT LIKE 'CLR_%' -- Exclude CLR wait types
AND wait_type NOT LIKE 'BROKER_%' -- Exclude Broker wait types
AND wait_type NOT LIKE 'XE_%' -- Exclude Extended Events wait types
ORDER BY
wait_time_ms DESC;