‫کدام برنامه را برای مانیتور کردن SQL پیشنهاد می‌کنید؟

SELECT 
    DB_NAME(ps.database_id) AS DatabaseName,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS SchemaName,
    OBJECT_NAME(ps.object_id, ps.database_id) AS ProcedureName,
    ps.execution_count AS TotalExecutions,
    
    -- زمان کل و میانگین زمان اجرا (به میلی‌ثانیه)
    (ps.total_elapsed_time / 1000.0) AS TotalDuration_ms,
    (ps.total_elapsed_time / ps.execution_count / 1000.0) AS AvgDuration_ms,
    (ps.last_elapsed_time / 1000.0) AS LastDuration_ms,

    -- زمان پردازنده (CPU Time به میلی‌ثانیه)
    (ps.total_worker_time / ps.execution_count / 1000.0) AS AvgCPUTime_ms,

    -- مصرف حافظه و دیسک (Logical Reads بر حسب Pageهای 8 کیلوبایتی)
    -- این شاخص دقیق‌ترین معیار فشار روی حافظه Cache است:
    (ps.total_logical_reads / ps.execution_count) AS AvgLogicalReads_Pages,
    (ps.total_logical_reads / ps.execution_count * 8 / 1024.0) AS AvgMemoryRead_MB,
    (ps.last_logical_reads * 8 / 1024.0) AS LastMemoryRead_MB,

    -- آخرین باری که اجرا شده
    ps.last_execution_time
FROM 
    sys.dm_exec_procedure_stats ps
WHERE 
    ps.database_id = DB_ID('YourDatabaseName') -- نام دیتابیس خود را اینجا وارد کنید
ORDER BY 
    AvgDuration_ms DESC;

نکته در مورد حافظه مصرفی: در دیتابیس‌های رابطه‌ای، مصرف حافظه در طول اجرای پروسیجر معمولاً با شاخص Logical Reads (تعداد صفحات ۸ کیلوبایتی خوانده‌شده از Buffer Cache) سنجیده می‌شود. همچنین برای پروسیجرهایی که نیاز به مرتب‌سازی سنگین دارند، ستون total_grant_kb (میزان حافظه رزرو شده یا Memory Grant) در نسخه‌های جدید SQL Server در همین DMV در دسترس است.

محدودیت DMV: اگر سرویس SQL Server ری‌استارت شود یا پروسیجر تغییر کند، آمار کش پاک می‌شود.

• اطلاعات حتی با ری‌استارت شدن سرور پاک نمی‌شوند و در دیسک ذخیره می‌مانند.
• گزارش‌های تصویری آماده (از جمله Longest Duration و Memory Consuming Queries) در SSMS در اختیارتان می‌گذارد.

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

• Top Resource Consuming Queries (برای بررسی مدت‌زمان و CPU)
• گزارش مصرف Memory Grants برای ردیابی مصرف مستقیم RAM

• module_end (هنگام اتمام اجرای هر SP تریگر می‌شود)
• فیلدهای دریافتی: duration، cpu_time، logical_reads و memory_grant

CREATE EVENT SESSION [Track_SP_Execution] ON SERVER 
ADD EVENT sqlserver.module_end(
    ACTION(
        sqlserver.database_name,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.sql_text
    )
    WHERE (
        [package0].[equal_uint64]([sqlserver].[database_id], DB_ID(N'YourDatabaseName')) -- فیلتر روی دیتابیس مشخص
        AND [object_type] = 'P ' -- فیلتر فقط برای Stored Procedureها
    )
)
ADD TARGET package0.event_file(
    SET filename = N'Track_SP_Execution.xel', -- مسیر ذخیره فایل گزارش
    max_file_size = (50),                      -- حداکثر حجم هر فایل: ۵۰ مگابایت
    max_rollover_files = (5)                   -- نگهداری تا حداکثر ۵ فایل چرخشی
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    TRACK_CAUSALITY = OFF
);
GO

ALTER EVENT SESSION [Track_SP_Execution] ON SERVER 
STATE = START;
GO

SELECT 
    event_data.value('(/event/@timestamp)[1]', 'datetime2') AS [EndTime_UTC],
    event_data.value('(/event/data[@name="object_name"]/value)[1]', 'sysname') AS [ProcedureName],
    
    -- زمان کل اجرا (میکروثانیه به میلی‌ثانیه تبدیل شده)
    event_data.value('(/event/data[@name="duration"]/value)[1]', 'bigint') / 1000.0 AS [Duration_ms],
    
    -- زمان مصرف CPU (میلی‌ثانیه)
    event_data.value('(/event/data[@name="cpu_time"]/value)[1]', 'bigint') / 1000.0 AS [CpuTime_ms],
    
    -- مصرف حافظه کش (تعداد صفحات ۸ کیلوبایتی و تبدیل به مگابایت)
    event_data.value('(/event/data[@name="logical_reads"]/value)[1]', 'bigint') AS [LogicalReads_Pages],
    (event_data.value('(/event/data[@name="logical_reads"]/value)[1]', 'bigint') * 8) / 1024.0 AS [MemoryRead_MB],
    
    -- تعداد نوشتن روی دیسک
    event_data.value('(/event/data[@name="writes"]/value)[1]', 'bigint') AS [Writes],
    
    event_data.value('(/event/action[@name="username"]/value)[1]', 'sysname') AS [UserName],
    event_data.value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS [ApplicationName]
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file(N'Track_SP_Execution*.xel', NULL, NULL, NULL)
) AS xed
ORDER BY [EndTime_UTC] DESC;

