پلن‌های اجرای کوئری SQL Server درک و بهینه‌سازی

تست سریع درک پلن‌های اجرای کوئری SQL Server: سه سوال کلیدی

این آزمون سریع برای ارزیابی درک شما از پلن‌های اجرای کوئری در SQL Server طراحی شده است. سه کوئری زیر را به دقت بررسی کرده و برای هر کدام، اطلاعاتی درباره پلن اجرایی آن‌ها ارائه دهید. پاسخ‌ها در ادامه آورده شده‌اند (لطفاً قبل از فکر کردن، نگاه نکنید!).

ابتدا، این جدول را ایجاد کرده و تعدادی داده به آن اضافه کنید تا محیط لازم برای بررسی پلن‌های اجرای کوئری در SQL Server فراهم شود:

USE tempdb;
GO
CREATE TABLE dbo.Test (
    ID INT IDENTITY PRIMARY KEY,
    Value VARCHAR(100)
);
GO
INSERT dbo.Test (Value)
SELECT TOP (1000) NEWID()
FROM sys.all_columns a, sys.all_columns b;
GO

حال، برای کوئری زیر، نوع اپراتوری که برای بازیابی داده‌ها از جدول استفاده خواهد شد را مشخص کنید. در نظر داشته باشید که بهینه‌سازی کوئری برای سرعت بخشیدن به بازیابی داده‌ها چقدر اهمیت دارد:

SELECT ID FROM dbo.Test WHERE ID = 500;

(به عنوان مثال: ‘clustered index seek’, ‘clustered index scan’, ‘table scan’ و غیره)

در ادامه، برای کوئری زیر، چه نوع اپراتور Join (الحاق) مورد استفاده قرار خواهد گرفت؟ درک اپراتورهای Join برای بهینه‌سازی کوئری‌های پیچیده و افزایش عملکرد SQL Server بسیار مهم است:

SELECT a.ID FROM dbo.Test a JOIN dbo.Test b ON a.ID = b.ID + 1;

(به عنوان مثال: ‘hash match’, ‘nested loops’, ‘merge join’ و غیره)

در نهایت، برای این کوئری، مقدار خصوصیت “RowCount” اپراتور “Clustered Index Scan” چه خواهد بود؟ این مقدار نشان‌دهنده تعداد سطرهایی است که اپراتور پایه باید پردازش کند:

SELECT ID, Value FROM dbo.Test WHERE Value LIKE 'A%';

(به عنوان مثال: اگر کوئری ۵۰ سطر را اسکن کند، پاسخ “۵۰” خواهد بود)

پاسخ‌ها در ادامه آمده‌اند:

برای سوال اول، پاسخ ‘Clustered Index Seek’ است. دلیل آن این است که ستون ID کلید اصلی کلاستر شده (clustered primary key) است و شما در حال انجام یک جستجوی برابری (equality search) روی آن ستون هستید. این یکی از کارآمدترین روش‌ها برای بازیابی داده‌ها در SQL Server است و نشان‌دهنده بهینه‌سازی عالی پلن‌های اجرای کوئری است.

در اینجا می‌توانید بخشی از پلن اجرایی را مشاهده کنید که اپراتور Clustered Index Seek را نشان می‌دهد:

<RelOp NodeId="0" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
    <OutputList>
        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Column="ID" />
    </OutputList>
    <IndexScan Lookup="0" Ordered="1" ScanDirection="FORWARD" ForcedHint="0" NoExpandHint="0" Storage="RowStore">
        <DefinedValues>
            <DefinedValue>
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Column="ID" />
            </DefinedValue>
        </DefinedValues>
        <Object Database="[tempdb]" Schema="[dbo]" Table="[Test]" Index="[PK__Test__3214EC27154B642C]" />
        <SeekPredicates>
            <SeekPredicateNew>
                <SeekKeys>
                    <StartRange ScanType="EQ">
                        <ScalarOperator ScalarString="[@1]">
                            <Identifier>
                                <ColumnReference Column="@1" />
                            </Identifier>
                        </ScalarOperator>
                    </StartRange>
                </SeekKeys>
            </SeekPredicateNew>
        </SeekPredicates>
    </IndexScan>
