Plan Cache در SQL Server چیست؟ آموزش کامل مدیریت Execution Plan و افزایش Performance

Plan Cache در SQL Server چیست ؟

یکی از موضوعات مهم در بهینه سازی عملکرد SQL Server ، نحوه مدیریت و استفاده مجدد از Execution Plan ها است . هر زمان SQL Server یک Query را دریافت میکند ، برای اجرای بهینه آن باید یک Execution Plan ایجاد یا از Plan موجود استفاده کند . فرآیند Optimization و تولید Execution Plan می تواند هزینه پردازشی داشته باشد . به همین دلیل SQL Server از سازکاری به نام Plan Cache استفاده می کند تا Planهای ایجاد شده را نگهداری کند و در اجرای مجدد Query ، در صورت امکان از همان Plan استفاده کند .

به زبان ساده :

Plan Cache محلی برای نگهداری Execution Plan های قابل استفاده مجدد در SQL Server است . هدف اصلی این قابلیت ، کاهش سربار ناشی از Compile و Optimization مجدد Query ها و در نتیجه بهبود Performance سیستم است . محتوای فایل نیز تاکید می کند که اصل اساسی Plan Cache ، استفاده مجدد از Execution Plan برای کاهش سربار Compilation است .

چرا Plan Cache اهمیت دارد ؟

فرض کنید یک Query بارها در یک سیستم اجرا می شود و هر بار دقیقا همان ساختار را دارد، اما SQL Server مجبور باشد در هربار اجرا دوباره آن را Compile و Optimize کند . در چنین شرایطی منابعی مانند CPU برای کاری مصرف می شوند که قبلا انجام شده است .

Plan Cache با ذخیره Execution Plan به SQL Server اجازه می دهد در اجرای بعدی ، در صورت مناسب بودن شرایط ، Plan موجود را دوباره استفاده کند .

مهمترین مزیت های Plan Cache عبارتند از :

    1. کاهش Compile شدن مکرر Query ها .

    2.کاهش سربار Optimization .

    3. افزایش امکان Reuse Plan .

    4. کاهش مصرف CPU در برخی Workload ها .

    5. جلوگیری از ایجاد تعداد زیادی Plan مشابه در Cache .

    6. بهبود Performance در Query های پر تکرار .

البته استفاده از Plan Cache به این معنا نیست که SQL Server همیشه باید یک Plan را برای تمام اجراها استفاده کند . تفاوت مقادیر ورودی و شرایط مختلف می تواند باعث ایجاد Execution Plan های متفاوت شود .

Execution Plan چیست ؟

execution Plan برنامه ای است که SQL Server برای اجرای Query انتخاب می کند .

برای مثال :

 ; SELECT  *  FROM Production.Product  WHERE  ProductID=416 

SQL Server باید تصمیم بگیرد داده ها را چگونه پیدا کند . برای این کار ممکن است از Index ، Scan یا روش های دیگر استفاده کند . این تصمیم در قالب Execution Plan نمایش داده می شود .

نکته مهم این است که ایجاد Execution Plan یک فرآیند رایگان نیست . SQL Server باید Query را بررسی و Optimize کند و سپس مناسب ترین روش اجرای آن را انتخاب کند . اگر Query بارها اجرا شود ، استفاده مجدد از Plan می تواند باعث کاهش این هزینه شود .

Plan Reuse چیست ؟

Plan Reuse یا استفاده مجدد از Plan یعنی SQL Server بتواند Execution Plan ایجاد شده برای یک Query را در اجرای بعدی دوباره استفاده کند .

فرض کنید Query زیر چندین بار اجرا شود :

; SELECT  *  FROM  Production.Product  WHERE  ProductID=461

اگر Query به شکلی نوشته شده باشد که SQL Server بتواند Plan آن را مجددا استفاده کند ، لازم نیست برای هر اجرای مشابه ، فرآیند Compile و Optimization را از ابتدا انجام دهد . اما اگر Query ها به شکل های مختلف و با ساختارهای متفاوت ارسال شوند ، ممکن است SQL Server مجبور شود Plan های بیشتری ایجاد و در Cache نگهداری کند . بنابراین یکی از اهداف مهم در مدیریت SQL Server این است که Reuse Plan تا حد مناسبی افزایش پیدا کند .

