دوره ۳ در ۱ آموزش performance tuning در SQL Server

1- آشنایی با مفهوم DMV و DMF در SQL Server
2- بررسی مفهوم Database
3- بررسی مفهوم Data File و Log File
4- بررسی معماری Log File
5- بررسی معماری منطقی Log File
6- بررسی معماری فیزیکی Log File
7- بررسی نحوه اعمال تنظیمات مناسب برای Data File و Log File
8- بررسی تنظیمات مفید سیستم عامل برای فایل‌های بانک اطلاعاتی
9- بررسی تنظیمات Database Instant File Initialization
10- آشنایی با Buffer Pool در SQL Server
11- بررسی تغییرات مربوط به Buffer Pool در SQL Server 2022
12- بررسی مفهوم Checkpoint
13- چه کارهایی در هنگام Checkpoint رخ می‌دهد؟
14- بررسی اولیه مفهوم Lock
15- بررسی اولیه مفهوم Latch
16- آشنایی با DMVها و DMFهای مهم و پرکابرد در این حوزه
17- معرفی ابزارهایی کاربردی برای مشاهده محتوای Pageها

Log File  و اعمال تنظیمات مناسب برای آن

همه ما می‌دانیم که بانک‌های اطلاعاتی در SQL Server حداقل از یک Data File و یک Log File تشکیل شده است. چنانچه ما معماری ذخیره‌سازی داده در هر کدام از این فایل‌ها را بدانیم رفتارمان نسبت به این فایل‌ها عوض می‌شود. یکی از مواردی که من همیشه در بانک‌های اطلاعاتی بزرگ تنظیمات مناسبی بر روی آن اعمال می‌کنم، تنظیمات مربوط به Log File است.

نوع دسترسی در این فایل به صورت Sequential (دسترسی ترتیبی) می‌باشد و برای افزایش کارایی بانک اطلاعاتی باید تنظیمات مناسبی بر روی آن اعمال نمود. در حال حاضر در SQL Server 2022 با تنظیم اصولی و مناسب برای Initial Size و همچنین نحوه رشد می‌توانیم به عملکرد قابل قبولی در این زمینه رسید. ما در این دوره با نحوه اعمال تنظیمات مناسب برای Log File و Data File آشنا خواهیم شد.

1- بررسی مفهوم Page در Data File
2- بررسی معماری ذخیره‌سازی داده‌ها در Page
3- معرفی اجمالی انواع Pageها و کاربرد هر کدام از آنها
4- بررسی مفهوم Allocation Page
5- بررسی مفهوم Row-Overflow
6- بررسی تکنیک‌های استفاده مناسب از Data Typeها
7- بررسی مفهوم Extent
8- معرفی انواع Extentها در SQL Server

چرا باید جزئیات نحوه ذخیره سازی داده‌ها در جداول را تا به این حد یاد بگیرم؟

این سوالی است که خیلی از دوستانی که در دوره  Performance Tuning در SQL Server شرکت می‌کنند از من می‌پرسند. شما هر چقدر با جزئیات و ریزه کاری‌های مربوط به SQL Server آشنا شوید رفتارتان با آن عوض می‌شود و به خوبی می‌توانید از عهده مشکلات مربوط به کارایی بانک‌های اطلاعاتی برآید.

1- بررسی مفهوم File Group
2- بررسی کاربردهای File Group
3- بررسی مزایای استفاده از File Group
4- بررسی انواع File Groupها
5- بررسی نحوه تعریف اصولی File Groupها
6- بررسی نحوه رشد Data Fileهای مربوط به File Group
7- بررسی نحوه قرار دادن ایندکس در یک File Group
8- بررسی نحوه ذخیره و بازیابی BLOBها در File Group جداگانه
9- بررسی فایل‌ گروه‌های Filestream
10- بررسی نحوه استفاده از فایل گروه‌های Filestream در جهت افزایش کارایی بانک اطلاعاتی
11- بررسی نحوه ایجاد و مدیریت فایل گروه‌های Filestream
12- بررسی نحوه پیکربندی SQL Server برای کار با Filestream
13- بررسی نحوه کار با Filestream در .NET
14- بررسی جداول FileTable
15- بررسی نحوه استفاده از FileTable در NET.

