دوره آموزش Performance Tuning در SQL Server – سطح دوم

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

   ▪️   بررسی تکنیک‌های تقسیم داده‌ها   
   ▪️   بـررسی Partitioned View
   ▪️   Linked Server چیست؟
   ▪️   استخراج داده‌ها با Linked Server
   ▪️   بررسی Data Partitioning
   ▪️   مراحل پیاده‌سازی Data Partitioning
   ▪️   اعمال رایج روی Partition
   ▪️   انجام تنظیمات Lock Escalation
   ▪️   بررسی روش‌های مدیریت پارتیشن
   ▪️   بررسی Split کـردن پـارتیشن
   ▪️   بررسی Switch کردن پارتیشن
   ▪️   حل تمرین و مثال‌های کاربردی

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

با کمی پرسش از بچه‌ها متوجه شدم به‌روزرسانی Statistics ها در سازمان، جزو دستور کار نبوده و با به‌روز کردن Statistics های سازمان به‌صورت Manual، مشکل حل شد. زمان اجرای Stored Procedure به کمتر از 2 ثانیه کاهش یافت.

آیا شما با مباحث Statistics آشنا هستید؟ آیا می‌دانید تغییرات مربوط به حوزه Statistics در SQL Server 2014 چه چیزهایی بوده؟

   ▪️   Statistics چیست و کجا قرار دارد؟   
   ▪️   بررسی کاربرد Statistics
   ▪️   بررسی چند تعریف ساده در Statistics
   ▪️   بررسی نحوه ایجاد Statistics
   ▪️   بررسی اجزاء Statistics
   ▪️   بررسی نمونه‌ای از استفاده Statistics
   ▪️   بررسی مشکلات عدم به‌روزرسانی Statistics
   ▪️   بررسی روش‌های به‌روزرسانی Statistics
   ▪️   معرفی Hypostatical Index
   ▪️   تغییرات جدید Statistics در SQL Server 2022

   ▪️   تأثیر After Trigger برروی ایندکس   
   ▪️   تأثیر استفاده از Index View برروی ایندکس‌ها
   ▪️   عدم استفاده از Select *
   ▪️   استفاده از شرط‌های قابل SARG
   ▪️   استفاده صحیح از اپراتور Like در Where Condition
   ▪️   عدم استفاده از Function در Where Condition
   ▪️   عدم استفاده از Implicit Conversion
   ▪️   تاریخ شمسی و چالش‌ها
   ▪️   عدم استفاده از Local Variable در Where Condition
   ▪️   عدم استفــاده از Index Hint ها
   ▪️   Domain Integrity و تأثیر بر کارایی
   ▪️   کار با Union و چالش‌های مربوط به آن
   ▪️   استفاده از Exists به جای Count(*)

   ▪️   Temporary Table چیست؟   
   ▪️   کاربردهای Temporary Table
   ▪️   بررسی Local Temp Table
   ▪️   بررسی Global Temp Table
   ▪️   بررسی Global Temp Table
   ▪️   بررسی Table Variable
   ▪️   بررسی Table Variable Parameter

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

   ▪️   Temp Table یا جداول موقت چیست؟   
   ▪️   تأثیر استفاده از Temp Table بر کارایی بانک اطلاعاتی
   ▪️   Table Variable چیست؟
   ▪️   تأثیر استفاده از Table Variable بر کارایی بانک اطلاعاتی
   ▪️   TVP چیست؟
   ▪️   تأثیر استفاده از TVP بر کارایی بانک اطلاعاتی
   ▪️   بررسی بانک اطلاعاتی TempDB
   ▪️   بررسی نحوه پیکربندی صحیح بانک اطلاعاتی TempDB
   ▪️   بررسی مفهوم Latch Contention و نحوه کاهش آن در بانک اطلاعاتی TempDB
   ▪️   بررسی Trace Flag های وابسته به Tempdb

   ▪️   بررسی پیشرفته اپراتورها در Execution Plan   
   ▪️   بررسی اپراتورهای فیزیکی مربوط به Join (Nested Loop, Merge Join , Hash Join)
   ▪️   بررسی اپراتورهای فیزیکی Aggregate (Stream Aggregate, Hash Aggregate)
   ▪️   بررسی Table Spool
   ▪️   بررسی Loop, Merge, Hash join Hint
   ▪️   بررسی NoExpancd Expand Hint

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

   ▪️   مروری بر مکانیزم Cache کردن Execution Plan در SQL Server   
   ▪️   آشنایی با Plan Cache در SQL Server
   ▪️   بررسی DMV های مربوط به مشاهده Plan Cache
   ▪️   بررسی دستورات مدیریت Plan Cache
   ▪️   بررسی استفاده مجدد از Execution Plan
   ▪️   Parameter sniffing چیست و چگونه به‌وجود می‌آید؟
   ▪️   راه حل رفع مشکل Parameter Sniffing چیست؟
   ▪️   بررسی تأثیر استفاده از پروسیجرها در افزایش کارایی کوئری‌ها
   ▪️   بررسی تکنیک‌هایی کاربردی برای ایجاد پروسیجرهای بهینه
   ▪️   بررسی استفاده از Dynamic SQL در دستورات TSQL
   ▪️   مشکلات Ad-Hoc Queryها چیست؟
   ▪️   Auto Parameterization چیست؟
   ▪️  Plan Guides چیست؟
   ▪️   استفاده ویژگی Adaptive Query Processing در SQL Server
   ▪️   استفاده از ویژگی Automatic Query Tuning در SQL Server
   ▪️   بررسی تأثیر استفاده از 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 از ویژگی‌های مربوط به آن به‌صورت هوشمندانه استفاده خواهد کرد. ما همیشه در پروژه‌های خودمان از این ویژگی استفاده می‌کنیم. شما چطور؟

   ▪️   آشنایی با مفهوم Lock   
   ▪️   بررسی مفهوم Concurrency
   ▪️   بررسی انواع مشکلات رایج هنگام کار با Resource ها به‌صورت همزمان
   ▪️   بررسی انواع Lock
   ▪️   بررسی مفهوم Blocking
   ▪️   بررسی نحوه به‌وجود آمدن Blocking
   ▪️   بررسی نحوه پیدا کردن Blocking
   ▪️   بررسی مفهوم Deadlock
   ▪️   بررسی حالت‌های مختلف به‌وجود آمدن Deadlock
   ▪️   بررسی روش‌های کشف Deadlock
   ▪️   بررسی نحوه رفع مشکلات Deadlock و کاهش آن
   ▪️   بررسی مفهوم Lock Escalation
   ▪️   بررسی مفهوم Isolation Level
   ▪️   بررسی انواع Isolation Level
   ▪️   بررسی Application Lock
   ▪️   بررسی Schema Lock
   ▪️   بررسی نحوه طراحی استفاده از Transaction در برنامه‌ها
   ▪️   بررسی Delay Durability Transaction و Blocking و عوامل به‌وجود آمدن آن

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

   ▪️   هم‌ زمانی چیست؟   
   ▪️   بررسی انواع مشکلات هم‌زمانی
   ▪️   بررسی Lost Update
   ▪️   بررسی Uncommitted Dependency
   ▪️   بررسی Inconsistent Analysis
   ▪️   روش جلوگیری از مشکلات هم‌زمانی
   ▪️   بررسی انواع Latch

   ▪️   بررسی Buffer Latches (BUF)   
   ▪️   بررسی IO Latch
   ▪️   بررسی Buffer Latches (BUF)
   ▪️   بررسی Non-Buffer Latches (Non-BUF)

   ▪️   بررسی ویژگی‌های Lock   
   ▪️   Locked Granularity چیست؟
   ▪️   فرآیند انتخاب Locked Granularity
   ▪️   Locked Mode چیست؟

   ▪️   بررسی جدیدترین ساختار IQP در SQL Server   
   ▪️   بررسی بهبودهای SQL Server 2022 در IQP
   ▪️   استفاده IQP از امکانات Query Store

   ▪️   بررسی Adaptive Query Processing   
   ▪️   بررسی Multi Statement Functionها
   ▪️   بررسی Interleaved Execution
   ▪️   بررسی چالش صفحه‌بندی در Data Grid
   ▪️   بررسی Approximate Count Distinct

   ▪️   بررسی Degrees Of Parallelism (DOP) Feedback   
   ▪️   چالش‌هایی موازی‌سازی و عـدم تنظیم صحیح MAXDOP
   ▪️   چند ویژگی جدید در IQP
   ▪️   Degrees of Parallelism
   ▪️   بررسی Cardinality Estimation (CE) Feedback       
   ▪️   بررسی Automatic Database Tuning
   ▪️   آشنایی با Query Hint هـا
   ▪️   قابلیت Memory Grant Feedback
   ▪️   بررسی Parameter Sensitivity Plan Optimization

   ▪️   آشنایی با برخی مباحث موجود در Data Warehouse ها   
   ▪️   آشنایی با Columnstore Index
   ▪️   بررسی معماری مربوط به Columnstore Index
   ▪️   آشنایی با مفهوم Batch Mode Processing
   ▪️   بررسی مفهوم Row Group
   ▪️   بررسی Delta store و Delete Bitmap در Columnstore Index
   ▪️   آشنایی با روش‌های فشرده‌سازی مربوط به Columnstore Index
   ▪️   آشنایی با DMV های مربوط به مانیتور کردن Columnstore Index
   ▪️   بایدها و نبایدهای مربوط به Columnstore Index
   ▪️   آشنایی با تغییرات مربوط به SQL Server 2017 به ازای Columnstore Index
   ▪️   بررسی نحوه اعمال تنظیمات مناسب برای Bulk Insert در Columnstore Index ها
   ▪️   بررسی تأثیر استفاده از Columnstore Index در Data Warehouse ها
   ▪️   بررسی نحوه استفاده از Columnstore Index در OLTP ها
   ▪️   بررسی استفاده از Columnstore Index هنگام کار با Memory Optimized Table

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

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