نظارت بر TempDB سرور SQL به‌وسیله Viewها

مقدمه

در این مقاله، ما یاد خواهیم گرفت که چگونه می‌توانیم تشخیص دهیم که کدام عملیات باعث پرشدن SQL Server tempdb از طریق نماهای (Views) مدیریت پویا می‌شود.

SQL Server tempdb برای چه چیزی استفاده می‌شود؟

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

• عبارات جدول رایج (CTE)
• جداول موقت و متغیرهای جدول
• Hash Joins
• محرک‌ها
• نشانگرها
• GROUP BY و ORDER BY
• نشانگرها (Index)
• نمایه‌سازی آنلاین
• سطوح جداسازی عکس فوری

همان‌طور که مشاهده می‌شود، tempdb مسئولیت‌های مختلف عملکردهای حیاتی را بر عهده می‌گیرد که ممکن است بر عملکرد موتور پایگاه‌داده تأثیر بگذارد. از سوی دیگر، در سیستم‌های پایگاه‌داده SQL که مدیریت ضعیفی دارند، پایگاه‌داده tempdb به طور ناگهانی شروع به رشد می‌کند و اگر مشکل دقیقاً شناسایی نشود، این مسئله ممکن است باعث بحران شود. اکنون بیایید روشی را بیاموزیم که به تشخیص اینکه کدام عملیات پایگاه‌داده tempdb را پر می‌کند کمک کند.

نگاه اول: sys.dm_db_file_space_usage

نماهای مدیریت پویا (DMVs) و توابع (DMFs) برای به‌دست‌آوردن اطلاعات دقیق در مورد فعالیت‌های SQL Server استفاده می‌شود. sys.dm_db_file_space_usage یکی از DMVهایی است که به نظارت بر اطلاعات استفاده شده از فضا در مورد پایگاه‌داده SQL Server کمک می‌کند. این نما اطلاعات مربوط به فضای استفاده شده از فایل‌های پایگاه‌داده را برمی‌گرداند. ویژگی اصلی این دیدگاه، برگرداندن داده‌های نقطه در زمان است که استفاده فعلی را فراهم می‌کند. کوئری زیر چهار اطلاعات مهم استفاده از فضا را در مورد پایگاه‌داده tempdb برمی‌گرداند.

SELECT
(SUM(unallocated_extent_page_count)*1.0/128) AS [Free space(MB)]
,(SUM(version_store_reserved_page_count)*1.0/128) AS [Used Space by VersionStore(MB)]
,(SUM(internal_object_reserved_page_count)*1.0/128) AS [Used Space by InternalObjects(MB)]
,(SUM(user_object_reserved_page_count)*1.0/128) AS [Used Space by UserObjects(MB)]
FROM tempdb.sys.dm_db_file_space_usage;

فضای خالی

فضای تخصیص نیافته فضای موجود در پایگاه‌داده tempdb را گزارش می‌دهد. درعین‌حال، این مقدار را می‌توان در ویژگی‌های پایگاه‌داده tempdb مشاهده کرد.

فضای استفاده شده توسط VersionStore

سطوح جداسازی ردیف – نسخه به ما امکان می‌دهد بر مشکلات تضاد بین عملیات خواندن و نوشتن غلبه کنیم. در این سطوح جداسازی، اصل کار بر اساس ذخیره‌سازی نسخه ردیف قبلی در tempdb است. version_store_reserved_page_count تعداد کل صفحاتی را نشان می‌دهد که برای version store اختصاص‌داده‌شده است.

نکته: فعال‌کردن ویژگی Accelerated Database Recovery (ADR) در SQL Server 2019 این رفتار را تغییر می‌دهد و ورژن قبلی در فایل پایگاه‌داده نگهداری می‌شوند.

فضای مورداستفاده توسط اشیاء داخلی

SQL Server از پایگاه‌داده tempdb SQL Server برای ذخیره موقت برخی از داده‌ها در طول اجرای یک پرس‌وجو برای انجام برخی فعالیت‌های داخلی استفاده می‌کند. مانند اپراتورهای spool جدول یک کپی از داده‌های ورودی را در طول اجرای دستور تولید می‌کنند و این داده‌های ورودی در tempdb سرور SQL ذخیره می‌شود. کوئری زیر به دلیل استفاده عملگر spool مقداری فضا در پایگاه‌داده tempdb اختصاص می‌دهد.