نحوه ارتباط دات نت با Filestream و File Table

ما در این دوره شما را با نحوه استفاده از تکنولوژی Filestream و همچنین File Table در  دات نت آشنا می‌کنیم. از این سبک کد نویسی خودم به شخص در اغلب پروژه‌ها استفاده کرده‌ام و کارایی بانک اطلاعاتی را افزایش داده‌ام.

یادم است در یکی از پروژه‌هایی که به تازگی درگیر آن بودیم، یک سیستم با کاربری ملی وجود داشت که برنامه‌نویسان آن به اشتباه ذخیره فایل‌ها را خودشان در NTFS و همچنین داده‌های مربوط به آن را در MongoDB انجام داده بودند که به محض افزایش تعداد فایل‌ها و تعداد کاربران دچار مشکلات زیادی شدند که با انجام تغییراتی در معماری برنامه و هدایت فایل‌ها و داده‌های مربوط به آن به سمت SQL Server و استفاده از تکنولوژی Filestream سیستم به راحتی توانست از عهده درخواست‌های کاربران بر بیاید.

1- بررسی مفهوم Logical Read و Physical Read
2- بررسی دستور Set Statistics IO
3- بررسی دستور Set Statistics Time
4- بررسی ترتیب اجرای منطقی و فیزیکی دستورات SQL Server
5- بررسی مفهوم Execution Plan و انواع آن
6- بررسی نحوه Compare کردن Execution Planها در SQL Server
7- بررسی نحوه ذخیره و بازیابی Execution Plan

معرفی یک ابزار حرفه‌ای برای آنالیز Execution Plan

ما در  SSMS می‌توانیم Execution Planها را مشاهده و آنالیز کنیم، اما SSMS امکانات محدودی برای آنالیز Plan دارد. ما در این دوره شما را با یک ابزار خیلی قوی برای آنالیز Execution Plan آشنا خواهیم کرد.

1- بررسی Heap Table
2- برررسی معماری جداول Heap در SQL Server
3- بررسی چند سناریوی کاربردی برای استفاده از Heap Table
4- آشنایی با Clustered Index
5- بررسی معماری Clustered Index
6- بررسی تاثیر انتخاب درست کلید Clustered Index در کارایی بانک اطلاعاتی
7- چگونه Where Condition مناسب جهت استفاده از Clustered Index بنویسیم؟
8- مقایسه Heap و Clustered Index
9- بررسی انتخاب کلید ایندکس به صورت ترکیبی و تاثیر آن بر کارایی بانک اطلاعاتی
10- بررسی ویژگی‌های مهم و کاربردی برای انتخاب کلید ایندکس
11- بررسی Non Clustered Index و معماری مربوط به آن
12- بررسی مفهوم lookup هنگام استفاده از ایندکس‌های Non Clustered
13- بررسی Key Lookup
14- بررسی RID Lookup

یکی از پروژه‌هایی که چند وقت است در نیک آموز درگیر انجام عملیات Performance & Tuning آن بودیم شامل یک جدول Heap با بیش از چهل میلیون رکورد بود. زمانی که کاربران در ساعت اداری با این سیستم کار می‌کردند، مصرف CPU سرور بالای 80 درصد بود و این موضوع باعث شده بود که کاربران به شدت شاکی باشند و کُندی در سیستم محسوس باشد. اولین کاری که برای رفع مشکل انجام دادیم تبدیل جدول به Clustered و ایجاد تعدادی Non Clustered ایندکس بر روی آن بود.

1- بررسی مفهوم Cover Index در SQL Server
2- بررسی نحوه حذف هزینه Lookup با استفاده از Cover Index
3- بررسی Filtered Indexها در SQL Server
4- بررسی استفاده از GUID به عنوان کلید ایندکس و مشکلات مربوط به آن
5- Computed Column و اعمال ایندکس بر روی آن‌ها
6- بررسی تاثیر عملیات Shrink بر روی ایندکس‌ها
7- بررسی تاثیر منفی ایجاد بیش از حد ایندکس‌ها در کارایی بانک اطلاعاتی
8- بررسی Optionهایی که هنگام ایجاد ایندکس باید در نظر داشت.

