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

سلام به همه! اخیراً با مشکلی روبرو شدم که در آن زمان زیادی طول کشید تا یک سند پردازش شود.

داده های ورودی: پیکربندی "مدیریت شرکت تولیدی، نسخه 1.3 (1.3.52.1)"، سند "سفارش پرداخت ورودی". شکایت: نگهداری در پایگاه داده کار 20-30 ثانیه طول می کشد که جالب است، در یک نسخه از پایگاه داده همان سند به مدت 2-4 ثانیه نگه داشته می شود. بررسی ها و دلیل این رفتار را در ادامه بخوانید.

بنابراین، با کمک اندازه گیری عملکردمن فکر می کنم همه می دانند چگونه از آن استفاده کنند، مقصر پیدا شد:

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

با توجه به اندازه گیری های عملکرد، این عملیات 13 ثانیه طول کشید، اگر میانگین را محاسبه کنید، 0.5 ثانیه به ازای هر ثبت، یک ابدیت دریافت می کنید.

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

نمایه آمار Showplan
نمایه آماری Showplan XML
RPC تکمیل شد
SQL:BatchCompleted.

در تنظیمات ردیابی وجود دارد فیلتر توسط SPID:

SPID شناسه فرآیند سرور پایگاه داده است. در مورد 1C، اساساً یک اتصال بین سرور 1C و DBMS است که می توانید آن را در کنسول مدیریت سرورهای 1C در ستون "اتصال به DBMS" مشاهده کنید.

نمایش داده می شود اگر این لحظهاتصال به پایگاه داده توسط جلسه ضبط می شود: یا یک تماس DBMS در حال انجام است، یا یک تراکنش باز است، یا شی "Temporary Table Manager" نگهداری می شود، که در آن حداقل یک جدول موقت ایجاد شده است.

بیایید یک پردازش برای نگهداری SPID بنویسیم، شامل یک رویه است:

مهم است که شی اتصال نگه داشته شده، در مورد ما مدیر جدول موقت، به عنوان یک متغیر پردازش تعریف شود. پردازش را باز می کنیم، رویه را اجرا می کنیم و تا زمانی که باز است، SPID ثابت می شود. کنسول مدیریت سرور 1C را باز کنید:

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

چیزی که توجه من را جلب کرد، تعداد مطالعه بود - 1872578 ، اما من فوراً هیچ اهمیتی به این قائل نشدم و شروع کردم به فهمیدن اینکه اینجا چه کاری و با کدام جدول انجام می شود.

exec sp_executesql <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))’,N’@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)’,0x8A2F00155DBF491211E87F56DD1A416E,’4018-05-31 23:59:59′,0x00000000000000000000000000000000,’4018-05-31 23:59:59′,0x9A95A0369F30F8DB11E46684B4F0A05F,’4018-05-31 23:59:59"

همانطور که از پرس و جوی SQL می بینید، جدول پردازش می شود "AccRg1465"این جدول در ثبت حسابداری خود پشتیبان است. نمایش متنی طرح اجرای پرس و جو:

همانطور که از برنامه اجرای کوئری SQL می بینید، هیچ اتفاق بدی نمی افتد، جدول " AcRg1465"، جستجوی فهرست خوشه ای در همه جا استفاده می شود.

من همچنین مشکلی در طرح گرافیکی ندیدم، اگرچه به نظرم خیلی پف کرده بود - یک ادغام و دو حلقه تو در تو بدون دلیل ظاهری وجود داشت. این تعداد قرائت و زمان غول پیکر اعدام از کجا می آید؟

همانطور که در بالا ذکر شد، مشکل در یک نسخه جدید از پایگاه داده تکثیر نشد، پس از اینکه مشکل در آن ظاهر شد، کپی از پایگاه داده کار گرفته شد، بنابراین تصمیم گرفته شد که رفتار آن در SQL Server Profiler در همان سند تجزیه و تحلیل شود.
در اینجا نتایج آمده است:

متن پرس و جو در SQL:

EXEC sp_executesql N"SELECT TOP 1 0x01 FROM dbo._AccRg1465 T1 WHERE (T1._RecorderTRef = 0x0000022D AND T1._RecorderRRef = @P1) AND ((((T1._Period<= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))" , N"@P1 varbinary(16)،@P2 datetime2(3)،@P3 varbinary(16)،@P4 datetime2(3)،@P5 varbinary(16)،@P6 datetime2(3)", 0x8A2F00155DBF491211E87F56DD1A416E, "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , 0x9D9A930F "40 18-05-31 23:59:59"

نمایش گرافیکی طرح پرس و جو:

متون درخواست یکسان است، برنامه های اجرایی کاملاً متفاوت است. قضیه چی میتونه باشه؟ من در مورد آمار در SQl اشتباه کردم، اما آنها بین کار و کپی پایگاه داده یکسان هستند و آمار برای هر جدول در پایگاه داده ذخیره می شود:

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

DBCC FLUSHPROCINDB(< database_id >)

جایی که< database_id >شناسه پایگاه داده است. برای پیدا کردن شناسه پایگاه داده باید اسکریپت را اجرا کنید

نام، database_id را از sys انتخاب کنید. پایگاه های داده

لیستی از پایگاه های داده و شناسه های آنها را به ما برمی گرداند.

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

نمایش متنی طرح پرس و جو:

نمایش گرافیکی طرح پرس و جو:

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

با تشکر از توجه شما!

آیا این مقاله به شما کمک کرد؟

6 پاسخ

راه های مختلفی برای دریافت برنامه اجرایی وجود دارد که به شرایط شما بستگی دارد. معمولاً می‌توانید از SQL Server Management Studio برای دریافت طرح استفاده کنید، با این حال، اگر به دلایلی نتوانستید درخواست خود را در SQL Server Management Studio اجرا کنید، ممکن است دریافت طرح از طریق SQL Server Profiler یا بررسی طرح مفید باشد. حافظه پنهان

روش 1 - استفاده از SQL Server Management Studio

SQL Server دارای برخی ویژگی‌های منظم است که جمع‌آوری یک برنامه اجرایی را آسان می‌کند، فقط مطمئن شوید که آیتم منوی "Include Actual Execution Plan" (که در منوی Query یافت می‌شود) علامت زده شده است و برنامه شما به طور معمول اجرا می‌شود.

اگر می‌خواهید برنامه اجرای دستورات را در یک رویه ذخیره شده دریافت کنید، رویه ذخیره شده را مانند زیر اجرا کنید:

Exec p_ مثال 42

هنگامی که پرس و جو شما کامل شد، یک برگه Execution Plan اضافی را در صفحه نتایج خواهید دید. اگر تأییدیه های زیادی را اجرا کرده اید، ممکن است طرح های زیادی را در این برگه مشاهده کنید.

در اینجا می توانید طرح اجرا را در SQL Server Management Studio بررسی کنید یا روی پلن کلیک راست کرده و گزینه Save Execution Plan As... را انتخاب کنید تا برنامه در یک فایل XML ذخیره شود.

روش 2 - استفاده از گزینه های SHOWPLAN

