کوئری‌های مهم در SQL Server که هر DBA باید بداند

کوئری‌های ضروری SQL Server برای هر DBA

هر DBA باید به کوئری‌های مانیتورینگ لحظه‌ای Performance مسلط باشد.
از مهمترین آن‌ها می‌توان به کوئری پیدا کردن کوئری‌های پرمصرف از نظر CPU با استفاده از sys.dm_exec_query_stats و CROSS APPLY اشاره کرد که میانگین و کل مصرف CPU را به همراه متن کوئری و پلان اجرا نمایش می‌دهد.
همچنین کوئری تشخیص Blocking با JOIN بین sys.dm_exec_requests و بررسی blocking_session_id، کوئری مشاهده Missing Indexes از طریق sys.dm_db_missing_index_details، و کوئری بررسی Index Fragmentation با تابع sys.dm_db_index_physical_stats از ابزارهای ضروری هستند.
این کوئری‌ها به DBA اجازه می‌دهند بدون نیاز به SSMS، گلوگاه‌های عملکردی را سریعاً شناسایی کند.

برای مدیریت روزمره، DBA باید کوئری‌ای برای مشاهده آخرین Backup تمام دیتابیس‌ها با JOIN بین sys.databases و msdb.dbo.backupset بنویسد تا وضعیت پشتیبان‌گیری را ارزیابی کند. کوئری مشاهده اتصالات فعال با sys.dm_exec_sessions و کیل کردن session‌های مزاحم با دستور KILL <session_id> نیز حیاتی است.
در حوزه امنیت، کوئری لیست کاربران و نقش‌های دیتابیس با sys.database_principals و همچنین مشاهده لاگین‌های با دسترسی sysadmin از sys.server_principals و sys.server_role_members توصیه می‌شود.
در نهایت، دستورات نگهداری مانند DBCC CHECKDB برای بررسی سلامت دیتابیس، sp_updatestats برای به‌روزرسانی آمار، و تولید اسکریپت Rebuild ایندکس بر اساس درصد fragmentation از کوئری‌های پایه‌ای هستند که هر DBA باید در زرادخانه خود داشته باشد.

کوئری‌های بررسی وضعیت سرور و دیتابیس 

• مشاهده نسخه SQL Server و سیستم عامل

SELECT  
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('MachineName') AS MachineName,
    SERVERPROPERTY('ServerName') AS ServerName,
    @@VERSION AS FullVersion;

• مشاهده تمام دیتابیس‌ها با مشخصات کلیدی

SELECT 
    name AS DatabaseName,
    state_desc AS State,
    recovery_model_desc AS RecoveryModel,
    compatibility_level,
    create_date,
    (SELECT SUM(size * 8 / 1024) FROM sys.master_files WHERE database_id = db.database_id AND type_desc = 'ROWS') AS DataSizeMB,
    (SELECT SUM(size * 8 / 1024) FROM sys.master_files WHERE database_id = db.database_id AND type_desc = 'LOG') AS LogSizeMB
FROM sys.databases db
ORDER BY name;

• بررسی Last Backup زمان آخرین بکاپ تمام دیتابیس‌ها

SELECT 
    d.name AS DatabaseName,
    MAX(b.backup_finish_date) AS LastBackupDate,
    CASE 
        WHEN MAX(b.backup_finish_date) IS NULL THEN 'No Backup'
        WHEN DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) > 24 THEN 'Critical'
        ELSE 'OK'
    END AS BackupStatus
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D'
WHERE d.database_id > 4  -- exclude system dbs
GROUP BY d.name
ORDER BY LastBackupDate DESC;

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

کوئری‌های Performance و عیب‌یابی  SQL

• پیدا کردن کوئری‌های پرمصرف از نظر CPU

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS AvgCPU,
    qs.total_worker_time AS TotalCPU,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

• مشاهده Blocking (جلوگیری از اجرا)

SELECT 
    blocking.session_id AS BlockingSessionID,
    blocked.session_id AS BlockedSessionID,
    blocked.wait_type,
    blocking_text.text AS BlockingQuery,
    blocked_text.text AS BlockedQuery
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text;

• مشاهده Deadlock از طریق Trace Flag (نیاز به بررسی Log)

SELECT 
    xed.value('@timestamp', 'datetime') AS DeadlockTime,
    xed.query('.') AS DeadlockGraph
FROM (SELECT CAST(target_data AS XML) AS target_data
      FROM sys.dm_xe_session_targets st
      JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
      WHERE s.name = 'system_health') AS data
CROSS APPLY target_data.nodes('//event[@name="xml_deadlock_report"]') AS xed(xed);

• یافتن Missing Indexes (ایندکس‌های پیشنهادی)