یکی از وحشتناک‌ترین کارهایی که یک DBA ممکن است خیلی زیاد آن را انجام دهد Shrink کردن Database است. این استراتژی را یکی از DBAهای یک کارخانه مشهور دولتی انجام داده و به این موضوع به شدت افتخار می‌کرد که با انجام این کار Database دوباره جان می‌گیرد.

به شکل دوستانه و با چند خط اسکریپت وضعیت بانک اطلاعاتی قبل از Shrink و بعد از آن را به او نشان دادم تا متوجه مشکلات مربوط به این کار شود؛ پس از حذف پروسه Shrink از Jobهای شبانه ایشان مشکلات مربوط به کندی برخی از کوئری‌ها رفع شد.

1- بررسی مفهوم Data Compression در SQL Server
2- بررسی فشرده سازی به روش Row Compression
3- بررسی فشرده‌ سازی به روش Page Compression
4- بررسی تاثیر فشرده‌سازی بر عملیات Select
5- بررسی تاثیر فشرده‌سازی بر عملیات Insert,Update,Delete
6- بررسی تاثیر فشرده‌سازی بر کارایی بانک اطلاعاتی
7- بررسی تاثیر فشرده‌سازی بر روی Clustered Indexها
8- بررسی تاثیر فشرده‌سازی بر روی Non Clustered Indexها
9- بررسی توابع مربوط به فشرده‌سازی در SQL Server
10- بررسی تاثیر عملیات فشرده‌سازی بر روی کارایی عملیات تهیه و بازیابی نسخه پشتیبان

در یکی از پروژه‌هایی که در آن درگیر بودم یکی از جداول بانک اطلاعاتی دارای حجم زیادی از داده بود که با توجه به اینکه حجم Selectهای این جدول خیلی زیاد بود تصمیم گرفتیم Data Compression را بر روی این جدول راه‌اندازی کنیم؛ با انجام این کار حجم داده‌های موجود در ایندکس‌های این جدول کاهش پیدا کرد و این موضوع باعث شد که IO کوئری‌ها کمتر شود. تصویر زیر نمونه‌ای از فضای یک جدول قبل از فشرده و پس از فشرده‌سازی به روش‌های مختلف را نمایش می‌دهد.

1- بررسی مفهوم Statistics
2- بررسی کاربرد Statistics در SQL Server
3- بررسی مفهوم Query Selectivity در SQL Server
4- بررسی تاثیر Statistics در هنگام انتخاب یک ایندکس مناسب
5- بررسی اجزاء مربوط به Statistics (Stat Header ,Density Vector ,Histogram)
6- بررسی روش‌های ایجاد Statistics در SQL Server
7- بررسی شرایط به روز رسانی Statistics در SQL Server
8- بررسی روش‌های به روز رسانی Statistics در SQL Server
9- بررسی تغییرات Statistics در نسخه 2014 (بازنویسی روال‌های مربوط به آن توسط مایکروسافت)
10- بررسی تاثیر منفی تغییرات Statistics نسخه 2014 بر روی بانک‌های اطلاعاتی
11- بررسی مشکلات به روز نبودن Statistics
12- بررسی مشکل Spill to Disk
13- بررسی Filtered Statisticsها
14- بررسی تغییرات جدید Statistics در SQL Server 2022

یکی از سازمان‌های دولتی که با آن کار می‌کنم، چند روز پیش با من تماس گرفته بود و می‌گفت که پروسیجر مربوط به درج رکوردهای فایل X در بانک اطلاعاتی به شدت کند عمل می‌کند. هر عملیات درج بیش از 30 ثانیه زمان می‌برد. این پروسیجر یکی از وحشتناک‌ترین SPهای مربوط به سازمان مورد نظر بود که شامل بیش از هزار خط کد (SP و Functionهای تو در تو) بود.

