دانلود ‫پروژه Profiler برای مدیریت SQL Server

بهینه‌ ساز پرس‌ و جو از اهمیت زیادی برای پایگاه داده ارتباطی برخوردار است، مخصوصا برای اجرای دستورات پیچیده SQL .

یک بهینه ساز پرس‌وجو بهترین استراتژی بر اجرای هر پرس‌وجو را تعیین می‌کند.

بهینه‌ساز پرس و جو به عنوان مثال انتخاب می‌کند آیا از شاخص برای یک پرس‌وجو مشخص استفاده کند یا نه، وکدام تکنیک الحاق هنگامی که جداول با هم الحاق می‌شوند استفاده شود.

این تصمیم تاثیری بسیار زیادی بر روی کارآیی SQL دارد، و بهینه‌ سازی پرس‌وجو یک تکنولوژی کلیدی بر هر کاربردی است، از سیستم‌های قابل استفاده (Operatianal system) تا انباره‌ های داده‌ ای (Data warehause) و سیستم‌ های تحلیل (analysis systems) تا سیستم‌ های مدیریت محتویات (canternt – management) .

بهینه‌ساز پرس‌وجو برای برنامه‌های کاربردی و کاربران نهایی کاملا ناپیدا است .

از آنجا که برنامه‌‌های کاربردی ممکن است هر SQL پیچیده‌ای راتولید کنند، بهینه سازها پرس و جو باید فوق‌العاده سطح بالا و قدرتمند باشد.

برای مطمئن شدن به ایجاد یک کارآیی خوب.

برای مثال بهینه سازهای دستورات SQL را تغییر شکل می‌دهد، به دلیل این که این دستورات می‌توانند به معادل‌هایی تبدیل شوند اما با کارآیی بالاتر.

بهینه‌سازهای جستجو معمولا بر مبنای هزینه می‌باشند.

در یک استراتژی بهینه سازی بر مبنای هزینه، طرحهای اجرایی چندگانه‌ای برای یک پرس و جو شخص تولید می‌شود، و آنگاه یک هزینه تخمینی برای هر طرح محاسبه می‌شود.

بهینه ساز پرس‌وجو طرحی که دارای کمترین هزینه تخمینی است را انتخاب می‌کند.

بهینه‌سازی پرس وجو بهبود کارآیی پرس وجو به صورت خودکار بهبود به معنی تضمین بهینه بودن نیست مراحل فرآیند بهینه سازی انتخاب یک نمایش داخلی (internal representation) اعمال تغییرات لازم جهت بهبود کارآیی انتخاب رویه‌های دسترسی سطح پایین به داده‌ها تولید طرحهای اجرایی پرس وجو و تخصیص هزینه به آنها انتخاب یک طرح اجرایی با کمترین هزینه درختهای پرس‌وجو نمایش درخت عبارت جبر رابطه‌ای با شرایط: پیمایش میانوندی درخت عبارت اصلی را تولید کند.

عملگرهای دوتایی موجود – 0 U,X می‌باشند.

عملگرهای یکتایی موجود می‌باشند.

همه برگ‌ها دردرخت رابط‌های پایه ای می‌باشند.

مثال 1: مثال 2 : تبدیلات (Tranformations) طراحی دستکاریهای جبری و معنایی جهت دوری از انجام اعمال هزینه بری باشد.

دستکاریهای جبری عبارت رابطه‌ای E3,E2,E1 را در نظر بگیرید.

قوانین تبدیل زیر برای حاصلضرب نمایش داده شده‌اند اما می‌توان آنها را جهت انواع دیگری از عملیات الحاق به کار برد: 1 قانون جابه‌جایی 2 قانون شرکت‌پذیری 3 آبشار تصاویر (Cascade of projection) 4- آبشار انتخاب‌ها (Cascade of selections) 5 تبدیل عملگر انتخاب و عملگر تصویر (project) اگر شرط F تنها با صفات ضرب درگیر باشد آنگاه 6 تبدیل عملگرهای انتخاب به عملگر ضرب متقابل (Cross product) اگر شرط F تنها با صفات E1 درگیر باشد آنگاه اگر F برابر باشد با حاصل البته به شرط این که F1 به E1 وابسته باشد و F2 به E2 واسته باشد آنگاه 7 تبدیل عملگر انتخاب به عملگر اجتماع (Union) 8 تبدیل عملگر انتخاب به عملگر تفاضل (Difference) 9 تبدیل عملگر تصویر به عملگر ضرب متقابل 10 تبدیل عملگر تصویر به عملگر اجتماع نکته: عملگر تصویر و عملگر تفاضل دارای خاصیت جابه جایی نیستند.

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

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

بهینه‌ساز از اطلاعات کاتالوگ سیستم (شاخص‌ها، کاردینالیتی و غیره) جهت تعیین هزینه هر روال کاندید استفاده می‌کنند.

این فرآیند انتخاب مسیر دسترسی نامیده می‌شود.

تولید طرح‌های پرس و جو و انتخاب یکی از آنها بهینه ساز یک مجموعه از طرح‌های پرس و جو را به وسیله ترکیب روال‌های سطح پایین کاندید تولید می‌کند.

چندین تابع اکتشافی (Heurisic) جهت محدود کردن تعداد طرح‌های پرس‌وجوی تولید شده استفاده می‌شود یک هزینه (از نظر میزان I/O دیسک) به هر طرح اختصاص داده می‌شود.