SELECT 
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS Impact,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY Impact DESC;

• مشاهده Index Fragmentation (قطعه‌قطعه شدن ایندکس‌ها)

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5
ORDER BY ips.avg_fragmentation_in_percent DESC;

 

کوئری‌های مدیریت اتصالات و تراکنش‌ها  SQL

• مشاهده تمام اتصالات فعال با برنامه و زمان آخرین درخواست

SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    status,
    last_request_start_time,
    last_request_end_time,
    text AS LastQuery
FROM sys.dm_exec_sessions s
OUTER APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle)
WHERE session_id > 50;  -- system sessions below 50

• مشاهده تراکنش‌های باز و طولانی

SELECT 
    st.session_id,
    st.transaction_id,
    at.name AS TransactionName,
    at.transaction_begin_time,
    DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) AS DurationSeconds,
    est.text AS QueryText
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions at ON st.transaction_id = at.transaction_id
LEFT JOIN sys.dm_exec_requests r ON st.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) est;

• کیل کردن یک session (قطع اتصال)

-- ابتدا session_id را پیدا کنید، سپس:
KILL <session_id>;

کوئری‌های امنیتی و دسترسی‌ها در SQL

• مشاهده کاربران دیتابیس و نقش‌های آن‌ها

SELECT 
    dp.name AS UserName,
    dp.type_desc AS UserType,
    r.name AS RoleName
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members rm ON dp.principal_id = rm.member_principal_id
LEFT JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE dp.type IN ('S', 'U', 'E', 'C', 'G');

• مشاهده آخرین زمان موفق یا ناموفق لاگین

-- نیاز به بررسی error log
EXEC xp_readerrorlog 0, 1, N'Login', N'failed';
EXEC xp_readerrorlog 0, 1, N'Login', N'succeeded';

• مشاهده پروسه‌های با دسترسی بالا (مثلاً sysadmin)

SELECT 
    l.name AS LoginName,
    l.type_desc,
    s.name AS ServerRole
FROM sys.server_principals l
JOIN sys.server_role_members rm ON l.principal_id = rm.member_principal_id
JOIN sys.server_principals s ON rm.role_principal_id = s.principal_id
WHERE l.is_disabled = 0;

کوئری‌های فضای دیسک و فایل‌ها

• مشاهده اندازه فایل‌های دیتابیس و فضای خالی

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    type_desc AS FileType,
    name AS LogicalName,
    physical_name,
    (size * 8 / 1024) AS SizeMB,
    (size * 8 / 1024 - FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024) AS FreeSpaceMB,
    CAST((size * 8 / 1024 - FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024) * 100.0 / (size * 8 / 1024) AS DECIMAL(5,2)) AS FreePercent
FROM sys.master_files
WHERE database_id = DB_ID()  -- دیتابیس جاری
ORDER BY SizeMB DESC;

• مشاهده Auto Growth تنظیمات فایل‌ها

SELECT 
    name,
    type_desc,
    growth,
    is_percent_growth,
    CASE 
        WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR) + '%'
        ELSE CAST(growth * 8 / 1024 AS VARCHAR) + ' MB'
    END AS AutoGrowthSetting
FROM sys.master_files
WHERE database_id = DB_ID();

کوئری‌های نگهداری (Maintenance) در SQL

• گزارش fragmentation به همراه دستور بازسازی ایندکس

-- برای همه ایندکس‌های با fragmentation بالای 30%
SELECT 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(ips.object_id) + '] REBUILD;' AS RebuildCommand
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
    AND i.name IS NOT NULL;

• به‌روزرسانی آمار (Update Statistics)

-- برای تمام جداول دیتابیس جاری
EXEC sp_updatestats;

• دستور DBCC CHECKDB برای بررسی سلامت دیتابیس

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

کوئری‌های هشدار و لاگ در SQL

• مشاهده آخرین SQL Server Error Log

EXEC xp_readerrorlog 0, 1, NULL, NULL, 'DESC';

• مشاهده Agent Jobهای شکست خورده

SELECT 
    j.name AS JobName,
    h.run_date,
    h.run_time,
    h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.run_status = 0  -- 0 = Failed
ORDER BY h.run_date DESC, h.run_time DESC;

نتیجه‌گیری

مسلط بودن به این کوئری‌ها به هر DBA کمک می‌کند تا بدون وابستگی به رابط گرافیکی، عیب‌یابی، تیونینگ، مانیتورینگ و مدیریت روزمره SQL Server را انجام دهد.
پیشنهاد می‌شود این کوئری‌ها را در یک فایل ذخیره کرده و به مرور به نیازهای محیط خود سفارشی کنید.