• در Object Explorer منوی Management و سپس Extended Events را باز کنید.
• روی پوشه Sessions کلیک راست کرده و New Session Wizard یا New Session را انتخاب کنید.
• در تب Events رویداد module_end را پیدا و اضافه کنید.
• در تب Data Storage گزینه event_file را انتخاب کنید تا لاگ‌ها روی دیسک بنویسند.
• پس از استارت سشن، با کلیک راست روی سشن و انتخاب Watch Live Data، اجرای SPها را به‌صورت لحظه‌ای در جدول زنده تماشا کنید.

-- توقف موقت
ALTER EVENT SESSION [Track_SP_Execution] ON SERVER STATE = STOP;

-- یا حذف کامل سشن
DROP EVENT SESSION [Track_SP_Execution] ON SERVER;

USE [YourDatabaseName];
GO

CREATE TABLE dbo.SP_ExecutionHistory (
    HistoryID BIGINT IDENTITY(1,1) PRIMARY KEY,
    EventTimestampUTC DATETIME2(3) NOT NULL,
    ProcedureName SYSNAME NOT NULL,
    DurationMs DECIMAL(18, 2) NOT NULL,
    CpuTimeMs DECIMAL(18, 2) NOT NULL,
    LogicalReads BIGINT NOT NULL,
    MemoryReadMB DECIMAL(18, 2) NOT NULL,
    PhysicalWrites BIGINT NOT NULL,
    UserName SYSNAME NULL,
    ApplicationName NVARCHAR(500) NULL,
    InsertDate DATETIME2(3) DEFAULT SYSUTCDATETIME()
);

-- ایجاد ایندکس روی ستون زمان برای جلوگیری از تکرار و سرعت در جستجوها
CREATE NONCLUSTERED INDEX IX_SP_ExecutionHistory_Timestamp 
ON dbo.SP_ExecutionHistory(EventTimestampUTC);
GO

USE [YourDatabaseName];
GO

CREATE OR ALTER PROCEDURE dbo.usp_Archive_SP_Executions
AS
BEGIN
    SET NOCOUNT ON;

    -- پیدا کردن زمان آخرین رکورد درج‌شده
    DECLARE @LastRecordedTime DATETIME2(3);
    SELECT @LastRecordedTime = ISNULL(MAX(EventTimestampUTC), '1900-01-01') 
    FROM dbo.SP_ExecutionHistory;

    -- خواندن داده‌ها از فایل‌های XEL و درج در جدول
    INSERT INTO dbo.SP_ExecutionHistory (
        EventTimestampUTC,
        ProcedureName,
        DurationMs,
        CpuTimeMs,
        LogicalReads,
        MemoryReadMB,
        PhysicalWrites,
        UserName,
        ApplicationName
    )
    SELECT 
        parsed.EventTimestampUTC,
        parsed.ProcedureName,
        parsed.DurationMs,
        parsed.CpuTimeMs,
        parsed.LogicalReads,
        (parsed.LogicalReads * 8.0) / 1024.0 AS MemoryReadMB,
        parsed.PhysicalWrites,
        parsed.UserName,
        parsed.ApplicationName
    FROM (
        SELECT 
            event_data.value('(/event/@timestamp)[1]', 'datetime2(3)') AS EventTimestampUTC,
            event_data.value('(/event/data[@name="object_name"]/value)[1]', 'sysname') AS ProcedureName,
            event_data.value('(/event/data[@name="duration"]/value)[1]', 'bigint') / 1000.0 AS DurationMs,
            event_data.value('(/event/data[@name="cpu_time"]/value)[1]', 'bigint') / 1000.0 AS CpuTimeMs,
            event_data.value('(/event/data[@name="logical_reads"]/value)[1]', 'bigint') AS LogicalReads,
            event_data.value('(/event/data[@name="writes"]/value)[1]', 'bigint') AS PhysicalWrites,
            event_data.value('(/event/action[@name="username"]/value)[1]', 'sysname') AS UserName,
            event_data.value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(500)') AS ApplicationName
        FROM (
            SELECT CAST(event_data AS XML) AS event_data
            -- در صورت لزوم مسیر کامل فایل را مشخص کنید: N'C:\LogPath\Track_SP_Execution*.xel'
            FROM sys.fn_xe_file_target_read_file(N'Track_SP_Execution*.xel', NULL, NULL, NULL)
        ) AS raw_xed
    ) AS parsed
    WHERE parsed.EventTimestampUTC > @LastRecordedTime
      AND parsed.ProcedureName IS NOT NULL
    ORDER BY parsed.EventTimestampUTC ASC;
END;
GO