این روش بسیار شبیه به روش 1 است (این در واقع همان کاری است که SQL Server Management Studio به صورت داخلی انجام می دهد)، با این حال من آن را برای کامل بودن یا اگر شما SQL Server Management Studio را در دسترس ندارید، اضافه کردم.

قبل از اجرای کوئری، اجرا کنید یکیعملگرهای زیر عبارت باید تنها عبارت در بسته باشد، i.e. شما نمی توانید دستور دیگری را همزمان اجرا کنید:

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS XML ON -- گزینه پیشنهادی برای استفاده است

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

پس از اتمام کار، می توانید این گزینه را با عبارت زیر غیرفعال کنید:

تنظیم<

مقایسه فرمت های طرح اجرا

اگر ترجیح قوی دارید، توصیه می کنم از گزینه STATISTICS XML استفاده کنید. این گزینه معادل گزینه "Include Actual Execution Plan" در SQL Server Management Studio است و بیشترین اطلاعات را در مفیدترین فرمت ارائه می کند.

SHOWPLAN_TEXT - برنامه اجرای تخمینی مبتنی بر متن را بدون اجرای پرس و جو نمایش می دهد.
SHOWPLAN_ALL - یک برنامه اجرایی تخمینی مبتنی بر متن را با برآورد هزینه بدون اجرای پرس و جو نمایش می دهد.
SHOWPLAN_XML - یک طرح اجرای تخمینی مبتنی بر XML را با برآورد هزینه بدون اجرای پرس و جو نمایش می دهد. این معادل گزینه "نمایش طرح اجرای نمونه..." در SQL Server Management Studio است.
مشخصات آماری - پرس و جو را اجرا می کند و برنامه اجرای واقعی را بر اساس متن نمایش می دهد.
STATISTICS XML - پرس و جو را اجرا می کند و برنامه اجرای واقعی را بر اساس XML نمایش می دهد. این معادل گزینه "Include Actual Execution Plan" در SQL Server Management Studio است.

روش 3 - استفاده از SQL Server Profiler

اگر نمی توانید پرس و جو را مستقیماً اجرا کنید (یا هنگام اجرای مستقیم پرس و جو به کندی اجرا نمی شود - به یاد داشته باشید که ما می خواهیم طرح پرس و جو ضعیف اجرا شود)، می توانید با استفاده از SQL Server Profiler طرح را ضبط کنید. ایده این است که درخواست خود را در حالی اجرا کنید که ردیابی یکی از رویدادهای "Showplan" در حال اجرا است.

لطفا توجه داشته باشید که بسته به بار شما تو می توانیاز این روش در محیط تولید استفاده کنید، اما بدیهی است که باید احتیاط کنید. مکانیسم های پروفایل SQL Server برای به حداقل رساندن تأثیر بر پایگاه داده طراحی شده اند، اما این بدان معنا نیست که تأثیری بر عملکرد نخواهد داشت. همچنین ممکن است در فیلتر کردن و تعیین طرح صحیح در ردیابی خود مشکل داشته باشید اگر پایگاه داده شما تحت استفاده زیاد باشد. بدیهی است که باید با DBA خود چک کنید تا مطمئن شوید که از انجام این کار در پایگاه داده ارزشمند خود راضی هستند!

SQL Server Profiler را باز کنید و یک Trace جدید ایجاد کنید که به پایگاه داده مورد نظر متصل می شود که می خواهید ردیابی را از آن ضبط کنید.
در تب Event Selection، چک باکس Show all events را علامت بزنید، خط Performance -> Showplan XML را علامت بزنید و ردیابی را اجرا کنید.
در حالی که ردیابی در حال اجرا است، هر کاری را که نیاز دارید انجام دهید تا پرس و جو کند اجرا شود.
صبر کنید تا درخواست تکمیل شود و ردیابی متوقف شود.
برای ذخیره ردیابی، روی طرح xml در پروفایل SQL Server کلیک راست کرده و "Extract Event Data..." را انتخاب کنید تا طرح در یک فایل XML ذخیره شود.

طرحی که دریافت می کنید معادل گزینه "Include Actual Execution Plan" در SQL Server Management Studio است.

روش 4 - بررسی کش Query

اگر نمی‌توانید پرس و جو خود را مستقیماً اجرا کنید و همچنین نمی‌توانید ردیابی نمایه‌گر را ضبط کنید، همچنان باید بتوانید با بررسی طرح کش SQL Query یک طرح تخمینی دریافت کنید.

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

UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan)(plan_handle) را انتخاب کنید

این پرس و جو را اجرا کنید و روی طرح XML کلیک کنید تا طرح در یک پنجره جدید باز شود - راست کلیک کرده و "Save Execution Plan As..." را انتخاب کنید تا طرح در یک فایل با فرمت XML ذخیره شود.

یادداشت:

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

شما نمی توانید برنامه اجرا را برای رویه های ذخیره شده رمزگذاری شده انجام دهید.

برنامه های اجرایی "واقعی" و "تخمینی".

طرح اجرای واقعی برنامه ای است که در آن SQL Server در واقع پرس و جو را اجرا می کند، در حالی که برنامه اجرای تخمینی SQL Server بر روی آنچه می تواند بدون اجرای پرس و جو انجام دهد کار می کند. اگرچه از نظر منطقی معادل است، اما طرح اجرای واقعی بسیار مفیدتر است زیرا حاوی داده ها و آمارهای اضافی در مورد آنچه واقعاً هنگام اجرای پرس و جو اتفاق افتاده است. این هنگام تشخیص مشکلات در زمانی که ارزیابی های سرور SQL غیرفعال هستند (به عنوان مثال، زمانی که آمار قدیمی هستند) مهم است.

چگونه طرح اجرای پرس و جو را تفسیر کنیم؟

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

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

اعلام @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help "sys.objects" /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

ابزار مورد علاقه من برای به دست آوردن و تجزیه و تحلیل عمیق طرح های اجرای پرس و جو است SQL Sentry Plan Explorer. برای تجزیه و تحلیل دقیق و تجسم برنامه های اجرایی بسیار راحت تر، کاربر پسندتر و کاملتر از SSMS است.

در اینجا یک صفحه مثال برای شما آورده شده است تا بفهمید این ابزار چه عملکردی ارائه می دهد:

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

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

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

می‌توانید برنامه ApexSQL را در استودیوی مدیریت سرور SQL نصب و ادغام کنید، بنابراین برنامه‌های اجرایی می‌توانند مستقیماً از طریق SSMS مشاهده شوند.

برنامه های اجرایی پیش بینی شده را در ApexSQL Plan مشاهده کنید

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

نمودار طرح اجرا، برگه برنامه ریزی اجرا را در قسمت نتایج نشان می دهد. سپس بر روی برنامه اجرا کلیک راست کرده و از منوی زمینه گزینه "Open in ApexSQL Plan" را انتخاب کنید.

طرح اجرای برآورد شده در برنامه ApexSQL باز می شود و می تواند برای بهینه سازی پرس و جوها تجزیه و تحلیل شود.

مشاهده برنامه های اجرایی واقعی در یک برنامه ApexSQL

برای مشاهده طرح اجرای واقعی پرس و جو، به مرحله دوم که قبلا ذکر شد بروید، اما اکنون، پس از ظاهر شدن طرح تخمینی، روی دکمه "واقعی" در نوار نوار اصلی در ApexSQL Plan کلیک کنید.

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

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

