آموزش پیشرفته SQL Server (جلسه هشتم) -Bottleneck چیست؟

Bottleneck چیست؟، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی Bottleneck، قسمت های قبلی را مرور فرمایید.

Bottleneck چیست؟

Bottleneck بمعنی گلوگاه است.

Bottleneck عاملی است که باعث می شود SQL Server بصورت بهینه اجرا نشود.

Bottleneck یا گلوگاه در واقع زمانی رخ می دهد که تعداد ورودی ها، خیلی بیشتر از خروجی باشد. برای تفهیم بیشتر مسئله، Bottleneck را می توان به یک بطری آب تشبیه کرد. زمانی که می خواهید آب را از بطری خارج کنید هر چقدر دهانه بطری باریک تر باشد، آب کمتری از آن خارج خواهد شد و برعکس هرچه دهانه گشادتری داشته باشد، آب با سرعت بیشتری خارج می شود.

عوامل بوجود آوردنده Bottleneck

شناسایی و رفع گلوگاه ها مهم است اما بطور عمده بروز مشکل Bottleneck دو دلیل اصلی زیر را دارد:

1- کمبود سخت افزار

• RAM
• Disk & RAID
• CPU
• Network
• و...

2- تنظیمات نرم افزاری نامناسب

• برنامه کاربردی شما
• سیستم عامل سرور
• آنتی ویروس
• و...

آشنایی با برنامه ها و ابزارهای Monitoring

با توجه به توضیحات ارائه شده تا اینجا، حتماً می توانید عوامل ایجاد کننده Bottleneck را تشخیص داده و آنها را رفع نمایید.

در زیر بعضی از برنامه ها و ابزارهای Monitoring که به شما کمک می کند تا Bottleneckها را پیدا کنید آمده است:

• استفاده از DMV و DMF های SQL Server (اطلاعات آماری ایندکس ها)
• استفاده از برنامه SQL Profiler (برنامه SQL Profiler)
• استفاده از برنامه Resource Monitor (برنامه Resource Monitor)
• استفاده از برنامه Performance Monitor (برنامه Performance Monitor)
• و...

در ادامه آموزش Bottleneck چیست؟ خواهید دید...!

با خرید جلسه 8 از بسته آموزشی "افزایش کارآیی پایگاه داده" موارد زیر را خواهید دید:

1. توضیح بیشتر در مورد Buttleneck
2. نحوه کار کاردن با برنامه Resource Monitor
3. و...

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 8
• نام فایل ویدئو: 04
• فرمت فایل: mp4.

نقطه شروع بحث بالا (Bottleneck چیست؟) در ویدئو: 02:49

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.

آموزش پیشرفته SQL Server (جلسه پنجم) - Page Split

Page Split، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی Page Split، قسمت های قبلی را مرور فرمایید.

Page Split چیست؟

Page Split بمعنی تقسیم کردن یا دو نیم کردن است.

عامل بوجود آمدن Page Split در واقع Fragmentation است.
در مثال زیر سعی می کنیم، مشکل Page Split را بصورت واقعی نشان دهیم. در این مثال، Extent 1 شامل Page 1 و Page 2 است و Extent 2 شامل Page 3 است:

حالا قصد اضافه کردن رکورد شماره 5 را داریم که قائدتاً در Page 1 و بعد از رکورد 4 باید قرار بگیرد، اما اتفاقی که در عمل خواهد افتاد و منجر به Page Split خواهد شد، این است که ابتدا در Extent 2 یک Page جدید ایجاد می شود و سپس رکورد جدید به همراه رکورد شماره 6 و 8 به آن منتقل خواهد شد:

همان طور که قبلاً گفته شد هر Page به صفحه ی "قبلی"  و "بعدی" خودش اشاره می کند. بنابراین اگر صفحه ی قبلی و بعدی Page جاری دقیقاً Pageهای کناری خودش باشد، بهترین حالت ممکن اتفاق افتاده است و مشکل Fragmentation وجود نخواهد داشت، اما با توجه به شکل بالا، صفحه ی "بعدی" Page شماره یک، Page شماره چهار است که از لحاظ فیزیکی و منطقی یکسان نیست.

توجه: در مثال بالا، هِد هارد برای خواندن Page 1 و Page 4 مجبور است از Page 2 و Page 3 نیز عبور کند که این امر باعث افزایش I/O خواهد شد.

معایت Page Split

1. Page Split باعث افزایش عملیات I/O خواهد شد.
2. Page Split باعث افزایش Waiting ایندکس خواهد شد.
3. Page Split باعث افزایش مدت زمان Locking جدول خواهد شد.

راه حل های رفع مشکل Fragmentation ناشی از Page Split

1. Rebuild کردن ایندکس ها
2. Reorganize کردن ایندکس ها
3. استفاده از گزینه های Fillfactor و Pad_Index هنگام ساخت ایندکس ها
4. Defrag کردن دیسک

در ادامه به اختصار در مورد هر کدام از موارد بالا، توضیحاتی را ارائه خواهد شد.

1- Rebuild کردن ایندکس ها

همان طور که می دانید عملیات های Insert / Update / Delete مختلف روی اطلاعات باعث ایجاد Fragmentation در داده های ایندکس خواهد شد و برای افزایش سرعت کوئری ها بهتر است هر از چند گاهی ایندکس ها را Rebuild نماییم.