</RelOp>

اپراتور Clustered Index Seek با فرض وجود کلید اصلی (primary key) روی ستون ID، دقیقاً یک سطر را پیدا می‌کند. این یک عملیات بسیار کارآمد است، زیرا SQL Server می‌تواند مستقیماً به صفحه داده‌ای که سطر با ID = 500 در آن قرار دارد، هدایت شود، به جای اینکه چندین صفحه را اسکن کند. این روش بهینه‌سازی کوئری در SQL Server را به خوبی نشان می‌دهد و عملکرد آن را بهبود می‌بخشد.

برای سوال دوم، پاسخ ‘Nested Loops Join’ است. یک Nested Loops Join زمانی رخ می‌دهد که SQL Server دو ورودی برای Join کردن داشته باشد: یکی کوچک (ورودی بیرونی) و دیگری بزرگ‌تر (ورودی داخلی). این اپراتور هر سطر را از ورودی بیرونی می‌گیرد و به دنبال سطرهای مطابق در ورودی داخلی می‌گردد. در این حالت، از آنجایی که یک clustered index روی ستون ID وجود دارد، می‌تواند به طور کارآمد به ورودی داخلی (ارجاع دوم به dbo.Test) برای هر سطر از ورودی بیرونی (ارجاع اول به dbo.Test) دسترسی پیدا کند. این نوع Join معمولاً برای Joinهای با حجم کم و ایندکس‌های مناسب، عملکرد خوبی دارد.

اینجا بخشی از پلن اجرایی است که اپراتور Nested Loops Join را به همراه ورودی‌های آن نشان می‌دهد:

<RelOp NodeId="3" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="999" EstimateIO="0.003125" EstimateCPU="0.0131114" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
    <OutputList>
        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Alias="[a]" Column="ID" />
    </OutputList>
    <NestedLoops Optimized="0">
        <SetPredicate>
            <ScalarOperator ScalarString="[tempdb].[dbo].[Test].[ID] as [a].[ID]=[tempdb].[dbo].[Test].[ID] as [b].[ID]+(1)">
                <Compare CompareOp="EQ">
                    <ScalarOperator>
                        <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Alias="[a]" Column="ID" />
                        </Identifier>
                    </ScalarOperator>
                    <ScalarOperator>
                        <Arithmetic Operation="ADD">
                            <ScalarOperator>
                                <Identifier>
                                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Alias="[b]" Column="ID" />
                                </Identifier>
                            </ScalarOperator>
                            <ScalarOperator>
                                <ConstWithCast CastType="int" CastLength="4" PreCompiledValue="(1)">
                                    <ScalarOperator>
                                        <Const ConstType="int" ConstValue="1" />
                                    </ScalarOperator>
                                </ConstWithCast>
                            </ScalarOperator>
                        </Arithmetic>
                    </ScalarOperator>
                </Compare>
            </ScalarOperator>
        </SetPredicate>
    </NestedLoops>
    <InputList>
        <RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1000" EstimateIO="0.003125" EstimateCPU="0.0001581" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
            <OutputList>
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Alias="[a]" Column="ID" />
            </OutputList>
            <IndexScan Lookup="0" Ordered="1" ScanDirection="FORWARD" ForcedHint="0" NoExpandHint="0" Storage="RowStore">
                <DefinedValues>
                    <DefinedValue>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Alias="[a]" Column="ID" />
                    </DefinedValue>
                </DefinedValues>
                <Object Database="[tempdb]" Schema="[dbo]" Table="[Test]" Alias="[a]" Index="[PK__Test__3214EC27154B642C]" />
            </IndexScan>
        </RelOp>
        
            
                
            
            
                
                    
                        
                    
                
                
                
                    
                        
                            
                                
                                    
                                        
                                            
                                                
                                            
                                        
                                        
                                            
                                                
                                                    
                                                
                                            
                                        
                                    
                                
                            
                        
                    
                
            
        
    

