تشخیص کمبود و نیاز به ایندکس گذاری
یکی از موارد بسیار کاربردی که به ورژن های جدید Sql Server نیز هر روز افزوده می شود داینامیک ویو ها هستند، داینامیک ویوی Missing_Index_Group کمک بسیار زیادی برای تشخیص نیاز جداول به ایندکس بما میکند ، این داینامیک ویو آمار های جمع آوری شده توسط Engine اس کیو ال سرور جهت تشخیص نیاز به ایندکس استفاده می کند. در زیر دو نمونه اسکریپت جهت مشاهده جداولی که نیاز به ایندکس دارند را مشاهده می کنید:
SELECT TOP 50
ROUND(s.avg_total_user_cost * s.avg_user_impact * ( s.user_seeks
+ s.user_scans ),
0) AS [Total Cost] ,s.user_seeks ,s.user_scans ,s.last_user_seek,s.avg_user_impact ,
d.[statement] AS [Table Name] ,
equality_columns ,
inequality_columns ,
included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
در اسکریپت بالا فیلد های avg user impact , user scans , user seeks , total user avg cost و … جهت مشاهده میزان مصرف کوئری های کاربران بر روی جداول و اینکه آیا نیاز به گذاشتن ایندکس بر روی جدول قابل مشاهده در فیلد Table Name می باشد یا خیر را می توان مشاهده کرد . اسکریپت بالا مورد استفاده خودم بوده و بسیاری از شرایط بکار آمده است ، نمونه ساده تر اسکریپت Missing index در زیر موجود بوده و این اسکریپت نیز بسیار کاربردی می باشد .
USE master;
SELECT d.database_id,
d.object_id,
d.index_handle,
d.equality_columns,
d.inequality_columns,
d.included_columns,
d.statement AS TABLE_Need_Index,
gs.*
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats gs
ON gs.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d
ON g.index_handle = d.index_handle
تا اینجای کار لیست بلندی از جداولی که نیاز به ایندکس دارند را مشاهده کردیم ، ولی آیا همهی این ایندکس های پیشنهادی بعد از ایجاد تایثیری بر روی سرعت و پرفورمنس ما دارد ؟ این مورد با توجه به بیزینس کار شما تعیین می شود ، ممکن است جدولی در انتهای هر روز توسط یک جاب Truncate شده و دوباره چند ساعت بعد توسط جاب دیگری میلیون ها رکورد در آن Bulk Insert شود و شما بعد از Heap شدن جدول اقدام به تهیه لیست جداولی که نیاز به ایندکس دارند بکنید ، خب قطعا با ایندکس گذاری بر روی این جدول عملا فقط سرباری بر روی سرور گذاشته اید و در انتها دیتا پاک شده و مجددا غیر مرتب ریخته شده است . پس بهتر است بعد از گذاشتن ایندکس حتما بررسی های لازمه انجام شود چون ایجاد ایندکس زیادی هم سربار زیادی از نظر مصرف منابع دارند. با اسکریپت زیر می توانید میزان Avg Percentage benefit قابل دستیابی بعد از ایجاد ایندکسی را مشاهده کنید که آیا بعد از ایجاد ایندکس تغییر محسوسی در سرعت واکشی دیتا از این جدول مشاهده می شود یا خیر .
SELECT mig.index_group_handle,
mid.index_handle,
migs.avg_total_user_cost AS AvgTotalUserCostThatCouldbeReduced,
migs.avg_user_impact AS AvgPercentageBenefit,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle)
+ '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'')
+ CASE
WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns
IS NOT NULL THEN ','
ELSE ''
END
+ ISNULL (mid.inequality_columns, '') + ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS
create_index_statement
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
تشخیص نیاز به ایندکس برای جدول مسئله ای تقریبا حساسی بوده که می تواند نتایج بسیار متفاوتی برای ما ایجاد کند ، معمولا برنامه نویسان در زمان ایجاد برنامه پیشنهاد های انجین sql را قبول کرده و ایندکس های پیشنهادی را بی درنگ ایجاد میکنند (با نام گذاری های عجیب غریب ) و متاسفانه در اکثر مواقع این ایندکس ها نفع که هیچ ضرر هم داشته پس بهتر است ایجاد ایندکس و تشخیص نیاز به آن توسط شخصی متخصص انجام شود .
شناسایی کمبود و نیاز به ایندکس گذاری در SQL Server میتواند به بهبود عملکرد و سرعت اجرای کوئریها کمک کند. در زیر، روشهایی برای تشخیص کمبود ایندکس و نیاز به ایندکس گذاری در SQL Server آورده شده است:
1. استفاده از ابزارهای مدیریت و نظارت: SQL Server ابزارهایی مانند Query Store و Database Engine Tuning Advisor را برای تشخیص و پیشنهاد ایندکسهای مورد نیاز در اختیار شما قرار میدهد. با استفاده از این ابزارها، میتوانید کوئریهایی را شناسایی کنید که به علت کمبود ایندکس، عملکرد ضعیفی دارند.
2. مطالعه و تحلیل کوئریها: با مطالعه و تحلیل کوئریهای اجرا شده در SQL Server، میتوانید الگوها و الزامات مورد نیاز برای ایندکس گذاری را شناسایی کنید. بررسی اجرای کوئریها، استفاده از نحوهنویسی مناسب، توضیح دادن جداول و فیلدها، و تحلیل برنامه اجرا شده میتواند به شما کمک کند تا نیاز به ایندکسهای جدید را تشخیص دهید.
3. استفاده از نمایهها و آمارها: SQL Server از نمایهها و آمارهای جدول استفاده میکند تا بهینهسازی اجرای کوئریها را انجام دهد. با بررسی و بروزرسانی نمایهها و آمارهای جداول، میتوانید نقاط ضعف و نیاز به ایندکسهای جدید را شناسایی کنید.
4. مطالعه و آنالیز خطوط عملکرد: در SQL Server، میتوانید خطوط عملکرد (Execution Plans) کوئریها را مورد بررسی و آنالیز قرار دهید.
با مطالعه خطوط عملکرد، میتوانید الگوها و عملکرد نامطلوب را شناسایی کنید و نیاز به ایندکسهای جدید را مشخص کنید.
5. استفاده از Dynamic Management Views (DMVs): SQL Server از DMVs برای ارائه اطلاعات مربوط به عملکرد سرور و دیتابیس استفاده میکند. با استفاده از DMVs، میتوانید اطلاعات مربوط به استفاده از ایندکسها، تعداد اسکنها و استفاده از حافظه را بررسی و نیاز به ایندکس گذاری را تشخیص دهید.
6. استفاده از SQL Server Profiler: ابزار SQL Server Profiler به شما اجازه میدهد تا کوئریهایی که در سرور اجرا میشوند را ضبط و تحلیل کنید. با استفاده از Profiler، میتوانید نیاز به ایندکس گذاری را با توجه به زمان اجرا و تعداد دسترسیها به جداول تشخیص دهید.
7. بررسی اجرای کوئریها در SQL Server Management Studio: با استفاده از SQL Server Management Studio (SSMS)، میتوانید کوئریهای خود را اجرا کرده و خطوط عملکرد (Execution Plans) آنها را بررسی کنید. با مطالعه و آنالیز خطوط عملکرد، میتوانید نقاط ضعف و نیاز به ایندکسهای جدید را تشخیص دهید.
8. استفاده از ابزارهای تحلیل عملکرد سرور: SQL Server ابزارهایی مانند SQL Server Profiler، Extended Events و Query Store را برای تحلیل و نظارت بر عملکرد سرور ارائه میدهد. با استفاده از این ابزارها، میتوانید عملکرد کوئریها و استفاده از ایندکسها را مشاهده و نیاز به ایندکس گذاری را تشخیص دهید.
9. استفاده از راهنمای ایندکس گذاری: SQL Server ابزارهایی مانند Database Engine Tuning Advisor و Missing Index DMVs را برای راهنمایی در مورد نیاز به ایجاد ایندکسهای جدید ارائه میدهد. با استفاده از این ابزارها، میتوانید پیشنهادات و راهنماییهای مربوط به ایندکس گذاری را دریافت کنید.
با استفاده از روشهای فوق، میتوانید کمبود و نیاز به ایندکس گذاری در SQL Server را شناسایی کرده و بهبود عملکرد و سرعت سرور خود را تحقق بخشید. البته، باید توجه داشته باشید که تصمیم نهایی در مورد ایجاد ایندکسهای جدید باید با توجه به نیازهای خاص سیستم و شرایط موجود گرفته شود.
با استفاده از روشهای فوق، میتوانید کمبود و نیاز به ایندکس گذاری در SQL Server را شناسایی کنید و با ایجاد ایندکسهای مناسب، عملکرد و سرعت سرور را بهبود بخشید.
دیدگاه ها 1