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

توسعه 
به‌طور كلي براي افزايش سرعت يك بانك اطلاعاتي مي‌توان به دو روش اقدام كرد. در واقع پنج عامل مورد اشاره در بالا‌، به دو دسته طولي و عرضي تقسيم‌بندي مي‌شوند. در توسعه طولي كه در اصطلاح انگليسي به Scalp up نيز شناخته مي‌شود، مدير سيستم با صرف هزينه‌، به ارتقاي سخت‌افزار (مثل پردازنده‌ها يا هاردديسك‌ها) يا به‌طوركلي ايجاد شبكه‌اي سريع‌تر اقدام مي‌نمايد يا مثلاً سيستم‌عامل خود را به نسخه‌اي جديدتر و پايدارتر ارتقا مي‌دهد. اما در روش عرضي (Scale out) تقريباً با حفظ همان سخت‌افزار و ساختار شبكه، به بهينه‌سازي روابط موجود ميان عناصر دخيل در سرعت مثل برنامه‌هاي كاربردي، بانك اطلاعاتي و سرور اقدام مي‌كند.

توسعه طولي (Scale up) 
هدف اين مقاله پرداختن به توسعه عرضي براي بهره‌برداري بهينه از امكانات موجود است. اما قبل از آن، جادارد به‌صورت خلا‌صه و فهرست‌وار به توسعه طولي و راه‌حل‌هاي آن نيز پرداخته شود تا زمينه براي بررسي‌هاي بيشتر در آينده فراهم گردد.

راه‌حل يكم: افزايش حافظه مورد استفاده SQL Server از يك به سه گيگابايت. اين كار را بايد با دستكاري در فايلBoot.ini سرور 2000 يا 2003 كه SQL Server در آنجا قرار دارد، انجام دهيد. براي اطلاع از چگونگي انجام‌دادن اين كار، به سايت پشتيباني مايكروسافت رجوع كنيد نشاني(http://support.microsoft.com) و در آنجا عبارت AWE SQLServer را جستجو كنيد تا مقالاتي كه در اين زمينه وجود دارد، در دسترس شما قرار گيرد.

راه‌حل دوم: ارتقاي سيستم‌عامل ويندوز 2000 به 2003 كه در فرايند caching، سيستم‌عاملي پايدارتر و هوشمندتر قلمداد مي‌شود.

راه‌حل سوم: استفاده از پردازنده‌هاي Xeon به جاي پنتيوم 4 در سرور. اين پردازنده‌ها به دليل ويژگيhyper threading، مي‌توانند سرعت پردازش اطلاعات در سمت سرور را به دو برابر افزايش دهند.

راه‌حل چهارم: هاردديسك‌هاي اسكازي با 15‌هزار دور در دقيقه و سرعت سه مگابيت در ثانيه و يا Sata با 10‌هزار دور در دقيقه و دو مگابيت در ثانيه نسبت به هاردديسك‌هاي IDE با 7500 دور در دقيقه و يك مگابيت در ثانيه از عملكرد بهتري برخوردارند.پس درصورت امكان، از اين ادوات ذخيره‌سازي در سرور بانك اطلا‌عاتي استفاده كنيد.

 راه‌حل پنجم: جداسازي محل ذخيره فايل‌هاي داده‌اي بانك اطلاعاتي (mdf) و فايل‌هاي لاگ (ldf) برروي دو هاردديسك مختلف يا دو ديسك مختلف از يك RAID. معمولاً براي نگهداري mdf استفاده از RAID1 و براي ldf  استفاده از RAID5 توصيه مي‌شود.

با جداسازي اين فايل‌ها از يكديگر، عمل ايجاد لاگ، وقفه‌اي در خواندن و نوشتن اطلاعات بر روي هاردديسكي كه حاوي فايل‌هاي داده‌اي mdf است، ايجاد نمي‌كند.

راه‌حل ششم: راه‌حل آخر و در واقع مشكل‌ترين راه، تقسيم بانك اطلاعاتي (در صورت لزوم) به دو بانك جدا از هم و بر روي دو سرور مختلف است. به عنوان مثال، فرض كنيد كه عمليات روزانه سيستم شما به دو دسته تقسيم مي‌شود: دسته يكم عملياتي است كه طي آن بايد از آخرين اطلاعات موجود بر روي سيستم استفاده شود و هرگونه تغيير نيز بايد فوراً  در همان لحظه بر روي بانك سيستم‌ها (جداول مربوط به آن‌ها كه به
online transactional Processing) OLTP) مشهورند،) اعمال شود.