• زمان Rebuild، ایندکس مورد نظر بطور کامل پاک شده و مجدداً ساخته می شود.
• با Rebuild کردن ایندکس ها، فضاهای غیرقابل استفاده، حذف خواهند شد.
• عملیات Rebuild باعث می شود تا Statistics مربوط به ایندکس به روز رسانی شود.
• عملیات Rebuild، بشدت Log سیستم را افزایش خواهد داد.

نکته مهم: پیشنهاد می شود اگر میزان fragmentation بزرگتر از 30 درصد باشد عملیات Rebuild و اگر کوچکتر از 30 درصد بود عملیات Reorganize انجام شود. (در ادامه همین بخش، با DMV ای آشنا می شوید که میزان fragmentation را گزارش خواهد داد.)

مثال: در این مثال، تمام ایندکس های تعریف شده روی جدول، Rebuild خواهند شد:

با استفاده از گزینه ONLINE و تنظیم آن با مقدار ON، عملیات Rebuild بصورت Online انجام خواهد شد و کاربران هنگام Rebuild ایندکس ها Wait نخواهند شد.

مثال: Rebuild کردن کلیه ایندکس ها

در خط اول از اسکریبت بالا، با استفاده از پروسیجر SP_MSFOREACHTABLE لیست تمام جداول، چاپ خواهد شد.

ولی در خط دوم بجای علامت ؟ هر بار نام جدول قرار می گیرد و اسکریبت مربوط به Rebuild جدول مذکور ساخته شده و اجرا می شود و این کار تا آخرین جدول از پایگاه داده انجام خواهد شد.

گزینه FILLFACTOR و PAD_INDEX در ادامه توضیح داده خواهد شد.

2- Reorganize کردن ایندکس ها

• در عملیات Reorganize، تنها داده ها در سطح Leaf Level مرتب خواهند شد.
• Reorganize کردن ایندکس ها  Statistics ها را به روز رسانی نمی کند.

نکته مهم: همان طور که قبلا اشاره شد، پیشنهاد می شود اگر میزان Fragmentation کوچکتر از 30 درصد است، عملیات Reorganize انجام شود.

مثال: در این مثال، تمام ایندکس های تعریف شده روی جدول، Rebuild خواهند شد:

 با استفاده از گزینه REORGANIZE و تنظیم آن با مقدار ON، عملیات Rebuild بصورت Online انجام خواهد شد و کاربران هنگام REORGANIZE ایندکس ها Wait نخواهند شد.

گزینه های Fillfactor و Pad_Index در مطالب بعدی توضیح داده خواهد شد.

در ادامه آموزش Page Split خواهید دید...!

با خرید جلسه 5 از بسته آموزشی "افزایش کارایی پایگاه داده" موارد زیر را خواهید دید:

1. با یک مثال عملی، مشکل Page Split نشان داده خواهد شد.
2. چگونه می توان از میزان Page Split مربوط به داده های یک ایندکس مطلع شد.

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 5
• نام فایل ویدئو: 03
• فرمت فایل: mp4.

نقطه شروع بحث بالا (Page Split) در ویدئو: 23:30

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.

آموزش پیشرفته SQL Server (جلسه نهم) - Resource Governor چیست؟

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

Resource Governor چیست؟

Resource Governor بمعنی فرماندهی منابع است.

با استفاده از قابلیت Resource Governor می توان منابع سرور (Memory و Processor و IO) را سهم بندی نمود.

اصطلاحات و مفاهیم مربوط به Resource Governor:

1. Classifier Function چیست؟
2. Workload Group چیست؟
3. Resource Pool چیست؟

در ادامه به توضیح مختصر هر کدام از موارد بالا خواهیم پرداخت.

1- Classifier Function چیست؟

Classifier Function در واقع تابعی است که با استفاده از آن می توان Sessionهای وارد شده به SQL Server را دسته بندی نمود.

دسته بندی Sessionها بر اساس موارد زیر انجام می شود:

1. Login Name
2. App Name (تنظیم App Name در Connection String انجام می شود)
3. و...

2- Workload Group چیست؟

Workload Group گروه هایی است که هر کدام از Sessionهای وارد شده در آن قرار می گیرد.

3- Resource Pool چیست؟

Resource Pool ظرفی است که نحوه ی استفاده از منابع را مشخص می کند.

در شکل زیر، سه مفهوم بالا بخوبی قابل مشاهده است، زمانی که کاربران درخواست های خود را به سرور ارسال می کنند ابتدا توسط Classifier Function دسته بندی شده و تحویل Workload Group می شود، در ادامه توسط Resource Pool منابع اختصاص داده می شود:

مراحل پیکربندی Resource Governor

1. ایجاد Resource Pool
2. ایجاد Workload Group
3. ایجاد Classifier Function
4. پیکربندی SQL Server جهت استفاده از Resource Governor

در ادامه آموزش Resource Governor چیست؟ خواهید دید...!

با خرید جلسه 9 از بسته آموزشی "افزایش کارآیی پایگاه داده" موارد زیر را خواهید دید:

1. استفاده از Resource Governor در یک مثال واقعی
2. در SQL Server 2014 چه تغییراتی به ازای Resource Governor ایجاد شده است؟
3. و...

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 9
• نام فایل ویدئو: 04
• فرمت فایل: mp4.

نقطه شروع بحث بالا (Resource Governor چیست؟) در ویدئو: 31:19

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.

آموزش پیشرفته SQL Server (جلسه هشتم) -SQL Profiler چیست؟

