مدیریت و کنترل Long Running SQl Job

مدیریت و کنترل Long Running SQl Job: راهنمای جامع

در محیط‌های دیتابیس، اجرای جاب‌هایی با زمان اجرای طولانی ( Long Running SQl Job‌ در SQL Server ) می‌تواند به یک چالش مهم تبدیل شود و منجر به کاهش عملکرد و بروز مشکلات سیستمی شود. این مقاله به شما کمک می‌کند تا این جاب‌ها را شناسایی، مانیتور و در نهایت کنترل کنید تا از عملکرد بهینه سیستم خود اطمینان حاصل کنید.

چرا مانیتورینگ Long Running SQl Job ضروری است؟

جاب‌های SQL Server Agent وظایف حیاتی مانند پشتیبان‌گیری، نگهداری شاخص‌ها و پاکسازی لاگ‌ها را انجام می‌دهند. اگر این جاب‌ها بیش از حد معمول طول بکشند، می‌توانند منابع سیستم را مسدود کرده، تراکنش‌های دیگر را کند کرده و حتی باعث قطعی شوند. بنابراین، داشتن مکانیزمی برای شناسایی و مدیریت این جاب‌ها برای هر مدیر دیتابیس (DBA) ضروری است.

شناسایی Long Running SQl Job

برای شناسایی جاب‌هایی که در حال حاضر در حال اجرا هستند و مدت زمان اجرای آن‌ها غیرعادی است، می‌توانیم از ویوهای سیستمی `msdb` استفاده کنیم. ویو `msdb.dbo.sysjobactivity` اطلاعاتی درباره فعالیت جاب‌ها ارائه می‌دهد، از جمله زمان شروع و وضعیت فعلی آن‌ها.

یکی از چالش‌ها این است که برخی جاب‌ها ممکن است برای ساعت‌ها یا حتی روزها در حال اجرا باشند، و این حالت ممکن است طبیعی نباشد. برای تعیین اینکه یک جاب چقدر طول کشیده است، می‌توانیم زمان شروع اجرا را از زمان فعلی کم کنیم.

با استفاده از دو کوئری زیر می‌توانید جاب‌های فعال را مشاهده کنید:

کوئری اول

SELECT
    sja.job_id,
    sja.run_requested_date,
    sja.start_execution_date,
    DATEDIFF(MINUTE, sja.start_execution_date, GETDATE()) AS ElapsedMinutes,
    sj.name AS JobName
FROM
    msdb.dbo.sysjobactivity sja
INNER JOIN
    msdb.dbo.sysjobs sj ON sja.job_id = sj.job_id
WHERE
    sja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
    AND sja.start_execution_date IS NOT NULL
    AND sja.stop_execution_date IS NULL;

این کوئری تمام جاب‌هایی را که در حال حاضر در حال اجرا هستند (یعنی `start_execution_date` دارند و `stop_execution_date` ندارند) و مربوط به آخرین سشن SQL Server Agent هستند، نشان می‌دهد.

کوئری دوم 

SELECT
    sja.session_id,
    j.name AS JobName,
    js.step_name AS StepName,
    sja.start_execution_date,
    GETDATE() AS CurrentDateTime,
    DATEDIFF(MINUTE, sja.start_execution_date, GETDATE()) AS RunningDurationMinutes,
    s.host_name,
    s.login_name,
    s.program_name,
    s.status,
    s.cpu_time,
    s.total_elapsed_time,
    s.reads,
    s.writes,
    s.logical_reads
FROM msdb.dbo.sysjobactivity sja
INNER JOIN msdb.dbo.sysjobs j ON sja.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps js ON sja.job_id = js.job_id AND sja.step_id = js.step_id
INNER JOIN sys.dm_exec_sessions s ON sja.session_id = s.session_id
WHERE sja.run_requested_date IS NOT NULL
  AND sja.stop_execution_date IS NULL
ORDER BY RunningDurationMinutes DESC;

این کوئری با اتصال به `msdb.dbo.sysjobactivity`، `msdb.dbo.sysjobs`، `msdb.dbo.sysjobsteps` و `sys.dm_exec_sessions`، اطلاعات کاملی از Jobهای در حال اجرا را فراهم می‌کند. فیلتر `sja.run_requested_date IS NOT NULL` و `sja.stop_execution_date IS NULL` تضمین می‌کند که فقط Jobهای فعال نمایش داده شوند. نتایج شامل نام Job، نام مرحله، زمان شروع اجرا، مدت زمان اجرا (بر حسب دقیقه)، نام میزبان، نام کاربری، نام برنامه، وضعیت Session و آمار مصرف منابع مانند CPU، زمان سپری شده کل، خواندن و نوشتن دیسک است. این اطلاعات برای تعیین اینکه چرا یک Job طولانی شده است، بسیار ارزشمند است.