SELECT TOP 150 SO.AccountNumber FROM
Sales.SalesOrderHeader SO
INNER JOIN Sales.SalesOrderDetailEnlarged SD
ON SO.ModifiedDate = SD.ModifiedDate

فضای استفاده از پایگاه‌داده tempdb به دلیل استفاده عملیات داخلی در ستون interior_object_reserved_page_count نشان‌داده‌شده است.

فضای استفاده شده توسط UserObjects

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

ردیابی استفاده از فضای Tempdb SQL Server بر اساس سطح جلسه

dm_db_session_space_usage DMV دیگری است که می‌تواند برای ردیابی تعداد تخصیص‌یافته و اختصاص‌داده‌شده صفحات بر اساس سطح جلسه در tempdb SQL Server استفاده شود. با کمک این DMV مفید، می‌توانیم بفهمیم که تا پایان جلسه، چند صفحه توسط جلسات تخصیص‌ داده‌ شده است. این دیدگاه مستقیماً به جلسه مشکل‌دار اشاره می‌کند. اکنون اجازه دهید جزئیات استفاده از این نما(View) را در یک پرس‌وجو بررسی کنیم. ابتدا، آمار IO جلسه را فعال می‌کنیم و سپس برنامه اجرای واقعی را فعال می‌کنیم.در مرحله دوم، پرس‌وجو را اجرا می‌کنیم و روی تب پیام (Message)کلیک می‌کنیم و شروع به تجزیه‌وتحلیل آمار IO می‌کنیم.

SELECT
Sh.AccountNumber ,SUM(Sh.SubTotal),Sd.CarrierTrackingNumber
FROM Sales.SalesOrderHeader Sh
INNER JOIN Sales.SalesOrderDetail Sd
ON Sd.SalesOrderID =Sh.SalesOrderID
CROSS APPLY
(
SELECT MAX(th.Quantity)
FROM Production.TransactionHistory AS Th
WHERE
TH.ModifiedDate = Sd.ModifiedDate
GROUP BY ()
) AS th (Quantity)
GROUP BY Sh.AccountNumber ,Sd.CarrierTrackingNumber
ORDER BY Sd.CarrierTrackingNumber

در متن خروجی آمار IO، نام جدول WorkTable و WorkFile را مشاهده می‌کنیم .زمانی که سرور SQL نیاز به ذخیره موقت داده‌ها در tempdb دارد، این دو جدول در حین اجرای پرس‌وجو در tempdb ایجاد می‌شوند.در مثال ما می‌بینیم که ۵۲۴ صفحه از پایگاه‌داده tempdb خوانده شده است. در این مرحله، یک سؤال جدید در ذهن ما ظاهر می‌شود که دلیل این عملیات خواندن در پایگاه‌داده tempdb چیست؟ این پاسخ سؤال در طرح اجرای پرس‌وجو پنهان است.همان‌طور که در طرح اجرا مشاهده می‌شود، یک علامت هشدار بر روی عملگر مرتب‌سازی وجود دارد و زمانی که ماوس را روی این عملگر قرار می‌دهیم، می‌توانیم دلیل این علامت هشدار را بفهمیم. بخش هشدارها جزئیات گسترده‌ای در مورد این موضوع به ما می‌دهد و مشکل ما به tempdb مربوط می‌شود.
TempDB Spill: بهینه‌ساز پرس‌وجو میزان حافظه موردنیاز برای اجرای یک پرس‌وجو را تخمین می‌زند و سپس آن حافظه درخواستی به آن کوئری اختصاص می‌یابد. بااین‌حال، برآوردهای نادرست باعث درخواست حافظه کمتر از نیاز واقعی می‌شود. در این نوع موارد، SQL Server متوجه می‌شود که به حافظه بیشتری نسبت به آنچه که داده شده نیاز دارد، به همین دلیل تصمیم می‌گیرد از پایگاه‌داده tempdb استفاده کند. نقطه‌ضعف اصلی این مکانیسم این است که استفاده از یک منبع دیسک همیشه کندتر از استفاده از حافظه بافر است، بنابراین بر عملکرد پرس‌وجو تأثیر منفی می‌گذارد.
نکته: می‌توانیم نقص‌های tempdb را برای استفاده در رویدادهای (Store Procedure) Extended Events sort_warning و hash_warning نظارت کنیم. این رویداد زمانی را ضبط می‌کند که یک عملیات مرتب‌سازی یا اتصال هش، یک spill tempdb را انجام دهد.خروجی این رویداد به شرح زیر خواهد بود:ما می‌توانیم با کمک dm_db_session_space_usage، بر مصرف tempdb داخلی SQL Server نظارت کنیم.

