as_perf

CREATE PROCEDURE [dbo].[as_perf]

AS
BEGIN


select 'use master; GRANT VIEW SERVER STATE TO login' 'Дать права на спец запросы'

select 'Запросы с большим 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 'TOP 5 хранимок по CPU' SECTION
SELECT TOP 5    total_worker_time / execution_count AS AVG_CPU,
    db_name(d.database_id) db, OBJECT_NAME(object_id, database_id) 'sp'

      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS,

    d.last_elapsed_time, d.execution_count,
    d.cached_time, d.last_execution_time, d.total_elapsed_time
FROM sys.dm_exec_procedure_stats AS d
ORDER BY total_worker_time / execution_count DESC;






select 'Проблемные запросы по памяти' 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 'Запросы с большим кол-вом строк' 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 'Долгие запросы по времени' 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 'Процессы 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 'Часто выполняемые sql запросы' SECTION

SELECT distinct top 10 execution_count,   DB_NAME(dbid), text , objectid
       FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
   ORDER BY execution_count DESC

select 'Долгие SP по времени выполнению' SECTION

SELECT top 5  DB_NAME(database_id) AS DBName
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
ORDER BY AVG_ELAPSED DESC

select 'Тяжелые  SP по CPU' SECTION

SELECT top 5  DB_NAME(database_id) AS DBName
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
ORDER BY AVG_CPU DESC

select 'Самые часто вызываемые SP' SECTION

SELECT top 5  DB_NAME(database_id) AS DBName
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
ORDER BY execution_count DESC



select 'Размеры таблиц (по количеству строк)' 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

select 'Размеры таблиц (Мб)' SECTION

SELECT top 10
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    t.NAME AS TableName,
    --s.Name AS SchemaName,
    p.rows,
    --SUM(a.total_pages) * 8 AS TotalSpaceKB,
    --SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    --(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB DESC, t.Name


select 'БЛОКИРОВКИ - ПРОЦЕССЫ с блокировкой' SECTION

SELECT * FROM sys.sysprocesses
WHERE blocked > 0

select 'БЛОКИРОВКИ - Сессии с блокировкой' SECTION
SELECT
    der.blocking_session_id AS BlockingSessionID
    ,dest.text AS BlockingStatement, *
FROM sys.dm_exec_connections AS sdec
INNER JOIN sys.dm_exec_requests AS der
    ON sdec.session_id = der.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS dowt
    ON der.session_id = dowt.session_id
CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS dest

select 'БЛОКИРОВКИ - Транзакции с блокировкой' SECTION
SELECT
    request_session_id AS SPID
    ,DB_NAME(resource_database_id) AS DatabaseName,
    *
FROM sys.dm_tran_locks

select 'БЛОКИРОВКИ - sp_lock' SECTION
exec sp_lock

END;