SQL Profiler چیست؟، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی SQL Profiler چیست؟، قسمت های قبلی را مرور فرمایید.

SQL Profiler چیست؟

همان طور که در مطلب قبل (Bottleneck چیست؟) مشاهده فرمودید، برنامه ها و ابزارهای Monitoring مختلفی وجود دارد که به شما کمک می کند تا Bottleneckها را پیدا کنید. اما یکی از مهمترین ابزارهایی که به پیدا کردن Bottleneck به ما کمک می کند برنامه SQL Profiler است، که در اینجا سعی می کنیم بصورت خلاصه در مورد آن توضیح دهیم.

SQL Profiler یک ابزار گرافیکی است.

هدف SQL Profiler نظارت بر کارایی SQL Serverاست.

مزیت های SQL Profiler:

• با استفاده از SQL Profiler می توان کوئری های کند را پیدا کرد.

• دستورات Transact-SQL که منجر به ایجاد مشکل شده اند را شناسایی می کند. (Capture)

نحوه ی اجرای برنامه SQL Profiler

با استفاده از یکی از روش های زیر می توانید SQL Profiler را اجرا نمایید:

1- SQL Server Management Studio را اجرا نموده و منوی Tools -> SQL Server Profiler را اجرا نمایید:

2- در پنجره Run کلمه Profiler را تایپ کرده و سپس روی دکمه OK کلیک نمایید:

آشنایی با اصطلاحات رایج SQL Profiler

برای کار با SQL Profiler باید با مفاهیم زیر آشنا باشید:

1. بررسی مفهوم Event در SQL Profiler
2. بررسی مفهوم Event Class در SQL Profiler
3. بررسی مفهوم Event Category در SQL Profiler
4. بررسی مفهوم Data Columnدر SQL Profiler
5. بررسی مفهوم Template در SQL Profiler
6. بررسی مفهوم Trace در SQL Profiler
7. بررسی مفهوم Filter در SQL Profiler

در ادامه به توضیح مختصر هر کدام از اصطلاحات بالا می پردازیم:

1- بررسی مفهوم Event در SQL Profiler

Event کار یا Actionی است که توسط موتور پایگاه داده ایجاد می شود. بعنوان مثال هر کدام از کارهای زیر یک Event است:

• Login کردن به SQL server
• Disconnect شدن ارتباط کاربر
• اجرای Stored Procedure
• شروع و پایان اجرای یک مجموعه دستور (Batch)
• ایجاد یک Lock روی اشیاء موجود در بانک اطلاعاتی
• و...

تمام داده هایی که توسط یک Event تولید می شود داخل یک trace و در یک ردیف نشان داده می شود. هر ردیف شامل ستون هایی است که جزئیات هر Event را شامل می شود.

2- بررسی مفهوم Event Class در SQL Profiler

Event Class نمونه ای از Event است، Event Class شامل تمام داده هایی است که می تواند توسط یک Event گزارش شود.

در زیر چند نمونه از event Classها آورده شده است:

• SQL:BatchCompleted

• Audit Login

• Audit Logout

• Lock:Acquired

• Lock:Released

SQL:BatchCompleted یک Event Class برای مشاهده دستورات SQL است که بطور کامل اجرا شده اند.

3- بررسی مفهوم Event Category در SQL Profiler

همان طور که از اسم این رویداد مشخص است، جهت گروه بندی Event Class ها مورد استفاده قرار می گیرد.

برای مثال تمام رویدادهای که منجر به Lock می شوند در یک Event Category بنام Locks قرار می گیرند.

4- بررسی مفهوم Data Column در SQL Profiler

به ازای هر رویداد (Event) یک سری اطلاعات قابل نمایش است، با استفاده از مفهوم Data Column می توانید مشخص کنید که کدام یک از خصوصیات و جزئیات رویداد نمایش داده شود.

بعنوان مثال در رویداد SQL:BatchCompleted ستون TextData متن دستور TSQL می باشد.

5- بررسی مفهوم Template در SQL Profiler

تنظیمات پیش فرض برای یک Trace را مشخص می کند.

هر Template شامل یکسری رویداد یا Event از پیش تعریف شده و پیشفرض است.

بعنوان مثال شما می توانید Template ی را تعریف کنید که Eventها و Data Column های خاصی را نشان دهد.

6- بررسی مفهوم Trace در SQL Profiler

هدف یک Trace جمع آوری داده براساس  Even Class ها، Data Columnها و فیلترهاست.

برای مثال شما می توانید یک Trace برای مونیتور کردن exception error ها تعریف نمایید. برای انجام این کار شما باید از بین Event Classها Exception را انتخاب نمایید و از قسمت Data Column می توانید Error و State و Severity را انتخاب نمایید.

7- بررسی مفهوم Filter در SQL Profiler

با استفاده از مفهوم Filter در SQL Profiler می توانید برای جمع آوری Data شرط تعیین نمایید. بعنوان مثال می توانید تعیین کنید که کوئری هایی که زمان اجرای آنها بالای 5 ثانیه است را جمع آوری کند.

فیلتر کردن باعث کاهش حجم Trace می شود.

در ادامه آموزش SQL Profiler چیست؟ خواهید دید...!

با خرید جلسه 8 از بسته آموزشی "افزایش کارآیی پایگاه داده" موارد زیر را خواهید دید:

