تعریف و کاربرد sys.dm_os_spinlock_stats

sys.dm_os_spinlock_stats برای آمار Spinlock و رقابت بسیار کوتاه داخلی استفاده می‌شود. خروجی آن باید در کنار هدف تشخیص، نسخه SQL Server و Counterهای مکمل خوانده شود تا میان نشانه و علت ریشه‌ای اشتباه نشود.

در محیط Production بهتر است Query مشاهده‌ای، محدود و قابل ثبت باشد. هر اقدام تغییردهنده مانند Reset Counter یا خاتمه Session باید جدا از مرحله مشاهده، با مجوز و برنامه بازگشت انجام شود.

نحو پایه

SELECT *
FROM sys.dm_os_spinlock_stats;

ستون‌ها و معنای آن‌ها

نوع خروجی و دامنه Counter

خروجی یک Rowset از Counterهای تجمعی است. مقادیر از زمان آغاز دامنه مربوط رشد می‌کنند و برای تحلیل بازه‌ای باید دو Snapshot معتبر با زمان ثبت‌شده مقایسه شوند.

پیش‌نیاز دسترسی و ملاحظات نسخه

مشاهده DMVهای سطح سرور نیازمند مجوز مناسب است. در نسخه‌های قدیمی معمولاً VIEW SERVER STATE مطرح است و در SQL Server 2022 بسیاری از اطلاعات کارایی به VIEW SERVER PERFORMANCE STATE منتقل شده‌اند. در Azure SQL و سرویس‌های مدیریت‌شده، دامنه دید و نقش لازم ممکن است متفاوت باشد.

برای حفظ اصل کمترین دسترسی، مجوز را به حساب Collector یا نقش مانیتورینگ محدود کنید و دسترسی به متن Query را جداگانه ارزیابی نمایید. خروجی تشخیصی ممکن است نام کاربر، برنامه، Object یا متن حساس داشته باشد.

مثال‌های عملی

مثال ۱: نمایش Spinlockهای دارای Collision

این Query نام و Counterهای اصلی Spinlockهایی را که برخورد داشته‌اند نمایش می‌دهد.

SELECT
    name,
    collisions,
    spins,
    spins_per_collision,
    sleep_time,
    backoffs
FROM sys.dm_os_spinlock_stats
WHERE collisions > 0
ORDER BY collisions DESC;

عدد تجمعی به‌تنهایی مشکل نیست؛ Uptime و حجم Batchها را کنار آن ثبت کنید.

مثال ۲: ده Spinlock با بیشترین Backoff

Backoff نشان می‌دهد Thread پس از تلاش فعال عقب‌نشینی کرده و برای اولویت‌بندی مفید است.

SELECT TOP (10)
    name,
    collisions,
    backoffs,
    spins
FROM sys.dm_os_spinlock_stats
WHERE backoffs > 0
ORDER BY backoffs DESC;

Backoff بالا را با افزایش Latency و CPU در همان بازه هم‌بسته کنید.

مثال ۳: محاسبه Backoff به‌ازای هزار Collision

این نرخ امکان مقایسه بهتر Spinlockهایی با حجم متفاوت را فراهم می‌کند و تقسیم بر صفر را کنترل می‌کند.

SELECT TOP (20)
    name,
    collisions,
    backoffs,
    CAST(1000.0 * backoffs /
         NULLIF(collisions, 0) AS decimal(18,2)) AS backoffs_per_1000_collisions
FROM sys.dm_os_spinlock_stats
WHERE collisions > 0
ORDER BY backoffs_per_1000_collisions DESC;

نرخ بالا با Collision بسیار کم ممکن است اهمیت عملی نداشته باشد؛ حداقل حجم را نیز لحاظ کنید.

مثال ۴: محاسبه Spin به‌ازای Collision

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

SELECT TOP (20)
    name,
    spins_per_collision,
    CAST(spins * 1.0 /
         NULLIF(collisions, 0) AS decimal(18,2)) AS calculated_spins_per_collision
FROM sys.dm_os_spinlock_stats
WHERE collisions > 0
ORDER BY calculated_spins_per_collision DESC;

اختلاف جزئی می‌تواند از نوع داده یا زمان Snapshot ناشی شود؛ Query DMV یک Snapshot کاملاً اتمیک تضمین نمی‌کند.

مثال ۵: فیلتر یک Spinlock مشخص

پارامتر نام امکان تمرکز روی Counterی را می‌دهد که در Delta یا مستندات نسخه برجسته شده است.

DECLARE @SpinlockName nvarchar(256) = N'LOCK_HASH';

SELECT
    name,
    collisions,
    spins,
    sleep_time,
    backoffs
