img

تنظیم پرس و جوی SQL Server یک برنامه 12 مرحله‌ای

/
/
/

تنظیم پرس و جوی SQL Server
یک برنامه 12 مرحله‌ای

مقدمه:
تنظیم پرس و جو، یک ابزار قدرتمند برای مدیران پایگاه اطلاعاتی و توسعه دهندگان در جهت توسعه عملکرد SQL Server است. برخلاف مقیاس‌های مربوط به عملکرد سرور در سطح- سیستم ( حافظه، پردازنده‌ها، و غیره)، تنظیم پرس و جو بیشتر بر کاهش مقدار I/O منطقی در یک پرس و جو تاکید دارد، چرا که هر چه مقدار ورودی‌ها و خروجی‌ها کمتر باشد، پرس و جو سریعتر خواهد بود. در حقیقت، برخی مشکلات مربوط به عملکرد، می‌توانند از طریق تنظیم پرس و جو حل شوند. تمرکز روی منابع سیستم می‌تواند منجر به سرمایه‌گذاری‌های پرهزینه و غیرضروری سخت افزاری گردد که در نهایت هم پرس و جو را سریعتر نمی‌کند.
هنوز هم اکثر مدیران پایگاه اطلاعاتی با تنظیم پرس و جو مشگل دارند. چگونه به پرس و جو دسترسی دارید؟ چگونه می‌توانید نواقص موجود در نحوه نوشتن یک پرس وجو را کشف کنید؟ چگونه می‌توانید فرصت‌های پنهان برای ترقی و پیشرفت را آشکار نمایید؟
چگونه مطمئن می‌شوید که با انجام اصلاحات و تغییرات خاص می‌توانید در واقع سرعت پرس و جو را بهبود ببخشید؟ آنچه موجب می‌شود که تنظیم پرس و جو بیشتر شبیه یک هنر باشد تا علم، این است که پاسخ درست یا غلطی وجود ندارد، غیر از این که چه چیزی بیشترین تناسب را با موقعیت فعلی دارد.
این مقاله با اراده یک پروسه 12 مرحله ای پرده از راز و رمز تنظیم پرس و جو برمی‌دارد و متخصصین پایگاه اطلاعاتی در هر سطحی می‌توانند به طور سیستماتیک به آن دسترسی داشته باشند و عملکرد پرس و جو را تنظیم کنند، از تکنیک‌های ابتدایی گرفته تا تنظیم پیشرفته پرس و جو همچون شاخص‌گذاری. وقتی این پروسه را از ابتدا تا انتها به کار بگیرید، می‌توانید عملکرد پرس و جو را به روشی که قابل اندازه‌گیری باشد، بهبود ببخشید و متوجه خواهید شد که پرس و جو را تا حد امکان بهینه سازی کرده‌اید.
همیشه از اصول اولیه آنالیز پرس و جو آغاز کنید
وقتی در مورد اصلاح یک پرس و جوی کُند سوال می‌شود، مدیران با تجربه پایگاه اطلاعاتی مستقیماً به سراغ آزمایش طرح‌های اجرایی می‌روند و سپس با اجرای پرس و جو شگفت زده می‌شوند که برگردان داده‌ها چقدر طول می‌کشد! در آن لحظه، گاهی اوقات این فهم وجود دارد که جدول واقعاً بزرگ است. به همین دلیل است که همیشه توصیه می‌شود با اصول اولیه آغاز کنید، یعنی از ابتدا بفهمید که با چه چیزی سر و کار دارید.
1- جدول‌ها و rowcount های خود را بشناسید
ابتدا، مطمئن شوید که در واقع روی یک جدول کار می‌کنید، نه یک ویو یا تابع table-valued. اگر ویو باشد، به تعریف ویو نیاز دارید. توابع table-valued مفاهیم اجرایی خودشان را دارند.
نکته: برای آزمایش این جزئیات می‌توانید از SSMS استفاده کنید
با پرس و جو کردن DMV ها ( مثال زیر)، recount را بررسی کنید. اگر، برای مثال، پرس و جو در محیط توسعه ساخته و آزمایش شده است، اما برای اولین بار در محیط تولید اجرا می‌شود، rowcount واقعی احتمالاً بالاتر است.

2- فیلترهای پرس و جو را آزمایش کنید. عبارت‌های WHERE و JOIN را آزمایش کنید و rowcount فیلتر شده را یادداشت نمایید
نکته: اگر فیلتری وجود ندارد، و بیشتر جدول بازگردانده شده، ببینید آیا تمام آن داده‌ها مورد نیاز هستند. اگر به طور کل هیچ فیلتری وجود ندارد، این می‌تواند یک زنگ خطر باشد و دلیل قانع کننده‌ای برای بررسی بیشتر. در واقع همین مسئله می‌تواند موجب کند شدن پرس و جو گردد.

