SQL Server از کدام ایندکس برای شمارش همه ردیف ها استفاده می کند؟

SQL Server از کدام ایندکس برای شمارش همه ردیف ها استفاده می کند؟

نوشته شده توسط: تورج عزیزی
۰۷ آذر ۱۳۹۴
زمان مطالعه: 5 دقیقه
۰
(۰)

مقدمه

در این مقاله عملگرد SQL Server در اجرای کوئری پرکاربرد زیر توضیح داده می شود.

SELECT COUNT (*) FROM mytable

یک سوال در مورد کوئری بالا می تواند این باشد که آیا SQL Server همیشه برای بدست آوردن تعداد رکوردها از table scan استفاده می کند یا خیر؟
جواب خیر است. Query Processor از ایندکس با حداقل تعداد page برای شمارش استفاده می کند تا I/O کمتری زده شود.
اجازه دهید این موضوع را آزمایش کنیم.

CREATE TABLE CTest (c1 INT IDENTITY, c2 BIGINT DEFAULT 1, c3 CHAR (1000) DEFAULT ‘a’);
GO
SET NOCOUNT ON;
GO
INSERT INTO CTest DEFAULT VALUES;
GO 10000

قبل از اجراین کوئری دکمه Include Actual Query Plan را غیرفعال کنید:
در غیر اینصورت ۱۰۰۰۰ پلن گرافیکی در SSMS تولید می شود و پیام زیر صادر می شود:

The query has exceeded the maximum number of result sets that can be displayed in the Execution Plan pane. Only the first 250 result sets are displayed in the Execution Plan pane.

حالا اگر SELECT COUNT(*) بگیریم، پلن حاصل به این شکل خواهد بود:
Query Processor راهی جز انتخاب Table Scan ندارد. حالا من یک ایندکس Nonclustered اضافه می کنم که دارای page های کمتری نسبت به خود جدول است:

CREATE NONCLUSTERED INDEX CTest_1 ON CTest (c2);
GO

و پلن SELECT به شکل زیر است:

توجه کنید که عملگر table scan به Index Scan روی ایندکس CTest_1 تغییر کرده است. به دلیل اینکه ایندکس دارای Page های کمتری نسبت به جدول است و بنابراین هزینه I/O کمتر است.
حالا من یک ایندکس ایجا می کنم که از ایندکس CTest_1 هم کوچکتر است، یعنی روی ستونی با دیتاتایپ integer:

CREATE NONCLUSTERED INDEX CTest_2 ON CTest (c1);
GO

و دوباره پلن به پلن با ایندکس کوچکتر تغییر می کند:

و همانطوری که انتظارش را داشتم دوباره تغییر کرد.
حالا اجازه دهید به تعداد page های هر ایندکس نگاهی بیندازیم:

SELECT [index_id], [page_count]
FROM sys.dm_db_index_physical_stats (DB_ID (), OBJECT_ID (‘CTest’), NULL, NULL, ‘LIMITED’);
GO

index_id page_count
———– ——————–
۰ ۱۴۳۶
۲ ۲۸
۳ ۱۹

هر باری که Query Processor اقدام به انتخاب یک ایندکس می کند یکی از عواملی که در انتخابش دخیل است هزینه I/O است.
و به یاد داشته باشید که کوچکرین ایندکس برای این منظور ایجاد ایندکس Nonclustered روی ستونی است که روی آن ایندکس Clustered ایجاد شده و البته موارد کارایی آن هم به مراتب کمتر از سایر ایندکس ها خواهد بود.
امیدوارم مفید بوده باشد.

چه رتبه ای می‌دهید؟

میانگین ۰ / ۵. از مجموع ۰

اولین نفر باش