• در محیط SSMS، بخش SQL Server Agent را گسترش داده و روی پوشه Jobs راست‌کلیک کرده و New Job را بزنید.
• در تب General یک نام انتخاب کنید (مثلاً DBA - Archive SP Execution Logs).
• در تب Steps دکمه New را بزنید:
• Step name:Run usp_Archive_SP_Executions
• Database: دیتابیس مربوطه را انتخاب کنید.
• Command: عبارت EXEC dbo.usp_Archive_SP_Executions; را درج کنید.
• در تب Schedules دکمه New را بزنید و بازه زمانی اجرا (مثلاً هر ۱۵ یا ۳۰ دقیقه یک‌بار) را تنظیم نمایید.
• روی OK کلیک کنید تا جاب ذخیره و فعال شود.

DELETE FROM dbo.SP_ExecutionHistory
WHERE EventTimestampUTC < DATEADD(DAY, -60, SYSUTCDATETIME());

SELECT TOP 10
    ProcedureName,
    COUNT(*) AS ExecutionCount,
    -- زمان بر حسب ثانیه
    CAST(AVG(DurationMs) / 1000.0 AS DECIMAL(10, 2)) AS AvgDuration_Sec,
    CAST(MIN(DurationMs) / 1000.0 AS DECIMAL(10, 2)) AS MinDuration_Sec,
    CAST(MAX(DurationMs) / 1000.0 AS DECIMAL(10, 2)) AS MaxDuration_Sec,
    CAST(AVG(CpuTimeMs) / 1000.0 AS DECIMAL(10, 2))  AS AvgCpu_Sec,
    CAST(AVG(MemoryReadMB) AS DECIMAL(10, 2))        AS AvgMemory_MB
FROM dbo.SP_ExecutionHistory
WHERE EventTimestampUTC >= DATEADD(DAY, -7, SYSUTCDATETIME()) -- بازه ۷ روز اخیر
GROUP BY ProcedureName
HAVING COUNT(*) >= 5
ORDER BY AvgDuration_Sec DESC;

SELECT TOP 10
    ProcedureName,
    COUNT(*) AS ExecutionCount,
    -- مجموع حافظه خوانده شده از دیسک/کش در کل دوره
    CAST(SUM(MemoryReadMB) / 1024.0 AS DECIMAL(10, 2)) AS TotalMemoryRead_GB,
    CAST(AVG(MemoryReadMB) AS DECIMAL(10, 2))          AS AvgMemoryRead_MB,
    CAST(MAX(MemoryReadMB) AS DECIMAL(10, 2))          AS MaxMemoryRead_MB,
    SUM(PhysicalWrites)                                AS TotalWrites
FROM dbo.SP_ExecutionHistory
WHERE EventTimestampUTC >= DATEADD(DAY, -7, SYSUTCDATETIME())
GROUP BY ProcedureName
ORDER BY TotalMemoryRead_GB DESC;

SELECT TOP 10
    ProcedureName,
    COUNT(*) AS ExecutionCount,
    -- مجموع کل زمانی که CPU درگیر این پروسیجر بوده
    CAST(SUM(CpuTimeMs) / 1000.0 / 60.0 AS DECIMAL(10, 2))   AS TotalCpu_Minutes,
    CAST(SUM(DurationMs) / 1000.0 / 60.0 AS DECIMAL(10, 2))  AS TotalDuration_Minutes,
    CAST(SUM(MemoryReadMB) / 1024.0 AS DECIMAL(10, 2))       AS TotalRead_GB,
    -- درصد سهم از کل زمان CPU در کل دیتابیس
    CAST(100.0 * SUM(CpuTimeMs) / SUM(SUM(CpuTimeMs)) OVER() AS DECIMAL(5, 2)) AS PctOfTotalCpu
FROM dbo.SP_ExecutionHistory
WHERE EventTimestampUTC >= DATEADD(DAY, -1, SYSUTCDATETIME()) -- ۲۴ ساعت گذشته
GROUP BY ProcedureName
ORDER BY TotalCpu_Minutes DESC;

SELECT TOP 10
    ProcedureName,
    COUNT(*) AS ExecutionCount,
    CAST(MIN(DurationMs) / 1000.0 AS DECIMAL(10, 2)) AS MinDuration_Sec,
    CAST(MAX(DurationMs) / 1000.0 AS DECIMAL(10, 2)) AS MaxDuration_Sec,
    CAST(AVG(DurationMs) / 1000.0 AS DECIMAL(10, 2)) AS AvgDuration_Sec,
    -- نسبت بدترین حالت به بهترین حالت
    CASE 
        WHEN MIN(DurationMs) > 0 THEN CAST(MAX(DurationMs) / MIN(DurationMs) AS DECIMAL(10, 1))
        ELSE NULL 
    END AS PerformanceVarianceRatio
FROM dbo.SP_ExecutionHistory
WHERE EventTimestampUTC >= DATEADD(DAY, -7, SYSUTCDATETIME())
GROUP BY ProcedureName
HAVING COUNT(*) >= 10 AND MIN(DurationMs) > 100 -- حذف اجراهای خطای آنی
ORDER BY PerformanceVarianceRatio DESC;