بررسی و Tune کردن این نوع SPها خیلی خیلی زمان‌بر بوده و عموما برای بهینه کردن هرچه بهتر نیاز است که شما به Business مربوط به آن هم کمی تسلط داشته باشید تا بتوانید در برخی از مواقع بازنویسی SP را با دستورات بهتر انجام دهید، خب اما من وقت کافی برای این کار نداشتم. بچه‌های سازمان به من می‌گفتند کل کاری که قبلا به ازای این SP انجام می‌شد، 2 ثانیه زمان می‌برد؛ اما حالا بیش از 30 ثانیه، با کمی پرسش از بچه‌ها متوجه شدم به‌روزرسانی Statisticsها در سازمان جز دستور کار نبوده و با بروز کردن Statisticsهای سازمان به صورت Manual مشکل حل شد.

زمان اجرای Stored Procedure به کمتر از 2 ثانیه کاهش یافت. آیا شما با مباحث Statistics آشنا هستید؟ آیا می‌دانید تغییرات مربوط به حوزه Statistics در SQL Server 2014 چه چیزهایی بوده؟

1- بررسی مفهوم Primary Key و Unique Key
2- نحوه انتخاب صحیح Primary Key در جداول
3- Primary Key & Unique Key و ایندکس‌های مرتبط به آنها
4- بررسی مفهوم Check Constraint و تاثیر آن بر کارایی بانک اطلاعاتی
5- بررسی مفهوم Foreign Key و تاثیر آن بر کارایی بانک اطلاعاتی

خیلی از دوستانی که فیلد یا فیلدهایی را به عنوان Unique Key ایجاد می‌کنند اطلاعی ندارند که Unique Key توسط یک Unique Non Clustered Index کنترل می‌شود؛ چنانچه ما با این مفهوم و همچنین معماری داخلی این ایندکس آشنا باشیم می‌توانیم به خوبی از این ویژگی در پروژه‌های عملیاتی خود استفاده کنیم.

1- بررسی مفهوم Fragmentation
2- بررسی انواع Fragmentation در SQL Server
3- بررسی تاثیر استفاده از FillFactor و Pad_Index بر روی Fragmentation
4- بررسی روش‌های کاهش Fragmentation ایندکس‌ها در SQL Server
5- بررسی نحوه تنظیم اصولی FillFactor برای ایندکس‌ها
6- بررسی نحوه مانیتور کردن Fragmentation
7- بررسی Rebuild کردن ایندکس‌ها و کاهش Fragmentation
8- بررسی روش‌های مختلف Rebuild کردن ایندکس‌ها
9- بررسی حالت‌های مختلف Rebuild کردن ایندکس‌ها به صورت آنلاین و حالت‌های مختلف آن

شاید به بانک‌های اطلاعاتی برخورد کرده باشید که چندین سال است کار می‌کند و به یک باره کند می‌شود. بیشتر مشکلات کندی مربوط به این نوع برنامه‎‌ها با یک Rebuild ساده ایندکس ممکن است حل شود. از این نوع بانک‌های اطلاعاتی زیاد دیده‌ام؛ کارخانه X، کارخانه Y و غیره. همه این‌ها نفرات که به طور تخصصی با SQL Server کار کنند را ندارند.

یک Admin شبکه مسئول همه‌چیز است؛ حق هم دارد به این مسائل مسلط نباشد، چون حیطه تخصصی آن شبکه و مسائل مربوط به آن است، ولی SQL Server را به او تحمیل کرده‌اند، اما این وسط برنده کسی است که با این مباحث آشنایی داشته باشد و بتواند درآمد و سطح دانش خود را افزایش دهد.

1- معرفی DMVهای مربوط به مانیتور کردن ایندکس‌ها
2- بررسی ویو مدیریتی Index Usage Stats
3- بررسی ویو مدیریتی Index Operational Stats
4- بررسی ویو مدیریتی Index Physical Stats
5- پیدا کردن ایندکس‌ها تکراری و تصمیم‌گیری در خصوص وجود یا عدم وجود آن
6- پیدا کردن ایندکس‌های بلا استفاده در SQL Server
7- بررسی و آنالیز Missing Indexها در SQL Server