1. استفاده از SQL Profiler برای آنالیز دستوراتی که روی یک پایگاه داده خاص اجرا می شوند.
2. استفاده از SQL Profiler برای آنالیز دستوراتی که زمان اجرای آنها بالای 2000 میلی ثانیه است.
3. ذخیره و بازیابی اطلاعات Trace شده توسط SQL Profiler
4. نمایش Execution Plan کوئری های اجرا شده در سرور
5. Trace کردن Stored Procedur های در حال اجرا
6. ایجاد Trace با استفاده از دستورات TSQL
7. و...

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 8
• نام فایل ویدئو: 04
• فرمت فایل: mp4.

نقطه شروع بحث بالا (SQL Profiler چیست؟) در ویدئو: 08:22

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.

آموزش پیشرفته SQL Server (جلسه دهم) - Transaction چیست؟

بررسی Transaction، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی Transaction چیست؟، قسمت های قبلی را مرور فرمایید.

Transaction چیست؟

Transaction بمعنی تراکنش بوده و یک واحد منطقی کار است.

Transaction در واقع مجموعه ای از چند دستور است که یا با هم با موفقیت اجرا می شوند و یا هیچکدام از آنها اجرا نخواهند شد.

توجه: تضمین موفقیت اجرای کامل Transaction در SQL با استفاده از Log File خواهد بود.

ACID چیست؟

ACID یکی از ویژگی های است که یک Transaction باید داشته باشد.

ACID سرنام واژگان Atomicity Consistency Isolation Durability بحساب می آید.

1. Atomicity: بمعنی اتمی بودن است و نشان می دهد که بخشی از Transaction به تنهایی قابل اجرا نمی باشد.
2. Consistency: بمعنی ثبات و سازگاری است و تضمین می کند که پس از اجرای موفقیت آمیز Transaction و یا شکست آن داده ها در حالت سازگار و معتبر قرار می گیرند.
3. Isolation: بمعنی ایزولاسیون (جداسازی) است و تضمین می کند که داده های درگیر در Transaction، توسط سایر پروسه ها غیرقابل تغییر هستند.
4. Durability: بمعنی دوام (بقا) است و تضمین می کند که عوامل ناخواسته مثل رفتن برق و... باعث حذف تأثیر Transaction نهایی شده (Commit) نشوند.

انواع Transaction در SQL Server

در SQL Server سه نوع Transaction وجود دارد:

1. Auto Commit Transaction
2. Explicit Transaction
3. Implicit Transaction

در ادامه به توضیح هر کدام از موارد بالا خواهیم پرداخت.

1- Auto Commit Transaction

Auto Commit Transaction بمعنی اعمال خودکار تراکنش است.

در حالت Auto Commit Transaction تغیرات بلافاصله بعد از اجرای دستورات، اعمال یا Commit می شوند. به همین خاطر است که این حالت را Auto Commit Transaction می نامند. Auto Commit Transaction حالت پیشفرض SQL Server است. 

در SQL Server هر کوئری به تنهایی یک Transaction است. بعنوان مثال کوئری ای را در نظر بگیرد که قرار است 500 هزار رکورد را حذف کند. اگر در میانه ی اجرای کوئری برق برود، SQL Server تضمین می کند که بعد از بالا آمدن دوباره سرور، کل داده ها در حالت معتبر قرار بگیرند. (Consistency)

2- Explicit Transaction

Explicit Transaction بمعنی تراکنش های صریح است.

در حالت Explicit Transaction می توانیم روی شروع و پایان Transaction ها کنترل داشته باشیم. این نوع Transaction در Stored Procedure ها بسیار پرکاربرد است.

مثال: در مثال زیر دو تراکنش از نوع Explicit Transaction داریم. توجه داشته باشید که Transactionها زمانی پایان می یابند که یا Commit و یا Rollback شوند. (Commit بعمنی اعمال تغییرات و RollBack بمعنی برگشت به عقب است)

زمانی که مثال زیر را در محیط SQL Server اجرا نمایید، Transaction اول با موفقیت اجرا  می شود و پیغام "Data Inserted" در ناحیه Message نمایان خواهد شد.

توجه: در مثال زیر فیلد "contract" که با رنگ قرمز متمایز شده است از نوع Not Null است. بنابراین حتماً باید مقدار دهی شود.

اما در Transaction دوم بدلیل اینکه فیلد "contract" مقدار دهی نشده است با خطای زمان اجرا (Runtime Error) مواجه می شویم و Error@@ یک مقدار بزرگتر از صفر را بر می گرداند بنابراین Transaction دوم RollBack شده و پیغام "Error Occurred. Transaction Cancelled" نمایان خواهد شد. توجه داشته باشید زمانی که کوئری مورد نظرمان بدون هیچ خطایی اجرا شود متغییر سراسری Error@@، مقدار صفر را بر می گرداند:

Declare @ErrNo int
Begin Transaction;
Insert into Authors(Au_id, au_lname, au_fname, Phone, contract)
values ('112-33-1811', 'Jane', 'Marey', '409 210-2331', 1);
Set @ErrNo = @@Error;
if @ErrNo != 0
        Begin
               Print 'Error Occurred. Transaction Cancelled';
               RollBack;
        End
else
        Begin
               Print 'Data Inserted.';
               Commit;
        End

Begin Transaction;
Insert into Authors(Au_id, au_lname, au_fname, Phone)
values('112-33-1234', 'Billy', 'Jones', '409 234-2232');
Set @ErrNo = @@Error;
if @ErrNo != 0
        Begin
               Print 'Error Occurred. Transaction Cancelled';
               RollBack;
        End
