شناسایی گلوگاه‌های مربوط به عملکردSQL Server و روش حل آن

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

شناسایی Bottleneck در SQL Server چیست؟

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

علائم شناسایی Bottleneck در SQL Server

اگر این سؤال را در ذهن دارید، چگونه بدانید که آیا SQL دارای Bottlenecks است یا نه، در اینجا پاسخ وجود دارد. ابتدا استاندارد خاصی را برای عملکرد خود تعیین کرده و سپس علائم را مطابق با آن پایه در نظر بگیرید. این پایه به شما در تعیین Bottlenecks و دوره کم فعالیت و همچنین مقایسه اثرات تغییرات ایجاد شده به شما کمک می‌کند. به همین دلیل، تنظیم یک پایه خوب مهم است. در اینجا علائمی که باید در نظر بگیرید:

۱- Disk Bottleneck: زمان پاسخگویی طولانی مربوط به دیسک می‌تواند یکی از دلایل Bottleneck باشد که شما برای حل این مشکل یکی از کارهایی که می‌توانید انجام دهید استفاده از هاردهای پر سرعت SSD می‌باشد.
۲- Memory Bottleneck: پیغام‌هایی مانند out of memory و یا Memory Resource Timeout
۳- مربوط به Bottleneck‌های سمت حافظه می‌باشد. البته افزایش زمان اجرای کوئری‌ها و افزایش I/O نیز مربوط به Bottleneck حافظه Ram باشد.
۴- CPU Bottleneck: این نوع شناسایی Bottleneck ها آسان ترین راه بدست آوردن Bottleneck‌ ها می‌باشد. در این حالت CPU همیشه مورد استفاده استفاده SQL Server بوده ولی توان کلی آن کم می‌باشد.

عوامل ایجاد کننده شناسایی Bottleneck و روش حل آن

در زیر بعضی از برنامه‌ها و ابزارهای Monitoring که به شما کمک می‌کند تا شناسایی کنید Bottleneck آمده است. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

۱- ابزار SQL Profiler

یکی از مهمترین ابزارهایی گرافیکی که به پیدا کردن Bottleneck در SQL Server ، به ما کمک می‌کند برنامه SQL Profiler می‌باشد. SQL Profiler کار نظارت بر کارایی SQL Server را بر عهده دارد. به کمک SQL Profiler شما می‌توانید کوئری‌های کند را پیدا کرده و دستوراتی که منجر به ایجاد مشکل شده‌اند را شناسایی کنید. به طور مثال می‌توان به کمک SQL Profiler دستوراتی که زمان اجرای آنها بالای ۴ ثانیه می‌باشد را شناسایی کنید. همچنین می‌توانید دستوراتی که باعث Deadlock و Blocking می‌شوند را شناسایی کنید.همچنین امکان ردیابی اطلاعاتی در خصوص رویدادهای مختلف و ثبت این داده‌ها در یک فایل (با پسوند trc) یا جدول برای تحلیل‌های آتی نیز وجود دارد. برای اجرای SQL Profiler کافیست در محیط SSMS، مطابق شکل زیر از منو Toolsبر روی گزینه SQL Server Profiler کلیک کنید.

۲- ابزار Stored Procedureهای سیستمی

SPهای سیستمی روال‌هایی از قبل نوشته شده هستند که شما می‌توانید از آنها استفاده کنید. این SP‌ها با SP_ شروع می‌شوند و در دیتابیس Master قرار دارند. همچنین در دیتابیس‌های دیگر در دسترس هستند. لازم به ذکر است که هیچگاه نام SP‌های خود را با SP_ شروع نکنید.در ضمن رنگ SP‌های سیستمی به رنگ قهوه‌ای می‌باشند.
به عنوان مثال می‌توانیم به مثال‌های زیر اشاره کنیم:

• SP_SPACEUSED
• SP_HELPFILE
• SP_FILEGROUP

۳- با استفاده از DMVها و DMFها

در حقیقت DMVها، یک سری ویوهای سیستمی پیشرفته بوده که از نسخه SQL Server 2005 به SQL Server اضافه شدند. DMVها در واقع نسل جدیدی از SP سیستمی می‌باشند. لازم به ذکر است که DMVها یا با کلمه SYS و یا با کلمه Information_Schema شروع می‌شوند.

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

• SELECT * FROM SYS .databases
• SELECT * FROM SYS.database_files

همچنین DMFها، یک سری فانکشن‌های پیشرفته بوده که از نسخه SQL Server 2005 به اسکیوال سرور اضافه شدند. لازم به ذکر است که DMFها بر خلاف DMVها ، پارامتر می‌پذیرند. پیشنهاد میکنیم برای درک بهتر مفاهیم کوئری نویسی را مطالعه کنید.

۴- دستورات DBCC