یکی از کارهایی که من همیشه بر روی بانک‌های اطلاعاتی انجام می‌دهم پیدا کردن ایندکس‌های بلا استفاده بر روی سرور است. وجود ایندکس‌های اضافی بر روی سرور باعث بالا رفتن افزایش Fragmentation ایندکس، افزایش مدت زمان Blocking، افزایش احتمال وجود Deadlock و…. می‌شود. ما در SQL Server می‌توانیم ایندکس‌های بلا استفاده را با استفاده از بررسی تعدادی DMV مدیریتی به دست آوریم.

1- بررسی مفهوم Trigger
2- بررسی انواع DML Trigger
3- بررسی کنترل Business Ruleها با استفاده از Trigger و تاثیر آن بر کارایی بانک اطلاعاتی

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

چند سال پیش با مجموعه‌ای که با SQL Server 2008 کار می‌کرد همکاری داشتم. در یکی از جداول بزرگ این مجموعه در جهت ثبت سوابق تغییرات رکوردهای این جدول از After Trigger استفاده شده بود؛ پس از آنالیز و بررسی تریگر موجود در جدول متوجه دو موضوع مهم شدیم:

• 1. ارتباط After Trigger با Fragment شدن جدول
• 2. افزایش مدت درج و به روز رسانی رکوردهای موجود در جدول

1- بررسی مفهوم View
2- بررسی نحوه ایجاد Partition View و تاثیر آن بر کارایی بانک اطلاعاتی
3- بررسی نحوه ایجاد ایندکس بر روی Viewها
4- بررسی نحوه اعمال Clustered Index بر روی Viewها
5- بررسی نحوه اعمال Non Clustered Index بر روی Viewها

استفاده به جا از Index View

در یکی از پروژه‌هایی که برای افزایش کارایی آن درگیر بودم با تعداد زیادی از جداول اطلاعات پایه درگیر بودم که هزینه Join آنها بالا بود با بررسی نوع جداول و فیلدهای مورد استفاده در کوئری‌ها تصمیم به ایجاد Index View در بانک اطلاعاتی مربوط به پروژه گرفتم. استفاده از این ویژگی باعث شد که هزینه مربوط به Join کوئری‌ها حذف گردد.

1- بررسی مفهوم Function
2- بررسی انواع Functionها
3- بررسی Scalar Function و تاثیر آن بر کارایی بانک اطلاعاتی
4- بررسی تغییرات جدید مربوط به Scaler Functionها در حوزه کارایی در SQL Server 2019,2022
5- بررسی In Line Function و تاثیر آن بر کارایی بانک اطلاعاتی
6- بررسی Multi Statement Function و تاثیر آن بر کارایی بانک اطلاعاتی
7- بررسی مشکلات مربوط به Cardinality Estimation هنگام کار با Functionها

تاریخ شمسی و چالش‌های مربوط به آن

یکی از روش‌های ذخیره تاریخ در بانک‌های اطلاعاتی ذخیره آن به صورت میلادی است اما مشکل زمانی است بخواهیم این تاریخ را به صورت شمسی نمایش دهیم. برای انجام این کار در SQL Server ما می‌توانیم Scaler Function استفاده کنیم. این روش توسط اکثر شرکت‌های نرم‌افزاری استفاده می‌شود، اما زمانی که حجم داده‌های موجود در جداول شما زیاد باشد دیگر این روش جوابگوی کار نخواهد بود و باید از روش‌های بهینه و مناسبی استفاده کنید.

1- پارتیشن‌بندی چیست؟
2- چه زمانی باید سراغ پارتیشن‌بندی جداول برویم؟
3- چگونه یک جدول را پارتیشن‌بندی کنیم؟
4- چگونه ایندکس‌های خود را پارتیشن‌بندی کنیم؟
5- معرفی انواع تکنیک‌های پارتیشن‌بندی
6- آشنایی با تابع $Partition
7- بررسی نحوه استفاده از File Groupها هنگام کار پارتیشن‌بندی جداول
8- پارتیشن‌بندی و فشرده‌سازی داده‌ها
9- انجام عملیات Merge و Split به ازای پارتیشن‌ها در SQL Server
10- استفاده از ویژگی Incremental Statistics هنگام پارتیشن‌بندی
11- معرفی Best Practices برای پارتیشن‌بندی جداول