دسته دوم نيز شامل عملياتي است كه طي آن مي‌توان از اطلاعات چند ساعت يا چند روز پيش نيز استفاده كرد و لزومي به داشتن آخرين اطلاعات به صورت لحظه‌اي نيست. به عنوان نمونه فرض كنيد تعدادي از گزارش‌هاي سيستم مربوط به تحليل آماري فرايندهاي مختلف ماه پيش است. بنابراين بايد تمهيداتي انديشيده شود تا تهيه اين گزارش‌ها -كه البته ارزش آني ندارند، اما به دليل بازه زماني و نوع تحليل آن‌ها، منابع زيادي از سيستم براي خواندن اطلاعات انبوه و تجزيه و تحليل صرف مي‌شود، بايد بر روي سرور دومي در شبكه كه به
سيستم‌هاي online Analytical Processing) OLAP) مشهورند قرار گيرند تا در كار كساني كه مشغول  كار با OLTP  هستند، خللي ايجاد نشود.

بنابراين سرور دومي را در شبكه در نظر بگيريد و كپي بانك اطلاعاتي موجود در سرور اول را به سرور دوم انتقال دهيد. سپس با استفاده از روش Replication سيستم را طوري تنظيم كنيد تا در مواقع خلوت‌بودن ترافيك سيستم (مثلاً نيمه شب) اطلاعات Upgrade شده آن روز را از سرور اول به سرور دوم كپي كند. كليه برنامه‌هايي كه با OLAP  كار مي‌كنند را به بانك مشابه، اما با آدرس سرور دوم ارجاع دهيد.
 
براي كسب اطلاعات بيشتر در زمينه نحوه انجام‌دادن Replication، عبارت مذكور را در سايت ماهنامه شبكه جستجو كنيد. تا به مقالا‌تي در اين زمينه دست پيدا كنيد.