طرح های پرس و جو را می توان از یک جلسه رویدادهای گسترده از طریق رویداد query_post_execution_showplan به دست آورد. در اینجا یک نمونه جلسه XEvent آورده شده است:

/* از طریق الگوی "ردیابی جزئیات پرس و جو" ایجاد شد. */ CREATE EVENT SESSION در سرور اضافه کردن رویداد sqlserver.query_post_execution_showplan(ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_serversqlt,sqlserver.query_plan_serversqlt,sql server.tsql _frame,sqlserver.tsql_stack))، / * هر یک از رویدادهای زیر را به دلخواه حذف کنید (یا رویدادهای اضافی را اضافه کنید). */ ADD EVENT sqlserver.error_reported(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_server.query_ssionlsserver. ,sqlserv er.tsql_frame,sqlserver.tsql_stack ) WHERE (.(.،(4)) و .(.،(0))))، ADD EVENT sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_serverid, .plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_frame,sqlserver.tsql_stack) WHERE (.(.)(4.) sqlserver.rpc_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash.qsession_servlts,sqlserver.query_plan_hash. ql _frame,sqlserver.tsql_stack) WHERE (. ( .,(4)) AND .(.,(0)))، ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.databaleserver.sqlserver.sqlserver. query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.،(4)) AND .(.،(0) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sql_server.session_id,sql_server. l_stack) WHERE (.(.،( 4 )) و. (.، (0))))، افزودن رویداد SQLSERVER.SQL_STATEMENT_COMPLETED (عملیات (PACKAGE0.Event_seQUENCE، SQLSERVER.CLIENT_APP_NAME، SQLSERVER.DATABASE_ID، SQLSERVER.P LANQRY_PRY_HANDE. HASH، SQLSERVER. SESSION_ID، sqlserver.sql_text، sqlserver.tsql_frame، sqlserver.tsql_stack) WHERE (.(.،(4)) و .(.،(0))) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY_DE_ALL_ENTS) از دست دادن، MAX_DISPATCH_ تأخیر =30 ثانیه، MAX_EVENT_SIZE=0 کیلوبایت، MEMORY_PARTITION_MODE=هیچ‌کدام، TRACK_CAUSALITY=روشن،STARTUP_STATE=خاموش) رفتن

پس از ایجاد جلسه (در SSMS)، به Object Browser بروید و به Manage | بروید رویدادهای گسترده | جلسات. روی جلسه «GetExecutionPlan» کلیک راست کرده و آن را اجرا کنید. روی آن کلیک راست کرده و «Watch Live Data» را انتخاب کنید.

سپس یک پنجره پرس و جو جدید باز کنید و یک یا چند کوئری را اجرا کنید. در اینجا یکی برای AdventureWorks وجود دارد:

از AdventureWorks استفاده کنید. GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesASOrderProIDYDt . نام محصول DESC; برو

پس از یک یا دو دقیقه، برخی از نتایج را در برگه "GetExecutionPlan: Live Data" مشاهده خواهید کرد. یکی از رویدادهای query_post_execution_showplan را در شبکه انتخاب کنید و سپس روی زبانه Query Plan در زیر شبکه کلیک کنید. باید چیزی شبیه به این باشد:

ویرایش کنید: کد و اسکرین شات XEvent از SQL/SSMS 2012 w/SP2 ایجاد شده است. اگر از SQL 2008/R2 استفاده می کنید، می توانید یک اسکریپت برای اجرای آن تنظیم کنید. اما این نسخه رابط کاربری گرافیکی ندارد، بنابراین باید فایل showplan XML را استخراج کنید، آن را به عنوان یک فایل *.sqlplan ذخیره کرده و در SSMS باز کنید. دست و پا گیر است. XEvents در SQL 2005 یا قبل از آن وجود نداشت. بنابراین، اگر شما در SQL 2012 یا جدیدتر نیستید، من یکی از پاسخ های دیگری را که در اینجا پست شده است را پیشنهاد می کنم.

اشتراک گذاری

بهینه سازی پرس و جو در SQL Server 2005، آمار پایگاه داده SQL Server 2005، CREATE STATISTICS، UPDATE STATISTICS، SET NOCOUNT ON، طرح های اجرای پرس و جو، تعداد خواندن های منطقی، نکات بهینه ساز، MAXDOP، OPTIMIZE FOR، tutorials (plangu_planate guides)

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

بسیاری از متخصصان نگرش مبهم نسبت به بهینه سازی پرس و جو دارند. از یک طرف، عملکرد ماژول نرم افزار Query Optimizer، که طرح های اجرای پرس و جو را تولید می کند، باعث انتقادات منصفانه بسیاری در SQL Server 2000 و SQL Server 2005 می شود. Query Optimizer اغلب بهینه ترین طرح های اجرای پرس و جو را انتخاب نمی کند و در برخی شرایط از دست می دهد. به ماژول های مشابه از Oracle و Informix. از سوی دیگر، بهینه سازی پرس و جو دستی یک فرآیند بسیار کار فشرده است. شما می توانید زمان زیادی را برای چنین بهینه سازی صرف کنید و در نهایت متوجه شوید که هیچ چیز بهینه نشده است: طرح پیشنهادی Query Optimizer در ابتدا بهینه ترین بود (این در اکثر موارد اتفاق می افتد). علاوه بر این، ممکن است اتفاق بیفتد که طرح اجرای پرس و جو که به صورت دستی ایجاد کرده اید پس از مدتی (پس از افزودن اطلاعات جدید به پایگاه داده) بهینه نباشد و عملکرد را هنگام اجرای پرس و جو کاهش دهد.

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

آمار- این اطلاعات خدمات ویژه در مورد توزیع داده ها در ستون های جدول است. بیایید تصور کنیم، برای مثال، یک پرس و جو در حال اجرا است که باید همه ایوانوف های ساکن در شهر سن پترزبورگ را برگرداند. فرض کنید 90 درصد رکوردهای این جدول دارای مقدار یکسانی در ستون هستند شهر - "سن پترزبورگ". البته از نظر اجرای پرس و جو ابتدا سود بیشتری دارد که تمام ایوانف ها را در جدول انتخاب کنید (بدیهی است که 90٪ نخواهند بود) و سپس مقدار ستون را بررسی کنید. شهربرای هر رکورد انتخاب شده با این حال، برای اطلاع از نحوه توزیع مقادیر در یک ستون، ابتدا باید یک پرس و جو را اجرا کنید. بنابراین، SQL Server به طور مستقل اجرای چنین پرس و جوهایی را آغاز می کند و سپس اطلاعات مربوط به توزیع داده ها (که آمار نامیده می شود) را در جداول سرویس پایگاه داده ذخیره می کند.