در یکی از پروژه‌های بزرگی که درگیر بودم جدولی در حدود 900 گیگابایت حجم داشت. برای افزایش سرعت عملکرد این جدول تصمیم به پارتیشن‌بندی جدول بر اساس ماه گرفتیم. مهم‌ترین مزیتی که این تکنیک به ما داد این بود که توانستیم Active Partitionها یا پارتیشن‌هایی که مراجعه به آن‌ها زیاد می‌باشد را در دیسک‌های پرسرعت قرار دهیم. همچنین ما ویژگی Incremental Statistics را در این جدول فعال کردیم که تاثیر بسیار مناسبی در عملکرد کوئری‌هایمان داشت. رفتن به سمت Data Partitioning اصولی دارد که ما در این دوره این اصولی دارد که در این دوره این اصول را به خوبی یاد می‌گیریم.

1- Temp Table یا جداول موقت چیست؟
2- تاثیر استفاده از Temp Table بر کارایی بانک اطلاعاتی
3- Table Variable چیست؟
4- تاثیر استفاده از Table Variable بر کارایی بانک اطلاعاتی
5-  TVP چیست ؟
6- تاثیر استفاده از TVP بر کارایی بانک اطلاعاتی
7- بررسی بانک اطلاعاتی TempDB
8- بررسی نحوه پیکربندی صحیح بانک اطلاعاتی TempDB
9- بررسی مفهوم Latch Contention و نحوه کاهش آن در بانک اطلاعاتی TempDB
10- بررسی Trace Flagهای وابسته به Tempdb

بانک اطلاعاتی TempDB یکی از مهم‌ترین بانک‌های اطلاعاتی سیستمی موجود در SQL Server است که تنظیم صحیح آن به طور مستقیم در کارایی بانک اطلاعاتی تاثیر دارد. یکی از راه‌های بهینه‌سازی TempDB افزایش تعداد Data Fileهای آن به نسبت CPU Coreهای سرور است. اما این افزایش تعداد Data Fileها همراه با آیتم‌های دیگر باید انجام شود. خود من این موضوع را در اکثر پروژه‌های Tuningای که درگیر می‌شوم انجام می‌دهم.

1- بررسی پیشرفته اپراتورها در Execution Plan
2- بررسی اپراتورهای فیزیکی مربوط به Join (Nested Loop, Merge Join , Hash Join)
3- بررسی اپراتورهای فیزیکی Aggregate (Stream Aggregate , Hash Aggregate)
4- بررسی Table Spool
5- بررسی Loop, Merge, Hash join Hint
6- بررسی NoExpancd Expand Hint

در کورس قبلی Performance & Tuning یکی از دوستان شرکت کننده در دوره کوئری داشت که پلن اجرایی آن به صورت Serial بوده و زمان اجرای آن بیش از 3 دقیقه بود. ما با بازنویسی کوئری مربوطه توانستیم روند اجرای کوئری را به حالت Parallel در بیاوریم. طی این حالت ردیف‌های بازگشتی به ازای هر کدام از اپراتورها توانایی اجرای بر روی CPU Coreهای مختلف دارند.

1- مروری بر مکانیزم Cache کردن Execution Plan در SQL Server
2- آشنایی با Plan Cache در SQL Server
3- بررسی DMV مربوط به مشاهده Plan Cache
4- بررسی دستورات مدیریت Plan Cache
5- بررسی استفاده مجدد از Execution Plan
6- Parameter sniffing چیست و چگونه بوجود می‌آید؟
7- راه‌حل رفع مشکل Parameter Sniffing چیست؟
8- بررسی تاثیر استفاده از پروسیجرها در افزایش کارایی کوئری‌ها
9- بررسی تکنیک‌هایی کاربردی برای ایجاد پروسیجرهای بهینه
10- بررسی استفاده از Dynamic SQL در دستورات TSQL
11- مشکلات Ad-Hoc Queryها چیست؟
12- Auto Parameterization چیست؟
13- Plan Guides چیست؟
14- استفاده ویژگی Adaptive Query Processing در SQL Server
15- استفاده از ویژگی Automatic Query Tuning در SQL Server
16- بررسی تاثیر استفاده از Plan Regression در SQL Server

