hecrus

as_perf

Feb 11th, 2021
940
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE PROCEDURE [dbo].[as_perf]
  2.    
  3. AS
  4. BEGIN
  5.    
  6.  
  7. select 'use master; GRANT VIEW SERVER STATE TO login' 'Дать права на спец запросы'
  8.  
  9. select 'Запросы с большим CPU' SECTION
  10.  
  11. SELECT TOP (5)
  12.     qs.total_worker_time AS Total_CPU,
  13.     db_name(st.dbID) db,
  14.     st.text,
  15.     total_CPU_inSeconds = --Converted from microseconds
  16.     qs.total_worker_time/1000000,
  17.     average_CPU_inSeconds = --Converted from microseconds
  18.     (qs.total_worker_time/1000000) / qs.execution_count,
  19.     qs.total_elapsed_time,
  20.     total_elapsed_time_inSeconds = --Converted from microseconds
  21.     qs.total_elapsed_time/1000000
  22.     qs.execution_count
  23.     --qp.query_plan
  24. FROM sys.dm_exec_query_stats AS qs
  25. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
  26. CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
  27. ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
  28.  
  29. select 'TOP 5 хранимок по CPU' SECTION
  30. SELECT TOP 5    total_worker_time / execution_count AS AVG_CPU,
  31.     db_name(d.database_id) db, OBJECT_NAME(object_id, database_id) 'sp'
  32.    
  33.       ,total_elapsed_time / execution_count AS AVG_ELAPSED
  34.       ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
  35.       ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
  36.       ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS,
  37.  
  38.     d.last_elapsed_time, d.execution_count,
  39.     d.cached_time, d.last_execution_time, d.total_elapsed_time 
  40. FROM sys.dm_exec_procedure_stats AS d
  41. ORDER BY total_worker_time / execution_count DESC;
  42.  
  43.  
  44.  
  45.  
  46.  
  47.  
  48. select 'Проблемные запросы по памяти' SECTION
  49.  
  50. set transaction isolation level read uncommitted
  51. select
  52.     top 10
  53.     convert(money, (qs.total_elapsed_time))/(execution_count*1000)  AVG_DURATION_MS,
  54.     db_name(st.dbid)as db,
  55.     case
  56.         when sql_handle IS NULL then ' '
  57.         else(substring(st.text,(qs.statement_start_offset+2)/2,(
  58.             case
  59.                 when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
  60.                 else qs.statement_end_offset    
  61.             end - qs.statement_start_offset)/2  ))
  62.     end as text,
  63.     creation_time,
  64.     last_execution_time,
  65.     execution_count,
  66.     total_worker_time/1000 as CPU,
  67.     convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
  68.     qs.total_elapsed_time/1000 as TotDuration,
  69.     total_logical_reads as [Reads],
  70.     total_logical_writes as [Writes],
  71.     total_logical_reads+total_logical_writes as [AggIO],
  72.     convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],
  73.     object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
  74. from sys.dm_exec_query_stats  qs
  75. cross apply sys.dm_exec_sql_text(sql_handle) st
  76. where total_logical_reads > 0
  77. order by AVG_DURATION_MS desc
  78.  
  79. select 'Запросы с большим кол-вом строк' SECTION
  80. set transaction isolation level read uncommitted
  81. select
  82.     top 5
  83.     max_rows ROWS_COUNT,
  84.     db_name (dbID) db,
  85.     case
  86.         when sql_handle IS NULL then ' '
  87.         else(substring(st.text,(qs.statement_start_offset+2)/2,(
  88.             case
  89.                 when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
  90.                 else qs.statement_end_offset    
  91.             end - qs.statement_start_offset)/2+1  ))
  92.     end as query_text,
  93.     execution_count,
  94.     convert( nvarchar, last_execution_time, 120) last_time,
  95.     total_worker_time/1000 as CPU,
  96.     convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
  97.     qs.total_elapsed_time/1000 as TotDuration,
  98.     convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur]  
  99. from sys.dm_exec_query_stats  qs
  100. cross apply sys.dm_exec_sql_text(sql_handle) st
  101. --where total_logical_reads > 0
  102. order by max_rows desc
  103.  
  104.  
  105. select 'Долгие запросы по времени' SECTION
  106.  
  107. SELECT TOP 10
  108.     qs.total_elapsed_time / qs.execution_count / 1000000.0 AS AVG_SEC,
  109.     DB_NAME(qt.dbid) AS db,
  110.     case
  111.         when sql_handle IS NULL then ' '
  112.         else(substring(qt.text,(qs.statement_start_offset+2)/2,(
  113.             case
  114.                 when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),qt.text))*2      
  115.                 else qs.statement_end_offset    
  116.             end - qs.statement_start_offset)/2+1  ))
  117.     end as query_text,
  118.     qs.total_elapsed_time / 1000000.0 AS total_seconds,
  119.     qs.execution_count,    
  120.     o.name AS object_name    
  121.   FROM sys.dm_exec_query_stats qs
  122.     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  123.     LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
  124. where   qs.total_elapsed_time / qs.execution_count / 1000000.0 > 0.2
  125.   ORDER BY AVG_SEC DESC;
  126.  
  127. select 'Процессы SQL Server по CPU (DBCC INPUTBUFFER(@spid))' SECTION
  128.  
  129. SELECT top 5 cpu, db_name(dbid) db,  spid, kpid,  memusage FROM sysprocesses
  130. order by cpu desc
  131.  
  132. select 'Часто выполняемые sql запросы' SECTION
  133.  
  134. SELECT distinct top 10 execution_count,   DB_NAME(dbid), text , objectid
  135.        FROM sys.dm_exec_query_stats AS a
  136. CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
  137.    ORDER BY execution_count DESC
  138.  
  139. select 'Долгие SP по времени выполнению' SECTION
  140.  
  141. SELECT top 5  DB_NAME(database_id) AS DBName
  142.       ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
  143.       ,cached_time
  144.       ,last_execution_time
  145.       ,execution_count
  146.       ,total_worker_time / execution_count AS AVG_CPU
  147.       ,total_elapsed_time / execution_count AS AVG_ELAPSED
  148.       ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
  149.       ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
  150.       ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
  151. FROM sys.dm_exec_procedure_stats  
  152. ORDER BY AVG_ELAPSED DESC
  153.  
  154. select 'Тяжелые  SP по CPU' SECTION
  155.  
  156. SELECT top 5  DB_NAME(database_id) AS DBName
  157.       ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
  158.       ,cached_time
  159.       ,last_execution_time
  160.       ,execution_count
  161.       ,total_worker_time / execution_count AS AVG_CPU
  162.       ,total_elapsed_time / execution_count AS AVG_ELAPSED
  163.       ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
  164.       ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
  165.       ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
  166. FROM sys.dm_exec_procedure_stats  
  167. ORDER BY AVG_CPU DESC
  168.  
  169. select 'Самые часто вызываемые SP' SECTION
  170.  
  171. SELECT top 5  DB_NAME(database_id) AS DBName
  172.       ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
  173.       ,cached_time
  174.       ,last_execution_time
  175.       ,execution_count
  176.       ,total_worker_time / execution_count AS AVG_CPU
  177.       ,total_elapsed_time / execution_count AS AVG_ELAPSED
  178.       ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
  179.       ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
  180.       ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
  181. FROM sys.dm_exec_procedure_stats  
  182. ORDER BY execution_count DESC
  183.  
  184.  
  185.  
  186. select 'Размеры таблиц (по количеству строк)' SECTION
  187.  
  188. SELECT top 5 QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
  189.       , SUM(sPTN.Rows) AS [RowCount]
  190. FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN
  191.             ON sOBJ.object_id = sPTN.object_id
  192. WHERE sOBJ.type = 'U'
  193.       AND sOBJ.is_ms_shipped = 0x0   AND index_id < 2 -- 0:Heap, 1:Clustered
  194. GROUP BY sOBJ.schema_id, sOBJ.name
  195. ORDER BY [RowCount] desc
  196.  
  197. select 'Размеры таблиц (Мб)' SECTION
  198.  
  199. SELECT top 10
  200.     CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
  201.     t.NAME AS TableName,
  202.     --s.Name AS SchemaName,
  203.     p.rows,
  204.     --SUM(a.total_pages) * 8 AS TotalSpaceKB,
  205.     --SUM(a.used_pages) * 8 AS UsedSpaceKB,
  206.     CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
  207.     --(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
  208.     CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
  209. FROM
  210.     sys.tables t
  211. INNER JOIN      
  212.     sys.indexes i ON t.OBJECT_ID = i.object_id
  213. INNER JOIN
  214.     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  215. INNER JOIN
  216.     sys.allocation_units a ON p.partition_id = a.container_id
  217. LEFT OUTER JOIN
  218.     sys.schemas s ON t.schema_id = s.schema_id
  219. WHERE
  220.     t.NAME NOT LIKE 'dt%'
  221.     AND t.is_ms_shipped = 0
  222.     AND i.OBJECT_ID > 255
  223. GROUP BY
  224.     t.Name, s.Name, p.Rows
  225. ORDER BY
  226.     TotalSpaceMB DESC, t.Name
  227.    
  228.  
  229. select 'БЛОКИРОВКИ - ПРОЦЕССЫ с блокировкой' SECTION
  230.  
  231. SELECT * FROM sys.sysprocesses
  232. WHERE blocked > 0
  233.  
  234. select 'БЛОКИРОВКИ - Сессии с блокировкой' SECTION
  235. SELECT  
  236.     der.blocking_session_id AS BlockingSessionID
  237.     ,dest.text AS BlockingStatement, *
  238. FROM sys.dm_exec_connections AS sdec
  239. INNER JOIN sys.dm_exec_requests AS der
  240.     ON sdec.session_id = der.blocking_session_id
  241. INNER JOIN sys.dm_os_waiting_tasks AS dowt
  242.     ON der.session_id = dowt.session_id
  243. CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS dest
  244.  
  245. select 'БЛОКИРОВКИ - Транзакции с блокировкой' SECTION
  246. SELECT
  247.     request_session_id AS SPID
  248.     ,DB_NAME(resource_database_id) AS DatabaseName,
  249.     *
  250. FROM sys.dm_tran_locks
  251.  
  252. select 'БЛОКИРОВКИ - sp_lock' SECTION
  253. exec sp_lock
  254.  
  255. END;
  256.  
RAW Paste Data