یکسری دستورات (Command) Performance پیشرفته وجود دارند، به نام دستورات DBCC که مخفف Database Console Command می‌باشد. یکسری از کارهای حرفه ای SQL Server را مجبوریم از دستورات DBCC استفاده نماییم. این دستورات بسیار زیاد بوده و بعضی از آنها undocumented هستند و به صراحت در Books online اشاره نشده است. جنس این دستورات می‌تواند Informational (اطلاعاتی) و یا Validation (اعتبارسنجی) و یا Maintenance (تعمیر و نگهداری) ای باشد که برای هر کدام مثال‌هایی را آورده‌ایم. افرادی می‌توانند این دستورات را اجرا کنند که دسترسی آنها یا DB_Owner و یا SysAdmin باشند.

به نمونه‌هایی از مثال‌های مربوط به DBCC توجه فرمایید:

• DBCC HELP(‘SHRINKFILE’)
• DBCC SHRINKFILE
• DBCC DROPCLEANBUFFERS
• DBCC SQLPERF
• DBCC CHECKDB
• DBCC MemoryStatus
• DBCC Checkdb(‘xyz’,REPAIR_ALLOW_DATA_LOSS)

۵- با استفاده از برنامه Resource Monitor

یکی از ابزارهایی گرافیکی ویندوز که به پیدا کردنBottleneck ها، به ما کمک می‌کند برنامه Resource Monitor می‌باشد. کافیست در پنجره RUN دستور RESMON را وارد نموده و دکمه OK را کلیک کنید در این صورت پنجره‌ای مطابق شکل زیر نمایان می‌شود و شما می‌توانید وضعیت مربوط به CPU، Memory، DISK و Network سرور را بررسی نمایید.

تذکر: در قسمت Network شما در ستون Address می‌توانید متوجه شوید که از چه IPای به سرور شما متصل شده است. مطابق شکل زیر چک مارک مربوط به SQL Server.exe را علامت زده و به ستون Address دقت فرمایید در این صورت شما در این ستون IP و یا نام سرورهایی را که به سرور شما متصل شده‌اند را خواهید دید.

۶- با استفاده از برنامه Performance Monitor (Perfmon)

یکی دیگر از ابزارهایی گرافیکی ویندوز که به پیدا کرد Bottleneck ها، به ما کمک می‌کند برنامه Performance Monitor می‌‌باشد. برای اجرای آن کافیست در پنجره Run دستور Perfmon را وارد کرده و دکمه OK را کلیک کنید در این صورت پنجره‌ای مطابق شکل زیر نمایان می گردد.

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

۷- با استفاده از نرم افزارهای جانبی مثل Red gate و یا Apex و غیره

Redgate نام شرکت نرم‌افزاری است در سال ۱۹۹۹ تاسیس شد. یکی از برترین شرکت‌های ارائه‌دهنده‌ی ابزارهای مختلف و کاملی برای توسعه‌دهندگان پایگاه‌های داده و برنامه‌نویسان .NET، SQL Server، Oracle و… می‌باشد. ابزارهای مختلفی برای نظارت و مدیریت، پشتیبان‌گیری، مقایسه‌کردن دیتابیس‌ها و… در این مجموعه‌ی SQL وجود دارد. ابزار SmartAssembly نیز برای محافظت از کدهای دات‌نت در مجموعه‌ی NET. ارائه شده است، همچنین نرم‌افزار NET Reflector. که یک کاوش‌گر و دیباگر قدرتمند کدهای دات‌نت است و قبلا به‌عنوان یک نرم‌افزار رایگان توسط Lutz Roeder ارائه می‌شد، از نسخه‌ی ۷ توسط Red-Gate خریداری و گسترش پیدا کرد. قیمت واقعی این پکیج حدود ۲۰۰۰ دلار می باشد.

نحوه رفع شناسایی Bottleneck در SQL Server

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

• حافظه سرور را تحت نظارت نگه دارید

شما به عنوان یک DBA باید به طور مرتب حافظه سرور را تحت کنترل و نظارت داشته باشید و در صورت مشکل آن را بر طرف سازید. البته همانطور که می‌دانید SQL Server، نرم افزاری است که هر چقدر به سرور Ram اضافه کنید آن را مصرف می‌کند (به اصطلاح Ram خور است).به عنوان مثال باید توسط نرم افزارهای جانبی مانند Redgate میزان رم مصرفی سرور را کنترل کنید و در صورت مشاهده مورد مشکوک کوئری‌های مشکل دار را یافته و آنها را بهینه سازید.
لازم به ذکر است که میزان Maximum server memory را باید محاسبه نموده و تنظیم نمایید برای این کار کافیست بر روی Instance خود کلیک راست نموده و کلیک راست و سپس گزینه Properties را انتخاب نمایید. در پنجره باز شده بر روی گزینه Memory کلیک نموده و عدد مربوط به Maximum server memory را که به مگابایت است را وارد نمایید. دقت کنید که این عدد معمولا به این صورت به دست می‌آید که کل Ram سرور را مشاهده نموده و سپس به اندازه ۴ یا ۸ گیگ از آن کم نموده (بستگی به میزان Ram سرور شما داشته و این ۴ یا ۸ گیگ رم برای ویندوز کافی می‌باشد) و باقی مانده را به مگابایت در این قسمت وارد می‌نماییم.