برای پایگاه داده های SQL Server 2005، تنظیمات پیش فرض هستند AUTO_CREATE_STATISTICSو AUTO_UPDATE_STATISTICS. در این صورت، آمار ستون های پایگاه داده به صورت خودکار ایجاد و به روز می شود. برای بزرگ‌ترین و مهم‌ترین پایگاه‌های داده، ممکن است عملیات ایجاد و به‌روزرسانی آمار با تجربه کاربر فعلی تداخل داشته باشد. بنابراین، برای چنین پایگاه هایی، گاهی اوقات این پارامترها غیرفعال می شوند و عملیات ایجاد و به روز رسانی آمار به صورت دستی در شب انجام می شود. دستورات استفاده شده برای این عبارتند از ایجاد آمارو به روز رسانی آمار.

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

اولین کاری که باید انجام دهید این است که پرس و جوهایی را پیدا کنید که در درجه اول در معرض بهینه سازی هستند. ساده ترین راه برای انجام این کار با کمک یک پروفایلر، تنظیم یک فیلتر برای مدت زمان درخواست (فیلتر مدت زماندر پنجره ویرایش کنیدفیلتر کنید(ویرایش فیلتر)، که با استفاده از دکمه باز می شود ستونفیلترهاروی زبانه مناسبت هاانتخابپنجره ویژگی های جلسه ردیابی). به عنوان مثال، نامزدهای بهینه سازی ممکن است شامل پرس و جوهایی باشند که زمان اجرای آنها بیش از 5 ثانیه بوده است. همچنین می توانید از اطلاعات پرس و جو ارائه شده توسط Database Tuning Advisor استفاده کنید.

سپس باید بررسی کنید که آیا پارامتر برای اتصالات، رویه‌های ذخیره شده و عملکردهای شما تنظیم شده است یا خیر NOCOUNT. با استفاده از دستور می توانید آن را نصب کنید NOCOUNT را روشن کنید. هنگام تنظیم این پارامتر، ابتدا بازگشت از سرور و نمایش اطلاعات مربوط به تعداد سطرها در نتایج پرس و جو غیرفعال می شود (یعنی ردیف "N ردیف(های) تحت تاثیر قرار گرفت"روی زبانه پیام ها(C messages) پنجره هایی برای کار با کد هنگام اجرای درخواست در مدیریت استودیو). ثانیاً، انتقال پیام سرور خاص غیرفعال است DONE_IN_PROC، که به طور پیش فرض برای هر مرحله رویه ذخیره شده برگردانده می شود. هنگام فراخوانی بیشتر رویه های ذخیره شده، فقط به نتیجه اجرای آنها نیاز دارید و هیچکس به تعداد ردیف های پردازش شده برای هر مرحله اهمیت نمی دهد. بنابراین، تنظیم پارامتر NOCOUNTبرای رویه های ذخیره شده می تواند عملکرد آنها را به طور جدی بهبود بخشد. سرعت اجرای پرس و جوهای معمولی نیز افزایش می یابد، اما به میزان کمتر (تا 10٪).

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

ساده ترین راه برای مشاهده طرح اجرای پرس و جو از SQL Server Management Studio است. برای به دست آوردن اطلاعات در مورد طرح اجرای پرس و جو مورد انتظار می توانید از منو استفاده کنید پرس و جو(پرس و جو) دستور را انتخاب کنید نمایش دادنتخمین زدهاجراطرح(نمایش طرح اجرای مورد انتظار). اگر می خواهید برنامه واقعی برای اجرای یک پرس و جو را بدانید، می توانید قبل از اجرای آن پارامتر را در همان منو تنظیم کنید. عبارتند ازواقعیاجراطرح(شامل طرح اجرای واقعی). در این صورت پس از اجرای کوئری، تب دیگری در پنجره نتایج در SQL Server Management Studio ظاهر می شود. اجراطرح(Execution Plan) که طرح اجرای پرس و جو واقعی را نمایش می دهد. هنگامی که ماوس خود را روی هر یک از مراحل قرار می دهید، می توانید اطلاعات بیشتری در مورد آن دریافت کنید (شکل 11.15).

برنج. 11.15.طرح اجرای کوئری در استودیوی مدیریت سرور SQL

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

q ابتدا در پنجره Management Studio دستور را اجرا کنید SET STATISTICS IO ON. در نتیجه پس از هر بار اجرای درخواست، اطلاعات تکمیلی نمایش داده می شود. در آن ما به مقدار تنها یک پارامتر علاقه مند هستیم - منطقی می خواند. این پارامتر به معنی تعداد خوانده‌های منطقی هنگام اجرای پرس‌و‌جوها است، یعنی چند عملیات خواندنی باید هنگام اجرای یک پرس‌وجو بدون در نظر گرفتن تأثیر حافظه پنهان (تعداد خواندن از حافظه پنهان و دیسک) انجام شود. این مهمترین پارامتر است. تعداد خوانده‌های فیزیکی (فقط از روی دیسک خوانده می‌شود) اطلاعات چندان نشان‌دهنده‌ای نیست، زیرا بستگی به این دارد که آیا دسترسی‌های قبلی به این جداول وجود داشته است یا خیر. آمار زمان نیز متغیر است و به عملیات دیگری که سرور در آن زمان انجام می دهد بستگی دارد. اما تعداد قرائت های منطقی عینی ترین شاخص است که کمترین تأثیر را از عوامل اضافی می گیرد.

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

نکات بهینه ساز زیادی در SQL Server 2005 وجود دارد. می توانید اطلاعات مربوط به آنها را در Books Online (در لیست موجود در برگه) بخوانید فهرست مطالب(شاخص) باید انتخاب شود پرس و جونکات [SQLسرور](نکات پرس و جو) پیوستننکات(پیوستن به نکات) یا جدولنکات [SQLسرور](نکات جدول)). رایج ترین نکات مورد استفاده عبارتند از:

q بدون قفل, ROWLOCK, PAGLOCK, TABLOCK, HOLDLOCK, READCOMMITTEDLOCK, UPDLOCK, XLOCK- این نکات برای مدیریت قفل ها استفاده می شود (به بخش 11.5.7 مراجعه کنید);

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

q دستور اجباری- پیوستن جداول هنگام اجرای پرس و جو دقیقاً به ترتیبی که این جداول در پرس و جو فهرست شده اند انجام می شود.

q MAXDOP(از Maximum Degree of Parallelism - حداکثر درجه موازی سازی یک درخواست) - با استفاده از این اشاره، حداکثر تعداد پردازنده هایی که می توان برای اجرای درخواست استفاده کرد نشان داده می شود. به طور معمول این اشاره در دو موقعیت استفاده می شود:

· زمانی که به دلیل جابجایی بین پردازنده ها ( متن نوشتهسوئیچینگ) سرعت اجرای پرس و جو بسیار کاهش یافته است. این رفتار برای SQL Server 2000 در سیستم های چند پردازنده معمولی بود.

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

q بهینه سازی برای- این راهنمایی به شما امکان می دهد تعیین کنید که درخواست برای مقدار خاصی از پارامتر ارسال شده به آن بهینه شده باشد (مثلاً برای مقدار فیلتر برای جایی که);