کم‌هزینه‌ترین طرح انتخاب می‌شود.

(تخمین هزینه دقیق مشکل است زیرا بعضی از پرس و جوها به تولید نتایج میانی نیاز دارند و اندازه این نتایج وابستگی زیادی به مقادیر داده‌ها واقعی دارد.) روش‌های بهینه‌سازی پرس‌و‌جو تبدیل پرس‌و‌جو (Transformation Query) هر گاه یک زبان دستکاری داده (DML) نظیر SQL جهت ارایه یک پرس‌و‌جو به سیستم مدیریت پایگاه داده رابطه‌ای (RDBMS) مورد استفاده قرار می‌گیرد، گامهای فرآیندی مستقلی جهت تبدیل پرس‌و‌جو اصلی مورد نیاز است.

هر یک از این گامها باید قبل از این که RDBMS پرس‌و‌جو را پردازش کند، انجام شود.

فرآیند تجزیه (The parsing process) فرآیند تجزیه شامل دو عملکرد زیر است: کنترل کردن پرس‌و‌جو ورودی ازنظر نحوی (Syntax) شکستن پرس‌و‌جو به قسمتهای مولفه‌ای که می‌تواند به وسیله RDBMS ارزیابی شود.

قسمتهای مولفه‌ای در یک ساختارداخلی ذخیره می‌شوند این ساختار می‌تواند صورت گراف یا معمولا به صورت یک درخت پرس‌و‌جو باشد.

یک درخت پرس‌و‌جو در حقیقت نمایش داخلی قسمتهای مولفه‌ای یک پرس‌و‌جو باشد که به راحتی می تواند به وسیله RDBMS دستکاری شود.

بعد ازتولید این درخت مرحله فرآیند تجزیه کامل می‌شود.

فرآیند طبقه‌بندی (The standardization process) برخلاف سیستم‌های سلسله مراتبی محض (Strictly hierarchical systerm) ، یکی از مزایای بزرگ یک ROBMS توانایی پذیرفتن پرس‌و‌جو پویای سطح بالا از کاربر است، در حالی که کاربر هیچ دانشی از بستر ساختار داده‌ای ندارد.

هدف فرآیند طبقه‌بندی تبدیل پرس‌و‌جو به یک قالب مفید برای بهینه‌سازی است.

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

از آنجا که این احکام مستقل از مقادیر داده‌ها می‌باشند برای تماس اعمال می‌توانند مورد استفاده قرار گیرند.

در مدت انجام این فرآیند، RDBMS درخت پرس‌و‌جوی را باز چینی می‌کند به شکلی که طبقه‌بندی بیشتری شده باشد در بسیاری از موارد، قسمتهای نحوی اضافه به طور کامل حذف می شود.

این طبقه‌بندی درخت پرس‌و‌جوی، ساختاری را تولید می‌کند که می‌تواند به وسیله بهینه‌ساز پرس‌و‌جوی RDBMS مورد استفاده قرار گیرد.

بهینه ساز پرس‌و‌جو (The Query optimizer) هدف بهینه‌ساز پرس‌و‌جوی توید یک طرح اجرایی کارآمد برای پردازش پرس‌و‌جوی ارائه شده به وسیله درخت پرس‌و‌جوی طبقه‌بندی شده است.

بنابراین یک بهینه‌ساز می‌تواند ازنظر تئوری یک طرح اجرایی بهینه را برای هر درخت پرس‌و‌جوی پیدا کند، یک بهینه ساز واقعا یک طرح اجرایی کارآمد ومورد قبول را تولید می کند.

هنگامی که یک پرس‌و‌جوی پیچده می شود تعداد جداولی که ممکن است لازم باشد الحاق شوند افزایش می‌یابد.

بدون استفاده از تکنیک‌های هرس کردن (pruning) یا روش‌های اکتشافی (heuristical) دیگر جهت کاهش تعداد ترکیبات داده‌ایمورد نیاز، زمان مورد نیاز بهینه‌ساز پرس‌وجو جهت ارائه یک طرح اجرایی کارآمد برای یک پرس‌و‌جوی پیچیده به راحتی می‌تواند بیشتر از زمان مورد نیاز یک طرح اجرایی با کارآمد کمتر شود.

بهینه‌سازی اکتشافی (Hevristic Optimization) بهینه‌سازی اکتشافی یک روش قانونمند است که می‌تواند یک طرح اجرایی کارا برای اجرای پرس‌و‌جوی را توید کند.

از آنجا که خروجی مرحله طبقه‌بندی یک صورت یک درخت پرس‌وجو ارائه می‌شود، هر نود از این درخت به صورت مستقیم به یک عبارت جبری رابطه‌ای نگاشت می‌شود.

عملکرد بهینه‌ساز پرس‌و‌جوی اکشانی به این صورت است که قوانین جبری رابطه‌ای هم ارز با این درخت عبارت را به کار می‌برد و این عبارات را به نمایشی کاراتر تبدیل می کند.

با استفاده از قوانین هم اند جبر رابطه‌ای که اطلاعات غیر ضروری در هنگام تبدیل این درخت حذف می‌شوند.