مشکلات احتمالی با `sysjobactivity`

گاهی اوقات، `start_execution_date` در `sysjobactivity` ممکن است به درستی ریست نشود، به خصوص اگر Agent برای مدت طولانی در حال اجرا باشد و جاب‌ها بین اجراها متوقف و شروع نشوند. این می‌تواند باعث شود که زمان اجرای گزارش شده بسیار طولانی‌تر از حد واقعی باشد.

برای رفع این مشکل، می‌توانیم تاریخ شروع SQL Server Agent را از `msdb.dbo.syssessions` به دست آوریم و از آن برای فیلتر کردن فعالیت‌های جاب استفاده کنیم. آخرین `session_id` در `msdb.dbo.syssessions` مربوط به شروع فعلی Agent است.

SELECT TOP 1 session_id, agent_start_date
FROM msdb.dbo.syssessions
ORDER BY agent_start_date DESC;

این کوئری تاریخ شروع فعلی SQL Server Agent را به شما می‌دهد.

شناسایی Jobهای طولانی‌مدت از طریق تاریخچه با دو کوئری

اولین گام برای مدیریت Jobهای طولانی‌مدت، شناسایی آن‌ها است. SQL Server Agent تاریخچه تمامی Jobهای اجرا شده را ثبت می‌کند. با بررسی این تاریخچه، می‌توانیم Jobهایی را که به‌طور مداوم طولانی‌تر از میانگین زمان اجرای خود عمل می‌کنند، پیدا کنیم. کوئری زیر به شما کمک می‌کند تا Jobهایی را که زمان اجرای آن‌ها بیش از یک و نیم برابر میانگین زمان اجرایشان است، شناسایی کنید:

کوئری اول

;WITH JobHistory_CTE (job_id, instance_id, job_name, step_name, run_duration_sec, start_execution_date, end_execution_date)
AS
(
    SELECT
        jh.job_id,
        jh.instance_id,
        j.name AS job_name,
        js.step_name,
        jh.run_duration / 10000 * 3600 +
        (jh.run_duration / 100) % 100 * 60 +
        jh.run_duration % 100 AS run_duration_sec,
        msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS start_execution_date,
        DATEADD(SECOND, jh.run_duration / 10000 * 3600 + (jh.run_duration / 100) % 100 * 60 + jh.run_duration % 100, msdb.dbo.agent_datetime(jh.run_date, jh.run_time)) AS end_execution_date
    FROM msdb.dbo.sysjobhistory jh
    INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
    INNER JOIN msdb.dbo.sysjobsteps js ON jh.job_id = js.job_id AND jh.step_id = js.step_id
    WHERE jh.step_id <> 0 -- Filter out job-level history (step_id = 0)
)
SELECT
    j.name AS JobName,
    js.step_name AS StepName,
    AVG(jh.run_duration / 10000 * 3600 + (jh.run_duration / 100) % 100 * 60 + jh.run_duration % 100) AS AverageDurationSeconds,
    MAX(jh.run_duration / 10000 * 3600 + (jh.run_duration / 100) % 100 * 60 + jh.run_duration % 100) AS MaxDurationSeconds,
    (SELECT TOP 1 CAST(msdb.dbo.agent_datetime(run_date, run_time) AS DATETIME) FROM msdb.dbo.sysjobhistory WHERE job_id = j.job_id AND step_id = js.step_id ORDER BY instance_id DESC) AS LastRunTime
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps js ON jh.job_id = js.job_id AND jh.step_id = js.step_id
WHERE jh.step_id <> 0 AND jh.run_status = 1 -- Filter out job-level history and successful runs only
GROUP BY j.name, js.step_name
HAVING AVG(jh.run_duration / 10000 * 3600 + (jh.run_duration / 100) % 100 * 60 + jh.run_duration % 100) * 1.5 < MAX(jh.run_duration / 10000 * 3600 + (jh.run_duration / 100) % 100 * 60 + jh.run_duration % 100)
ORDER BY JobName, StepName;