3- گزینش‌پذیری جدول‌های خود را بدانید
بر اساس جدول‌ها و فیلترهای موجود در دو مرحله قبلی، بدانید که با چه تعداد سطر کار خواهید کرد و اندازه مجموعه واقعی، منطقی چقدر است. در مورد بحث گزینشی‌پذیری، SQL Tuning دن تو (Dan Tow) را پیشنهاد می‌کنیم و استفاده از رسم نمودار به عنوان یک ابزار قدرتمند در ارزیابی پرس و جوها و گزینش پرس و جو.
این موضوع بخصوص برای joinهای Right ،Left و Outer مهم است. باید خوب بفهمید که چه زمانی از گزاره استفاده کنید به این ترتیب می‌توانید مطمئن شوید که با کوچکترین مجموعه ممکن آغاز می‌کنید و فیلترها به موقع به کار گرفته می‌شوند.
4- ستون‌های پرس و جوی اضافی را آنالیز کنید- چیزهای اضافی خارج از فیلترها و Joinها
توابع اسکالر یا * SELECT را به دقت بررسی کنید تا مشخص شود که آیا ستون‌های اضافی درگیر هستند. آیا CASE ،CAST و CONVERT در عبارت WHERE رخ می‌دهند؟ آیا SARGable (شاخص قابل جستجو) است؟ هرچه ستون‌های بیشتری را برگردانید، دیگر برای طرح اجرایی مطلوب نیست که از عملیات شاخص خاص استفاده کند، و این می‌تواند باعث تنزل عملکرد شود.

ادامه دهید تا به آنالیز پیشرفته‌تر پرس و جو برسید
5- کلیدهای موجود، قیدها (Constrain)، شاخص‌ها را بازبینی کنید تا مطمئن شوید که تلاش مضاعف نمی‌کنید یا همپوشانی شاخص‌هایی که از قبل وجود داشتند اتفاق نمی‌افتد
این قیدها را بشناسید و استفاده کنید چراکه می‌توانند برای شروع تنظیم مفید باشند.
تعریف کلید اصلی (Primary Key) چیست؟ آیا آن کلید نیز Clusteredاست؟ اگر یک کلید Clustered وسیع دارید، باید از این کلید در شاخص‌های non-Clustered کپی بگیرید، یعنی برای حل مشگل پرس و جو باید صفحات بیشتری را در مخزن بافر بخوانید.
اگر از قیدهای کلید خارجی (Foreign Key) استفاده می‌کنید، بررسی کنید که آیا آن‌‌ها در مدل داده‌ای شما کار می‌کنند. بهینه ساز می‌تواند از قیدهای کلید خارجی استفاده کند تا طرح‌های اجرایی را بهتر نماید، این به اجرای سریعتر پرس و جو کمک می‌کند.
برای بدست آوردن اطلاعات مربوط به شاخص‌ها، رویه ذخیره شده sp_helpindex را اجرا نمایید:

توجه داشته باشید که ستون‌های گنجانده شده به حساب آورده نشوند. اگر به این اطلاعات نیاز دارید؛ باید از یک پرس و جوی متفاوت استفاده نمایید.
6- طرح اجرایی واقعی ( نه طرح تخمین زده شده) را آزمایش کنید
طرح‌های تخیمنی از آمارهای تخمینی برای تعیین سطرهای تخمینی استفاده می‌کنند؛ طرح‌های واقعی از آمارهای واقعی در زمان‌اجرا استفاده می‌کنند. اگر طرح‌های واقعی و تخمینی متفاوت هستند، ممکن است به جستجوی بیشتری نیاز داشته باشید.
توجه کنید که در این مرحله، ممکن است بخواهید آمارها را در ) SET STATISTIC IO On و SET STATISTICSTIME On) تنظیم کنید.