گامهای اجرایی در بهینه سازی اکتشافی صورت زیر می‌باشند: 1 شکستن انتخاب‌های ربطی (Canjuctive seleot) به انتخاب‌های آبشاری (Cacadin select) 2 انتقال انتخاب‌ها به پایین درخت پرس‌و‌جوی جهت کاهش تعداد تاپل‌های (Tuple) خروجی پرس‌و‌جوی 3 انتقال Proyect به پایین درخت پرس‌و‌جوی جهت حذف صفات غیر ضروری 4 ترکیب عملگر ضرب کارتزین که به دنبال یک عملگر انتخاب آمده است به یک عملگر الحاق ساده .

هنگامی که این گامها انجام شود، میزان کارآیی یک پرس‌وجو می‌تواند به وسیله باز چینی (rearranging) انتخاب‌ها (Select) و الحاق‌های (Join) باقیمانده افزایش پیدا کند.

به طوری که کمترین سربار را به سیستم تحمیل می‌کنند بهینه‌ساز اکشانی.

بیش از جهت تجزیه پرس‌وجو کاری انجام نمی‌دهد.

بهینه‌سازی نحوی (Syntactical optimizer) بهینه‌سازی نحوه به درک کلمه بداند ساختار زیربنایی پایگاه داده و توزیع داده‌های ذخیره شده در جدول، تکیه دارد.

همه جداول به ترتیبی که کاربرد در پرس‌وجو مشخص کرده است الحاق می‌شوند.

بهینه‌ساز سعی در بهبود کارآیی این الحاق‌ها دارد به وسیله شاخص‌هایی (Index) که برای بازیابی داده‌ها نیز هستند.

این نوع از بهینه‌ساز هنگام دستیابی به داده‌ها در یک محیط نسبتا ایستا می‌تواند کارایی بسیار زیادی داشته باشد.

بااستفاده از بهینه‌ساز نحوی هنگامی رخ می دهد که ساختار زیربنایی داده به صورت خوبی پویا داده‌ها را تغییر می دهند اغلب نیاز پیاده می‌کنند که مجدد کامپایل شوند تا کارایی دسترسی آنها به داده‌ها بیشتر شود.

بهینه‌سازی بر مبنای ارزش (Cost – based optimizotion) جهت حل این قبیل مسائل مطرح می شود.

بهینه‌سازی بر مبنای هزینه (Cost – based optimization) جهت اجرای بهینه‌سازی بر مبنای هزینه، بهینه‌ساز اطلاعات مشخصی در مورد داده‌های ذخیره شده نیاز دارد.

این اطلاعات بسیار زیاد به سیستم وابسته است و می‌تواند شامل اطلاعاتی نظیر اندازه فایل، نوع ساختار فایل ، شاخص‌های اولیه وثانویه (Primany and secondary Index) موجود، و صفات انتخابی (attribute selectivity) (درصد تابل هایی که انتظار می رود در یکانتخاب برابر بازیابی شوند).

از آنجا که هدف هر مرحله بهینه‌سازی بازیابی اطلاعات درخواست شده به صورت کالا کارا است، یک بهینه‌ساز بر مبنای ارزش از دانش خود در مورد داده‌های زیربنایی (underying data) و ساختار ذخیره‌سازی؟

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

همان طور که قبلا اشاره شد، هدف نهایی بهینه ساز بر مبنای هزینه توید یک طرح اجرایی کارا جهت بازیابی داده‌ها نیست، اما تولید یک طرح اجرایی معتدل را می‌توان به عنوان هدف نهایی آن درنظر گرفت.

برای پرس‌وجو های پیچیده، هزینه‌ای که محاسبه می‌شود برمبنای ازیابی تمام زیرمجموعه‌های ممکن و بر مبنای اطلاعات اماری که گزینندگی (Selectivity) هر عملکرد رابطه‌ای را تخمین می‌زنند، است.

به دلیل اینکه نگهداری این اطلاعات سبب به وجود آمدن سربار می شود، اغلب سیستم‌های مدیریت پایگاه داده ایناطلاعات را در جداول یا کاتالوگ‌های سیستمی نگهداری می کنند که به صورت دستی می توانند بروزرسانی شوند.

مدیر سیستم پایگاه داده باید این اطلاعات را نگهداری کند به دلیل این که بهینه‌ساز بر مبنای هزینه می تواند به دین وسیله هزینه عملکردهای مختلف را تخمین بزند.

بهینه‌ ساز معنایی (Semantic optimization) اگر چه هنوز تکنیک بهینه سازی پیاده‌سازی شده‌ای وجود ندارد اما تحقیقات قابل توجهی درمورد بهینه‌سازی معنایی در حال انجام است.

عملکرد بهینه‌ساز معنایی بر مبنای این فرض است که بهینه‌ساز یک درک مقدماتی از شمای پایگاه داده واقعی دارد.

هنگامی یک پرس‌وجو ارائه می شود، بهینه‌ساز از دانش خود درمورد محدودیت‌های سیستمی جهت ساده کردن یا صرف نظر کردن از پرس‌وجو خاصی (البته اگر ضمانت شود که یک مجموعه تهی را بر می گرداند) استفاده می‌کند.

بهینه‌ساز پرس‌وجوی Microsoft SQL sevaer .

موتور پایگاه داده Microsoft SQL sevaer از بهینه‌ساز پرس‌وجو بر منای هزینه استفادهمی‌کند جهت بهینه‌سازی پرس‌وجوهایی که برای دستکاری داده‌ها با استفاده از SQL ارائه می‌شوند.