else
        Begin
               Print 'Data Inserted.';
               Commit;
        End

3- Implicit Transaction

Implicit Transaction بمعنی تراکنش های ضمنی است.

در حالت Implicit Transaction می توانیم روی عملیات های RollBack و Commit کنترل داشته باشیم. یک Transaction جدید زمانی می تواند شروع شود که یک Commit یا یک RollBack اتفاق بیافتد.

در حالت Implicit Transaction در پایان هر تراکنش باید به صراحت دستور Commit و یا RollBack ذکر شود.

توجه: با استفاده از دستورات زیر می توانیم این نوع Transaction را هر جا که نیاز شد فعال یا غیرفعال نماییم.

Set Implicit_Transactions On;
Set Implicit_Transactions Off;

زمانی که Implicit Transaction غیرفعال شود، SQL Server به حالت Auto Commit Transaction برمی گردد.

مثال:

 Set Implicit_Transactions On;
--Transaction 1
Update Authors Set Au_LName = 'Black' where Au_Id = '172-32-1176';
commit;

--Transaction 2
Update Authors Set Au_LName = 'Voyer' where Au_Id = '213-46-8915';
Rollback;

--Transaction 3
Update Authors Set Au_LName = 'Peterson' where Au_Id = '238-95-7766';
commit;

--Select the Authors, to see the effect of Update statement
Select Au_Id, Au_Fname + ',' + Au_Lname as Name,Phone, City

from Authors 
where Au_id in ('172-32-1176','213-46-8915','238-95-7766');

Set Implicit_Transactions Off;

در مثال بالا، سه دستور Update همراه با Commit یا RollBack وجود دارد. توجه داشته باشید که در اینجا ما آزادیم که به ازای هر دستور، عملیات RollBack و یا Commit را انجام دهیم در صورتی که در حالت Auto Commit Transaction چنین قابلیتی وجود نداشت. اما با اینکه می توانیم پایان Transaction را مشخص کنیم اما روی شروع Transaction کنترلی نداریم.

بعد از اولین Update دستور Commit آمده است یعنی ما می خواهیم تغییرات Commit شوند بنابراین در پایگاه داده، نام خانوادگی نویسنده به مقدار "Black" تغییر پیدا می کند. بلافاصله بعد از آن Transaction جدید آغاز می شود.

در نهایت اولین و آخرین دستور Update اجرا می شوند و تغییرات روی پایگاه داده اعمال می شود و Update دوم انجام نشده و دور انداخته می شود.

در ادامه آموزش Transaction چیست؟ خواهید دید...!

با خرید جلسه 10 از بسته آموزشی "افزایش کارآیی پایگاه داده" موارد زیر را خواهید دید:

1. توضیحات بسیار جامع تر در مورد انواع Transaction همراه با مثال توسط مدرس
2. و...

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 10
• نام فایل ویدئو: 03
• فرمت فایل: mp4.

نقطه شروع بحث بالا (Transaction چیست؟) در ویدئو: 14:18

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.

آموزش پیشرفته SQL Server (جلسه پنجم) - Column Store Index

Column Store Index، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی Column Store Index، قسمت های قبلی را مطالعه فرمایید.

آشنایی با ColumnStore Index

قابلیت Column Store Index در SQL Server 2012 ارائه شده است.

Column Store Index برای کار کردن با بانک های اطلاعاتی حجیم که اصطلاحاً Data Warehouse یا انبار داده نامیده می شوند، مناسب است.

Column Store Index یک انقلاب بزرگ در ایندکس ها ایجاد کرده است و به شدت سرعت کوئری ها را افزایش می دهد.

مهمترین مزیت Column Store Index به حداقل رساندن Logical Read و Physical Read است.

در Column Store Index، داده ها بصورت فشرده ذخیره می شوند.

روش های ذخیره داده در ایندکس:

1. Row Based
2. Column Based

در ادامه به طور اختصار در مورد هر کدام از موارد بالا توضیح خواهیم داد.

1- ذخیره اطلاعات بصورت Row Based

 داده های هر رکورد در یک یا چند Page ذخیره می شوند. لطفاً به مثال زیر توجه کنید، ردیف 1 و 2 و 3 در Page 1 و ردیف 4 و 5 و 6 در Page 2 قرار دارند:

<------

2- ذخیره اطلاعات بصورت Column Based

به ازای هر کدام از فیلدهای جدول یک Page مجزا در نظر گرفته می شود.

اگر داده های یک فیلد در یک Page جا نشد، یک Page دیگر اختصاص داده می شود.

<------

<------

 اگر به قسمت های متمایز شده در شکل بالا دقت کنید، متوجه خواهید شد که در فیلد Name مقدار "مهدی" و در فیلد Family مقدار "پهلوان" و "طاهری" تکراری است، در اینجا حتماً این فکر به سرتان خطور کرده که می توان بسادگی مقادیر "مهدی"، "پهلوان" و "طاهری" را بنحوی یکبار درج کرد و از این طریق حجم داده ها را کاهش داد.

بله همین طور است در اینجا به یکی از مهمترین مزیت های Column Store Index پی بردید و آن چیزی نیست جز، کاهش حجم داده های ایندکس و این کاهش حجم باعث می شود که سرعت جستجو در ایندکس ها به شدت افزایش یابد.

مثال: مقایسه جستجو در ایندکس از نوع Column Store Index در مقابل ایندکس معمولی