FROM sys.dm_os_spinlock_stats
WHERE name = @SpinlockName;

اگر نام در نسخه مقصد وجود ندارد، آن را با مشابه حدسی جایگزین نکنید.

مثال ۶: شناسایی حجم بالا و نرخ Backoff بالا

این مثال شرط حداقل Collision را با نرخ Backoff ترکیب می‌کند تا نویز نمونه کوچک کمتر شود.

DECLARE @MinCollisions bigint = 10000;
DECLARE @MinBackoffRate decimal(9,2) = 10.00;

SELECT
    name,
    collisions,
    backoffs,
    CAST(1000.0 * backoffs /
         NULLIF(collisions, 0) AS decimal(18,2)) AS backoff_rate
FROM sys.dm_os_spinlock_stats
WHERE collisions >= @MinCollisions
  AND 1000.0 * backoffs / NULLIF(collisions, 0) >= @MinBackoffRate
ORDER BY backoff_rate DESC;

آستانه‌ها باید از Baseline همان سامانه استخراج شوند و مثال عدد ثابت عمومی نیست.

مثال ۷: ثبت Snapshot آغاز بازه

برای جدا کردن رفتار بازه جاری از Uptime، Counterها در جدول موقت ذخیره می‌شوند.

DROP TABLE IF EXISTS #SpinStart;

SELECT
    name,
    collisions,
    spins,
    sleep_time,
    backoffs
INTO #SpinStart
FROM sys.dm_os_spinlock_stats;

SELECT COUNT(*) AS captured_spinlocks
FROM #SpinStart;

در مخزن دائمی، captured_at_utc، نام سرور، Build و Uptime را نیز ذخیره کنید.

مثال ۸: محاسبه Delta Spinlockها

اختلاف Counterهای جاری و Snapshot آغاز، فعالیت واقعی بازه را نشان می‌دهد.

SELECT TOP (15)
    C.name,
    C.collisions - B.collisions AS delta_collisions,
    C.spins - B.spins AS delta_spins,
    C.backoffs - B.backoffs AS delta_backoffs
FROM sys.dm_os_spinlock_stats AS C
INNER JOIN #SpinStart AS B
    ON B.name = C.name
WHERE C.collisions >= B.collisions
ORDER BY delta_backoffs DESC;

Counter کمتر از Snapshot اول نشانه Restart یا Clear است و Delta آن بازه معتبر نیست.

مثال ۹: هم‌بستگی Snapshot با Schedulerهای قابل مشاهده

دو Result Set در یک Capture، Spinlock و صف Scheduler را با زمان UTC مشترک برای بررسی CPU فراهم می‌کنند.

DECLARE @CapturedAtUtc datetime2(3) = SYSUTCDATETIME();

SELECT TOP (10)
    @CapturedAtUtc AS captured_at_utc,
    name,
    collisions,
    backoffs
FROM sys.dm_os_spinlock_stats
ORDER BY backoffs DESC;

SELECT
    @CapturedAtUtc AS captured_at_utc,
    scheduler_id,
    runnable_tasks_count,
    current_tasks_count
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE';

این هم‌بستگی علت را اثبات نمی‌کند، اما مشخص می‌کند افزایش Backoff با صف Runnable هم‌زمان بوده است یا خیر.

مثال ۱۰: پاک‌سازی کنترل‌شده Spinlock Stats

این دستور Counterهای Spinlock را Reset می‌کند و برای Production باید آخرین گزینه و با تأیید عملیاتی باشد.

-- فقط پس از Snapshot و هماهنگی با تیم مانیتورینگ.
DBCC SQLPERF(N'sys.dm_os_spinlock_stats', CLEAR);

SELECT SUM(collisions) AS collisions_after_clear
FROM sys.dm_os_spinlock_stats;

ذخیره Snapshot و محاسبه Delta معمولاً تاریخچه را بهتر حفظ می‌کند و ریسک تشخیصی کمتری دارد.

نکات فنی و تفسیر حرفه‌ای

collisions و spins با حجم کار رشد می‌کنند. برای مقایسه باید نرخ در ثانیه یا مقدار به‌ازای Batch Request محاسبه شود و Uptime یکسان نباشد، Delta مبنا قرار گیرد.

backoffs معمولاً از Collision مهم‌تر است، زیرا نشان می‌دهد تلاش فعال کافی نبوده و Thread عقب‌نشینی کرده است. با این حال آستانه عمومی برای همه سخت‌افزارها وجود ندارد.

spins_per_collision نسبت شدت تلاش فعال را نشان می‌دهد، ولی ممکن است به‌علت Counterهای بزرگ یا تغییر پیاده‌سازی داخلی قابل مقایسه میان نسخه‌ها نباشد.