7- نتایج خود را ثبت کنید، روی تعداد I/O های منطقی تمرکز کنید
این مرحله بسیار مهم است و ممکن است از سوی مدیران پایگاه اطلاعاتی نادیده گرفته شود، اگر نتایج را ثبت نکنید، قادر نخواهید بود اثر واقعی تغییراتی که اعمال می‌کنید را مشخص نمایید.
8- پرس و جو را براساس آنچه یافته‌اید، تنظیم کنید؛ تغییرات کوچک و فردی را در لحظه اعمال نمایید
اگر تغییرات زیادی را در لحظه اعمال کنید، ممکن است ببینید که تغییرات همدیگر را لغو می‌کنند. کار را با جستجوی پرهزینه‌ترین عمل‌ها آغاز کنید. هیچ پاسخ درست و غلطی وجود ندارد، فقط مناسب موقعیت فعلی است ( توجه کنید آمارهای منقضی شده می‌تواند همگی آنها را تحت تاثیر قرار دهد).
برخی از عملیات پرهزینه‌ای که ممکن است با آن‌ها مواجه شوید:
– انتقال داده‌ها از یک عمل به عمل دیگر. آیا تعداد واقعی سطرها بسیار بیشتر از تعداد تخمین زده شده است؟ اگر تخمین‌ها از واقعیت‌ها متفاوتند، ممکن است نیاز به بررسی بیشتر باشد.
– آیا جستجوها یا اسکن‌ها در این سناریوی خاص بسیار گرانتر هستند؟ برخلاف باور عموم، در برخی نمونه‌ها اسکن یک جدول ممکن است کم هزینه‌تر از یک جستجو باشد. برای مثال، اگر جدول بسیار کوچک است، SQL Server کل جدول را در حافظه می‌خواند و بنابراین جستجو نیاز نیست.
– آیا اسنیفینگ پارامتر یک مسئله است ( اسنیفینگ پارامتر نتیجه استفاده مجدد از طرح از پیش کش‌شده است که برای مقادیر پارامتر، از اجرای اصلی بهینه سازی می‌شود، و آن پارامترها ممکن است بسیار متفاوت باشند؟) آیا از مقایر محلی استفاده می‌کند؟
– آیا spool operations وجود دارند ( مجموعه نتایج ذخیره شده در tempdb برای استفاده‌ بعد) و اگر وجود دارد، آیا ضروری هستند؟
– در این موقعیت چه چیزی بهتر است:join های LOOP ، MERGE یا Hash ؟ بستگی به شرایط خاص و آماری که بهینه ساز استفاده می‌کند، دارد.
– آیا جستجوها وجود دارند، اگر اینطور است آیا ضروری هستند؟
9- اجرای مجدد پرس و جو و ضبط نتایج حاصل از تغییری که انجام داده‌اید
اگر شاهد بهبودی در I/O های منطقی هستید، ولی این بهبودی به قدر کافی نیست، به مرحله 8 برگردید تا فاکتورهای دیگری که ممکن است به تنظیم نیاز داشته باشند را بررسی نمایید. در هر لحظه یک تغییر را انجام دهید، پرس و جو را مجدد اجرا نمایید و نتایج را مقایسه کنید تا زمانی که این رضایتمندی در شما ایجاد شود که تمام عملیات پرهزینه را مخاطب قرار داده‌اید.
10- در این لحظه اگر باور دارید که پرس و جو به خوبی آنچه که می‌توانست باشد، نوشته شده و شما هنوز به بهبود بیشتری نیاز دارید، تنظیم شاخص را برای کاهش I/O منطقی در نظر بگیرید
اضافه کردن و تنظیم کردن شاخص‌ها همیشه بهترین کار نیست، اما اگر نمی‌توانید کد را تغییر دهید، این تنها کاریست که می‌توانید انجام دهید.
– شاخص‌های موجود را در نظر بگیرید. آیا به شکل کارآمدی مورد استفاده قرار می‌گیرند؟ با حداقل گزینش‌پذیری روی آن جدول‌ها تمرکز کنید.
– یک Covering index را در نظر بگیرید- شاخصی که شامل هر ستون متناسب با پرس و جو است. مطمئن شوید ابتدا دستورهای Delete/Update/Insert را آزمایش می‌کنید: حجم آن تغییرات چقدر است؟
– یک شاخص فیلترشده را در نظر بگیرید (SQL Server 2008 به بعد) – یک شاخص non-clustered که دارای گزاره یا عبارت WHERE است. اما توجه داشته باشید که اگر یک دستور پارامتربندی شده یا متغییرهای محلی دارید، بهینه ساز نمی‌تواند از شاخص فیلتر شده استفاده کند.
11- اگر تنظیمات را در مرحله 10 انجام دادید، پرس و جو را دوباره اجرا کنید و نتایج را ثبت کنید
12- در نهایت، هر وقت که نیاز شد، این بازدارنده‌های اجرایی را که غالباً با آن‌‌ها مواجه می‌شوید را حذف نمایید
– توجه داشته باشید که code-first generator ( برای مثال، EMF،LNQ ،nHibernate ) می‌توانند باعث پر شدن Plan Cache شود.
نکته: اگر از code-first generatorها استفاده می‌کنید، در نظر داشته باشید که OPTIMIZE FOR AD HOC WORKLOADS را فعال کنید.
– به دنبال سوءاستفاده از wildcardهای (*) باشید که می‌تواند ستون‌های بیشتری را بازگرداند
– توابع اسکالر و توابع چند دستوری برای هر سطری که بازگردانده می‌شود، فراخوانده می‌شوند و می‌توانند مورد سوء استفاده قرار بگیرند
– ویوهای تو در تو که در تمام سرورهای لینک شده مورد استفاده قرار می‌گیرند، می‌توانند زمان پردازش را اضافه کنند
– مکان‌نماها و پردازش سطر به سطر می‌توانند پردازش را کند کنند
– hint های Join/query/index/table می‌توانند به شکل چشمگیری نحوه کار کردن یک پرس و جو را تغییر دهند. از آنها تنها زمانی استفاده کنید که از تمام امکانات دیگر خسته شده‌اید.

نظر بدهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

It is main inner container footer text