Find HIGH CPU, IO, Blocking

--CPU usage
SELECT TOP 50
    [Average CPU used] = total_worker_time / qs.execution_count,
    [Total CPU used] = total_worker_time,
    [Execution count] = qs.execution_count,
    [Individual Query] = SUBSTRING(
                                      qt.text,
                                      qs.statement_start_offset / 2,
                                      (CASE
                                           WHEN qs.statement_end_offset = -1 THEN
                                               LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                           ELSE
                                               qs.statement_end_offset
                                       END - qs.statement_start_offset
                                      ) / 2
                                  ),
    [Parent Query] = qt.text,
    DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY [Average CPU used] DESC;

--IO
DECLARE @SystemIO FLOAT
SELECT @SystemIO = SUM(total_logical_reads + total_logical_writes)
FROM sys.dm_exec_query_stats;

SELECT TOP 20 [Row Number] = ROW_NUMBER() OVER (ORDER BY total_logical_reads + total_logical_writes DESC),
    [Query Text] = CASE
        WHEN [sql_handle] IS NULL THEN ' '
        ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
            (CASE
                WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),ST.text)) * 2
                ELSE QS.statement_end_offset
                END - QS.statement_start_offset) / 2))
        END,
    [Execution Count] = execution_count,
    [Total IO] = total_logical_reads + total_logical_writes,
    [Average IO] = (total_logical_reads + total_logical_writes) / (execution_count + 0.0),
    [System Percentage] = 100 * (total_logical_reads + total_logical_writes) / @SystemIO,
    [Object Name] = OBJECT_NAME(ST.objectid),
    [Total System IO] = @SystemIO,
    [SQL Handle] = [sql_handle]
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text ([sql_handle]) ST
WHERE total_logical_reads + total_logical_writes > 0
ORDER BY [Total IO] DESC



--Put this below in job and run for every 10 seconds
IF EXISTS (SELECT 1 FROM sys.sysprocesses WHERE blocked >0)
BEGIN

    INSERT INTO AuditDB.dbo.SYSprocess
    SELECT *
    FROM sys.sysprocesses
    WHERE blocked >0


    INSERT INTO AuditDB.dbo.SYSprocess_sp_who2
    EXEC sp_who2 'Active';

END;
ELSE
BEGIN
    PRINT 'No Blocking found' + ' at ' + CONVERT(VARCHAR(100), GETDATE());
END;






---read data

SELECT  * FROM AuditDB.dbo.SYSprocess_sp_who2  WHERE Blkby   NOT LIKE '%.%'

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT
      *
  FROM [AuditDB].[dbo].[SYSprocess]
WHERE
--blocked>1
--AND lastwaittype   NOT LIKE '%LATCH_EX%'
--AND
last_batch BETWEEN '2017-03-16 10:00:05'  AND '2017-03-16 11:20:05'
ORDER BY login_time desc




 DECLARE @Handle varbinary(64);
SELECT @Handle =0x0300170020029172DA72F40033A6000001000000
FROM sys.dm_exec_requests  

SELECT * FROM sys.fn_get_sql(@Handle);

other way to caprture blocking is


SELECT * FROM sys.configurations
WHERE description LIKE '%blocked%'
GO


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'blocked process threshold', 2;
GO
RECONFIGURE;
GO

https://www.brentozar.com/sql/locking-and-blocking-in-sql-server/
----TRACE RUN
 sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'blocked process threshold', 0;
GO
RECONFIGURE;
GO