افزایش Spinlock می‌تواند با تعداد هسته، الگوی هم‌زمانی، مسیر Log، Cache داخلی یا Bug یک Build مرتبط باشد. مستندات رسمی و بررسی Updateهای تجمعی بخشی از فرایند است.

اقدام‌هایی مانند Trace Flag ناشناخته یا دستکاری تنظیمات داخلی بدون راهنمایی پشتیبانی می‌تواند ریسک پایداری ایجاد کند. ابتدا مسئله را با تست قابل تکرار و Counterهای مکمل اثبات کنید.

برای هر مشاهده، زمان UTC، نام سرور، نسخه، Uptime و شناسه رخداد را همراه خروجی ثبت کنید. این Metadata امکان تشخیص Restart، مقایسه درست Snapshotها و ممیزی تصمیم‌ها را فراهم می‌کند.

هم‌بستگی زمانی به معنی علت قطعی نیست. اگر Counter با کندی هم‌زمان رشد کرد، فرضیه‌ای بسازید که با Query، Plan، شاخص سیستم‌عامل یا آزمایش کنترل‌شده قابل رد یا تأیید باشد.

خطاهای رایج

• ترسیدن از عدد بزرگ تجمعی بدون محاسبه نرخ.
• مقایسه مستقیم دو سرور با Uptime و workload متفاوت.
• فرض اینکه هر Collision باعث کندی کاربر شده است.
• اعمال Trace Flag غیرمستند از روی یک نام Spinlock.
• نادیده گرفتن Build و Known Issueهای نسخه.

خطای مشترک دیگر، ارائه خروجی DMV بدون واحد، زمان Capture و توضیح دامنه Counter است. گزارش حرفه‌ای باید به خواننده بگوید عدد دقیقاً چه چیزی را در چه بازه‌ای اندازه گرفته است.

ملاحظات کارایی

Query مانیتورینگ را با ستون‌های موردنیاز، فیلتر مشخص و TOP معقول بنویسید. دریافت همه ردیف‌ها در فاصله بسیار کوتاه، به‌ویژه همراه متن SQL یا Plan، حجم داده و سربار پردازش مخزن را افزایش می‌دهد.

محاسبه‌های تاریخی و نمودارها را روی مخزن مانیتورینگ انجام دهید. سرور Production بهتر است فقط Snapshot خام و سبک را تولید کند. خطا، Timeout، Reset و Failover را به‌عنوان وضعیت داده نگه دارید و با صفر ساختگی جایگزین نکنید.

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

• دو Snapshot و Delta زمان‌دار ثبت کنید.
• Collisions، spins و backoffs را با CPU و Throughput نرمال کنید.
• Baseline همان سرور و workload را معیار قرار دهید.
• Build و Cumulative Update را در گزارش ثبت کنید.
• تغییرات را با پشتیبانی و تست بار کنترل‌شده انجام دهید.

پیش از تغییر، معیار موفقیت قابل اندازه‌گیری تعریف کنید و پس از تغییر همان بار و همان شاخص‌ها را دوباره بسنجید. کاهش یک Counter داخلی زمانی ارزشمند است که Latency، Throughput یا پایداری سرویس نیز بهتر شود.

کاربرد واقعی در پروژه سازمانی

در سامانه‌ای با چند سرویس، Snapshotهای sys.dm_os_spinlock_stats باید با شناسه سرور، برنامه، بازه Incident و رخدادهای Deploy در یک Timeline قرار گیرند. این کار امکان می‌دهد تیم DBA، توسعه و زیرساخت به‌جای تبادل Screenshotهای پراکنده روی یک مجموعه داده مشترک گفتگو کنند.

در Runbook تعیین کنید چه کسی Collector را اجرا می‌کند، چه آستانه‌ای Incident می‌سازد، چه داده‌ای حساس است و کدام اقدام نیازمند تأیید مدیر شیفت است. فرایند روشن معمولاً بیش از یک Query پیچیده زمان رفع مشکل را کاهش می‌دهد.

برای داشبورد، مقدار خام، Delta، نرخ بر ثانیه، Baseline و اثر کاربری را کنار هم نمایش دهید. رنگ هشدار باید از انحراف پایدار و چندشاخصی ساخته شود تا تیم با هشدارهای بی‌عمل خسته نشود.

سؤالات متداول

پرسش ۱: Spinlock چیست و چرا Spin می‌کند؟

برای حفاظت بسیار کوتاه از ساختار داخلی است؛ Thread چند بار فعال تلاش می‌کند تا هزینه Sleep و Wake برای انتظار کوتاه پرداخت نشود.

پرسش ۲: کدام Counterها مهم‌ترند؟