XML برای Nested Loops Join می‌تواند بسیار طولانی باشد زیرا شامل جزئیات هر دو ورودی بیرونی و داخلی است. نکته کلیدی در اینجا این است که چگونه SQL Server از clustered index روی ستون ID برای پیدا کردن کارآمد سطرهای منطبق برای شرط Join یعنی a.ID = b.ID + 1 استفاده می‌کند. این یک نمونه عالی از بهینه‌سازی پلن‌های اجرای کوئری است.

برای سوال سوم، پاسخ ‘۱۰۰۰’ است. یک Clustered Index Scan زمانی رخ می‌دهد که SQL Server نیاز به خواندن تمام سطرها در clustered index داشته باشد (که اگر از non-clustered index دیگری استفاده نشده باشد، کل جدول را شامل می‌شود). حتی با وجود یک عبارت WHERE، از آنجایی که این عبارت روی ستون Value است (که هیچ ایندکسی ندارد) و از اپراتور LIKE 'A%' استفاده می‌کند، SQL Server باید تمام سطرها را اسکن کند تا موارد منطبق را پیدا کند. بهینه‌ساز نمی‌تواند از یک index seek در اینجا استفاده کند، زیرا ستون Value ایندکس نشده است و اپراتور LIKE با یک wildcard در ابتدا، حتی در صورت وجود ایندکس روی Value، از یک index seek کارآمد جلوگیری می‌کند. این وضعیت بهینه‌سازی کوئری در SQL Server را به چالش می‌کشد.

در اینجا بخشی از پلن اجرایی را مشاهده می‌کنید که جزئیات Clustered Index Scan را نشان می‌دهد:

<RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="100" EstimateIO="0.003125" EstimateCPU="0.000329" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
    <OutputList>
        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Column="ID" />
        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Column="Value" />
    </OutputList>
    <IndexScan Lookup="0" Ordered="0" ScanDirection="FORWARD" ForcedHint="0" NoExpandHint="0" Storage="RowStore">
        <DefinedValues>
            <DefinedValue>
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Column="ID" />
            </DefinedValue>
            <DefinedValue>
                <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Column="Value" />
            </DefinedValue>
        </DefinedValues>
        <Object Database="[tempdb]" Schema="[dbo]" Table="[Test]" Index="[PK__Test__3214EC27154B642C]" />
        <Predicate>
            <ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(100), [tempdb].[dbo].[Test].[Value], 0) like N'A%'">
                <Compare CompareOp="LIKE">
                    <ScalarOperator>
                        <Convert DataType="varchar" Length="100" Style="0">
                            <ScalarOperator>
                                <Identifier>
                                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[Test]" Column="Value" />
                                </Identifier>
                            </ScalarOperator>
                        </Convert>
                    </ScalarOperator>
                    <ScalarOperator>
                        <Const ConstType="nvarchar" ConstValue="N'A%'" />
                    </ScalarOperator>
                </Compare>
            </ScalarOperator>
        </Predicate>
    </IndexScan>
</RelOp>

توجه داشته باشید که حتی اگر تعداد سطرها (EstimateRows) تخمینی ۱۰۰ باشد، خصوصیت “RowCount” واقعی برای Clustered Index Scan (اپراتور پایه که داده‌ها را می‌خواند) همیشه ۱۰۰۰ خواهد بود، زیرا برای یافتن سطرهایی که با شرط ‘A%’ مطابقت دارند، باید تمام سطرها را بخواند. اگر این فیلتر پس از اسکن اعمال می‌شد، تعداد سطرهای بازگردانده شده ممکن بود کمتر از ۱۰۰۰ باشد، اما خود اسکن همچنان تمام ۱۰۰۰ سطر را پردازش می‌کند. این نکته، تمایز مهمی بین سطرهای تخمینی و سطرهای واقعی پردازش شده توسط یک اپراتور در پلن‌های اجرای کوئری SQL Server را برجسته می‌کند و برای درک دقیق عملکرد SQL و بهینه‌سازی آن حیاتی است.