راهكارهايي براي افزايش سرعت در بانك‌هاي اطلاعاتي SQL Server
اشاره :
شايد بعضي از شما تاكنون دست‌اندركار يكي دو پروژه مبتني بر بانك‌هاي اطلاعاتي بوده‌ايد و يا اكنون با چنين پروژه‌هايي سروكار داريد. اگر تجربه كار در محيط‌هاي متوسط (مثلاً با يكصد كاربر) يا بزرگ را نيز داشته باشيد، قطعاً با مسائل و مشكلات مربوط به كاهش سرعت ناشي از افزايش تعداد كاربران يا حجم پردازشي آن‌ها مواجه شده‌ايد.
اين مقاله با استناد به منابع مايكروسافتي، راهكارهايي را براي بهبود سرعت و كارايي سيستم در بانك‌هاي اطلاعاتي با تعداد كاربر و حجم پردازش زياد مورد بررسي قرار مي‌دهد. شايان ذكر است كه در تمامي نمونه‌هاي مورد اشاره، بانك‌هاي اطلاعاتي مبتني بر محصول مايكروسافت يعني SQL Server2000 مدنظر قرار گرفته است.
طبق بررسي‌هايي كه كارشناسان مايكروسافت انجام داده‌اند، كارايي يك سيستم بانك اطلاعاتي به پنج عامل مختلف بستگي دارد كه به ترتيب اهميت عبارتند از: برنامه نوشته شده، پايگاه داده موردنظر، سخت‌افزار سرور يا كلاينت، تنظيمات و نسخه مورد استفاده 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 مي‌شود. بنابراين سعي كنيد ايندكس‌هاي ضروري را در سيستم تعريف كنيد. اما در اين راه دست و دلبازي بي‌مورد از خود نشان ندهيد. به عنوان مثال، فرض كنيد در يك شعبه اداره تأمين اجتماعي، جدولي ويژه تعريف بيمه‌شدگان به شكل زير وجود دارد.
مبلغ
تاريخ
شماره سريال
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).
http://pnu-club.com/imported/2009/11/1725.jpg ايندكس گذاري برروي ديده ها(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) پردازشي بي‌نهايت ببرد و باعث افت شديد سرعت اجراي برنامه‌ها شود. بنابراين در اين‌گونه سيستم‌ها، استفاده بجا و مناسب از منابع سيستم و شبكه و دقت در طراحي و پيادهسازي جداول، ديدها، روال‌هاي ذخيره‌شده و تريگرها بسيار مهم و حياتي است.
نوشته :مهيار داعي‌الحق ماهنامه شبکه
