راهکارهای افزایش سرعت بانک‌ اطلاعاتی SQL Server

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

 برگرفته از وبلاگ احمد محمدی:

 

توسعه   به‌طور کلی برای افزایش سرعت یک بانک اطلاعاتی می‌توان به دو روش اقدام کرد. در واقع پنج عامل مورد اشاره در بالا‌، به دو دسته طولی و عرضی تقسیم‌بندی می‌شوند. در توسعه طولی که در اصطلاح انگلیسی به Scalp up نیز شناخته می‌شود، مدیر سیستم با صرف هزینه‌، به ارتقای سخت‌افزار (مثل پردازنده‌ها یا هارددیسک‌ها) یا به‌طورکلی ایجاد شبکه‌ای سریع‌تر اقدام می‌نماید یا مثلاً سیستم‌عامل خود را به نسخه‌ای جدیدتر و پایدارتر ارتقا می‌دهد. اما در روش عرضی (Scale out) تقریباً با حفظ همان سخت‌افزار و ساختار شبکه، به بهینه‌سازی روابط موجود میان عناصر دخیل در سرعت مثل برنامه‌های کاربردی، بانک اطلاعاتی و سرور اقدام می‌کند. توسعه طولی (Scale up)   هدف این مقاله پرداختن به توسعه عرضی برای بهره‌برداری بهینه از امکانات موجود است. اما قبل از آن، جادارد به‌صورت خلا‌صه و فهرست‌وار به توسعه طولی و راه‌حل‌های آن نیز پرداخته شود تا زمینه برای بررسی‌های بیشتر در آینده فراهم گردد.  راه‌حل یکم: افزایش حافظه مورد استفاده SQL Server ، برای اطلاع از چگونگی انجام‌دادن این کار، به سایت پشتیبانی مایکروسافت رجوع کنید نشانی(http://support.microsoft.com) و در آنجا عبارتAWE SQLServer را جستجو کنید تا مقالاتی که در این زمینه وجود دارد، در دسترس شما قرار گیرد. یا استفاده از SQL Server 2014 که جهت توضیحات بیشتر به نشانی  http://fumblog.um.ac.ir/fumindex.php?op=ViewArticle&articleId=12041&blogId=569 راه‌حل دوم: ارتقای سیستم‌عامل ویندوز سرور 2008 به بالا که در فرایند caching، سیستم‌عاملی پایدارتر و هوشمندتر قلمداد می‌شود.  راه‌حل سوم: استفاده از پردازنده‌های قویتر در سرور. این پردازنده‌ها به دلیل ویژگیhyper threading، می‌توانند سرعت پردازش اطلاعات در سمت سرور را به دو برابر افزایش دهند.  راه‌حل چهارم: ارتقاء هارددیسک‌ سرور  راه‌حل پنجم: جداسازی محل ذخیره فایل‌های داده‌ای بانک اطلاعاتی (mdf) و فایل‌های لاگ (ldf) برروی دو هارددیسک مختلف یا دو دیسک مختلف از یک RAID.   با جداسازی این فایل‌ها از یکدیگر، عمل ایجاد لاگ، وقفه‌ای در خواندن و نوشتن اطلاعات بر روی هارددیسکی که حاوی فایل‌های داده‌ای mdf است، ایجاد نمی‌کند.  راه‌حل ششم: راه‌حل آخر و در واقع مشکل‌ترین راه، تقسیم بانک اطلاعاتی (در صورت لزوم) به دو یا چند  بانک جدا از هم و بر روی دو سرور مختلف است.   توسعه عرضی (Scale out)  نام خانوادگی نام شماره تامین اجتماعی بیمه شده شماره سریال بیمه شده ب الف ایندکس خوشه‌ای یا خاصیت منحصر به فرد کلید اولیه ایندکس غیرخوشه‌ای راه‌های موجود در توسعه عرضی در واقع سریع‌ترین راه‌حل‌های افزایش سرعت در بانک‌های اطلاعاتی را تشکیل می‌دهند. برخی از این راه‌ها فقط با یک بار استفاده، اثر دایمی خود را روی سیستم به جا می‌گذارند. اما برخی دیگر باید به عنوان یک الگوی دوره‌ای در مراحل زمانی مناسب ازسوی مدیر سیستم اجرا شود. این راه‌ها در واقع جزئی از دستورالعمل‌های نگهداری و پشتیبانی سیستم محسوب می‌شوند. در ادامه  به بررسی آن‌ها می‌پردازیم: 1 - از ساخت جداولی که فاقد کلید اولیه (Primary key) باشند، خودداری کنید. کلید اولیه علاوه بر جلوگیری از  ورود اشتباه اطلاعات از سوی کاربر، به دلیل داشتن خاصیت منحصر به‌فرد بودن (Unique) به سریع‌تر پیدا‌شدن رکورد موردنظر از همان جدول کمک شایانی می‌کند. تا آنجا که برای سیستم امکان دارد برای کلید اولیه از فیلدهای عددی استفاده کنید.  استفاده از فیلدهای رشته‌ای (string) مثلchar یاvarchar به‌عنوان کلید اولیه، کمی کندتر از فیلدهای عددی است. از انتخاب فیلدهای رشته‌ای با طول زیاد و یا فیلدهایی مثل Memo ،Text و Picture به عنوان کلید اولیه نیز اجتناب کنید.  2 - تمام کلیدهای خارجی (Foreign key) قابل تعریف در بانک را تعریف کنید. وجود کلیدهای خارجی نیز علاوه بر جلوگیری از اشتباه کاربر در واردکردن یا حذف اطلاعات، موجب می‌شود هنگام لینک شدن (join) جداول مادر و فرزند از طریق کلیدهای خارجی، سیستم سرعت بیشتری را در انجام دستورات Select شما از خود نشان دهند.  3 - همان‌طور که می‌دانید ایندکس‌ها در دو نوع خوشه‌ای (cluster) و غیرخوشه‌ای (Non cluster) قابل ساخت هستند. ایندکس‌ها باعث افزایش سرعت خواندن اطلاعات به‌وسیله دستورSelect می‌شوند.  ما تعریف بی‌رویه آن‌ها در سیستم نیز باعث کاهش سرعت اجرای دستورات فرایندی مثل Insert ،Update و Delete  می‌شود. بنابراین سعی کنید ایندکس‌های ضروری را در سیستم تعریف کنید. اما در این راه دست و دلبازی بی‌مورد از خود نشان ندهید. به عنوان مثال، فرض کنید در یک شعبه اداره تأمین اجتماعی، جدولی ویژه تعریف بیمه‌شدگان به شکل زیر وجود دارد.   مبلغ تاریخ شماره سریال 1 جزء دوم کلید اولیه جزء اول کلید اولیه 1 کلید خارجی از جدول قبل 1 جزئی از ایندکس خوشه ای جزئی از ایندکس خوشه ای جدولی نیز برای نگهداری وجه حق بیمه از بیمه‌شدگان نیز تعریف شده است.  همان‌طور که مشاهده می‌کنید، ایندکس نوع خوشه‌ای به فیلدی داده شده که نسبت به بقیه فیلدها در یک جدول کاربرد بیشتری دارد. چرا که این نوع ایندکس نسبت به نوع غیرخوشه‌ای سرعت بیشتری دارد. در ضمن در هر جدول از بانک اطلاعاتی شما فقط قادر به تعریف یک ایندکس خوشه‌ای هستید که انتخاب فیلد آن اهمیت زیادی دارد. بنابراین لزومی ندارد فیلدی که کلید اولیه است، حتماً به عنوان ایندکس خوشه‌ای انتخاب شود.  نکته مهم دیگر این است که لا‌زم است تمام کلیدهای اولیه جداول ایندکس دارای باشند (خوشه‌ای یا غیرخوشه‌ای) نکته دیگر در زمان ساخت ایندکس‌ها فاکتور پرشدن (Fill Factor) آن‌ها است. این فاکتور در واقع بیانگر میزان فضای میانی است که باید برای رکوردهایی که در آینده درج یا حذف می‌شوند، خالی نگه داشته شود. بنابراین اگر احساس می‌کنید جدول شما به‌طور مداوم مورد عملیات حذف و درج (Insert،‌Delete) قرار می‌گیرد، این فاکتور را پایین (مثلاً 30 درصد) انتخاب کنید. اما اگر صرفاً عملیات درج بر روی یک جدول انجام می‌گیرد و میزان حذف اطلاعات از آن بسیار کم است، می‌توانید این میزان را به ارقام بالاتر مثلاً 90 درصد افزایش دهید. زیرا این نوع جداول نیازی به داشتن فضای خالی میانی برای رکوردهایی که در آینده جانشین رکوردهای حذف شده می‌شوند، ندارد.  این مسئله برای ایندکس‌هایی که برروی دیدها (Indexed Views) ساخته می‌شوند نیز صادق است. به‌طورکلی گذاشتن ایندکس برروی دیدها به افزایش سرعت آن‌ها کمک می‌کند. در این حالت، کلیه مطالب مذکور از جمله سیاست استفاده از ایندکس‌های خوشه‌ای و غیرخوشه‌ای و همچنینFill Factor در جداول، در مورد دیدها نیز عیناً باید رعایت گردد.  4 - در هنگام نوشتن دستورات Select یا در هنگام ساختن دیدها، از استفاده بی‌مورد از پارامترهای پردازش مثلDistinct و LIKE order by و لینک‌های خارجی (Outer join) اجتناب کنید. در صورت استفاده از این پارامترها، مطمئن باشید که گذاشتن آن‌ها کاملاً ضروری است و چاره دیگری ندارید.  5 - از واگذاری پردازش‌های ریاضی یا آماری سنگین و مداوم به سرور بانک اطلاعاتی بپرهیزید. مثلا‌ً به دستور زیر نگاهی بیندازید. SELECT( a*( b+c ) +( d* E+F))  %G/H From ... WHERE ... به‌جای این‌کار، می‌توانید ابتدا با استفاده از یک Select معمولی مثلSelect a ,b ,c ,d ,E ,F ,G ,h  فیلدهای موردنظر را در حافظه کلاینت لود کنید و سپس عملیات ریاضی مذکور را در همان جا انجام دهید. با این کار پردازشی که سرور باید مثلاً برای 50 کلاینت در عرض چند دقیقه انجام دهد، بین آن 50 کلاینت تقسیم می‌شود و در واقع هر کلاینت فقط سهم پردازشی مربوط به خود را انجام می‌دهد.  6 - گاهی عمل اجتماع بین دو Select  توسط دستور Union به شدت بر عملکرد و سرعت سیستم اثر منفی می‌گذارد. بنابراین در صورت امکان به جای استفاده از روش مذکور، از روش‌های دیگری که هدفتان را برآورده نماید، استفاده کنید.  7 - سعی نمایید فیلدهایی که از نظر مقدار و ارزش با یکدیگر مقایسه می‌شوند، از یک جنس (type) باشند. در غیر این‌صورت سیستم‌مجبور می‌شود به طور ضمنی، عمل تبدیل داده را انجام دهد که کمی برایش وقت‌گیر است. به مثال زیر توجه کنید و فرض بگیرید فیلد customer ID در جدول customers از جنس ncharتعریف شده است.  Declare@custID char (5) Set @ CustID =" FDLKO" Select * From Customers where customerID=@custID 8 - تاحد ممکن از به کار بردن توابع (چه پیش ساخته توسط SQL Server و چه ساخته شده توسط کاربر) در قسمت WHERE یاorder by اجتناب کنید. مثال زیر نمونه‌ای از این مورد است: Select * Form orders Where DateAdd (Day, 15, orderdata) = "2005/23/07" 9 - در زمان نوشتن تریگر (trigger) بر روی جداول یک بانک اطلاعاتی، از نوشتن تعداد زیادی دستورالعمل در آن‌ها خودداری کنید. به عبارت دیگر تریگرها را تا حد امکان کوتاه کنید و دستورالعمل‌ پیاد‌ه‌سازی آن‌ها را کم نمایید.  10 - در زمان ساخت کرسر (cursor) درون توابع، روال‌ها و تریگرها از پارامترهای Forward only یا read only و همچنین local استفاده کنید تا SQL Server با دانستن این نکته که شما قصد تغییر داده‌ها در کرسر موردنظر را ندارید، تغییر یافتنی بودن آن‌ها را درنظر نگیرد و آن را برای شما سریع‌تر بسازد.  11 - در صورتی که تکه‌ای از برنامه شما به ساخت یک جدول موقت (temporary table) نیاز دارد، این کار باید با ظرافت خاصی صورت بگیرد. اصولا SQL Server برای اجتناب برنامه‌نویسان از ساخت جداول موقت، از یک نوع داده(Data type) خاص به نام Tableپشتیبانی می‌کند که مزیت استفاده از آن این است که به‌جای هارددیسک، در حافظه رم قرارگرفته است و در نتیجه نسبت به جداول موقت سرعت بیشتری دارد.  اما به یاد داشته باشید که استفاده بی‌رویه از این نوع داده، حافظه زیادی را صرف می‌کند که می‌تواند باعث کاهش کارایی سیستم شود. بنابراین اگر احساس می‌کنید تعداد جداول موقت، رکوردهای آن‌ها و زمان استفاده از آن‌ها کم است، از این نوع داده استفاده کنید. در غیر این‌صورت، راه‌حل جدول موقت را انتخاب کنید.   12-  قفل‌گذاری بر روی رکوردهایی که در حال خواندن، درج شدن، حذف شدن یا تغییر کردن هستند، همیشه از مباحث مهم بانک‌های اطلاعاتی بوده‌است. همان‌طور‌که می‌دانید یک فرایند (Transaction) شامل یک یا چند دستورالعمل SQL است که یا باید همگی به صورت موفقیت‌آمیز اجرا شوند (committed) یا در صورت ایجاد خطا در زمان اجراشدن یکی، اجرای بقیه نیز منتفی شود (Rollbacked).   ایندکس گذاری برروی دیده ها(Indexed Views) یکی از بهترین راههای فوری جهت افزایشسرعت جستجو بر روی دیدهااست. در حالت عادی گزینهManage Indexesبر روی دیدها قابل انتخاب نیست مگر آنکه اولا کلیه جداول یا دیدهای موجود در آن، خود دارای ایندکس باشد و دوم اینکه کلیه دیدهای موجود در آن و هم خود دید مورد نظر با دستور زیر ساخته شده باشند. Create View....Whit Schema Binding AS.......  فرایند به دو صورت قابل پیاده‌سازی است. این کار یا با استفاده از دستورات Begin trans وCommittrans انجام می‌شود که به آن حالت صریح (Explicit) می‌گویند یا به صورت ضمنی (Implicit) صورت می‌گیرد که در آن اثری از دو دستور مذکور دیده نمی‌شود و هر دستورSQL یک فرایند مجزا به حساب می‌آید. در هر دو روش رکوردهایی که تحت‌تأثیر دامنه فرایند قرار می‌گیرند، توسط سیستم قفل می‌گردند و برای دیگر کاربران نیز غیرقابل استفاده می‌شوند و در نتیجه باعث کاهش سرعت کار آن‌ها به دلیل ایجاد انتظار برای آزاد شدن رکوردها می‌شود.   بنابراین برای رسیدن به حداکثر کارایی سیستم، باید از ایجاد قفل‌های بی‌مورد بر روی رکوردهای جداول بانک اطلاعاتی جلوگیری کرد. این کار با استفاده از دستور SET Transaction Isolation Level Read Uncommitted برای فرایندهای صریح (قبل از شروع فرایند، یعنی قبل از دستور (begin Trans  و یا استفاده از دستور WITH NOLOCK  برای فرایندهای ضمنی (پس از قسمت From هر دستور SQL) قابل انجام است. در مورد مسئله فرایندها و انواع قفل‌گذاری مطالب خواندنی زیادی در سایت مایکروسافت وجود دارد که درصورت تمایل می‌توانید به آن‌ها نیز مراجعه کنید.  13 - روال‌های ذخیره شده (stored Procedures) پس از هر اجرا، به ازای هر دستورالعملی که اجرا می‌کنند،  جهت اطلاع برنامه فراخوان (کلاینت) از موفقیت‌آمیز بودن اجرای آن دستور SQL، پیغامی را به سمت آن برنامه می‌فرستند. این مسئله باعث افزایش ترافیک شبکه در اثر فرستادن مداوم پیغام ازSP به سمت کاربر می‌شود. با تایپ دستور زیر در ابتدای یکSP، می‌توانید آن را از انجام این کار منع کنید:  SET NOCOUNT ON  نتیجه‌گیری‌ مطالب فوق تنها قسمتی از راهکارهای قابل انجام برای رسیدن به‌سرعت و بازدهی مناسب در بانک‌های اطلا‌عاتی مبتنی بر SQL Server است. در ضمن‌ باید این نکته را هم درنظر داشت که اصولا‌ً در سیستم‌های بزرگ اطلا‌عاتی تحت شبکه، توپولوژی و نوع اجزای موجود در شبکه از اهمیت بسیار زیادی در تعیین سطح کارایی یک بانک اطلا‌عاتی برخورداراست. گاهی حتی در حالی‌که بهترین طراحی و پیکربندی SQL Server برای یک بانک اطلا‌عاتی انجام شده، یک اشتباه کوچک در سطح شبکه می‌تواند تمام زحمات را بر ‌باد دهد یا مثلا‌ً یک سهل‌انگاری در نوشتن روال‌های ذخیره شده یا تریگرها می‌تواند سیستم را به‌یک لوپ (Loop) پردازشی بی‌نهایت ببرد و باعث افت شدید سرعت اجرای برنامه‌ها شود. بنابراین در این‌گونه سیستم‌ها، استفاده بجا و مناسب از منابع سیستم و شبکه و دقت در طراحی و پیاده‌سازی جداول، دیدها، روال‌های ذخیره‌شده و تریگرها بسیار مهم  و حیاتی است.