select * from sys.dm_db_session_space_usage
where session_id =126

Query Store معیارهای مختلفی را در مورد کوئری‌ها و طرح‌های پرس‌وجو جمع‌آوری می‌کند و این داده‌ها را با گزارش‌های مختلف به کاربران ارائه می‌دهد. پرس‌وجوهای مصرف‌کننده منابع برتر، پرس‌وجوهایی را نشان می‌دهد که بیشترین مصرف منابع را دارند. ما می‌توانیم این گزارش را بر اساس معیارهای مختلف سازماندهی کنیم:حافظه Temp Db (KB) مورداستفاده به‌عنوان یکی از معیارهایی است که می‌توانیم اطلاعاتی در مورد کوئری‌هایی که در پایگاه‌داده tempdb سرور SQL ایجاد می‌کنند، به دست آوریم. به طور پیش‌فرض، این گزارش در نمای نمودار نشان داده می‌شود، اما می‌توانیم آن را به نمای شبکه‌ای تغییر دهیم.حافظه Temp Db (KB) مورداستفاده به‌عنوان یکی از معیارهایی است که می‌توانیم اطلاعاتی در مورد کوئری‌هایی که در پایگاه‌داده tempdb سرور SQL ایجاد می‌کنند، به دست آوریم. به طور پیش‌فرض، این گزارش در نمای نمودار نشان داده می‌شود، اما می‌توانیم آن را به نمای شبکه‌ای تغییر دهیم.

با کلیک بر روی نماد شبکه، نمای گزارش تغییر می‌کند.ستون میانگین دمای db استفاده شده نشان می‌دهد که یک کوئری چقدر حافظه را در پایگاه‌داده tempdb سرور SQL مصرف می‌کند. پرس‌وجو نمونه ما ۴۳۵۲ کیلوبایت حافظه مصرف می‌کند و این مقدار را می‌توان با استفاده از نما dm_db_session_space_usage اصلاح کرد. این نما نشان می‌دهد که تعداد ۵۴۴ برای این پرس‌وجو اختصاص‌داده‌شده است و اندازه یک صفحه ۸ کیلوبایت است؛ بنابراین هنگامی که (۵۴۴*۸=۴۳۵۲) این دو مقدار را تلاقی می‌کنیم، می‌توانیم مقدار کلی حافظه موقت استفاده شده در db را پیدا کنیم.

نکته: ما علامت هشدار را در عملگر مرتب‌سازی برنامه اجرا نمی‌بینیم، زیرا query store برنامه‌های اجرایی تخمینی و مسائل ریخته شده tempdb را که در طول اجرای یک پرس‌وجو رخ می‌دهد را نشان می‌دهد.

ردیابی فضای TempDB Server SQL بر اساس استفاده از سطح کار

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

CREATE TABLE #TempTest
(CNumber VARCHAR(100))
GO
INSERT INTO #TempTest WITH(TABLOCK)
SELECT CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged

در طول اجرای این پرس‌وجو، sys.dm_db_task_space_usage نشان می‌دهد که چند صفحه به هر وظیفه اختصاص‌داده‌شده است.

نتیجه گیری

SQL Server tempdb یکی از پایگاه‌های داده حیاتی سیستم است که می‌تواند بر عملکرد پایگاه‌داده تأثیر بگذارد. دانستن اینکه کدام DMV می‌تواند به نظارت بر پایگاه‌داده tempdb کمک کند، به حل مشکلات مربوط به استفاده از فضای tempdb کمک می‌کند.

منبع

https://www.sqlshack.com/monitoring-sql-server-tempdb-with-dynamic-management-views/