q استفاده از طرح- این قدرتمندترین فرصت است. با استفاده از چنین اشاره ای، می توانید به صراحت طرح اجرای پرس و جو را با ارسال طرح به عنوان یک مقدار رشته در قالب XML تعریف کنید. اشاره استفاده از طرحفقط در SQL Server 2005 ظاهر شد (در نسخه های قبلی امکان تعریف صریح برنامه های اجرای پرس و جو وجود داشت، اما این با استفاده از ابزارهای دیگر انجام می شد). یک طرح در قالب XML می تواند به صورت دستی نوشته شود، یا می توان آن را به صورت خودکار تولید کرد (به عنوان مثال، با کلیک راست بر روی صفحه گرافیکی با طرح اجرا نشان داده شده در شکل 11.15 و انتخاب دستور در منوی زمینه صرفه جوییاجراطرحمانند(ذخیره برنامه اجرا به عنوان)).

SQL Server 2005 یک ویژگی مهم جدید را معرفی می کند که به شما امکان می دهد به صورت دستی طرح اجرای پرس و جو را بدون نیاز به دستکاری در متن پرس و جو تغییر دهید. اغلب اتفاق می افتد که کد درخواست را نمی توان تغییر داد: در کد برنامه کامپایل شده سخت کدگذاری شده است. برای مقابله با این مشکل، SQL Server 2005 رویه ذخیره شده را معرفی کرد sp_create_plan_guide. این اجازه می دهد تا شما را به ایجاد به اصطلاح راهنمای برنامه های اجرایی (طرحراهنماها) که به طور خودکار برای درخواست های منطبق اعمال می شود.

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

q هر چند وقت یکبار عملیات در طرح های اجرای پرس و جو ظاهر می شود جدولاسکن کنید(اسکن کامل جدول). ممکن است به خوبی معلوم شود که دسترسی به جدول با استفاده از شاخص ها کارآمدتر خواهد بود.

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

q آیا کد از جداول موقت استفاده می کند یا از نوع داده جدول. ایجاد جداول موقت و کار با آنها به منابع زیادی نیاز دارد، بنابراین در صورت امکان باید از آنها اجتناب کنید.

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

q گاهی اوقات کوئری ها داده های بیشتری از آنچه برنامه واقعاً نیاز دارد (ستون ها یا ردیف های اضافی) برمی گرداند. البته این باعث بهبود بهره وری نمی شود.

q اگر برنامه دستوراتی را به سرور ارسال کند اجرا کردن، پس منطقی است که در مورد جایگزینی آنها با یک فراخوان رویه ذخیره شده فکر کنید sp_executesql. مزایای عملکردی نسبت به یک فرمان معمولی دارد اجرا کردن;

q گاهی اوقات می توان با حذف نیاز به کامپایل مجدد رویه های ذخیره شده و ساختن طرح های جدید اجرای پرس و جو، به بهبود عملکرد دست یافت. باید به استفاده از پارامترها توجه کنید، سعی کنید دستورات DML و DDL را در کد رویه ذخیره شده مخلوط نکنید و مطمئن شوید که پارامترهای اتصال ANSI_DEFAULTS را تنظیم کنید, ANSI_NULLS را تنظیم کنید, ANSI_PADDING را تنظیم کنید, ANSI_WARNINGS را تنظیم کنیدو CONCAT_NULL_YIELDS_NULL را تنظیم کنیدبین درخواست ها تغییر نکرده اند (هر گونه تغییر در چنین پارامترهایی برنامه های اجرایی قدیمی را باطل می کند). به طور معمول، زمانی که این پارامترها در سطح درخواست فردی یا در کد رویه ذخیره شده تنظیم شده باشند، مشکل ممکن است ایجاد شود.

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

طرح اجرای پرس و جوی SQL،یا طرح پرس و جو، دنباله ای از مراحل یا دستورالعمل های DBMS است که برای اجرای پرس و جوی SQL لازم است. در هر مرحله، عملیاتی که این مرحله اجرای پرس و جوی SQL را آغاز کرد، ردیف هایی از داده ها را بازیابی می کند که می توانند نتیجه نهایی را تشکیل دهند یا برای پردازش بیشتر مورد استفاده قرار گیرند. دستورالعمل های طرح اجرای پرس و جوی SQL به عنوان دنباله ای از عملیات نشان داده می شود که توسط DBMS برای دستورات SQL SELECT، INSERT، انجام می شود. حذف و به روز رسانی. محتویات یک طرح پرس و جو معمولاً در یک ساختار درختی نشان داده می شود و شامل اطلاعات زیر است:

ترتیب اتصال منابع داده (جدول، نماها و غیره)؛
روش دسترسی برای هر منبع داده؛
روش های اتصال منابع داده؛
عملیات محدود کردن انتخاب، مرتب‌سازی و تجمیع داده‌ها؛
هزینه و شدت هر عمل؛
استفاده احتمالی از پارتیشن بندی و موازی سازی اطلاعات ارائه شده توسط طرح اجرای پرس و جوی SQL به توسعه دهنده اجازه می دهد تا ببیند بهینه ساز چه رویکردها و روش هایی را برای انجام عملیات SQL انتخاب می کند.

تفسیر طرح اجرای پرس و جوی SQL

تجسم طرح اجرای یک پرس و جو SQL به ابزارها و ابزارهای توسعه بستگی دارد که می توانند بخشی از DBMS باشند که پرس و جوی آن برای تجزیه و تحلیل مورد علاقه است، یا محصولات نرم افزاری جداگانه تجاری یا آزادانه توزیع شده باشند که مستقیماً به یک DBMS خاص مرتبط نیستند. سازنده. استفاده از یک ابزار تجسم طرح پرس و جو معمولاً تأثیر قابل توجهی بر درک آنچه طرح پرس و جو ارائه شده توصیف می کند ندارد. عامل تعیین کننده در فرآیند تحلیل مسیری که بهینه ساز هنگام اجرای یک پرس و جو خاص طی می کند، توانایی تفسیر صحیح اطلاعات ارائه شده در طرح پرس و جو است.

همانطور که قبلاً ذکر شد، طرح پرس و جوی SQL دارای ساختار درختی است که نه تنها توالی اجرای عملیات SQL، بلکه روابط بین این عملیات را نیز توصیف می کند. هر گره در درخت طرح پرس و جو یک عملیات است، مانند یک مرتب سازی، یا یک روش دسترسی به جدول. یک رابطه والد-فرزند بین گره ها وجود دارد. روابط والدین و فرزند تحت قوانین زیر تنظیم می شود:

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

بیایید نگاهی دقیق تر به اطلاعات ارائه شده توسط طرح اجرای پرس و جوی SQL بیندازیم. مثال های ارائه شده در محیط Oracle DBMS انجام شده است. Oracle SQL Developer به عنوان ابزاری برای اجرای کوئری ها و تجسم طرح پرس و جوی SQL استفاده شد. بخشی از طرح پرس و جوی SQL در شکل نشان داده شده است. 10.11.

I ID I عملیات

0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK اطلاعات محصول

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

برنج. 10.11. قطعه ای از طرح اجرای پرس و جوی SQL در محیط Oracle DBMS

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

عملیات 0 ریشه درخت طرح پرس و جو است. ریشه یک فرزند دارد: عملیات 1.

عملیات 1 - عملیات دارای یک فرزند است: عملیات 2.

عملیات 2 - عمل دو فرزند دارد: عملیات 3 و عملیات 6.

عملیات 3 - عمل دو فرزند دارد: عملیات 4 و عملیات 5.