توسعه عرضي (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  مي‌شود. بنابراين سعي كنيد ايندكس‌هاي ضروري را در سيستم تعريف كنيد. اما در اين راه دست و دلبازي بي‌مورد از خود نشان ندهيد. به عنوان مثال، فرض كنيد در يك شعبه اداره تأمين اجتماعي، جدولي ويژه تعريف بيمه‌شدگان به شكل زير وجود دارد.  

جدولي نيز براي نگهداري وجه حق بيمه از بيمه‌شدگان نيز تعريف شده است.

همان‌طور كه مشاهده مي‌كنيد، ايندكس نوع خوشه‌اي به فيلدي داده شده كه نسبت به بقيه فيلدها در يك جدول كاربرد بيشتري دارد. چرا كه اين نوع ايندكس نسبت به نوع غيرخوشه‌اي سرعت بيشتري دارد. در ضمن در هر جدول از بانك اطلاعاتي شما فقط قادر به تعريف يك ايندكس خوشه‌اي هستيد كه انتخاب فيلد آن اهميت زيادي دارد. بنابراين لزومي ندارد فيلدي كه كليد اوليه است، حتماً به عنوان ايندكس خوشه‌اي انتخاب شود.

نكته مهم ديگر اين است كه لا‌زم است تمام كليدهاي اوليه جداول ايندكس داراي باشند (خوشه‌اي يا غيرخوشه‌اي) نكته ديگر در زمان ساخت ايندكس‌ها فاكتور پرشدن (Fill Factor) آن‌ها است. اين فاكتور در واقع بيانگر ميزان فضاي مياني است كه بايد براي ركوردهايي كه در آينده درج يا حذف مي‌شوند، خالي نگه داشته شود. بنابراين اگر احساس مي‌كنيد جدول شما به‌طور مداوم مورد عمليات حذف و درج (Insert،‌Delete) قرار مي‌گيرد، اين فاكتور را پايين (مثلاً 30 درصد) انتخاب كنيد. اما اگر صرفاً عمليات درج بر روي يك جدول انجام مي‌گيرد و ميزان حذف اطلاعات از آن بسيار كم است، مي‌توانيد اين ميزان را به ارقام بالاتر مثلاً 90 درصد افزايش دهيد. زيرا اين نوع جداول نيازي به داشتن فضاي خالي مياني براي ركوردهايي كه در آينده جانشين ركوردهاي حذف شده مي‌شوند، ندارد.

اين مسئله براي ايندكس‌هايي كه برروي ديدها (Indexed Views) ساخته مي‌شوند نيز صادق است. به‌طوركلي گذاشتن ايندكس برروي ديدها به افزايش سرعت آن‌ها كمك مي‌كند. در اين حالت، كليه مطالب مذكور از جمله سياست استفاده از ايندكس‌هاي خوشه‌اي و غيرخوشه‌اي و همچنينFill Factor در جداول، در مورد ديدها نيز عيناً بايد رعايت گردد.

4 - در هنگام نوشتن دستورات Select يا در هنگام ساختن ديدها، از استفاده بي‌مورد از پارامترهاي پردازش مثلDistinct و LIKE order by و لينك‌هاي خارجي (Outer join) اجتناب كنيد. در صورت استفاده از اين پارامترها، مطمئن باشيد كه گذاشتن آن‌ها كاملاً ضروري است و چاره ديگري نداريد.

5 - از واگذاري پردازش‌هاي رياضي يا آماري سنگين و مداوم به سرور بانك اطلاعاتي بپرهيزيد. مثلا‌ً به دستور زير نگاهي بيندازيد.

به‌جاي اين‌كار، مي‌توانيد ابتدا با استفاده از يك Select معمولي مثل Select a ,b ,c ,d ,E ,F ,G ,h  فيلدهاي موردنظر را در حافظه كلاينت لود كنيد و سپس عمليات رياضي مذكور را در همان جا انجام دهيد. با اين كار پردازشي كه سرور بايد مثلاً براي 50 كلاينت در عرض چند دقيقه انجام دهد، بين آن 50 كلاينت تقسيم مي‌شود و در واقع هر كلاينت فقط سهم پردازشي مربوط به خود را انجام مي‌دهد.

6 - گاهي عمل اجتماع بين دو Select  توسط دستور Union به شدت بر عملكرد و سرعت سيستم اثر منفي مي‌گذارد. بنابراين در صورت امكان به جاي استفاده از روش مذكور، از روش‌هاي ديگري كه هدفتان را برآورده نمايد، استفاده كنيد.

7 - سعي نماييد فيلدهايي كه از نظر مقدار و ارزش با يكديگر مقايسه مي‌شوند، از يك جنس (type) باشند. در غير اين‌صورت سيستم‌مجبور مي‌شود به طور ضمني، عمل تبديل داده را انجام دهد كه كمي برايش وقت‌گير است. به مثال زير توجه كنيد و فرض بگيريد فيلد customer ID در جدول customers از جنس nchar تعريف شده است. 

8 - تاحد ممكن از به كار بردن توابع (چه پيش ساخته توسط SQL Server و چه ساخته شده توسط كاربر) در قسمت WHERE يا order by اجتناب كنيد. مثال زير نمونه‌اي از اين مورد است:

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.......