(یک پرس‌وجوی دستکاری می تواند هر پرس‌وجویی که از کلمات کلیدی Haoing, where پشتیبانی می‌کند.

باشد؛ برای مثال (Update , Delete, select) بهینه سازی در سه فاز زیر انجام می‌شود: تحلیل پرس‌وجو (Query Analysis) انتخاب شاخص (Index Selection) انتخاب الحاق (Join Selection) تحلیل پرس‌وجو (Query Andysis) در فاز تحلیل پرس‌وجو، بهینه ساز SQL sevaer هر عبارت ارائه شده را ه وسیله درخت پرس‌وجو بررسی می‌کند و تشخیص می دهد که آیا می توان آن را بهینه کرد یا نه.

SQL sevaer سعی می‌کند عباراتی که یک پویش (Scan) را محدود می‌کند را بهینه کند؛ برای مثال ، عبارات الحاق و جستجو.اما،‌تمام عبارات صحیح در SQL قابل بهینه‌سازی نمی‌باشند، نظیر عبارتی که شامل عملکرد رابطه‌ای (نامساوی) باشد.

هنگامی که یک پرس‌وجوی رابطه‌ای شامل یک عبارتی است که قابل بهینه‌سازی نمی‌باشد، طرح اجرایی به این قسمت از پرس‌وجو با استفاده از پویش جدول دسترسی پیدا می کند.

اگر درخت پرس‌وجو شامل دستورات و عباراتی SQL باشد که قابل بهینه‌سازی است، بهینه‌ساز انتخاب شاخص را برای هر کدام از آن عبارات به کار می‌برد.

انتخاب شاخص (Index selection) برای هر عبارت قابل بهینه سازی، بهینه ساز جداول سیستمی پایگاه داده را بررسی می‌کند برای این که ببیند آیا یک شاخص مفید برای دسترسی به داده‌ها وجود دارد یاخیر.

یا شاخص زمانی مفید تلقی می‌شود که یک مجموعه پیشونید از ستونها را شامل شود به طوری که دقیقا باس تونهای به کار رفته در پرس و جو دقیقا تطبیق پیدا کند.

این تطبیق باید دقیقا وجود داشته باشد، زیر یک شاخص بر مبنای ترتیب ستونها در زمان ایجاد جدول، ساخته می‌شود.

برای یک شخاص کلاستر شده Clustered Index داده بر مبنای ترتیب ستونهای یک شاخص ذخیره می‌شوند.

تلاش جهت استفاده تنها یک ستون ثانویه از یک شاخص جهت دسترسی به داده‌ها شبیه تلاش جهت استفاده از یک دفترچه تلفن به صورت جستجو درتمام مداخل موجود با نام یکسان است.

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

درمبحث بهینه‌سازی بر مبنای هزینه، توضیح داده شده که بهینه‌ساز هزینه ارزیابی شده برای هر عبارات بر مبنای اطلاعات آ,اری محاسبه می کند.

این اطلاعات آماری برای ارزیابی عبارت گزینندگی Clauses selectivity) ) استفاده می‌شودند (درصد تاپل‌هایی که برای هر عبارت برگردانده می‌شود) Microsoft SQL server این اطلاعات آماری تنها در زمانهای زیر بروز رسانی می شوند: در خلال ساخت ابتدایی شاخص (اگر داده در جدول وجود داشته باشد) هنگامی که دستور UPDATE SRATIICS برای شاخص با جداول مرتبط شده اجرا می‌شود.

جهت آماده‌سازی SQL sever با اطلاعات آماری دقیق که بازتاب میزان توزیع دقیق تاپل‌ها در یک جدول دارای داده است، مدیر سیستم پایگاه داده باید اطلاعات آماری را برای شاخص‌های جداول نگهداری کند.

اگر هیچ اطلاعات آماری برای شاخص‌ها موجود نباشد، یک اکتشاف بر مبنای عملگر رابطه‌ا‌ی استفاده می‌شود برای تولید یک ارزیابی از گزینندگی .

اطلاعات در مورد گزینندگی عبارت ونوع شاخص موجود جهت محاسبه هزینه عبارت استفاده می‌شود.

SQL servaer میزان رخ دادن I/O دیسک را تخمین می‌زند، اگر شاخص برای بازیابی داده از جدول استفاده شود.

اگر این تخمین کمتر از هزینه I/O دیسک در حالت پویش تمام جدول باشد، طرح دسترسی که از این شاخص استفاده می کند ایجاد می شود.

انتخاب الحاق (Join selection) هنگامی که انتخاب شاخص کامل می شود و همه عبارات دارای هزینه پردازش بر مبنای طرح دسترسی می‌باشند، بهینه ساز انتخاب الحاق را اجرا می‌کند.

انتخاب الحاق جهت یافتن یک ترتیب کارا برای ترکیب طرحهای دسترسی استفاده می‌شود.

برای اجرای این مورد.

بهینه ساز ترتیب‌های مختلف عبارات را با هم مقایسه می‌کند و سپس طرح الحاق را انتخاب می‌کند با توجه به این که کمترین هزینه پردازشی I/O دیسک را داشته باشد.