عملیات 4 - عمل بچه ندارد.

عملیات 5 - عمل بچه ندارد.

عملیات 6 - عمل بچه ندارد.

تعامل والد-فرزند بین عملیات طرح پرس و جو در شکل نشان داده شده است. 10.12.

عملیات انجام شده در یک طرح پرس و جو را می توان به سه نوع تقسیم کرد: عملیات اتصال مستقل، عملیات اتصال بدون محدودیت، و عملیات اتصال پیوندی (شکل 10.13).

خود مختار		عملیات غیر مرتبط		عملیات مرتبط
عملیات		انجمن ها		انجمن ها

برنج. 10.12.

برنج. 10.13.

عملیات خودمختار -اینها عملیاتی هستند که حداکثر یک عمل فرزند دارند.

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

2. هر عملیات فرزند فقط یک بار اجرا می شود.
3. هر عملیات فرزند نتیجه خود را به عملیات والد برمی گرداند.

در شکل شکل 10.14 طرح پرس و جو زیر را نشان می دهد:

o.order_id ,o.order_status FROM orders o ORDER BY o.order_status را انتخاب کنید

این پرس و جو فقط شامل عملیات مستقل است.

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

1. طبق قانون دنبال کردن عملیات خودمختار شماره 1، ابتدا عملیات با ID = 2 اجرا می شود.
2. در مرحله بعد، عملیات با ID = 1 انجام می شود.
3. عملیات با ID = 0 انجام می شود مجموعه داده های به دست آمده برگردانده می شود.

عملیات اتحادیه بدون محدودیت

عملیات اتحادیه بدون محدودیتعملیاتی هستند که بیش از یک عملیات فرزند را به طور مستقل اجرا می کنند. مثال: HASH JOIN، MERGE Join، IntersECTION، MINUS، UNION ALL.

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

1. عملیات فرزند قبل از عملیات والد اجرا می شود.
2. عملیات فرزند به صورت متوالی اجرا می شود و با کوچکترین مقدار شناسه عملیات به ترتیب صعودی این مقادیر شروع می شود.
3. قبل از شروع هر عملیات فرزند بعدی، عملیات فعلی باید به طور کامل تکمیل شود.
4. هر عملیات فرزند بدون توجه به سایر عملیات فرزند فقط یک بار اجرا می شود.
5. هر عملیات فرزند نتیجه خود را به عملیات والد برمی گرداند.

در شکل شکل 10.15 طرح پرس و جو زیر را نشان می دهد:

o.order_id را از سفارشات o UNION ALL انتخاب کنید

oi.order_id را از order_items oi انتخاب کنید

این پرس و جو حاوی یک عملیات پیوستن نامحدود است اتحاد همه. دو عملیات باقی مانده مستقل هستند.

برنج. 10.15. عملیات پیوستن بدون محدودیت، طرح پرس و جو


	1 بیانیه انتخاب کنید I

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

1. مطابق با قوانین 1 و 2 زیر عملیات پیوستن غیرمرتبط، عملیات با ID = 2 ابتدا به صورت متوالی خوانده می شود.
2. مطابق با قانون 5، عملیات با ID = 2، ردیف های عملیات والد را با ID = 1 خوانده شده در مرحله 1 برمی گرداند.
3. عملیات با ID = 3 تنها زمانی شروع به اجرا می کند که عملیات با ID = 2 به پایان برسد.
4. پس از اتمام عملیات با ID = 2، عملیات با ID = 3 شروع می شود به ترتیب تمام ردیف های جدول order_item خوانده می شود.
5. مطابق با قانون 5، عملیات با ID = 3، ردیف های عملیات والد را با ID = 1 خوانده شده در مرحله 4 برمی گرداند.
6. عملیات با ID = 1 یک مجموعه نتیجه از داده ها را بر اساس داده های دریافتی از تمام عملیات های فرزند خود (با ID = 2 و ID = 3) تولید می کند.
7. عملیات با ID = 0 انجام می شود مجموعه داده های به دست آمده برگردانده می شود.

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

عملیات پیوند پیوند خورده

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

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

1. عملیات فرزند قبل از عملیات والد اجرا می شود.
2. عملیات فرزند با کمترین شماره عملیات (ID) اجرای عملیات فرزند باقی مانده را کنترل می کند.
3. عملیات فرزند که دارای یک عملیات والد مشترک هستند با کمترین مقدار شناسه عملیات به ترتیب صعودی این مقادیر شروع می شوند. عملیات فرزند باقی مانده به صورت متوالی اجرا نمی شوند.
4. فقط اولین عملیات فرزند یک بار اجرا می شود. تمام عملیات های فرزند دیگر چندین بار یا اصلا اجرا نمی شوند.

در شکل شکل 10.16 طرح پرس و جو زیر را نشان می دهد:

FROM order_items oi, orders about

WHERE o.order_id= oi.order_id

و oi.product_id>100

و o.customer_id بین 100 و 1000

این پرس و جو حاوی یک عملیات پیوستن مرتبط است، NESTED LOOPS.

I ID I عملیات

		انتخاب بیانیه \|

برنج. 10.16. عملیات پیوستن پیوند، طرح پرس و جو

با در نظر گرفتن قوانین پیروی از عملیات اتصال زنجیره ای، ترتیب اجرای آنها به صورت زیر خواهد بود.

1. طبق قوانین 1 و 2 عملیات اتصال زنجیره ای زیر، ابتدا باید عملیات با ID = 2 انجام شود، اما عملیات با 1D = 2 و 1D = 3 مستقل هستند و طبق قانون 1 عملیات مستقل زیر. عملیات با ID = 2 ابتدا ID = 3 انجام می شود. محدوده شاخص ORDCUSTOMERIX بر اساس شرایط زیر مشاهده می شود. شناسه مشتری بین 100 تا 1000
2. عملیات با ID=3 به عملیات والد (با Ш=2) فهرستی از شناسه های ردیف Rowld که در مرحله 1 به دست آمده است، برمی گرداند.
3. عملیات با ID = 2 ردیف هایی را در جدول سفارشات می خواند که در آن مقدار Rowld با لیست مقادیر Rowld به دست آمده در مرحله 2 مطابقت دارد.
4. عملیات با ID = 2 ردیف های خوانده شده عملیات والد (با ID = 1) را برمی گرداند.
5. برای هر سطر برگردانده شده توسط عملیات با ID = 2، دومین عملیات فرزند (با ID = 4) عملیات اجرا می شود. حلقه های تو در تو به این معنا که برای هر سطر برگردانده شده توسط عملیاتی با ID = 2، یک اسکن متوالی کامل از جدول order_items برای یافتن یک تطابق در ویژگی join انجام می‌شود.
6. مرحله 5 به تعداد سطرهای برگردانده شده توسط عملیات با ID = 2 تکرار می شود.
7. عملیات با ID = 1 نتایج عملیات والد (با ID = 0) را برمی گرداند.
8. عملیات با ID = 0 انجام می شود مجموعه داده های به دست آمده برگردانده می شود.