Query Hash و Query Plan Hash چیست ؟

یکی از ابزارهای مفید برای تحلیل Query های موجود در Plan Cache ، استفاده از مقادیر Hashe است .

دو مفهوم مهم در این زمینه عبارتند از :

    1. quey_hash

    2. quey_plan_hash

این دو مقدار می توانند در شناسایی Query های مشابه و Plan های مشابه کمک کنند .

Query Hash :

query_hash برای شناسایی ساختار منطقی Query کاربرد دارد .

نکته مهم این است که دو Query الزاما نباید از نظر متن کاملا یکسان باشند تا مقدار Hash مشابهی داشته باشند . برای مثال ، ممکن است دو Query تنها در بعضی قسمت ها تفاوت داشته باشند ، اما ساختار کلی آنها مشابه باشند .

Query Plan Hash :

query_paln_hash بیشتر برای شناسایی Execution Plan های مشابه کاربرد دارد . این قابلیت در زمان Performance Tuning بسیار ارزشمند است .

فرض کنید یک Query در چند نقطه مختلف برنامه استفاده شده و عملکرد آن ضعیف است . اگر چند Query مختلف دارای query_plan_hash یکسان باشند، می توان آنها را به عنوان مواردی که از یک Plan مشابه استفاده می کنند بررسی کرد . در نتیجه اگر یک راهکار برای بهبود Plan پیدا شود ، می توان سایر محل هایی را که همان Plan را استفاده می کنند نیز شناسایی کرد .

آیا Query Hash همیشه به معنی Plan یکسان است ؟

خیر .

یکی از نکات مهم در تحلیل Plan Cache این است که ممکن است Query Hash یکسان باشد اما Query Plan Hash متفاوت باشد .

فایل نمونه ای از دو Query تقریبا مشابه را نشان می دهد که فقط مقدار ProductID آنها متفاوت است :

,SELECT p.Name

     , tha.TransactionDate     

, tha.TransactionType     

 ,tha.Quantity     

       tha.ActualCost     

FROM Production.TransactionHistoryArchive AS tha

JOIN Production.Product AS p

    ON tha.ProductID = p.ProductID     

;WHERE p.ProductID = 461

و :

  , SELECT p.Name

  , tha.TransactionDate     

, tha.TransactionType     

, tha.Quantity     

       tha.ActualCost     

FROM Production.TransactionHistoryArchive AS tha

JOIN Production.Product AS p

    ON tha.ProductID = p.ProductID     

 ;WHERE p.ProductID = 712

در این مثال ، تفاوت های جزئی متن Query برای تغییر مقدار Query Hash کافی نیستند ، اما مقادیر متفاوت ارسال شده در WHERE می توانند باعث ایجاد Execution Plan های متفاوت شوند . این موضوع نشان می دهد که هنگام Performance Tuning نباید تنها به متن Query نگاه کنیم .

یکی از مشکلات مهم Plan Cache :

یکی از موضوعات مهم در مدیریت Plan Cache ها ، Ad Hoc Query ها هستند .

Ad Hoc Query ها Query هایی هستند که به شکل مستقیم و بدون سازکار مناسب برای استفاده مجدد از Plan ارسال می شوند . وجود مقدار مشخصی Ad Hoc Query در بسیاری از سیستم ها اجتناب ناپذیر است ، اما فزایش بیش از حد آنها می تواند برای Plan Cache مشکل ایجاد کند .Ad Hoc Query ها در بسیاری از موارد از Reuse Plan بهره مناسبی نمی برند و می توانند باعث افزایش سربار Compile و همچنین ایجاد Cache Bloat شوند .

Cache Bloat چیست ؟

Cache Bloat زمانی رخ می دهد که Plan Cache با تعداد زیادی Plan پر شود ، به خصوص زمانی که تعداد زیادی Query مشابه اما از نظر متن متفاوت وارد Cache شوند .

برای مثال تصور کنید برنامه ای Query زیر را ارسال کند :

SELECT *

FROM Products

WHERE ProductID = 10;

سپس :

SELECT *

