جديدترين مقالات مرتبط با مدیریت فناوری اطلاعات

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

ارسال شده توسط احمد محمدی | 6 February, 2016 | بازدید‌ها (573)

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



    چکیده :


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

    https://i-msdn.sec.s-msft.com/dynimg/IC42074.gif

    توسعه 
    به‌طور كلی برای افزایش سرعت یك بانك اطلاعاتی می‌توان به دو روش اقدام كرد. در واقع پنج عامل مورد اشاره در بالا‌، به دو دسته طولی و عرضی تقسیم‌بندی می‌شوند. در توسعه طولی كه در اصطلاح انگلیسی به 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) پردازشی بی‌نهایت ببرد و باعث افت شدید سرعت اجرای برنامه‌ها شود. بنابراین در این‌گونه سیستم‌ها، استفاده بجا و مناسب از منابع سیستم و شبكه و دقت در طراحی و پیاده‌سازی جداول، دیدها، روال‌های ذخیره‌شده و تریگرها بسیار مهم  و حیاتی است.

     


ارسال نظر
Info

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


در غیر اینصورت نظر شما پس از تایید توسط مالک وبلاگ منتشر خواهد شد.

 authimage

درباره من

  • 9163858398
  • جديدترين مقالات مرتبط با مدیریت فناوری اطلاعات (برنامه ريزي منابع سازماني ERP و..)
    احمد محمدی(کارشناس ارشد مدیریت - فناوری اطلاعات و مهندس نرم افزار)
    مشاور نظام مهندسی رایانه ای خراسان رضوی
    ahmad.mohammadi.a@gmail.com

آخرين مطالب بروز شده