بسته به پیچیدگی پرس و جو تحلیل شده، طرح اجرای آن ممکن است ساختار نسبتاً پیچیده ای داشته باشد که در نگاه اول تفسیر آن دشوار به نظر می رسد. اجرای روشی قوانین شرح داده شده در بالا و تجزیه عملیات به شما این امکان را می دهد که به طور مؤثر برنامه اجرای یک پرس و جوی SQL با هر پیچیدگی را تجزیه و تحلیل کنید. بیایید به نمونه ای از یک پرس و جو نگاه کنیم که فهرستی از مشتریان، تعداد کالاهایی که خریداری کرده اند و هزینه کل آنها را ایجاد می کند:

SELECT s. cust_first_name, نام مشتری،

COUNT (DISTINCT oi.product_id) به عنوان product_qty،

SUM(oi.quantity*oi.unit_price) به عنوان مجموع_هزینه FROM oe.سفارشات o مشتریان JOIN داخلی c ON

o.customer_id=c.customer_id

JOIN داخلی oe.order_items oi ON o.order_id= oi.order_id GROUP BY C. cust_first_name

توالی عملیات این طرح پرس و جو در شکل نشان داده شده است. 10.17.

انتخاب بیانیه I

مرتب سازی گروه بر اساس YG



	دسترسی به جدول کامل است
	اسکن دامنه شاخص
دسترسی به جدول بر اساس INDEX ROWIDd
دسترسی به جدول کامل است

برنج. 10.17. طرح پرس و جو، توالی عملیات

اجازه دهید یک رویکرد ممکن برای تفسیر طرح اجرای کوئری 80b ارائه شده در شکل 1 را شرح دهیم. 10.17. این رویکرد شامل دو مرحله اصلی است: تجزیه عملیات به بلوک و تعیین ترتیب عملیات.

در مرحله اول، لازم است عملیات انجام شده به بلوک ها تجزیه شود. برای انجام این کار، ما تمام عملیات اتحادیه را پیدا می کنیم، i.e. عملیات هایی که بیش از یک عملیات فرزند دارند (در شکل 10.17 اینها عملیات 2، 3 و 4 هستند)، و این عملیات فرزند را به بلوک ها جدا می کنند. در نتیجه، با استفاده از مثال در شکل. 10.17، ما سه عملیات اتحادیه و هفت بلوک عملیات را دریافت می کنیم.

در مرحله دوم، توالی اجرای بلوک های عملیات تعیین می شود. برای انجام این کار، باید قوانین مربوط به عملیات زیر را که در بالا توضیح داده شد، اعمال کنید. اجازه دهید یک سری ملاحظات را در مورد اجرای هر عملیات نسبت به شماره شناسایی آن (III) انجام دهیم.

عملیات Ш = 0 مستقل است و والد عملیات сШ = 1 است.

عملیات Yu = 1 نیز مستقل است. والد عملیات Ш = 2 است و قبل از عملیات Ш = 0 اجرا می شود.

عملیات GO = 2 یک عملیات اتحاد نامرتبط است و عملیات والد برای عملیات Yu = 3، Yu = 8 است. عملیات GO = 2 قبل از عملیات GO = 1 انجام می شود.

عملیات GO = 3 یک عملیات اتحاد پیوندی است، این عملیات والد برای عملیات GO = 4، GO = 7 است. عملیات GO = 3 قبل از عملیات GO = 2 انجام می شود.

عملیات GO = 4 یک عملیات اتحاد پیوندی است، این عملیات والد برای عملیات GO = 5، GO = 6 است. عملیات GO = 4 قبل از عملیات GO = 3 انجام می شود.

عملیات GO = 5 یک عملیات مستقل است که قبل از عملیات GO = 4 انجام می شود.

عملیات GO = 6 یک عملیات مستقل است که قبل از عملیات GO = 5 انجام می شود.

عملیات GO = 7 یک عملیات مستقل است که پس از اجرای بلوک عملیات "C" انجام می شود.

عملیات GO = 8 یک عملیات مستقل است که پس از بلوک عملیات "E" انجام می شود.

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

1. عملیات مستقل GO = 5 ابتدا انجام می شود، قوانین مربوط به توالی عملیات اتصال مرتبط را ببینید. کل جدول به ترتیب خوانده می شود.
2. نتیجه عملیات GO = 5 - ردیف های جدول خوانده شده - به عملیات GO = 4 منتقل می شود.
3. عملیات GO = 4 انجام می شود: برای هر ردیف برگردانده شده توسط عملیات GO = 5، عملیات GO = 6 انجام می شود، یعنی محدوده شاخص در برابر ویژگی join اسکن می شود. به دست آوردن لیستی از شناسه های ردیف Yaou1s1.
4. نتیجه عملیات GO = 4 به عملیات GO = 3 منتقل می شود. یعنی لیست شناسه های ردیف Kosh1s1 منتقل می شود.
5. عملیات GO = 3 انجام می شود: برای هر مقدار 11оу1с1 برگردانده شده در نتیجه عملیات بلوک عملیات "C"، عملیات GO = 7 انجام می شود، یعنی. ردیف‌های جدول از فهرست مشخصی از شناسه‌های ردیف ITMI، که پس از انجام عملیات Ш = 4 به دست می‌آید، خوانده می‌شوند.
6. عملیات مستقل GO = 8 انجام می شود - خواندن متوالی کل جدول.
7. عملیات اتصال نامرتبط GO = 2 انجام می شود: یک اتصال با هش کردن نتایج بلوک های عملیات "E" و "E" انجام می شود.
8. نتیجه عملیات GO = 2 به عملیات GO = 1 منتقل می شود.
9. عملیات ادغام نامرتبط GO = 1 انجام می شود: داده های به دست آمده در نتیجه عملیات GO = 2 تجمیع و مرتب می شوند.
10. عملیات GO = 0 انجام می شود.

قوانین زیر که برای انواع اصلی عملیات فرموله شده اند، برای اکثر طرح های اجرای پرس و جوی BSGO قابل اجرا هستند. با این حال، ساختارهایی وجود دارد که در جستارهای BSGO استفاده می شود که به نقض ترتیب عملیات شرح داده شده در قوانین زیر اشاره دارد. چنین موقعیت‌هایی می‌توانند در نتیجه استفاده از، برای مثال، زیرپرسش‌ها یا محمول‌های ضد پیوستن ایجاد شوند. در هر صورت، فرآیند تفسیر طرح اجرای یک پرس و جو BSGO تنها به استفاده از تعدادی قواعد دلالت نمی کند که دقیق ترین تحلیل را از آنچه بهینه ساز قرار است در هنگام اجرای یک پرس و جو BSGO انجام دهد، تضمین می کند. یکی دیگر از درخواست های BSGO همیشه یک مورد فردی است. و نحوه اجرای آن در DBMS به عوامل زیادی بستگی دارد، از جمله نسخه DBMS، نسخه و نوع سیستم عاملی که نمونه DBMS بر روی آن مستقر شده است، سخت افزار مورد استفاده، صلاحیت نویسنده پرس و جو 80b، و غیره.

1 msdevcon.ru #msdevcon

3 Olontsev Sergey SQL Server MCM, MVP Kaspersky Lab

4 زبان پرس و جو ساختاریافته