FROM Products

WHERE ProductID = 20;

و سپس :

SELECT *

FROM Products

WHERE ProductID = 30;

اگر این Query ها به شکلی ارسال شوند که SQL Server نتواند Plan را به درستی Reuse کند ، ممکن است Plan های متعددی در Cache ایجاد شوند . در سیستم های بزرگ ، این مسئله می تواند باعث مصرف غیر ضروری منابع Cache شود .

Parameterizetion چیست ؟

یکی از بهترین راهکارها برای افزایش Plan Reuse می باشد . در Parameterization ، قسمت های ثابت Query از مقادیری که در هر اجرا تغییر می کنند جدا می شوند و بجای اینکه Query برای هر مقدار جدید دوباره به شکل متفاوت ارسال شود ، می توان از parameter استفاده کرد .

به صورت مفهومی :

SELECT *

FROM Production.Product

WHERE ProductID = @ProductID;

در این حالت ساختار اصلی Query ثابت باقی می ماند و تنها مقدار Parameter تغییر می کند . توصیه می شود مقادیر موجود در Query به صورت صریح Parameterize شوند ؛ زیرا این روش می تواند میزان Reuse Plan را افزایش داده و تعداد Plan های موجود در Cache را کاهش دهد .

Forced Parameterization  و Simple Parameterization :

SQL Server روش هایی برای Parameterization دارد که از جمله آنها می توان به :

    1. Simple Parameterization 

    2. Forced Parameterization

اشاره کرد .

با این حال ، این روش ها محدودیت هایی دارند و همیشه پاسخ مناسب برای تمام Workload ها نیستند . به همین دلیل در شرایطی که کنترل بیشتری روی Query دارید ، Explicit Parameterization می تواند گزینه مناسبی باشد . 

Parameterization صریح می تواند در Workload ها به استفاده مجدد از Plan کمک کرده و تعداد Plan های موجود در Cache را کاهش دهد .

Stored Procedure و Plan Cache :

یکی از روش های مهم برای مدیریت Query ها و افزایش Reuse Plan ، استفاده از Stored Procedure است .

Stored Procedure می تواند Query و منطق مربوط به اجرای آن را در SQL Server نگهداری کند و پارامترهای لازم هنگام اجرا ارسال شوند .

به صورت ساده :

CREATE PROCEDURE GetProduct

    @ProductID INT

AS

BEGIN

    SELECT *

    FROM Production.Product

    WHERE ProductID = @ProductID;

END;

سپس :

EXEC GetProduct @ProductID = 461;

و در اجرای بعدی :

EXEC GetProduct @ProductID = 712;

ساختار کلی Query ثابت باقی می ماند و مقدار Parameter تغییر می کند .

مزایایی Store Procedure :

استفاده از Store Procedure در شرایط مناسب می تواند مزایایی داشته باشد :

    1. افزایش امکان Reuse Plan .

    2. کاهش حجم اطلاعات ارسالی در شبکه .

    3. نگهدار منطق Query در Database .

    4. کاهش نیاز به ارسال مکرر متن کامل Query .

در Store Procedure ، علاوه بر نام Procedure  ، فقط پارامترها باید ارسال شوند ؛ بنابر این در مقایسه با Ad Hoc Query می توان ترافیک شبکه کمتری داشت . همچنین Stored Proucedure ها می توانند از Plan Cache مجددا استفاده کند .

در شرایطی که Stored Procedure گزینه مناسبی نیست ، یکی از ابزارهای مهم SQL Server برای اجرای Query های SP_ executesql ، Parameterized است .

این روش به شما اجازه می دهد Query را همراه با Parameterها اجرا کنید .

یک نمونه ساده :

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'

SELECT *

FROM Production.Product

WHERE ProductID = @ProductID';

EXEC sp_executesql

    @SQL,

    N'@ProductID INT',

    @ProductID = 461;

در اجرای بعدی می توان مقدار دیگری ارسال کرد :

EXEC sp_executesql

    @SQL,

    N'@ProductID INT',

    @ProductID = 712

مزیت مهم این روش ، امکان Parameterize کردن Query است .

چرا استفاده از sp_executesql بهتر از EXECUTE برای Dynamic SQL است ؟