Select ID,Name,Family
from Employee Where Family="پهلوان"

اگر کوئری بالا از ایندکس عادی که ساختارش بصورت B-Tree است استفاده کند، با توجه به اینکه فامیلی "پهلوان" در 1 Page و Page 2 قرار دارد، باید هر دو Page را وارد حافظه کرده و شروع به Scan و آنالیز آنها نماید در حالی که در ایندکس Column Store Index فقط Page 3 به حافظه منتقل شده و به این ترتیب هم در میزان استفاده از حافظه صرفه جویی می شود و هم Scan داده ها سریع تر انجام می شود. تمام این موارد باعث کاهش چشم گیر I/O خواهد شد.

نحوه ی ایجاد Column Store Index در SQL Server 2012

در SQL Server 2012 فقط ایندکس از نوع Non-clustered می توان ایجاد کرد.

با ایجاد ایندکس از نوع Column Store Index جدول مذکور بصورت اتوماتیک در حالت Read-only قرار می گیرد. و اگر قصد Update / Insert / Delete دارید، ابتدا باید ایندکس Column Store Index را غیر فعال (Disable) کنید و بعد از انجام عملیات های مذکور، مجدداً آنرا Rebuild نمایید. (برای Disable یا Rebuild کردن ایندکس های Column Store Index در SQL Server 2012 وارد پنل Object Explorer شده و روی ایندکس مورد نظرتان راست کلیک کنید. در منوی ظاهر شده گزینه های Disable و Rebuild را مشاهده خواهید کرد)

مثال 1: اسکریبت ساخت یک ایندکس Non-clustered از نوع Column Store Index

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_ColumnStore 
    ON Employees(FirstName,LastName,HireDate)

در مثال بالا یک ایندکس با نام "IX_ColumnStore" روی سه فیلد FirstName,LastName,HireDate از جدول Employees البته از نوع Column Store Index ایجاد شده است.

نحوه ی ایجاد Column Store Index در SQL Server 2014

مشکل ساخت ایندکس از انواع دیگر که در SQL Server 2012 وجود داشت، در اینجا رفع شده است. همچنین مشکل Read-only نیز رفع شده و داده های جدول را می توان Update / Insert / Delete نمود.

نکته مهم: اگر در SQL Server 2014 قصد ساخت ایندکس Clustered از نوع Column Store Index دارید، قبل از آن باید از خیر تمام ایندکس های دیگر و حتی کلید جدولتان بگزرید...!  اما این حالت در مورد ایندکس Non-Clustered صادق نیست.

در ادامه آموزش Column Store Index خواهید دید...!

با خرید جلسه 5 از بسته آموزشی "افزایش کارایی پایگاه داده" موارد زیر را خواهید دید:

1. Data Typeها قابل استفاده در Column Store Index کدامند؟
2. چه محدودیت های برای ساخت Column Store Index وجود دارد؟
3. مثال عملی استفاده از Column Store Index

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 5
• نام فایل ویدئو: 02
• فرمت فایل: mp4.

نقطه شروع بحث بالا (گزینه DATA_COMPRESSION) در ویدئو:  21:56

منابع کمکی Column Store Index

1. Column Store Index Description

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.

دوره بسیار جامع آموزش پیشرفته SQL Server با هدف افزایش کارایی و سرعت بانک اطلاعاتی شامل 10 جلسه می باشد و شما کاربران محترم بیاموز هم اکنون می توانید با مطالعه و انجام موارد ذکر شده در این آموزش، سرعت چشمگیری را در بانک های اطلاعاتی مشاهده نمایید. 

آموزش پیشرفته SQL Server (جلسه پنجم) - گزینه FILLFACTOR

بررسی گزینه FILLFACTOR، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی گزینه FILLFACTOR، قسمت های قبلی را مطالعه فرمایید.

همان طور که در مطلب گزینه های ساخت ایندکس مشاهده کردید، هنگام ساخت ایندکس با گزینه های مختلفی سروکار داریم، در اینجا به بررسی گزینه FILLFACTOR که با رنگ قرمز متمایز شده خواهیم پرداخت:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON table_name (column [ ASC | DESC ] [ ,...n ] )    
    [ WITH (  
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR =fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP =max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE}  ]

بررسی گزینه FILLFACTOR

همان طور که در مطلب Page Split مطالعه فرمودید، یکی از راه حل های رفع مشکل Fragmentationاستفاده از گزینه FILLFACTOR است.

• گزینه FILLFACTOR با یک عدد بین 1 تا 100 تنظیم می شود.
• با استفاده از گزینه FILLFACTOR می توان یک فضای رزروی برای هر Page در قسمت Leaf Level ایندکس در نظر گرفت.
• پیشنهاد می شود، برای جداولی که تغییرات فیلد ایندکس شان بسیار بالاست، مقدار 75 تا 85 در نظر گرفته شود و جداولی که خیلی کم تغییر می کنند مقدار 93 الی 95 پیشنهاد می شود.

توجه: اگر گزینه FILLFACTOR را با مقدار مثلاً 75 تنظیم کنید، این یعنی اینکه 75 درصد از فضای Page پر خواهد شد و 25 درصد مابقی برای آینده رزرو خواهد شد. بنابراین عدد تنظیم شده برای FILLFACTOR، میزان پر شدن Page را تعیین خواهد کرد.

