مانیتور کردن میزان مصرف CPU در اس کیوال سرور
قطعا برای همه ی افرادی که در سازمان های مختلف به عنوان ادمین دیتابیس مشغول بکار هستند میزان مصرف منابع سرور آنها باید جزو چک لیست روزانه و لحظه ای باشد . گاهی وقتا پیش میاد که از سمت کاربر تماس هایی رو داریم که برنامه کند اجرا میشه و یا فلان کوئری قبلا 5 ثانیه زمان میبرده و الان شده 5 دقیقه !! خب چطوری میتونیم به این موضوع اشراف پیدا کنیم ؟ با من همراه باشید .
در این مواقع دو موضوع پیش می آید ، آیا در لحظه کل منابع هاست سرور زیر بار بوده ، Cpu سرور زیر بار بوده و یا کوئری مورد نظر دچار مشکل است ؟
برای مشاهده میزان مصرف Cpu توسط SQL Server در لحظه حال حاضر در چه وضعیتی می باشد میتوان از اسکریپت زیر استفاده کرد :
DECLARE @CPU_BUSY int, @IDLE int
SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE WAITFOR DELAY '000:00:01'
SELECT (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) *1.00) *100 AS 'CPU Utilization by sqlsrvr.exe'
برای مشاهده میزان مصرف Cpu توسط SQL Server تا 256 دقیقه پیش به صورت دقیقه به دقیقه می توانید از اسکریپت زیر استفاده کنید :
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));
--sqldba.ir
SELECT top 10000 SQLProcessUtilization AS [SQL Server Process CPU Utilization %],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization %],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%%') AS x
) AS y
--sqldba.ir
ORDER BY record_id DESC OPTION (RECOMPILE);
و برای مشاهده کوئری های در حال اجرا به همراه میزان مصرف منابع این کوئری ، شخصی که کوئری مورد نظر را اجرا کرده ، ساعت شروع اجرای این کوئری ، وضعیت wait_type , وضعیت Blk_by و درصد پیشرفت این کوئری می توان از اسکریپت زیر استفاده کنید :
--sqldba.ir
SELECT r.percent_complete,
s.session_id ,
r.status ,
r.blocking_session_id 'Blk by' ,
r.wait_type ,
wait_resource ,
r.wait_time / ( 1000 * 60 ) 'Wait M' ,
r.cpu_time ,
r.logical_reads ,
r.reads ,
r.writes ,
r.total_elapsed_time / ( 1000 * 60 ) 'Elaps M' ,
SUBSTRING(st.TEXT, ( r.statement_start_offset / 2 ) + 1,
( ( CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2 ) + 1) AS statement_text ,
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.'
+ QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text ,
r.command ,
s.login_name ,
s.host_name ,
s.program_name ,
s.last_request_end_time ,
s.login_time ,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
--AND r.session_id=283
--AND s.login_name='%p-bani%'
ORDER BY r.cpu_time DESC;
--sqldba.ir
در ادامه میخواهیم جابی را تنظیم کنیم که اگر مصرف Cpu بیشتر از 70 درصد شد ایمیلی به مسئول مربوطه و ادمین دیتابیس ارسال کند . تنظیمات این چک کردن را بر روی هر 10 دقیقه یک بار قرار می دهیم .
قبل از تنظیم این جاب می بایسن Database Mail را بر روی سرور راه اندازی کرده تا بتوانیم با آن ایمیل ارسال کنیم ، به صورت پیش فرض این گزینه بر روی سرور غیر فعال بوده و با اجرای اسکریپت زیر آن را فعال می کنیم :
USE [master]
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
--RECONFIGURE
GO
-- ایجاد یک پروفایل جدید برای اطلاع رسانی بویسله ایمیل
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA_Report',
@description = 'پروفایلی برای ارسال اتوماتیک ایمیل به مدیر پایگاه داده'
GO
--sqldba.ir
-- پیش فرض کردن پروفایل ایجاد شده در بالا
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBA_Report',
@principal_name = 'public',
@is_default = 1 ;
GO
-- ایجاد یک اکانت برای ارسال اطلاع رسانی
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLMonitor',
@description = 'اکانتی برای مدیر پایگاه داده',
@email_address = 'Email@SQLDBA.ir', -- ایمیل ارسال کننده
@display_name = 'SQL Monitor',
@mailserver_name = 'mail.site.net' -- آدرس میل سرور(SMTP SERVER) سازمان شما که از تیم شبکه می بایست پرسیده شود
GO
--sqldba.ir
-- اضافه کردن این اکانت به پروفایل مدیر پایگاه اده که در بالا ایجاد گردید
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA_Report',
@account_name = 'SQLMonitor',
@sequence_number = 1
GO
این اسکریپت از ورژن 2005 به بعد قابل اجرا می باشد .
برای راه اندازی DataBase Mail به صورت Wizard می توانید به لینک زیر (داکیومنت مایکروسافت برای کانفیگ ایمیل دیتابیس ) مراجعه کنید .
برای تست ارسال ایمیل توسط پروفایل ایجاد شده می توانید از اسکریپت زیر استفاده کنید :
EXECUTE msdb.dbo.sp_send_dbmail
@recipients = 'DATABASE_ADMIN@youremail.ir', -- آدرس ایمیل گیرنده ایمیل
@Subject = 'CPU Utilization by SQL Server is Critical',
@Body = 'CPU Utilization by SQL Server is upper than 70% '
خب تا اینجای کار ما ادرس ایمیل را از میل سرور دریافت کرده ایم ، در ادامه به تعریف جاب جهت بررسی وضعیت Cpu و ارسال ایمیل می پردازیم .
برای تعریف جاب به قسمت Jobs در قسمت Sql Server Agent رفته راست کلیک کرده و New Jobs را می زنیم .
اسکریپت زیر را در Step جاب اضافه کرده و در قسمت Schedule ساعت و دقیقه اجرای جاب را تنظیم میکنیم (پیشنهاد ما هر 5 دقیقه یک بار است).
با اجرای این جاب در هر 5 دقیقه اگر میزان مصرف Cpu بیشتر از 70 درصد شد برای ما ایمیلی ارسال شده و اطلاع رسانی می شود .
DECLARE @CPUUsage INT
DECLARE @CPU_BUSY INT,
@IDLE INT
SELECT @CPU_BUSY = @@CPU_BUSY,
@IDLE = @@IDLE
WAITFOR DELAY '000:00:01'
SELECT @CPUUsage = (@@CPU_BUSY - @CPU_BUSY) / ((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) * 1.00)
* 100 -- CPU Utilization by sqlsrvr.exe
IF @CPUUsage > 70
BEGIN
DECLARE @msg NVARCHAR(1000)
SET @msg = '**SQL-DBA** Critical , CPU Utilization is ' + CAST(@CPUUsage AS NVARCHAR(50))
+ '%.'
EXECUTE msdb.dbo.sp_send_dbmail
@recipients = 'DBA-admin@yourmail.ir', -- ایمیل گیرنده یا ادمین دیتابیس
@copy_recipients = 'network@yourmail.ir', -- رونوشت ایمیل به نفر بعدی
@Subject = 'CPU overload is Critical',
@Body = @msg
,@importance = 'High'
END
برای مانیتور کردن میزان مصرف CPU در SQL Server، میتوانید از چند روش استفاده کنید:
استفاده از Performance Monitor: برای مانیتور کردن میزان مصرف CPU در SQL Server، میتوانید از ابزار Performance Monitor در ویندوز استفاده کنید. برای این کار، ابتدا Performance Monitor را باز کرده و سپس به قسمت “Performance Counter” بروید. در اینجا، در لیست “Object” به “SQLServer:Buffer Manager” و در لیست “Counter” به “CPU usage %” رفته و برای مانیتور کردن میزان مصرف CPU، بر روی دکمه “Add” کلیک کنید.
استفاده از DMV: شما میتوانید از دستورات DMV برای مانیتور کردن میزان مصرف CPU در SQL Server استفاده کنید. به عنوان مثال، دستور زیر برای نمایش میزان مصرف CPU در SQL Server استفاده میشود:
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count], cpu_ticks/(cpu_count*DATEDIFF(SECOND, creation_time, GETDATE())) AS [Avg CPU Utilization] FROM sys.dm_os_sys_info
استفاده از نرمافزارهای مانیتورینگ: برای مانیتور کردن میزان مصرف CPU در SQL Server، میتوانید از نرمافزارهای مانیتورینگی مانند SolarWinds Database Performance Analyzer، Redgate SQL Monitor و Quest Foglight استفاده کنید. این نرمافزارها قابلیت نمایش میزان مصرف CPU در SQL Server را دارند و به شما اجازه میدهند تا میزان مصرف CPU را در طول زمان مانیتور کنید و بهترین راهکارهای بهینهسازی را برای کاهش مصرف CPU ارائه دهید.
در هر صورت، مانیتور کردن میزان مصرف CPU در SQL Server میتواند به شما در بهبود عملکرد سیستم کمک کند و از مشکلاتی مانند تبدیل شدن CPU به bottleneck جلوگیری کند.
دیدگاه ها 3