5 پرس و جوی مثال انتخاب pers.firstname، pers.lastname، emp.jobtitle، emp.nationalidnumber از HumanResources.Employee به عنوان emp inner join Person.Person as pers on pers.businessentityid = emp.businessentityid که در آن pers.firstname" و N"John emp.hiredate >= ""

6 درخت پرس و جو منطقی Project pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber D A T A Filter Join pers.firstname = N"John" and emp.hiredate >= " " pers.businessentityid = emp.businessentityid به عنوان Person.Person pers دریافت داده دریافت داده HumanResources.Employee as emp

7 طرح پرس و جو نشان می دهد که چگونه یک پرس و جو T-SQL در سطح فیزیکی اجرا می شود.

8 راه های مختلف

9 DEMO طرح ساده انتخاب همه داده ها از یک جدول، نحوه دریافت طرح پرس و جو

11 روش های اپراتور Init() متد Init() باعث می شود که اپراتور فیزیکی خود را مقداردهی اولیه کند و هرگونه ساختار داده لازم را آماده کند. یک اپراتور فیزیکی می تواند بسیاری از تماس ها را به Init() دریافت کند، اگرچه معمولاً فقط یک تماس دریافت می کند. GetNext() متد GetNext() باعث می شود که اپراتور فیزیکی ردیف اول یا بعدی داده ها را دریافت کند. یک اپراتور فیزیکی ممکن است بسیاری از تماس های GetNext() را دریافت کند یا هیچ کدام را دریافت نکند. متد GetNext() یک ردیف از داده ها را برمی گرداند و تعداد دفعاتی که فراخوانی می شود با مقدار ActualRows در خروجی دستور Showplan نشان داده می شود. Close() هنگامی که متد Close() فراخوانی می شود، عملگر فیزیکی مقداری پاکسازی را انجام می دهد و بسته می شود. اپراتور فیزیکی فقط یک تماس با Close() دریافت می کند.

12 تعامل بین اپراتورها اپراتور 1 اپراتور 2 اپراتور 3

13 تعامل بین عملگرها 1. درخواست اپراتور ردیف 1 اپراتور 2 اپراتور 3

14 تعامل بین اپراتورها 1. درخواست ردیف 2. درخواست اپراتور ردیف 1 اپراتور 2 اپراتور 3

15 تعامل بین اپراتورها 1. درخواست ردیف 2. درخواست اپراتور ردیف 1 اپراتور 2 اپراتور 3 3. ارسال ردیف

16 تعامل بین اپراتورها 1. درخواست ردیف 2. درخواست اپراتور ردیف 1 اپراتور 2 اپراتور 3 4. ارسال ردیف 3. ارسال ردیف

17 تعامل بین اپراتورها 1. درخواست ردیف 2. درخواست اپراتور ردیف 1 اپراتور 2 اپراتور 3 4. ارسال ردیف 3. ارسال ردیف

18 DEMO Operator TOP یا چرا بهتر است یک اپراتور را تکرار کننده صدا کنید

19 جدول وجود ندارد!

20 HoBT صفحه 1 صفحه 2 صفحه 3 صفحه 4 ردیف 1 ردیف 3 ردیف 5 ردیف 7 ردیف 2 ردیف 4 ردیف 6 ردیف 8

21 صفحه صفحه HoBT صفحه صفحه صفحه صفحه صفحه

22 دمو اپراتورهای دسترسی به داده اسکن، جستجو، جستجو

23 چه کسی فقط یک جدول در پایگاه داده دارد؟

24 حلقه تودرتو، درهم پیوستن و ادغام پیوستن

25 عضو عملگر حلقه های تودرتو اتصال داخلی، پیوستن بیرونی چپ، نیمه پیوستن چپ، نیمه چپ چپ، ادغام ضد نیمه چپ. , union Hash به همه انواع عملیات منطقی بپیوندید

26 DEMO Join، مرتب سازی و اولین اپراتور Nested Loops، Merge Join، Hash Join، Sort، First Operator

27 هشدار

28 DEMO خطاها و هشدارها در طرح های پرس و جو

29 می دانم که چیزی نمی دانم. سقراط

30 DEMO یک مثال کوچک از چیزی نامشخص

31 تشخیص طرح های پرس و جو -- 10 پرس و جوی برتر که بیشترین مصرف کننده CPU را دارند و طرح های آنها، top(10) substring (t.text، qs.statement_start_offset / 2، مورد زمانی که qs.statement_end_offset = -1 باشد، سپس len(t.text) دیگری انتخاب می کنند. (qs.statement_end_offset - qs.statement_start_offset) / 2 end)، qs.execution_count، cast(qs.total_worker_time / به صورت اعشاری(18, 2)) به عنوان total_worker_time_ms، cast(qs.total_worker_time * 1. / qs.exe به عنوان اعشار (18, 2)) به عنوان avg_worker_time_ms، cast (p.query_plan به عنوان xml) به عنوان query_plan از sys.dm_exec_query_stats به عنوان qs cross application sys.dm_exec_sql_text(qs.sql_handle) به عنوان t crossapply sys.dm_sq_stle_plan مجموعه ، qs.statement_end_offset) به ترتیب p توسط qs.total_worker_time desc; برو

32 تکنیک برای خواندن طرح های پرس و جو بزرگ سعی کنید آنها را به بلوک های منطقی تقسیم کرده و به تدریج آنها را تجزیه و تحلیل کنید. در SSMS، هنگامی که طرح به صورت گرافیکی نمایش داده می شود، یک دکمه در گوشه سمت راست پایین برای پیمایش آسان تر در طرح پرس و جو ظاهر می شود. می توانید از XQuery\XPath استفاده کنید.

33 DEMO طرح پرس و جو بزرگ

35 DEMO SQL Sentry Plan Explorer

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

37 بیایید جستجوی Bookmark\Key را خلاصه کنیم اگر تعداد کمی از آنها وجود داشته باشد، به احتمال زیاد مشکلی وجود ندارد. اگر تعداد آنها زیاد باشد، ایجاد یک شاخص پوششی به خلاص شدن از شر آنها کمک می کند. هشدارها باید بررسی کنید که چرا رخ می دهد و در صورت لزوم اقدام کنید.

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

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

40 سوال

41 تماس اولونتسف سرگئی کسپرسکی آزمایشگاه

42 2013 شرکت مایکروسافت. تمامی حقوق محفوظ است. مایکروسافت، ویندوز، ویندوز ویستا و نام‌های محصولات دیگر علائم تجاری و/یا علائم تجاری ثبت شده در ایالات متحده هستند یا ممکن است باشند. و/یا کشورهای دیگر اطلاعات اینجا فقط برای مقاصد اطلاعاتی است و نمای فعلی شرکت مایکروسافت را در تاریخ این ارائه نشان می دهد. از آنجایی که مایکروسافت باید به شرایط متغیر بازار پاسخ دهد، نباید به عنوان تعهدی از جانب مایکروسافت تفسیر شود و مایکروسافت نمی تواند صحت اطلاعات ارائه شده پس از تاریخ ارائه را تضمین کند. مایکروسافت هیچ گونه ضمانت نامه، صریح، ضمنی یا قانونی در رابطه با اطلاعات این ارائه نمی دهد.