از آنجا که تعداد ترکیبات ممکن از عبارات می تواند مانند پیچیدگی یک پرس‌وجوافزایش پیدا کند، بهینه ساز پرس‌وجو در SQL servar از تکنیک‌های هرس کردن درخت جهت کمینه کردن (minimize) سرباراین مقایسه‌ها استفاده می‌کند.

هنگامی که فاز انتخاب الحاق کامل می شود، بهینه ساز پرس‌وجو در SQL Server یک طرح اجرایی پرس‌وجو بر مبنای هزینه تهیه می‌کند که از سودمندهای شاخص‌های موجود استفاده می‌کند هنگامی که آنها برای دسترسی به داده‌ها مفید هستند و این کار به این دلیل انجام می‌شود که سربار سیستم کمینه شود و کارایی بهبود یابد تشریح روش کار بهینه ساز پرس‌وجو Microsoft SQL Server Microsoft SQL Server دارای یک بهینه‌ساز پرس‌و‌جوبرمبنای هزینه است که یک پرس‌و‌جودریافت کرده و به سرعت بهترین روشرا جهت‌ بازیابی داده‌ها مشخص می‌کند، این روش دستیابی می‌تواند شامل ترتیب الحاق جداول یا استفاده از شاخص های گذاشته شده بر روی این جداول باشد.

با استفاده از یک بهینه ساز پرس و جو بر مبای هزینه‌ها مدیر سیستم یا کاربر نهاییی از بدست آوردن موثرترین راه کارها در ساختار دهی پرس‌و‌جوها جهت بدست آوردن کارایی بهیه ازاد می شوند اما در عوض بهینه‌ساز باید تمام ترتیب‌های ممکن در الحاق جداول و هزینه استفاده از شاخص‌های موجود بر روی جدول را بدست آورد و در نهایت طرحی را که کمترین هزینه را از لحاظ I/O صفحات دارد راانتخاب کند.

جزئیات اطلاعاتی در مورد روش دسترسی نهایی که بهینه ساز انتخاب کرده است را می‌توان با اجرای یک دستور Trans act – SQL با عنوان "set show plan on' مشاهده نمود.

این دستور درهر مرحه نشان می دهد که بهینه‌ساز از کدام ترتیب الحاق جداول و یا کدام شاخص را که دارای کمترین هزینه جهت بازیابی داده‌ها استفاده می کند.

این نمایش مراحل فوق‌العاده مفید است مخصوصا هنگامی که می‌خواهیم ببینیم آیا شاخص های تعریف شده بر روی جداول آن قدر سفید هستند که توسط بهینه‌ساز مورد استفاده قرار گیرند.

در این مقاله سعی شده است خروجی‌های Shawplop برای یک پرس وجوی خاص توضیح داده شود.

“STEP n” این دستور در خروجی Showplan قرارمی‌گیرد برای هر پرس‌و‌جودر حقیقت n یک عدد صحیح است که می‌تواند به صورت SREP 1 شروع شود.

برای بعضی از پرس‌و‌جو ها، SQL Server نمی تواند نتایج را به صورت مؤثر در یک مرحله بازیابی کند، و باید پرس‌و‌جورا به چندین گام بشکند.

برای مثال، اگر یک پرس‌و‌جوشامل عبارت Group By ، پرس‌و‌جوباید در دو کام شکسته شود: یک گام برای انتخاب سطرهای محدود شده از جدول، و گام بعدی برای گروه‌بندی آنها باید انجام شود.

پرس‌و‌جوزیر یک پرس‌و‌جوی تک ـ گام را نشان می‌دهد.

نوع پرس‌و‌جوانتخاب است(در یک جدول موقت) “The type of query is SELECT” دستور Shawplan نشان می‌دهد که SQL Server نیاز دارد بعضی ازنتایج پرس‌و‌جورا بلافاصله در یک جدول موقت (warktable) درج کند، تا بعد از پردازش پرس‌و‌جومقادیر را از این جدول موقت انتخاب کند.

این موردبیشتر در پرس‌و‌جوکه با عبارت Group by درگیر می‌شوند اتفاق می‌افتد، به طوری که اولین نتیجه خروجی در یک جدول موقت قرار می‌گیرند، و سپس سطرهای قرار گرفته بر مبنای ستون مشخص شده در عبارت Group by گروه‌بندی می شوند.

پرس‌و‌جوی زیر فهرستی از تمام شهرها و تعداد نویسندگانی که در هر شهرزندگی می‌کنند نشان می‌دهد.

طرح پرس‌و‌جوبه دوگام شکسته می شود: اولین گام سطرها را از یک جدولموقت انتخاب می کند و دومین گام سطرهای گروه بندی شده را از جدول موقت بازیابی می‌کند: نوع پرس‌و‌جو است این عبارت نوع پرس و جو را در هر مرحله نشان می‌دهد.

برای اغلب پرس‌وجوهای کاربر، نوع INSERT,UPDATE,SELECT و یا DELETE است.

مثال‌های زیر خروجی های مختلف را برای پرس‌و‌جوها و دستورات گوناگون نشان می‌دهد:‌ حالت بروز رسانی به تعویق افتاده می‌شود “The update mode is deferred” دو روش یا دو حالت (mode) وجود دارد که SQL Server می‌تواند عملیات بروزرسانی نظیر SELECT INTO, UPDATE,INSERT,DELETE را اجرا کند.