title sign
دانلود مقاله
SQL Server از کدام ایندکس برای شمارش همه ردیف ها استفاده می کند؟
فرمت PDF
12 صفحه
حجم 11 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
تورج عزیزی
مقالات
17 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
تورج عزیزی
پروفایل نویسنده
title sign
دیدگاه کاربران

    •    عالی بود

    • این نوع ایندکس ها برای تست و آزمایش ساخته می شود تا در صورتیکه مفید واقع بود بتوان از آن در پروژه استفاده کرد.

      عملکرد این نوع ایندکس بدین حالت است که شما ایندکس خود را با With Statistics_only ایجاد می کنید تا به عنوان Hypothetical Indexدر نظر گرفته شود و پس از آن برای تست اثر گذاری کوئری خود را در مد Auto Pilot اجرا می کنید …
      لازم به ذکر است استفاده از این نوع ایندکس به ازای Session ایجاد کننده می باشد. همچنین برنامه Database Tuning Advisor پشت قضیه از این مبحث استفاده می کند.

      دوم اینکه) استفاده از ایندکس های (Hypothetical Index) در موضوع مورد بحث این مقاله می تونه اهمیت خودش رو نشون بده؟ در صورت امکان با یک مثال ساده توضیح دهید.
      پاسخ این سوال در جلسه نهم دوره Performance  بررسی شده است. در ضمن پرینت اسلایدها و… مربوط به اون مربوط به جلسه ۸ است. به علت کمبود وقت در جلسه ۸ بررسی نشده است اما پرینت مثال های آ« در جزوه جلسه ۸ وجود دارد
      ضمنا یکی دیگر از مواردی که برای تست و آزمایش و… در مبحث Statistics استفاده می شود .
      به روز رسانی Statisticsها با Row Count و Page Count غیر واقعی است (این مورد هم در طی دوره Performance بررسی شده برای کسب اطلاعات بیشتر در این خصوص منتظر سایر جلسات دوره باشید )
      برای کسب اطلاعات بیشتر نیز می توانید به لینک زیر مراجعه کنید
    • این نوع ایندکس ها برای تست و آزمایش ساخته می شود تا در صورتیکه مفید واقع بود بتوان از آن در پروژه استفاده کرد.

      عملکرد این نوع ایندکس بدین حالت است که شما ایندکس خود را با With Statistics_only ایجاد می کنید تا به عنوان Hypothetical Indexدر نظر گرفته شود و پس از آن برای تست اثر گذاری کوئری خود را در مد Auto Pilot اجرا می کنید …
      لازم به ذکر است استفاده از این نوع ایندکس به ازای Session ایجاد کننده می باشد. همچنین برنامه Database Tuning Advisor پشت قضیه از این مبحث استفاده می کند.

      دوم اینکه) استفاده از ایندکس های (Hypothetical Index) در موضوع مورد بحث این مقاله می تونه اهمیت خودش رو نشون بده؟ در صورت امکان با یک مثال ساده توضیح دهید.
      پاسخ این سوال در جلسه نهم دوره Performance  بررسی شده است. در ضمن پرینت اسلایدها و… مربوط به اون مربوط به جلسه ۸ است. به علت کمبود وقت در جلسه ۸ بررسی نشده است اما پرینت مثال های آ« در جزوه جلسه ۸ وجود دارد
      ضمنا یکی دیگر از مواردی که برای تست و آزمایش و… در مبحث Statistics استفاده می شود .
      به روز رسانی Statisticsها با Row Count و Page Count غیر واقعی است (این مورد هم در طی دوره Performance بررسی شده برای کسب اطلاعات بیشتر در این خصوص منتظر سایر جلسات دوره باشید )
      برای کسب اطلاعات بیشتر نیز می توانید به لینک زیر مراجعه کنید
    • سلام

      دوتا سوال از خدمت تون دارم:
      اول اینکه) بایدها ونبایدهای استفاده از ایندکس های (Hypothetical Index) چیه؟
      دوم اینکه) استفاده از ایندکس های (Hypothetical Index) در موضوع مورد بحث این مقاله می تونه اهمیت خودش رو نشون بده؟ در صورت امکان با یک مثال ساده توضیح دهید.
      با تشکر.
    •     سلام

      مطلبی که فرمودید درسته.
    • با سلام

      ممنون از بابت مقاله خوبتون
      جسارتا
      اگر این نوع کوئری رو هم به مقاله اضافه کنید فکر کنم مقاله کاملتری داشته باشیم
      SELECT  COUNT(fieldname)  FROM  tablename
      در این کوئری در صورتی که ایندکسی بر روی ستون fieldname وجود نداشته باشد، باز هم تمامی جدول بررسی خواهد شد(Table Scan)
      •     سلام

        اگر جدول شما Heap باشد و شما روی این جدول Heap یک ایندکس Non Clustered به ازای فیلد fieldname ایجاد کنید در صورتیکه Select شما مشابه کوئری زیر باشد
        SELECT  COUNT(fieldname)  FROM  tablename
        فرآیند Non Clustered Index Scan اتفاق خواهد افتاد که هزینه آن به مراتب از Table Scan کمتر است …. 
        البته بررسی یک مثال خیلی ساده برای این موضوع کافی نیست و…
    •     درود بر تورج

    • سلام
      بسیار عالی و کاربردی بود
      متشکرم

ثبت نام رایگان در همایش Tehran .NET Conf 2023 ، همین الان کلیک کنید
ثبت نام رایگان..
close-image