استفاده از ویژگی Adaptive Query Processing

یکی از ویژگی‌های جالبی که در SQL Server 2017 ارائه و در SQL Server 2022 امکانات جالبی به آن اضافه شد Adaptive Query processing می‌باشد. بااستفاده از این ویژگی‌های مصرف IO و Memory مربوط به کوئری‌ها کاهش می‌یابد که این موضوع باعث می‌شود زمان اجرای کوئری‌های کاهش یابد. توجه داشته باشید زمانی که ما از Adaptive Query Processing در SQL Server استفاده می‌کنیم نیاز به تغییر در کدها‌ی خود نداریم و SQL Server از ویژگی‌های مربوط به آن به صورت هوشمندانه استفاده خواهد کرد. ما همیشه در پروژه‌های خودمان از این ویژگی استفاده می‌کنیم.

1- آشنایی با Extended Event
2- آشنایی با اصطلاحات موجود در Extended Event
3- آشنایی با نحوه ایجاد Event Sessionها در Extended Event
4- نحوه اعمال Filter به ازای Event Sessionها
5- آشنایی با نحوه آنالیز خروجی Extended Event
6- بررسی Eventهای مشهور
7- بررسی سناریو Page Split
8- بررسی سناریو استخراج Deadlock
9- بررسی سناریو استخراج Blocking
10- بررسی سناریو استخراج کوئری‌های کند به همراه Execution Plan

کدام یک از عملیات‌های موجود در سیستم موجب Page Split می‌شود؟

یکی از عوامل مهمی که باعث کاهش کارایی سیتسم‌ها هنگام انجام عملیات Update و Insert می‌شود، عملیات Page Split می‌باشد. تیم ما معمولاً در پروژه‌های حرفه‌ای خود یکی از کارهایی که انجام می‌دهد آنالیز عملیات Page Split است؛ برای این کار می‌توانیم از Extended Eventها استفاده کنیم.

1- برررسی معماری Query Store
2- بررسی نحوه پیکربندی بانک اطلاعاتی برای استفاده از Query Store در SQL Server
3- بررسی DMV مرتبط با Query Store در SQL Server
4- بررسی نحوه Force کردن Plan با استفاده از Query Store
5- بررسی نحوه استفاده از گزارش‌‌های تکمیلی Query Store

بانک اطلاعاتی یکی از پروژه‌‌هایی که درگیر آن بودم SQL Server 2016 بود در جهت افزایش سرعت کوئری نیاز داشتیم به سریع‌ترین نحو ممکن از وضعیت اجرای کوئری‌ها و همچنین Execution Plan آنها اطلاع یابیم. یکی از بهترین ابزارهایی که از نسخه 2016 به بعد در SQL Server تعبیه شده است Query Store نام دارد. این ابزار به راحتی آب خوردن امکان انجام این کار را به ما می‌دهد.

1-  آشنایی با معماری Buffer Pool Extension
2- بررسی دلیل استفاده از SSD به عنوان RAM
3- آشنایی با نحوه خواندن و نوشتن Pageها در SSD
4- آشنایی با Extended کردن Buffer Pool در SQL Server بر روی SSD

یکی از قابلیت‌های SQL Server 2014 به بالا امکان Extended کردن Buffer Pool. در این روش چنانچه ما مشکل Memory داشته باشیم می‌توانیم به کمک SSD بخشی از داده‌هایی که قرار است در Buffer Pool قرار گیرد را بر روی SSD منتقل کنیم تا کارایی بانک اطلاعاتی به نحوه مناسبی افزایش یابد.

1- معرفی PMEM
2- بررسی نحوه استفاده از PMEM در ماشین‌های مجازی
3- بررسی استفاده از PMEM به عنوان Hybrid Buffer Pool در SQL Server

