Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 как узнать чем занят сервер?  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
Jun 12 2012 13:05:25
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

После ряда вставок в базу

CPU 22% Memory: 4Gb все что было разрешено взять этой редакции

а вот диск молотит 100% уже почти сутки. Причем если сервис перезагрузить - то все успокаивается.

Чем можно активность помониторить? Или это глюk редакции?
25 ноя 14, 18:01    [16903891]     Ответить | Цитировать Сообщить модератору
 Re: как узнать чем занят сервер?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.reads,
req.writes,
req.total_elapsed_time,
*
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
25 ноя 14, 18:15    [16903965]     Ответить | Цитировать Сообщить модератору
 Re: как узнать чем занят сервер?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1133
Lepsik,

сейчас выполняющиеся зопросы:

--------------------------------------------------
--    Liste der jetzt laufenden SQL - Abfragen ---
--------------------------------------------------

SELECT 
 req.command command_type
,sqltext.TEXT command_text
,db_name(req.database_id) db_name
,req.session_id
,req.blocking_session_id
,req.status as req_status
,req.cpu_time req_cpu_time_ms
,ses.cpu_time ses_cpu_time_ms
,req.total_elapsed_time / 1000.0 req_elapsed_sec
,ses.original_login_name
,ses.host_name
,ses.program_name
,ses.host_process_id
,ses.client_interface_name
,ses.memory_usage
--,ses.last_request_start_time
--,ses.last_request_end_time
,ses.reads
,ses.writes
,ses.logical_reads
,ses.row_count
FROM 
     sys.dm_exec_requests req with(nolock)
JOIN sys.dm_exec_sessions ses with(nolock) on req.session_id=ses.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
where 1=1
and req.database_id > 4
and req.session_id <> @@SPID



ну и сообщение по мылу в случае высокой загрузки процессора.

вызывать [sp_ckeck_and_alarm_cpu] 80, 'v.pupkin@bnd.de' из джоба.


CREATE PROCEDURE [dbo].[sp_ckeck_and_alarm_cpu]
	@alarm_level int,
	@SemikolongetrennteEmails varchar(200)	
AS
BEGIN	
	SET NOCOUNT ON;

--exec tools..sp_droptemptab '#T;#Q';    
DECLARE @ts_now bigint
DECLARE @body NVARCHAR(MAX)
DECLARE @TOP int
--
 
SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info;
 

WITH 
A as 
(
	SELECT TOP 3 timestamp, CONVERT (xml, record) AS record 
	FROM sys.dm_os_ring_buffers 
	WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
	AND record LIKE '%<SystemHealth>%' order by timestamp desc
),
B as
(
	SELECT 
    record.value('(Record/@id)[1]', 'int') AS record_id,
    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')     AS system_cpu_utilization_post_sp2,
    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 
    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')     AS system_cpu_utilization_pre_sp2,
    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
    FROM A
),
C as
(
	SELECT
	record_id, EventTime, 
	CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 
	CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization 
	FROM B
)
select * into #T from C;


IF EXISTS(select * from #T where sql_cpu_utilization > @alarm_level) 
BEGIN
 SET @TOP=3 	 
END
ELSE
BEGIN
 SET @TOP=0
END

SELECT top (@TOP)
	 req.command command_type
	,sqltext.TEXT command_text
	,db_name(req.database_id) db_name
	,req.session_id
	,req.blocking_session_id
	,req.status as req_status
	,req.cpu_time req_cpu_time_ms
	,ses.cpu_time ses_cpu_time_ms
	,req.total_elapsed_time / 1000.0 req_elapsed_sec
	,ses.original_login_name
	,ses.host_name
	,ses.program_name
	,ses.host_process_id
	,ses.client_interface_name
	,ses.memory_usage
	,ses.reads
	,ses.writes
	,ses.logical_reads
	,ses.row_count
	INTO #Q
	FROM 
		 sys.dm_exec_requests req with(nolock)
	JOIN sys.dm_exec_sessions ses with(nolock) on req.session_id=ses.session_id
	CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
	where 1=1
	and req.session_id <> @@SPID
	and req.cpu_time > 0
	order by req.cpu_time desc


---########################################################################### 

SET     @body = 
N'<head>
<style>
p{font-family: Courier New; font-size:11px; color:blue}
table{border-collapse:collapse;}
table{padding:0px 4px 0px 4px;}
table, td, th {border:1px solid black;}
th {background-color:Crimson; color:white;}
table{font-size:12px;}
</style>
</head>'

--set @body=@body+ N'<p>KEINE PANIK! Das ist TEST'
select @body=@body+ N'<p><font size="+1">Hohe Prozessorlast in letzte 3 Minuten auf ' +  + @@servername + '<br>' + 
'<b>System:' + (select cast(max(system_cpu_utilization) as varchar(4)) from #T) + '%</b><br>' + 
'<b>Sql&nbsp;&nbsp;&nbsp;:' + (select cast(max(sql_cpu_utilization) as varchar(4)) from #T) + '%</font></b><br>' +
'<b>Alarm wenn > ' + cast(@alarm_level as varchar(10)) + '%</font></b><p>' +
'<b>TOP 3 Abfragen</b><br>';



set @body = @body +  N'<table>'
    + N'
    <tr>
		<th>type</th>
		<th>text</th>
		<th>db</th>
		<th>session_id</th>
		<th>blocking_session_id</th>
		<th>req_status</th>
		<th>req_cpu_time_ms</th>
		<th>ses_cpu_time_ms</th>
		<th>req_elapsed_sec</th>
		<th>original_login_name</th>
		<th>host_name</th>
		<th>program_name</th>
		<th>host_process_id</th>
		<th>client_interface_name</th>
		<th>memory_usage</th>
		<th>reads</th>
		<th>writes</th>
		<th>logical_reads</th>
		<th>row_count</th>
    </tr>'
    + CAST((
        SELECT                  
			 p.command_type AS td
			,p.command_text AS td
			,p.db_name AS td
			,p.session_id AS td
			,p.blocking_session_id AS td
			,p.req_status AS td
			,p.req_cpu_time_ms AS td
			,p.ses_cpu_time_ms AS td
			,p.req_elapsed_sec AS td
			,p.original_login_name AS td
			,p.host_name AS td
			,p.program_name AS td
			,p.host_process_id AS td
			,p.client_interface_name AS td
			,p.memory_usage AS td
			,p.reads AS td
			,p.writes AS td
			,p.logical_reads AS td
			,p.row_count AS td                                            
        FROM    #Q p       
        FOR XML RAW('tr'), ELEMENTS
    ) AS NVARCHAR(MAX))
    + N'</table>'

 
    
set @body = @body + 
'<p>Job: "Tools:  Check CPU"' 
   
if len(@SemikolongetrennteEmails)>0 and @TOP > 0
begin   
    exec msdb..sp_send_dbmail
	@profile_name		='!!!! PROFILE !!!!',  --<<<< !!!!! 
	@recipients			=@SemikolongetrennteEmails, 	
	@copy_recipients	='', 
	@body_format		='HTML',
	@subject			='Hohe Prozessorlast auf SQL', 
	@body				=@body,
	@importance			='HIGH'   
end
else
begin
   select * from #Q  order by req_cpu_time_ms desc
end	



END
25 ноя 14, 18:57    [16904205]     Ответить | Цитировать Сообщить модератору
 Re: как узнать чем занят сервер?  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
спасибо нашел прибитый но все еше висяший .net процесс долбил базу
25 ноя 14, 21:26    [16904868]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить