Procedure for tracking problematic SQL Server queries

This procedure allows you to find various problematic queries in the database. 

Requests need Server viewstate permissions to work. By default, it already exists in the Falcon Space database and is called via exec as_perf

CREATE PROCEDURE [dbo].as_perf
AS
BEGIN

select 'use master; GRANT VIEW SERVER STATE TO login' 'Grant rights to special requests'

select 'Requests with a large CPU' SECTION

SELECT TOP (5)
	qs.total_worker_time AS Total_CPU,
	db_name(st.dbID) db,
	st.text,
	total_CPU_inSeconds = --Converted from microseconds
	qs.total_worker_time/1000000,
	average_CPU_inSeconds = --Converted from microseconds
	(qs.total_worker_time/1000000) / qs.execution_count,
	qs.total_elapsed_time,
	total_elapsed_time_inSeconds = --Converted from microseconds
	qs.total_elapsed_time/1000000,
	qs.execution_count
	--qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);




select 'Problematic queries in memory' SECTION

set transaction isolation level read uncommitted
select
	top 10
	convert(money, (qs.total_elapsed_time))/(execution_count*1000)  AVG_DURATION_MS,
	db_name(st.dbid)as db,
	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 as text,
	creation_time,
	last_execution_time,
	execution_count,
	total_worker_time/1000 as CPU,
	convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
	qs.total_elapsed_time/1000 as TotDuration,
	total_logical_reads as [Reads],
	total_logical_writes as [Writes],
	total_logical_reads+total_logical_writes as [AggIO],
	convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],
	object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads > 0
order by AVG_DURATION_MS desc

select 'Queries with a large number of lines' SECTION
set transaction isolation level read uncommitted
select
	top 5
	max_rows ROWS_COUNT,
	db_name (dbID) db,
	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+1  ))
	end as query_text,
	execution_count,
	convert( nvarchar, last_execution_time, 120) last_time,
	total_worker_time/1000 as CPU,
	convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
	qs.total_elapsed_time/1000 as TotDuration,
	convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur]
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
--where total_logical_reads > 0
order by max_rows desc


select 'Long time requests' SECTION

SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS AVG_SEC,
    DB_NAME(qt.dbid) AS db,
	case
		when sql_handle IS NULL then ' '
		else(substring(qt.text,(qs.statement_start_offset+2)/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+1  ))
	end as query_text,
	qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    o.name AS object_name
  FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where   qs.total_elapsed_time / qs.execution_count / 1000000.0 > 0.2
  ORDER BY AVG_SEC DESC;

select 'Processes SQL Server по CPU (DBCC INPUTBUFFER(@spid))' SECTION

SELECT top 5 cpu, db_name(dbid) db,  spid, kpid,  memusage FROM sysprocesses
order by cpu desc

select 'Table size' SECTION

SELECT top 5 QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0   AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY sOBJ.schema_id, sOBJ.name
ORDER BY [RowCount] desc

END;