این کوئری ابتدا با استفاده از یک Common Table Expression (CTE) به نام `JobHistory_CTE`، تاریخچه اجرای Jobها را همراه با مدت زمان اجرا بر حسب ثانیه و زمان شروع و پایان اجرا جمع‌آوری می‌کند. سپس، Jobها و مراحل آن‌ها را گروه‌بندی کرده و میانگین و حداکثر مدت زمان اجرای هر مرحله را محاسبه می‌کند. در نهایت، Jobهایی را فیلتر می‌کند که حداکثر زمان اجرای یک مرحله، بیش از 1.5 برابر میانگین زمان اجرای همان مرحله باشد. این روش به شما امکان می‌دهد Jobهایی را که دارای ناهنجاری در زمان اجرا هستند، به سرعت شناسایی کنید.

کوئری دوم

با ترکیب اطلاعات از `sysjobactivity` و `syssessions`، می‌توانیم یک کوئری دقیق‌تر برای مانیتورینگ جاب‌ها ایجاد کنیم که زمان اجرای صحیح آن‌ها را نشان دهد و فیلتر کند:

SELECT
    sj.name AS JobName,
    CASE sja.run_requested_source
        WHEN 1 THEN 'Scheduler'
        WHEN 2 THEN 'User'
        WHEN 3 THEN 'Step'
        ELSE 'Unknown'
    END AS RunSource,
    sja.run_requested_date AS RequestDate,
    sja.start_execution_date AS StartDate,
    sja.last_executed_step_id AS LastStep,
    CASE sja.last_executed_step_id
        WHEN 0 THEN 'Job Started'
        ELSE CONVERT(VARCHAR(50), sjs.step_id) + ' - ' + sjs.step_name
    END AS LastStepInfo,
    DATEDIFF(mi, sja.start_execution_date, GETDATE()) AS ElapsedMinutes,
    DATEDIFF(ss, sja.start_execution_date, GETDATE()) AS ElapsedSeconds,
    sja.next_scheduled_run_date AS NextRunDate
FROM
    msdb.dbo.sysjobs sj
INNER JOIN
    msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id
LEFT JOIN
    msdb.dbo.sysjobsteps sjs ON sja.job_id = sjs.job_id AND sja.last_executed_step_id = sjs.step_id
WHERE
    sja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
    AND sja.start_execution_date IS NOT NULL
    AND sja.stop_execution_date IS NULL
ORDER BY
    ElapsedMinutes DESC;

این کوئری اطلاعات جامعی مانند نام جاب، منبع اجرا، تاریخ درخواست و شروع، آخرین Step اجرا شده و مدت زمان سپری شده را ارائه می‌دهد. این لیست را می‌توانید بر اساس `ElapsedMinutes` مرتب کنید تا طولانی‌ترین جاب‌ها در بالا قرار گیرند.

مانیتورینگ Stepهای جاب

گاهی اوقات، یک جاب ممکن است در یک Step خاص برای مدت طولانی گیر کرده باشد. برای بررسی جزئیات بیشتر، می‌توانیم Stepهای هر جاب را از `msdb.dbo.sysjobsteps` بررسی کنیم. کوئری بالا اطلاعات `LastStepInfo` را شامل می‌شود که نشان می‌دهد جاب در کدام Step قرار دارد.

متوقف کردن Long Running SQl Job‌مدت

پس از شناسایی یک جاب که بیش از حد طولانی شده است، نیاز به مکانیزمی برای متوقف کردن آن دارید. SQL Server Agent دارای رویه ذخیره شده `sp_stop_job` است که می‌تواند یک جاب را متوقف کند.

برای متوقف کردن یک جاب با نام آن:

EXEC msdb.dbo.sp_stop_job N'نام_جاب';

یا با `job_id`:

EXEC msdb.dbo.sp_stop_job @job_id = 'GUID_جاب';

**نکته مهم:** استفاده از `sp_stop_job` به جاب فرصت می‌دهد تا به طور منظم متوقف شود. اگر جاب به این روش متوقف نشد، ممکن است نیاز به متوقف کردن آن به صورت تهاجمی‌تر با استفاده از دستور `KILL` باشد.

استفاده از دستور `KILL` 

برای استفاده از `KILL`، نیاز به پیدا کردن `SPID` (Session ID) مربوط به فرایند جاب دارید. این کار می‌تواند کمی پیچیده‌تر باشد زیرا جاب‌های Agent اغلب چندین SPID دارند، به خصوص اگر شامل اجرای چندین دستور SQL باشند.