این دو روش عبارتند از : بروزرسانی به تعویق افتاده (deferred update) و بروزرسانی مستقیم (direct update) .

هنگامی که روش بروزرسانی به تعویق افتاده استفاده می شود، تغییرات برای تمامی سطرها انجام می‌شود به وسیله ایجاد رکوردهای log در log تراکنش (Tranaction log) منعکس کردن مقدار قدیم و جدید ستون ‌هایی که باید تغییر داده شود (در مورد عملیات UPDATE) یا مقادیری که باید درج و یا حذف شوند به درترتیب در مورد دستور DELETE,INSERT) .

هنگامی که همه رکوردهای log ساخته می‌شوند، آنگاه تغییرات در صفحات داده‌ای انجام خواهد شد.

این روش رکوردهای log بیشتری نست به روش مستقیم (که در ادامه بررسی می شود) ایجاد می‌کند، اما در اجرای دستوراتی که جدول را به صورت آبشاری (cascade) تغییر می‌دهند سودمندی بیشتری دارد.

برای مثال، جدول را با یک ستون col 1 و یک شاخص یکتا برروی آن در نظر بگیرید، و مقادیری بین 1 تا 100 که در ستون col 1 قرار گرفته است .

حال فرض کنید دستور UPDATE برای افزایش مقدار هر سطر به اندازه یک واحد اجرا می‌شود: فرض کنید که قرار باشد از سطر اول تا سطر آخر تمام جدول بروزرسانی شود.

بروزرسانی اولی سطر (که دارای مقدار 1 است) به مقدار 2 با خطا مواجه خواهد شد.

چون با این کار شاخص یکتای تعریف شده بر روی جدول نقض می‌شود به دلیل این که مقدار 2 در جدول وجود دارد، همچنین بروزرسانی مقدار 2 به مقدار 3 نیز با خطا مواجه خواهد شد و به همین ترتیب بروزرسانی بقیه سطرها نیز با خطا مواجه می‌شوند به جز آخرین سطر.

با استفاده از بروزرسانی به تعویق افتاده به راحتی می‌توان از بروز چنین خطایی اجتناب کرد.

در ابتدا رکوردهای log ساخته می‌شوند تا مقادیر جدید برای هر سطر را نشان دهند، سپس سطرهای موجود حذف می شوند و مقادیر جدید درج می شوند.

دستور INSERT هم مانند دستور UPDATE به دلایل بسیار مشابهی ممکن است به تعویق افتاده شود.

پرس‌و‌جوی زیر را در نظر بگیرید (یک شاخص خوشه‌بندی شده (clustered Index) یا یک شاخص یکتا (unique index) بر روی جدول roysched وجود دارد): حالت بروزرسانی مستقیم است “The update mode is direct” SQL Server در هر زمان ممکن سعی می‌کند جهت بروزرسانی جداول از روش مستقیم بروزرسانی استفاده کند،‌البته تا زمانی که روش مستقیم نسبت به روش به تعویق انداختن سریع‌تر باشد و تعداد رکوردهای Log کمتری را تولید کند.

با توجه به نوع دستور، یک یا چند ضابطه باید جهت اجرای بروزرسانی به روش مستقیم توسط SQL Server رعایت شود این ضوابط به صورت زیر است: INSERT : برای اعمال INSERT در روش بروزرسانی مستقیم، جدولی که قرار است سطرهایی در ان درج شود نباید همان جدولی باشد که سطرها از آن خوانده می‌شود.

دومین مثال پرس‌و‌جودر بخش قبلی، این حالت را نشان می‌دهد، سطرها از همان جدولی انتخاب می شوند که قرار است عمل درج این سطرها در همان جدول انجام شود.

به علاوه اگر سطرهایی که قرار است در جدول مقصد درج شوند، و یک یاچند تا از ستون‌های جدول مقصد در عبارت Where پرس‌و‌جوآمده باشد، آنگاه روش به تعویق افتادن به مراتب سریعتر از روش مستقیم خواهد بود.

SELECT INTo : هنگامی که یک جدول با داده‌های خروجی دستور SELECT INTO ایجاد پر می‌شود، همیهش از روش مستقیم برای درج سطرهای جدید استفاده می‌شود.

DELETE :‌برای اعمال DELETE در روش بروزرسانی مستقیم، بهینه‌ساز پرس‌و‌جوباید توانایی تشخیص این که صفر یا 1 سطر جهت حذف محدود شده است.

این مورد به این خاطر کنترل می‌شود که یک شاخص یکتا بر روی جدول وجود داشته باشد، که عبارت Where دستور DELETE مشخص شده باشد، و جدول مقصد با هیچ جدول یا جداوی دیگر الحاق نشده باشد.

UPDATE : برای UPDARE در روش بروزرسانی مستقیم، ضوابط مشابهی با دستور DELETE مطرح می شود: یک شاخص یکتا باید وجود داشته باشد چرا که بهینه‌ساز پرس و جو بیشتر از یک سطر را نمی‌تواند جهت بروزرسانی تشخیص دهد و تنها جدول موجود دردستور UPDATE همان جدول مقصدی است که باید بروزرسانی شود.

علاوه، تمام ستونهایی که قرار است بروز رسانی شود باید ترجیحا دارای نوع داده‌ای با طول ثابت باشند.