• Bottlenecks مربوط به CPU را پیدا کرده و برطرف کنید

سعی کنید حذاکثر فشار را در یک تست بر روی CPUهای سرور خود اعمال نموده و نتیجه را گزارش کنید. همچنین دقت کنید که از تمام CPU‌های خود استفاده نمایید. لازم به ذکر است اگر چنانچه از تمام ‌های خود استفاده نمی‌کنید ممکن است که مربوط به نسخه نصبی شما باشد. به طور مثال Enterprise Edition تمام Coreهای CPU را نمی‌بیند و شما باید Enterprise Core Edition را نصب کنید.

• نظارت دقیق بر استفاده از دیسک I/O

برای بدست آوردن بهترین کارایی در مورد دیسک I/O سعی کنید که مواردی را که باعث ایجاد Bottleneck می شوند را برطرف سازید. به مثال‌های زیر توجه فرمایید:

• از دیسک‌های پر سرعت SSD استفاده کنید. فایل‌های داده را از لاگ فایل‌ها جدا نموده و در دو دیسک جداگانه قرار دهید (از لحاظ فیزیکی هاردها جدا باشند).
• فایل mdf خود را به فایل‌های کوچکتر شکسته و در فایل گروپ‌های جدا گانه قرار دهید و در صورت امکان هر فایل را نیز در دیسک‌های جداگانه قرار دهید.
• کوئری‌ها را طوری تغییر دهید تا برای بدست آوردن Data و نمایش آنها، کمترین IO را زده و داده‌ها را بدست آورد.
• باید Allocation Unit Size (دیسکی را که می خواهید فایل های اسکیوال سرور بر روی آنها قرار گیرد با فرمت ۶۴ کیلوبایت قرار دهید) و Strip Size (هنگام Raid باید به این موضوع دقت کنید) خود را بر روی عدد ۶۴ کیلوبایت تنظیم نمایید.
• به عبارت کلی شما برای بدست آوردن بهترین Performance باید تا جای که می توانید باید مواردی را که منجر به کاهش IO می‌شود را رعایت فرمایید.

• طراحی پایگاه داده خوب

یکی از مشکلات مربوط به هر دیتابیسی می‌تواند مربوط به طراحی بد جداول باشد. شما باید جداول خود را کاملا حرفه‌ای طراحی و آنها را نرمال سازی نموده تا از بروز یکسری گلوگاه‌ها جلوگیری کنید. به عنوان مثال یکی از مشکلات مربوط به طراحی بد استفاده از Data Type‌های منسوخ شده توسط مایکروسافت می‌باشد. شما چنانچه کلمه Deprecated Data Type را در سایت مایکروسافت جستجو کنید.
در این صورت شما می‌بینید که تایپ‌هایی مانند Text, nText , Image جزء دیتا تایپ های منسوخ شده به شمار می آیند و مایکروسافت پیشنهاد داده که به جای آنها از Varchar(max), nVarchar (Max), Varbinery (Max) استفاده نمایید. لازم به ذکر است که طبق تجربیان بنده شما حتی تا زمانی که مجبور نشده‌اید هرگز از این تایپ‌های هم استفاده نکنید (بنا به دلایل Performance‌). به عنوان مثال اگر شما مطمئن هستید که تعداد کاراکترهای شما از ۸۰۰۰ کاراکتر کمتر می‌باشد به جای varchar (Max) از varchar () استفاده نمایید.

• مدیریت صحیح ایندکس‌ها

پس از مدتی که از شروع به کار یک سیستم می‌گذرد، همانطور که تعریف ایندکس‌های مفید سرعت جستجوها را بالا می‌برد، ایجاد fragmentation و Page Split در آن‌ها نیز تاثیر منفی در کارآیی سیستم شما خواهد داشت. به همین منظور نیاز است هر از چندگاهی ایندکس‌ها سیستم شما بررسی شوند. حال چنانچه میزان fragmentation ایندکس‌ها بیش از ۳۰ درصد بود شما باید آنها را Rebuild نمایید. لازم به ذکر است که اجرای کوئری‌های مربوط به Rebuild بر روی یک دیتابیس حجیم زمان‌بر بوده و سیستم را تحت تاثیر قرار خواهد داد. بنابراین هنگام Rebuild حتما خاصیت online=on را استفاده نموده و همچنین این کار را سعی کنید خارج از وقت اداری انجام دهید.

سخن پایانی

شناسایی Bottleneck در SQL Server به معنای کاهش عملکرد SQL Server، و شناسایی منابع مشترک مثل بانک اطلاعاتی SQL به طور همزمان توسط خیلی افراد دسترسی دارند. در این مقاله به معرفی ابزارهای Monitoring که به شما کمک می‌کند تا Bottleneck ها را پیدا کنید آمده است. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.