در Dynamic SQL ممکن است برنامه Query را به صورت یک String بسازد .

روش نا مناسب می تواند چیزی شبیه این یاشد :

 

EXEC(

    'SELECT *

     FROM Production.Product

     WHERE ProductID = ' + @ProductID

);

این روش علاوه بر مشکلات مربوط به Reuse Plan ، می تواند ریسک های امنیتی ایجاد کند ؛ بخصوص اگر داده ورودی بدون اعتبار سنجی و Parameterization مناسب وارد Query شود . در مقابل ، می توان از sp_executesql همراه با Parameter استفاده کرد .

توصیه می شود برای Dynamic Query ها بجای EXECUTE از sp_executesql استفاده شود و اشاره می کند که Parameterization در این روش احتمال SQL Injection را کاهش می دهد .

SQL Injection و Dynamic Query :

SQL Injection یکی از خطرات مهم در برنامه هایی است که Query را به صورت Dynamic و با اتصال مستقیم ورودی کاربر تولید می کنند .

برای مثال ، ترکیب مستقیم ورودی کاربر با String مربوط به Query می تواند خطرناک باشد . به همین دلیل بهتر است از Parameter استفاده شود .

EXEC sp_executesql

    N'SELECT *

      FROM Production.Product

      WHERE ProductID = @ID',

    N'@ID INT',

    @ID = @ProductID

در این روش مقدار داده از ساختار Query جدا می شود .

با این حال باید توجه داشت که Parameterization یک راهکار مهم است ، اما امنیت یک سیستم Database فقط به این موضوع محدود نمی شود .

Execute/Prepare چیست ؟

روش دیگری که برای مدیریت Plan ها معرفی می شود ، مدل Execute/Prepare است .

اگر برنامه ای Query های Dynamic ایجاد کند و آنها را از طریق sp_executesql روی شبکه ارسال کند ، در برخی شرایط می توان از Execute/Prepare استفاده کرد . در این مدل Query کامل می تواند یک بار ارسال شود و آماده شود و اجرای بعدی با استفاده از Plan آماده انجام شود . یکی از مزایای این مدل این است که تنها یکبار لازم است رشته کامل Query از طریق شبکه ارسال شود . همچنین با داشتن Plan Handle ، بیش از یک Connection User می تواند از Prepared Plan استفاده کند .

Optimize for Ad Hoc Workloads چیست ؟

همان طور که گفتیم ، Ad Hoc Query ها در برخی سیستم ها اجتناب ناپذیر هستند .اما اگر تعداد زیادی Ad Hoc Query داشته باشیم ، ممکن است Plan های زیادی وارد Cache شوند .

SQL Server گزینه ای به نام Optimize for Ad Hoc Workloads در اختیار مدیر Database قرار می دهد . با فعال کردن این گزینه ، Plan تنها زمانی به شکل کامل وارد Cache می شود که Query بیش از یک بار اجرا شده باشد . به بیان ساده ، این قابلیت می تواند برای محیط هایی که تعداد زیادی Ad Hoc Query دارند ، به کاهش مصرف غیر ضروری Plan Cache کمک کند .

بهترین روش ها برای مدیریت Plan Cache :

برای داشتن Plan Cache سالم تر و افزایش Reuse Plan ، چند توصیه مهم وجود دارد :

1. Query ها را Parameterize کنید .

تا حد امکان مقادیر متغیر را به Parameter تبدیل کنید ، این کار باعث می شود ساختار Query ثابت تر بماند و امکان Reuse Plan  افزایش پیدا می کند .

2. از Stored Procedure در شرایط مناسب استفاده کنید .

Stored Procedure یکی از روش های مناسب برای ایجاد Workload هایی است که قابلیت استفاده مجدد از Plan دارند . اما نباید همه منطق برنامه را بدون دلیل داخل Database منتقل کرد .

3. برای Dynamic SQL از sp_executesql استفاده کنید .

اگر Dynamin SQL لازم است ، استفاده از sp_executesql همراه با Parameter ها گزینه مناسب تری نسیت به ساختن رشته های نا امن با EXECUTE است .