توجه کنید که هر ستونی که اجازه مقداردهی NULL را داشته باشد در SQL Server به صورت ستونی که دارای نوع داده‌ای با طول متغیر می‌باشد.

ذخیره می‌شود.

توجه داشته باشید که تنها تفاوت بین دومین و سومین مثال پرس‌و‌جودر ستون جدولی است که قرار است بروزرسانی شود.

در مثال دوم، روش بروزرسانی مستقیم استفاده می‌شود در صورتی که درمثال سوم روش بروز‌رسانی به تعویق افتاده استفاده می‌شود .

این تفاوت به دلیل نوع داده‌ای ستون مورد نظر می‌باشد (همان ستونی که قرار است بروزرسانی شود): ستون Titles.type به صورت NOT NULL char (12) تعریف شده است.

در صورتی که Titles price به صورت monery NuLL تعریف شده است.

از آنجا که ستون Price .

titles یک نوع داده‌ای با طول یکسان نیست ، روش مستقیم قابل استفاده نخواهد بود.

Group by این عبارت در خروجی Showplan ظاهرمی شود، برای هر پرس‌و‌جویی که شامل عبارت Group by باشد.

پرس‌و‌جوهایی که شامل عبارت Group by باشند، همیشه به حداقل دو مرحله تقسیم می شوند: گام اول انتخاب سطرهای خواسته شده از جدول و اصلی و قرار دادن آنها در یک جدول موقت و گروه‌بندی آنها، و گام دیگر جهت برگرداندن سطرها از جدول موقت می‌باشد.

مثال زیر این مسئله را نشان می‌دهد: تراکم ستونی “Scalar Aggregate” Transac – SQL شامل یکسری توابع تراکمی می باشد.

AVG( ) ، Count ( ) ، CouNt(*) ، MAX( ) ، MIN( ) و SUM( ) .

هر گاه یک تابع تراکمی در یک دستور SELECT که دارای عبارت Group by نمی‌باشد، استفاده شود، صرفنظر از این آیا عمل بر روی تمام سطرها یا بر روی زیرمجموعه‌ای از سطرهای جدول (به وسیله عبارت where) تعریف شده است، این تابع فقط یک مقدار را تولید می‌کند.

هنگامی که یک تابع تراکی یک مقدار را تولید می‌کند، تابع Scalar Aggreate نامیده می‌شود.

مثال زیر استفاده از توابع تراکمی را نشان می‌دهد.

توجه داشته باشید که دوگام نشان داده شده در این مثال، بسیار شبیه Sawplan عبارت Group by در مثال قبلی می‌باشد.

به دلیل این که پرس‌و‌جوشامل یک تراکم ستونی است، SQL Server یک متغیر داخلی را جهت ذخیره نتیجه تابع تراکمی تعریف می‌کند.

بعد از این که تمام سطرهای جدول با توجه به نوع تابع محاسبه می‌شوند (گام یک) ، مقدار نهایی از متغیر جهت برگرداندن نتیجه انتخاب می‌شود تراکم برداری “Vertor agyregate” هنگامی که یک عبارت Group by در پرس و جویی که شامل یک تابع تراکمی است، استفاده می‌شود، تابع تراکمی برای هر گروه یک مقدار را تولید می‌کند.

این مقادیر vector aggreagate (تراکم برداری) نامیده می‌شوند.

عبارت Vector aggregate در Showplan نشان می‌دهد که پرس‌و‌جوشامل یک تراکم برداری بوده است.

در ادامه یک مثال آمده است: FROM TABLE این گام از showplan جدولی ران نشان می‌دهد که پرس‌و‌جودر حال خواندن ازآن می‌باشد.

در اغلب پرسیها در ادامه FROM TABLE نام جدولی می‌آید که قرار است سطرهایی از آن انتخاب شود.

در دیگر موارد، این عبارت ممکن است نشان دهنده این باشد که انتخاب‌ها از یک جدول موقت می‌باشد که در ادامه در این مورد بحث خواهد شد) .

اهمیت اصلی بررسی نام جداول که خروجی FROM TABLE می‌باشند در این است که ترتیب الحاق جدول رامشخص می کند.

ترتیبی که جداول در خروجی Show plan لیست می‌شوند، همان ترتیبی است که آنها با یکدیگر الحاق می‌شوند؛ این ترتیب ممکن است با ترتیب مشخص شده بعد از FROM در پرس‌و‌جواصلی متفاوت باشد و یا حتی ترتیبی که در عبارت where آمده است این به دلیل آن است که بهینه‌ساز تمام ترتیب‌های ممکن از الحاق را بررسی می‌کند و ترتیبی را انتخاب می‌کند که کمترین مقدار I/O را نیاز دارد.

این مثال نشان می دهد که ترتیبیکه SQL Server برای الحاق جداول انتخاب می کند، ممکن است ترتیی نباشد که درعبارت FROM یا where در پرس و جوی اصلی امده است.

این سطر با جدوال Titleauthor الحاق می‌شوند.