نکته مهم: اگر مقدار فیلدی که قرار است روی آن ایندکس تعریف شود، بصورت پیوسته و منظم جلو نمی رود و داده ها بصورت کوچک و بزرگ Insert می شوند، بهتر است گزینه FILLFATCTOR برای آن تنظیم شود.

مثال عملی گزینه FILLFACTOR

مثال 1: در مثال زیر هنگام rebuild کردن ایندکس، گزینه FILLFACTOR تنظیم شده است:

ALTER INDEX ALL ON T2 REBUILD WITH (ONLINE=ON,FILLFACTOR=75)

در ادامه آموزش گزینه FILLFACTOR خواهید دید...!

با خرید جلسه 5 از بسته آموزشی "افزایش کارایی پایگاه داده" موارد زیر را خواهید دید:

1. با یک مثال عملی نحوه ی استفاده از FILLFACTOR نشان داده خواهد شد.

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 5
• نام فایل ویدئو: 03
• فرمت فایل: mp4.

نقطه شروع بحث بالا (گزینه FILLFACTOR) در ویدئو:  38:25

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.

آموزش پیشرفته SQL Server (جلسه پنجم) - گزینه PAD_INDEX

بررسی گزینه PAD_INDEX، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی گزینه PAD_INDEX، قسمت های قبلی را مطالعه فرمایید.

همان طور که در مطلب گزینه های ساخت ایندکس مشاهده کردید، هنگام ساخت ایندکس با گزینه های مختلفی سروکار داریم، در اینجا به بررسی گزینه PAD_INDEX که با رنگ قرمز متمایز شده خواهیم پرداخت:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON table_name (column [ ASC | DESC ] [ ,...n ] )    
    [ WITH (  
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR =fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP =max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE}  ]

بررسی گزینه PAD_INDEX

همان طور که در مطلب Page Split مطالعه فرمودید، یکی از راه حل های رفع مشکل Fragmentation استفاده از گزینه PAD_INDEX است.

• گزینه PAD_INDEX با یکی از مقادیر ON یا OFF تنظیم می شود.
• تنظیم گزینه FILLFACTOR باعث ایجاد فضای رزروی برای هر Page در قسمت Leaf Level خواهد شد، حالا اگر PAD_INDEX را با مقدار ON تنظیم کنید، فضای رزروی، برای قسمت میانی نیز در نظر گرفته خواهد شد.

در ادامه آموزش گزینه PAD_INDEX خواهید دید...!

با خرید جلسه 5 از بسته آموزشی "افزایش کارایی پایگاه داده" موارد زیر را خواهید دید:

1. راه حل مشکل File Level fragmentation چیست؟
2. بهترین راه حل برای راه اندازی سرور پایگاه داده کدام است؟

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 5
• نام فایل ویدئو: 04
• فرمت فایل: mp4.

نقطه شروع بحث بالا (گزینه PAD_INDEX) در ویدئو:  00:06

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.

آموزش پیشرفته SQL Server (جلسه نهم) - نظارت بر عملکرد دیسک

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

نظارت بر عملکرد دیسک

همان طور که در برنامه Performance Monitor مشاهده فرمودید یکی دیگر از اشیاءی که می توانیم مورد نظارت قرار دهیم دیسک سرور است. اما قبل از اینکه به نحوه ی مونیتور کردن دیسک بپردازیم به توضیح چند مفهوم می پردازیم:

مفهوم Physical Disk: به دیسک های فیزیکی موجود در سرور Physical Disk اطلاق می شود.

مفهوم Logical Disk: هر دیسک فیزیکی می تواند شامل چندین پارتیشن یا درایو باشد به پارتیشن های موجود در سرور Logical Disk گفته می شود.

مفهوم RPM: سرعت دوران صفحات مربوط به یک دیسک در دقیقه است و هرچقدر RPM بالا باشد، سرعت Reed و Write افزایش پیدا خواهد کرد.

مفهوم Seek Time: زمان دسترسی Head دیسک به محل ذخیره سازی بلاک داده را Seek Time یا زمان جستجو می گویند. این زمان بر اساس میلی ثانیه است. بنابران هرچه Seek Time پایین تری داشته باشیم، سرعت Read و Write نیز افزایش پیدا خواهد کرد.

مفهوم Buffer: حافظه ای موقت بر روی دیسک است که امکان Cache کردن اطلاعات را فرآهم می کند. مکانیزم Cache باعث کاهش مراجعه مکرر به دیسک می شود.

بررسی انواع استانداردهای موجود برای دیسک (HDD)

1- استاندارد IDE: این نوع دیسک ها بصورت مکانیکی هستند و سرعت بسیار پایینی دارند و در PC استفاده می شوند.

2- استاندارد SATA: این نوع دیسک ها به صورت مکانیکی هستند و دارای سرعت انتقال بالاتری هستند (1.5Gb و 3Gb و 6Gb) و در PC استفاده می شوند.

3- استاندارد SCASI: این نوع دیسک ها به صورت مکانیکی هستند و دارای سرعت ذخیره و بازیابی مناسبو در سرورهای قدیمی استفاده می شوند. در این نوع دیسک ها قابلیت Hot Plug یعنی شناسایی یا Detect پردازشگر یا RAM اضافه شده در حالت کار وجود دارد.

4- استاندارد SAS: این نوع دیسک ها به صورت مکانیکی هستند و دارای سرعت ذخیره و بازیابی بالاو در سرورهای جدید استفاده می شوند. در این نوع دیسک ها قابلیت Hot Plug وجود دارد.