4. تعداد Ad Hoc Query را کاهش دهید .

Ad Hoc Query ها در بسیاری از سیستم ها قابل حذف کامل نیستند ، اما بهتر است تعداد آنها تا حد امکان کنترل شود . وجود تعداد زیادی Plan کم استفاده می تواند باعث Cache Bloat شود .

5. Optimize for Ad Hoc Workload را بررسی کنید .

در سیستم هایی که تعداد زیادی Ad Hoc Query دارند ، فعال کردن این گزینه می تواند به مدیریت بهتر Cache کمک کند .البته فعال کردن آن باید بر اساس Workoload واقعی سیستم انجام شود .

6. Query Hash و Plan Hash را بررسی کنید .

هنگام Performance Tuning فقط به متن Query نگاه نکنید . با بررسی query_hash و query_plan_hash می توانید Query های مشابه و Plan های مشابه را بهتر شناسایی کنید . این موضوع برای پیدا کردن Query هایی که از یک Plan مشترک استفاده می کنند بسیار کاربردی است .

آیا یک Query همیشه یک Execution Plan دارد ؟

خیر !

یکی از نکات مهم در SQL Server این است که یک Query می تواند در شرایط مختلف Execution Plan های متفاوتی داشته باشد . برای مثال ، مقدار parameter یا داده های موجود در جدول ممکن است بر Plan انتخاب شده تاثیر بگذارد . 

Query های تقریبا یکسان با تغییر مقدار WHERE پلن های متفاوتی ایجاد می کند . بنابر این در زمان بررسی Performance علاوه بر متن Query باید Execution Plan واقعی را نیز بررسی کرد .

چگونه Plan Cache به بهینه سازی کمک میکند ؟

فرآیند کلی را می توان به شکل زیر خلاصه کرد :

Query ---> Compile/Optimize ---> Execution Plan ---> Plan Cache ---> اجرای مجدد ---> Reuse Plan

اگر SQL Server بتواند Plan موجود را مجددا استفاده کند ، نیاز به Compile و Optimization مجدد کاهش پیدا می کند ، در نتیجه منابع سیستم بهتر مصرف می شوند . اما اگر Query ها دائما با ساختارهای متفاوت ارسال شوند ، ممکن است تعداد زیادی Plan ایجاد شود ، بنابر این طراحی صحیح Query و نحوه ارسال آن از سمت Application اهمیت زیادی دارد .

یک سناریوی واقعی برای درک Reuse Plan :

فرض کنید یک فروشگاه اینترنتی دارید و برنامه باید اطلاعات محصولات مختلف را دریافت کند .

روش اول :

SELECT *

FROM Products

WHERE ProductID = 1001; 

سپس :

SELECT *

FROM Products

WHERE ProductID = 1002;

و :

SELECT *

FROM Products

WHERE ProductID = 1003;

در صورتی که این Query ها به شکل Ad Hoc و بدون parameterization مناسب مدیریت شوند ، احتمال ایجاد Plan های متعدد افزایش پیدا می کند .

اما می توان ساختار Query را Parameterized کرد :

SELECT *

FROM Products

WHERE ProductID = @ProductID;

در این حالت ساختار Query ثابت است و فقط مقدار Parameter تغییر می کند ، این دقیقا همان ایده ای است که Parameterization برای افزایش Reuse Plan دنبال می کند .

چه زمانی از Store Procedure استفاده کنیم ؟

Stored Procedure زمانی گزینه مناسبی است که :

    1. Query یا منطق database به شکل مشخص و قابل تکرار دارید .

    2. می خواهید پارامترها را به صورت ساختار یافته دریافت کنید .

    3. Reuse Plan برای Workload اهمیت دارد .

    4. می خواهید متن Query دائما از Application به Database ارسال نشود .

اکا Stored Procedure راه حل مطلق برای همه مسائل نیست . بعضی Business Process ها بهتر است در Database باشند و برخی دیگر نباید در Database پیاده سازی شوند .

چه زمانی sp_executesql انتخاب بهتری است ؟