با استفاده از عبارت الحاق ( با استفاده از عبارت الحاق ( TO TABLE هنگامی که بااجرای یک دستور سعی می شود تغییراتی بر روی یک یا چندین سطراز جدولایجاد شود، نظیر DELETE, INSERT ، UPDATE یا SELECT INTO ، عبارت TO TABLE جدول مقصد را که قرر است تغییرات بر روی ان اعمال شود، نشان می‌دهد.

برای برخی از اعمال که نیازمند اجرای یک گام میانی جهت درج سطرها در یک جدول موقت است ( که قبلا درمورد آن توضیح داده شد) ،‌TO TABLE نتایجی را که از جدولکاربر به جدول Worktable منتقل می شوند را نشان می دهد.

مثال زیر استفاده از عبارت To TABLE نتایجی را که از جدول کاربر به جدول Worktable منتقل می شوند رانشان می‌دهد.

مثال زیر استفاده از عبارت To TABLE را نشان می‌دهد.

توجه داشته باشید که Shaw plan برای دومین پرس و جو نشان می‌دهد که جدول ناشیر (publisher table) هم برای FROM TABLE و هم برای TO TABLE استفاده می‌شود.

در مورد اعمال UPPATE ، بهینه‌ساز نیاز دارد جدولی که شامل سطرها برای بروزرسانی است را بخواند، که این نتیجه در عبارت “FROM TABLE” ظاهر می شود و سپس سطرها را تغییر می دهد که نتیجه آن نیز در عبارت “TO TABLE” ظاهر می شود.

جدول موقت “Worktable” بعضی از انواع پرس‌و‌جوها .

نظیر آن پرس‌و‌جوهایی که باید نتایج را به صورت گروه‌بندی شده نشان دهند، بهینه ساز پرس‌و‌جوSQL Server ممکن است تشخیص دهد که ایجاد یک جدول موقت ضروری است، جدول موقت جهت نگهداری نتایج میانی (Intermediate) پرس‌و‌جوها استفاده می شود.

زمانی که سطرهای نتیجه باید مرتب و یا گروه‌بندی شود و سپس نتایج نهایی از جدول موقت انتخاب شوند.

هنگامی که تمام نتایج برگردانده می شوند، جدول موقت خود به خود حذف (drop) خواهد شد.

جداول موقت همیشه در پایگاه داده Tempdb ساخته می شوند.

از آنجا که بهینه‌ساز پرس‌و‌جواین جداول موقت را جهت استفاده خودش ایجاد می‌کند، نام این جداول در جدول tempdb.

Sy sobjects لیست نخواهند شد.

هنگامی که پرس‌و‌جوها شامل عبارت Graup by باشند این جداول موقت همیشه موردنیاز خواهند بود.

برای پرس و جوهایی که با Group by درگیر هستند، ممکن است Ordering بدون استفاده از جدول موقت انجام شود.

اگریک شاخص خوشه‌بندی شده (Clustered Index) بر روی ستون‌هایی که در عبارت Order by آمده است، وجود داشته باشد.

بهینه‌ساز می داند که سطرها به صورت مرتب ذخیره شده‌اند، به همین دلیل مرتب کردن سطرها در یک جدول موقت ضروری ندارند (اگر چه استثناهایی در این مورد وجود دارد، که به ترتیب مرتب‌سازی نصب شده بر روی سرور وابسته است).

از آنجا که داده برای شاخص‌های غیر خوشه‌بندی شده (nonclustered indexes) به صورت مرتب ذخیره نمی‌شوند،‌اگر کم هزینه‌ترین طرح دسترسی با استفاده از شاخص غیر خوشه‌بندی شده است، جدول موقت ضروری نخواهد بود.

به هر حال، اگر بهینه‌ساز تشخیص دهد که پویش تمام جدول I/O کمتری نسبت به استفاده از یک شاخص غیر خوشه‌بندی شده دارد، آنگاه یک جدول موقت برای مرتب‌ سازی نتایج ایجاد خواهد شد.

مثال زیر استفاده از جدول کاری را نشان می‌ هد.

در سومین مثال بالا، توجه داشته باشید که هیچ جدول موقتی برای عبارت Oder by ایجاد نمی شود.

این به دلیل این است که بر روی ستون Quthors – a – id یک شاخص خوشه‌بندی شده وجود دارد،بنابراین داده‌های موجود بر مبنای مقدار au – id مرتب شده است، ودیگر مرتب‌سازی برای Order by ضرورتی ندارد.

در دومین مثال، بر روی ستون‌های au – lname و au – fname یک شاخص غیر خوشه بندی شده ترکیبی وجود دارد.

بنابراین از آنجا که بهینه از از شاخص تعریف شده استفاده نمی‌کند، به دلیل ترتیب‌مرتب‌سازی بر مبنای SQL Server جهت انجام عمل مرتب سازینیاز به ایجاد یک جدول موقت می‌باشد.

جدول موقت ایجاد شده برای SELECT INTO “Worktable created for SELECT-INTO" عمل SLECT – INTO در SQL Server دو کار را انجام می‌دهد: ابتدا یک جدول ایجاد می کند که ساختار آن دقیقا یکسان با جدولی است که قرار است سطرها را از آن انتخاب شود، و سپس سطرهایی که در شرط where صدق می‌کنند (در صورت وجود عبارت Where) در جدول جدید درج می‌شوند، عبارت Worktabel created for SELECT _ INTO کمی گمراه کننده است، در صورتی که جدول موقت در حقیقت یک جدول فیزیکی جدید است که ایجاد می‌شود.

برخلاف دیگر جداول موقت، هنگامی که اجرای پرس‌و‌جوتمام می‌شود این جدول موقت حذف نمی‌شود.