استفاده از سخت‌افزارهای جدید تاثیر زیادی در عملکرد و کارایی SQL Server دارد. یکی از آنآها PMEM می‌باشد که می‌توان از آن به صورت بهینه بر روی ماشین‌های مجازی و سرورهای SQL Server استفاده کرد.

1- بررسی Resource Governor
2- بررسی سناریوهای قابل استفاده برای Resource Governor در SQL Server
3- بررسی نحوه محدود کردن CPU با استفاده از Resource Governor
4- بررسی نحوه محدود کردن Memory با استفاده از Resource Governor
5- بررسی نحوه محدود کردن IO با استفاده از Resource Governor
6- بررسی نحوه ایجاد Classifier Function برای کار با Resource Governor

چند سال پیش در یکی از سازمان‌های دولتی درگیر رفع مشکلات مربوط به کارایی بانک اطلاعاتی بودیم با بررسی که روی سرور انجام دادیم. مشخص شد که یک کوئری بهینه (Index Seek) به تعداد زیادی روی سرور در حال اجرا می‌باشد؛ به طوری که در طی ساعتی خاص امکان کار با سرور مهیا نبوده و سرور به علت فراخوانی مکرر این کوئری بهینه Busy می‌باشد. با بررسی دقیق‌تر متوجه شدیم که این کوئری توسط یک وب سرویس در حال فراخوانی است. برای اینکه این وب سرویس کلی Resourceهای سرور را در اختیار نگیرد Resource Governor را پیکربندی کردیم.

1- آشنایی با مفهوم Lock
2- بررسی مفهوم Concurrency
3- بررسی انواع مشکلات رایج هنگام کار با Resource به صورت همزمان
4- بررسی انواع Lock
5- بررسی مفهوم Blocking
6- بررسی نحوه بوجود آمدن Blocking
7- بررسی نحوه پیدا کردن Blocking
8- بررسی مفهوم Deadlock
9- بررسی حالت‌های مختلف بوجود آمدن Deadlock
10- بررسی روش‌های کشف Deadlock
11- بررسی نحوه رفع مشکلات Deadlock و کاهش آن
12- بررسی مفهوم Lock Escalation
13- بررسی مفهوم Isolation Level
14- بررسی انواع Isolation Level
15- بررسی Application Lock
16- بررسی Schema Lock
17- بررسی نحوه طراحی استفاده از Transaction در برنامه‌ها
18- بررسی Delay Durability Transaction و Blocking و عوامل بوجود آمدن آن

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

1- آشنایی با برخی مباحث موجود در Data Warehouseها
2- آشنایی با Columnstore Index
3- بررسی معماری مربوط به Columnstore Index
4- آشنایی با مفهوم Batch Mode Processing
5- بررسی مفهوم Row Group
6- بررسی Delta store و Delete Bitmap در Columnstore Index
7- آشنایی با روش‌های فشرده‌سازی مربوط به Columnstore Index
8- آشنایی با DMV مربوط به مانیتور کردن Columnstore Index
9- بایدها و نبایدهای مربوط به Columnstore Index
10- آشنایی با تغییرات مربوط به SQL Server 2017 به ازای Columnstore Index
11- بررسی نحوه اعمال تنظیمات مناسب برای Bulk Insert در Columnstore Indexها
12- بررسی تاثیر استفاده از Columnstore Index در Data Warehouseها
13- بررسی نحوه استفاده از Columnstore Index در OLTPها
14- بررسی استفاده از Columnstore Index هنگام کار با Memory Optimized Table

استفاده از Columnstore Index در Data Warehouse

در یکی از پروژه‌هایی که درگیر بودم جدولی با حدود 2 میلیارد و 400 میلیون رکورد داشتم که روزانه بیش از 10 میلیون رکورد به جدول مربوطه اضافه می‌شود. هدف ما در این پروژه افزایش سرعت کوئری‌ها بود. با توجه به اینکه اغلب کوئری‌های مربوط به این جدول از جنس تحلیلی می‌باشد، تصمیم گرفتیم از Columnstore Index در آن استفاده کنیم.

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