collisions، spins و backoffs باید با هم و در قالب Delta و نرخ دیده شوند. هیچ Counter منفردی تشخیص کامل نمی‌دهد.

پرسش ۳: آیا Spinlock بالا یعنی باید CPU بیشتری بخریم؟

خیر، افزایش هسته حتی می‌تواند الگوی رقابت را تغییر دهد. تحلیل workload و Build پیش از هزینه زیرساخت ضروری است.

پرسش ۴: Spinlock چگونه بر سرویس تجاری اثر می‌گذارد؟

اگر Backoff و CPU هم‌زمان با افت Throughput و افزایش Latency رشد کنند، اثر محتمل است. شاخص فنی باید به SLA متصل شود.

پرسش ۵: فرق Spinlock و Latch چیست؟

هر دو داخلی‌اند، اما Spinlock برای مسیر بسیار کوتاه با Busy-wait طراحی شده و Latch می‌تواند سازوکار انتظار متفاوت و ساختارهای دیگری را محافظت کند.

پرسش ۶: چه زمانی متخصص SQL Internals لازم است؟

وقتی Delta پایدار، قابل تکرار و هم‌بسته با افت کارایی است و راه‌حل در Query یا منابع عمومی یافت نمی‌شود، بررسی تخصصی و پشتیبانی سازنده مناسب است.

پرسش ۷: رایج‌ترین خطا در خواندن collisions چیست؟

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

پرسش ۸: Polling این DMV چه اثری دارد؟

خود خواندن هدفمند سبک است، ولی دوره بسیار کوتاه و ذخیره بی‌حد داده ایجاد می‌کند. نمونه‌برداری را با هدف تشخیص هماهنگ کنید.

پرسش ۹: بهترین روش مقایسه قبل و بعد چیست؟

بازه‌های هم‌نوع با Delta زمان‌دار، Batch Rate، CPU و Latency یکسان بسازید و فقط یک تغییر را در هر آزمایش اعمال کنید.

پرسش ۱۰: آیا نام Spinlockها بین نسخه‌ها ثابت است؟

جزئیات داخلی می‌توانند تغییر کنند. Build، Cumulative Update و مستندات دقیق نسخه مقصد باید در تحلیل ثبت شود.

سؤالات مصاحبه‌ای

دامنه داده sys.dm_os_spinlock_stats چیست؟

Counterهای تجمعی را بر اساس کلید اصلی DMV ارائه می‌کند و برای بازه باید Delta محاسبه شود.

چرا Snapshot زمان‌دار ضروری است؟

بدون زمان Capture نمی‌توان نرخ، Delta، هم‌بستگی با Incident یا اعتبار بازه پس از Restart را تعیین کرد.

چگونه تقسیم بر صفر را در نرخ‌ها مدیریت می‌کنید؟

در مخرج از NULLIF استفاده می‌کنیم و NULL را به‌عنوان داده غیرقابل محاسبه نگه می‌داریم، نه اینکه همیشه آن را صفر فرض کنیم.

چه زمانی مقدار تجمعی گمراه‌کننده است؟

وقتی Uptime طولانی، workload تغییرکرده یا Counter در میانه مقایسه Reset شده باشد. Delta بازه هم‌نوع راه‌حل اصلی است.

چگونه سربار Collector را کنترل می‌کنید؟

ستون و ردیف محدود، Interval هدفمند، جداسازی Snapshot خام از تحلیل و Retention چندلایه استفاده می‌شود.

چرا Correlation برای اثبات علت کافی نیست؟

دو متریک ممکن است از علت سوم اثر بگیرند. Query، Plan یا آزمایش کنترل‌شده برای کامل کردن زنجیره علت لازم است.

چک‌لیست نهایی

• مجوز و دامنه دید کنترل شده است.
• زمان UTC و Uptime ثبت شده است.
• واحد و دامنه Counter مشخص است.
• Snapshot با Baseline مناسب مقایسه شده است.
• NULL، Restart و Reset مدیریت شده‌اند.
• شواهد مکمل برای فرضیه جمع شده‌اند.
• معیار موفقیت تغییر و روش بازگشت تعریف شده است.

جمع‌بندی

sys.dm_os_spinlock_stats وقتی بیشترین ارزش را دارد که در یک فرایند منظم اندازه‌گیری، تفسیر و آزمون استفاده شود. مثال‌های این مقاله الگوی Query ایمن را نشان می‌دهند، اما آستانه و اقدام باید از Baseline و معماری واقعی شما استخراج شود.

برای دیدن ارتباط این DMV با چهار ابزار دیگر، به مقاله مادر Wait Statistics در SQL Server بازگردید.