اطلاعات آماری ایندکس ها

آموزش پیشرفته SQL Server (جلسه پنجم) - اطلاعات آماری ایندکس ها

اطلاعات آماری ایندکس ها، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی اطلاعات آماری ایندکس ها، قسمت های قبلی را مطالعه فرمایید.

اطلاعات آماری ایندکس ها - بررسی DMV و DMF های مشهور مدیریت ایندکس ها

برای بدست آوردن اطلاعات آماری ایندکس ها، می توان از DMV و DMF های سیستمی استفاده نمود.

DMV سرنام واژگان Dynamic Management View می باشد.

DMF سرنام واژگان Dynamic Management Function می باشد.

DMV و DMF های سیستمی، از SQL Server 2005 به بعد اضافه شده است و یک سری اطلاعات سیستمی در مورد Objectهای مختلف بانک اطلاعاتی در اختیار شما قرار می دهند.

در اینجا به بررسی سه مورد زیر خواهیم پرداخت:

1. sys.dm_db_index_physical_stats
2. sys.dm_db_index_operational_stats
3. sys.dm_db_index_usage_stats

1- بررسی sys.dm_db_index_physical_stats

sys.dm_db_index_physical_stats جزء DMF های سیستمی محسوب می شود.

با استفاده از dm_db_index_physical_stats می توان اطلاعات زیر را در مورد ایندکس ها بدست آورد:

•  اطلاعات آماری درباره تعداد Pageهای اختصاص داده شده به یک ایندکس
• استخراج اطلاعات آماری درباره وضعیت Fragmentation
• نمایش آمار صفحات فشرده شده
• نمایش اطلاعات آماری به تفکیک هر سطح ایندکس

ساختار sys.dm_db_index_physical_stats:

sys.dm_db_index_physical_stats ( 
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

برای کسب اطلاعات بیشتر در مورد پارامترهای DMF بالا، به لینک روبرو مراجعه فرمایید: sys.dm_db_index_physical_stats

توجه: در اسکریبت بالا، گزینه ی آخر یعنی mode با یکی از سه مقدار زیر تنظیم می شود:

• LIMITED
• SAMPLED
• DETAILED

در واقع گزینه mode، حالت یا مُد اسکن کردن سطح های ایندکس را مشخص می کند. بعنوان مثال مقدار SAMPLED باعث می شود تا DMF مذکور، از تعدادی صفحات سطح Leaf Level نمونه برداری انجام دهد و خروجی را برگرداند ولی مقدار DETAILED بر اساس تمام سطوح ایندکس، و مقدار LIMITED، فقط براساس سطح Leaf Level خروجی را برمی گرداند.

اگر تعداد Pageهای سطح Leaf Level ایندکس، کوچکتر از 10.000 باشد مقدار SAMPLED براساس تمام Page ها، خروجی خواهد داد و اگر بزرگتر از 10.000 باشد نمونه برداری بر اساس 1% Page ها انجام خواهد شد.

بررسی خروجی sys.dm_db_index_physical_stats

خروجی sys.dm_db_index_physical_stats را به سه بخش مختلف می توان تقسیم کرد:

1. بخشی از خروجی مربوط به Header Statistics است.
2. بخشی از خروجی مربوط به آمار ردیف ها است.
3. بخشی از خروجی مربوط به آمار مشکلات Fragmentation است.

مثال: در مثال زیر ابتدا دو ایندکس مختلف روی جدول Orders2 ایجاد کرده ایم:

CREATE CLUSTERED INDEX IX_Clustered ON Orders2(OrderID)
CREATE NONCLUSTERED INDEX IX_OrderDate ON Orders2(OrderDate)

در ادامه تصور فرمایید عملیات های مختلف Insert / Update /Delete روی جدول Orders2 اتفاق افتاده است، حالا با استفاده از DMF سیستمی sys.dm_db_index_physical_stats وضعیت ایندکس های Order2 را چک می کنیم:

SELECT * FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID('Orders2'),
    NULL,
    NULL,
    'LIMITED'
)

توضیح:

پارامتر اول: تابع ()DB_ID شناسه یا ID پایگاه داده ی جاری را برمی گرداند.

پارامتر دوم: تابع ()OBJECT_ID، شناسه جدول Orders2 را برمی گرداند.

پارامتر سوم: اگر با NULL تنظیم شود، اطلاعات آماری به ازای تمام ایندکس های جدول Orders2 را برمی گرداند.

پارامتر چهارم: اگر با NULL تنظیم شود، اطلاعات آماری به ازای تمام پارتیشن های جدول Orders2 را برمی گرداند.

پارمتر پنجم: چون با LIMITED تنظیم شده است، اطلاعات آماری، فقط بر اساس سطح Leaf Level نمایش داده خواهد شد.

اجرای sys.dm_db_index_physical_stats، از طریق کنسول SQL Server:

DMF بالا را بصورت ویژوال و از طریق کنسول SQL Server نیز می توان اجرا نمود. فقط کافی است از پنجره Object Explorer روی پایگاه داده مورد نظرتان راست کلیک کرده و مطابق شکل زیر گزینه Index Physical Statistics را انتخاب نمایید:

2- بررسی sys.dm_db_index_operational_stats

sys.dm_db_index_operational_stats جزء DMF های سیستمی محسوب می شود.

با استفاده از sys.dm_db_index_operational_stats می توان اطلاعات زیر را در مورد ایندکس ها بدست آورد:

• استخراج اطلاعات آماری فعالیت های Low Level ایندکس (تعداد Lock - تعداد I/O - مدت زمان Wait و ...)
• استخراج فعالیت های DML یا هر چیزی که منجر به Insert / Update / Delete شود.

ساختار sys.dm_db_index_operational_stats:

sys.dm_db_index_operational_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | 0 | NULL | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
)

برای کسب اطلاعات بیشتر در مورد پارامترهای DMF بالا، به لینک روبرو مراجعه فرمایید: sys.dm_db_index_operational_stats

3- بررسی sys.dm_db_index_usage_stats

sys.dm_db_index_usage_stats جزء DMV های سیستمی محسوب می شود.

با استفاده از sys.dm_db_index_usage_stats می توان اطلاعات زیر را در مورد ایندکس ها بدست آورد:

• مشخص کردن میزان فعالیت روی ایندکس
• مشخص کردن تعداد عملیات Index Seek
• مشخص کردن تعداد عملیات Index Scan
• مشخص کردن تعداد عملیات LookUp

ساختار sys.dm_db_index_usage_stats:

sys.dm_db_index_usage_stats

برای کسب اطلاعات بیشتر در مورد پارامترهای DMF بالا، به لینک روبرو مراجعه فرمایید: sys.dm_db_index_usage_stats

در ادامه آموزش اطلاعات آماری ایندکس ها  خواهید دید ...!

با خرید جلسه 5 از بسته آموزشی "افزایش کارایی پایگاه داده" موارد زیر را خواهید دید:

1. توضیح کامل در مورد خروجی dm_db_index_physical_stats
2. ذکر چندین مثال از نحوه ی استفاده از dm_db_index_physical_stats
3. بررسی کامل خروجی sys.dm_db_index_operational_stats و sys.dm_db_index_usage_stats

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 5
• نام فایل ویدئو: 04
• فرمت فایل: mp4.

نقطه شروع بحث بالا (اطلاعات آماری ایندکس ها) در ویدئو:  06:20

برای خرید و دانلود کامل آموزش پیشرفته SQL Server کلیک کنید.

• قبلی