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

مقدمه

در این مقاله عملگرد 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 ایجاد شده و البته موارد کارایی آن هم به مراتب کمتر از سایر ایندکس ها خواهد بود.
امیدوارم مفید بوده باشد.