آموزش پیشرفته SQL Server (جلسه دهم) - Plan Cache چیست؟

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

Execution Plan چیست؟

قبل از اینکه به توضیح Plan Cache بپردازیم، یکبار دیگر مفهوم Execution Plan را بررسی می کنیم.

برای اینکه بتوانید وضعیت کاراریی کوئری های موجود در سیستم خود را بهتر و دقیق تر زیر نظر بگیرد، بررسی Execution Plan یا همان نقشه اجرایی یکی از بهترین راه حل ها می باشد.

Execution Plan در حقیقت نقشه بدست آوردن داده می باشد.

SQL Server بوسیله Execution Plan متوجه می شود که برای بدست آوردن داده های شما، ابتدا باید سراغ چه ایندکس و یا جدولی برود.

Execution Plan ترتیب اجرای فیزیکی کوئری های شما را مشخص می کند.

نحوه ی اجرای دستورات SQL

زمانی که یک کوئری به SQL Server فرستاده می شود، ابتدا کوئری مورد نظر تجزیه شده و پس از آن توسط Query Optimizer (بهینه ساز کوئری)، Execution Plan ساخته می شود.

پس از آن Execution Plan ساخته شده به طور باینری داخل DataBase ذخیره می شود و مورد استفاده قرار می گیرد.

البته بصورت دقیق تر اجرای کوئری در SQL Server شامل 4 مرحله زیر است:

Plan Cache چیست؟

Plan Cache در واقع بخشی از حافظه RAM متعلق به SQL Server است و محل قرارگیری Execution Plan می باشد.

Procedural Cache نام قبلی Plan Cache است.

نکته: برای بدست آوردن اطلاعاتی در مورد Plan Cache، مانند میزان فضای اختصاص داده شده و... می توانید از دستور زیر استفاده نمایید:

DBCC MemoryStatus

مزایای استفاده از Plan Cache چیست؟

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

بنابراین یکی از مهمترین مزایای استفاده از Plan Chache عدم نیاز به ساخت مجدد Execution Plan می باشد که در این صورت فرآیندهای کامپایل و بهینه سازی حذف می گردد.

روش های جلوگیری از ساخته شدن مجدد Execution Plan چیست؟

روش های مختلفی را می توان بکار برد تا از ساخته شدن مجدد Execution Plan جلوگیری کرد، در زیر به بعضی از آنها اشاره می کنیم:

1. استفاده از Daynamic SQL
2. استفاده از Stored Procedure

در ادامه به توضیح مختصر هر کدام از موارد بالا خواهیم پرداخت.

1- جلوگیری از ساخته شدن مجدد Execution Plan - استفاده از Daynamic SQL

سوال: آیا به ازای هر کدام از کوئری های زیر Execution Plan ساخته می شود...1؟

1- SELECT * FROM Orders WHERE ShipCountry='uk'
2- SELECT * FROM Orders WHERE ShipCountry='Uk' 
3- SELECT * FROM orders WHERE ShipCountry='uk'
4- SELECT * FROM Orders WHERE ShipCountry='uk' --
5- SELECT * FROM Orders WHERE ShipCountry='usa'

جواب سوال:

دقیقاً به ازای هر کدام از کوئری های بالا یکبار Execution Plan ساخته می شود، اما با توجه به اینکه کوئری های بالا تقریباً یکسان هستند چرا نمی توان Execution Plan موجود را Reuse نمود...1؟

در زیر اشکال هر کدام از کوئری های بالا که باعث ساخته شدن مجدد Execution Plan می شود را توضیح داده ایم:

کوئری 2: در قسمت شرط از مقدار "UK" با حروف بزرگ استفاده شده است و چون Execution Plan به حروف بزرگ و کوچک حساس است بنابراین نمی توان از Execution Plan قبلی استفاده نمود.

کوئری 3: در این کوئری نیز مانند قبل، در قسمت نام جدول از کاراکتر "o" کوچک استفاده شده است.

کوئری 4: در انتهای کوئری از "--" استفاده شده است.

کوئری 5: مقدار شرط تغییر کرده است.

اما با توجه به مثال بالا چگونه می توان Execution Plan موجود را Reuse نمود؟ جواب در استفاده از قابلیت Daynamic SQL است. در مثال زیر زمانی که برای اولین بار کوئری مورد نظر با استفاده از sp_executesql اجرا می شود، execution Plan ساخته می شود و در دفعات بعدی از Execution Plan قبلی استفاده خواهد شد:

1- EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID=@OrderID',
    N'@OrderID INT', @OrderID=10253

2- EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID=@OrderID',
    N'@OrderID INT', @OrderID=10254

3- EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID=@OrderID',
    N'@OrderID INT', @OrderID=10255

sp_executesql یک SP سیستمی و از پیش تعریف شده است و رشته ای که بعنوان پارمتر اول به آن پاس می شود را اجرا می کند.

2- جلوگیری از ساخته شدن مجدد Execution Plan - استفاده از Stored Procedure

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

در ادامه آموزش Plan Cache چیست؟ خواهید دید...!

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

1. چگونه می توان لیست کوئری هایی که Cache شده اند را بدست آورد.
2. ساخته شدن و یا نشدن مجدد Execution Plan به ازای روش های مختلف مذکور در یک Application خاص
3. و...

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

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

نقطه شروع بحث بالا (Plan Cache چیست؟) در ویدئو: 13:15

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