5- استاندارد SSD: این نوع دیسک ها به صورت الکترونیکیهستند و دارای سرعت ذخیره و بازیابی بسیار بالا (4.4GB/s و 6.0GB/s)و در سرورهای جدید استفاده می شوند. در این نوع دیسک ها قابلیت Hot Plug پشتیبانی می شود و البته قیمت بالایی دارند. از مزایای هاردهای SSD می توان به عمر طولانی، بدون صدا، مقاوم در برابر ضربه، مصرف برق کمتر، گرمای کمتر و البته سرعت بالا اشاره نمود.

توجه: در هاردهای Hybrid همزمان از HDD و SSD استفاده می شود و البته داده های پر استفاده در SSD قرار می گیرد.

RAID چیست؟

RAID سرنام واژگان Redundant Array if Independent Disk بحساب می آید.

RAID آرایه ای از دیسک های مستقل است که بطور موازی عملیات IO انجام می دهند. در واقع در تکنولوژی RAID چند HDD در کنار هم قرار می گیرند و  به یک HDD واحد تبدیل می شوند.

مزایای RAID:

1. افزایش کارایی و Performance
2. جلوگیری از دست رفتن اطلاعات یا Fault Tolerant

نحوه پیاده سازی RAID:

تکنولوژی RAID را به دو صورت می توان پیاده کرد:

1. پیاده سازی به صورت نرم افزای
2. پیاده سازی به صورت سخت افزاری (RAID 0 و RAID 1 و RAID 5 و RAID 10 و...)

 RAID Controller چیست؟

قطعه ای سخت افزاری که تکنولوژی RAID را پیاده سازی می کند.

لیست Counterها یا شمارنده های مهم دیسک

حالا که با مفاهیم مقدماتی آشنا شدید نوبت به معرفی Counter های مهم برنامه Performance Monitor برای نظارت بر عملکرد دیسک می رسد.

Counterهای لیست شده در زیر، همه برای مشخص کردن عملکرد و کارایی دیسک استفاده می شوند:

1. Physical Disk : Disk Time
2. Physical Disk : Current Disk Queue Length
3. Physical Disk : AVG. Disk Queue Length
4. Physical Disk :Disk Transfer/sec
5. Physical Disk : Disk Byte/sec
6. Physical Disk : AVG. Disk/sec Read
7. Physical Disk : AVG. Disk/sec Write

در ادامه به توضیح مختصر هر کدام از Counterهای بالا خواهیم پرداخت.

1- Physical Disk : Disk Time

Physical Disk، نام Object این Counter است.

این Counter میزان مشغول بودن دیسک را به شکل درصدی نمایش می دهد.

توجه: اگر مقدار این Counter کمتر از 85 درصد باشد می توان گفت که دیسک بصورت نرمال در حال کار است.

2- Physical Disk : Current Disk Queue Length

Physical Disk، نام Object این Counter است.

این Counter صف انتظار برای دسترسی به دیسک را مشخص می کند.

توجه: اگر مقدار این Counter کمتر از 2 باشد می توان گفت که دیسک بصورت نرمال در حال کار است.

3- Physical Disk : AVG. Disk Queue Length

Physical Disk، نام Object این Counter است.

این Counter میانگین صف انتظار برای دسترسی به دیسک را مشخص می کند.

توجه: اگر مقدار این Counter کمتر از 2 باشد می توان گفت که دیسک بصورت نرمال در حال کار است.

4- Physical Disk :Disk Transfer/sec

Physical Disk، نام Object این Counter است.

این Counter میزان خواندن و نوشتن دستورالعمل ها در دیسک را مشخص می کند.

توجه: اگر مقدار این Counter کمتر از 400 باشد می توان گفت که دیسک بصورت نرمال در حال کار است.

5- Physical Disk : Disk Byte/sec

Physical Disk، نام Object این Counter است.

این Counter حجم داده هایی که از دیسک خوانده و یا نوشته می شود را نشان می دهد.

توجه: اگر مقدار این Counter کمتر از 800 مگابایت باشد می توان گفت که دیسک بصورت نرمال در حال کار است.

6- Physical Disk : AVG. Disk/sec Read

Physical Disk، نام Object این Counter است.

این Counter مدت زمان خواندن داده ها از دیسک را نشان می دهد.

توجه: اگر مقدار این Counter کمتر از 10 میلی ثانیه باشد می توان گفت که دیسک بصورت نرمال در حال کار است.

7- Physical Disk : AVG. Disk/sec Write

Physical Disk، نام Object این Counter است.

این Counter مدت زمان نوشتن داده ها از دیسک را نشان می دهد.

توجه: اگر مقدار این Counter کمتر از 10 میلی ثانیه باشد می توان گفت که دیسک بصورت نرمال در حال کار است.

در ادامه آموزش نظارت بر عملکرد دیسک خواهید دید...!

با خرید جلسه 8 از بسته آموزشی "افزایش کارآیی پایگاه داده" موارد زیر را خواهید دید:

1. توضیح در مورد نحوه پیاده سازی RAID 0 و RAID 1 و RAID 5 و RAID 10
2. راه حل های رفع مشکلات مربوط به دیسک
3. و...

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 9
• نام فایل ویدئو: 03
• فرمت فایل: mp4.

نقطه شروع بحث بالا (نظارت بر عملکرد دیسک) در ویدئو: 39:12

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.