sp_executesql زمانی اهمیت بیشتری پیدا می کند که Query شما Dynamic باشد . برای مثال ممکن است Application بر اساس شرایط مختلف ، بخش هایی از Query را تغییر دهد . در این حالت بجای ساختن Query با Concatenation نا امن ، می توان قسمت های متغیر را تا حد امکان Parameterize کرد .

مزایای مهم :

    1. مناسب برای Dynamic SQL .

    2. پشتیبانی از Parameter .

    3. کاهش ریسک SQL Injection در مقایسه با Concatenation مستقیم .

    4. کمک به Reuse Plan .

فایل نیز استفاده از sp_executesql را به عنوان جایگزینی برای Stored Procedure در شرایط مناسب مطرح می کند .

اشتباهات رایج در مدیریت Plan Cache 

استفاده بیش از حد از Ad Hoc Query

یکی از اشتباهات رایج، تولید تعداد زیادی Query با متن متفاوت است .

Concatenate کردن ورودی کاربر در Query

این روش می تواند هم از نظر امنیتی و هم از نظر مدیریت Plan مشکل ایجاد کند .

تصور اینکه یک Plan همیشه بهترین Plan است 

یک Execution Plan ممکن است برای یک مقدار parameter مناسب باشد ، اما برای مقدار دیگر عملکر خوبی نداشته باشد .

استفاده افراطی از Stored Procedure 

Stored Procedure ابزار قدرتمندی است ، اما نباید صرفا با هدف استفاده از Cache تمام ، منطق Business  را داخل Database قرار داد .

بی توجهی به Query Plan Hash 

گاهی چند Quey ظاهرا متفاوت هستند اما Plan یکسانی دارند .

برعکس ، Query هایی که بسیار شبیه به نظر می رسند ممکن است Planهای متفاوت ایجاد کنند .

استفاده از Hash ها می تواند به تحلیل این وضعیت کمک کند .

چک لیست بهینه سازی Plan Cache در SQL Server

اگر مسئول Performance یک SQL Server هستید، این موارد را بررسی کنید :

    1. آیا Query های پر تکرار Parameterize شده اند ؟

    2 آیا تعداد زیادی Ad Hoc Query در سیستم وجود دارد ؟

    3. آیا Plan های مشابه زیادی در Cache ایجاد شده اند ؟

    4.  آیا Query های Dynamic با sp_executesql اجرا می شوند ؟

    5. آیا استفاده از Store Procedure در بخش های مناسب بررسی شده است ؟

    6. آیا Query Hash ها برای پیدا کردن Query های مشابه بررسی شده اند ؟

    7. آیا Query Plan Hash ها برای پیدا کردن Plan های مشابه بررسی شده اند ؟

    8. آیا Cache Bloat در سیستم مشاهده می شود ؟

    9. آیا Optimize for Ad Hoc Workload برای Workload مورد نظر مناسب است ؟

    10 . آیا تفاوت Execution Plan ها برای parameter های مختلف بررسی شده است ؟

جمع بندی :

Plan Cache یکی از بخش های مهم SQL Server برای بهبود Performance است . ایده اصلی آن ساده است : اگر یک Query قبلا Comile و Optimize شده باشد و Execution Plan ، آن قابل استفاده مجدد باشد SQL Server می تواند به جای انجام دوباره این فرآیند ، از Plan موجود استفاده کند . 

برای افزایش احتمال Reuse Plan روش هایی مانند Prepare/Execute , sp_executesql , Store Procedure , Parameterization اهمیت دارند . همچنین بهتر است تعداد Ad Hoc Query ها کنترل شود و در محیط هایی که این Query ها اجتناب ناپذیر هستند ، قابلیت Optimize for Ad Hoc Workload مورد بررسی قرار گیرد .

از طرف دیگر ، نباید صرفا به دنبال بیشترین میزان Plan Reuse باشیم . همانطور که ، مثال ها نشان می دهند Query هایی با ساختار تقریبا مشابه می توانند در شرایط مختلف Execution Plan های متفاوتی ایجاد کنند . بنابر این Performance Tuning باید بر اساس Workload واقعی و Execution Plan های واقعی انجام شود .

با رعایت این اصول ، می توان مدیریت بهتری روی Plan Cache داشت و از منابع SQL Server به شکل موثرتری استفاده کرد .