ابتدا، باید `session_id` مربوط به جاب Agent را پیدا کنید. می‌توانید از `sys.dm_exec_requests` استفاده کنید و آن را با `msdb.dbo.sysjobactivity` جوین کنید تا SPIDهای مرتبط را پیدا کنید:

SELECT
    sj.name AS JobName,
    sja.start_execution_date AS StartDate,
    DATEDIFF(MINUTE, sja.start_execution_date, GETDATE()) AS ElapsedMinutes,
    der.session_id AS SPID,
    der.command,
    der.status,
    der.blocking_session_id,
    SUBSTRING(st.text, (der.statement_start_offset / 2) + 1,
              ((CASE der.statement_end_offset
                  WHEN -1 THEN DATALENGTH(st.text)
                  ELSE der.statement_end_offset
               END - der.statement_start_offset) / 2) + 1) AS CurrentStatement
FROM
    msdb.dbo.sysjobactivity sja
INNER JOIN
    msdb.dbo.sysjobs sj ON sja.job_id = sj.job_id
INNER JOIN
    sys.dm_exec_requests der ON sja.start_execution_date = der.start_time
OUTER APPLY
    sys.dm_exec_sql_text(der.sql_handle) st
WHERE
    sja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
    AND sja.start_execution_date IS NOT NULL
    AND sja.stop_execution_date IS NULL
ORDER BY
    ElapsedMinutes DESC;

این کوئری SPID مربوط به هر جاب در حال اجرا را به همراه دستور فعلی که در حال اجرای آن است، نشان می‌دهد. پس از پیدا کردن SPID هدف، می‌توانید از دستور `KILL` استفاده کنید:

KILL SPID_هدف;

**هشدار:** استفاده از `KILL` به صورت تهاجمی باعث می‌شود که تمام تراکنش‌های مرتبط با آن SPID Rollback شوند، که ممکن است زمان‌بر باشد و به طور بالقوه منجر به از دست رفتن داده‌ها (در صورت عدم تکمیل تراکنش) یا ناسازگاری شود. همیشه قبل از استفاده از `KILL`، تاثیر آن را به دقت ارزیابی کنید.

پیشگیری و بهترین شیوه‌ها

برای جلوگیری از مشکلات آینده ناشی از Jobهای طولانی‌مدت، اتخاذ بهترین شیوه‌ها ضروری است:

• بهینه‌سازی کوئری‌ها: اطمینان حاصل کنید که تمامی کوئری‌های T-SQL داخل Jobها بهینه‌سازی شده‌اند. از Indexهای مناسب استفاده کنید و از کوئری‌های ناکارآمد اجتناب کنید.
• تنظیم زمان‌بندی مناسب: Jobها را در زمان‌هایی که کمترین بار روی سرور است، اجرا کنید. از تداخل Jobهای با بار کاری سنگین خودداری کنید.
• تقسیم Jobهای پیچیده: Jobهای بسیار بزرگ و پیچیده را به Jobهای کوچکتر و مدیریت‌پذیرتر تقسیم کنید. این کار به شما امکان می‌دهد تا هر قسمت را به‌طور مستقل نظارت و عیب‌یابی کنید.
• تنظیم زمان‌بندی تایم‌اوت (Timeout): برای مراحل Job، تایم‌اوت مناسبی تنظیم کنید تا از اجرای بی‌نهایت آن‌ها جلوگیری شود و به شما اطلاع داده شود که یک مرحله بیش از حد انتظار به طول انجامیده است.
• پایش و هشداردهی: سیستم‌های پایش و هشداردهی را برای Jobهای طولانی‌مدت یا ناموفق تنظیم کنید تا به محض بروز مشکل، از آن مطلع شوید.
• نگهداری منظم: به طور منظم آمارهای دیتابیس را به‌روزرسانی کنید و Indexها را بازسازی یا سازماندهی کنید تا عملکرد کوئری‌ها بهبود یابد.

نتیجه‌گیری

مدیریت و کنترل جاب‌های SQL Server با زمان اجرای طولانی برای حفظ سلامت و کارایی سیستم دیتابیس شما حیاتی است. با استفاده از کوئری‌های ارائه شده، می‌توانید جاب‌های مشکل‌ساز را شناسایی کرده و با `sp_stop_job` یا در موارد ضروری با `KILL` آن‌ها را متوقف کنید. پیاده‌سازی یک سیستم مانیتورینگ فعال به شما کمک می‌کند تا پیش از اینکه این جاب‌ها به مشکلات جدی تبدیل شوند، آن‌ها را شناسایی و حل کنید.