In-Memory OLTP و Columnstore در SQL Server

۱. آشنایی با concurrecnt در SQL Server
۲. معرفی انواع Concurrency

– کنترل همزمانی Pessimistic
– کنترل همزمانی Optimistic

۳. بررسی انواع خواندن دیتا در SQL Server

– Dirty read
– Phantom read
– Non-repeatable read
– Lost Update(write-write conflict)

۴. معرفی انواع Isolation level

– Pessimistic isolation levels

۵. READ UNCOMMITTED
۶. READ COMMITTED
۷. REPEATABLE READ
۸. SERIALIZABLE

– Optimistic isolation levels
– SNAPSHOT
– Read commited Snapshot
– مقایسه انواع Isolation Level و بررسی نقاط ضعف و قوت آن ها

۹. آشنایی با مفهوم تراکنش در SQL Server
۱۰. معرفی و بررسی انواع تراکنش

– تراکنش های AutoCommitted
– تراکنش های Explicit
– تراکنش های Implicit
– مقایسه انواع تراکنش ها

۱۱. بررسی و مقایسه تولید Log تراکنش ها و تاثیر آن ها روی Transaction Log
۱۲. آشنایی با مدیریت خطا در تراکنشo

– بررسی عملکرد Try..Catch
– نحوه استفاده از XACT_STATE() و XACT_ABORT() و مزایای آنها

۱۳. معرفی راه‌های بهینه‌سازی تراکنش‌ها

۱. آشنایی با انواع Lock

– Exclusive Lock(X)
– Intent Lock()
– Update Lock
– Shared Lock
– Key-Range Lock
– Schema Lock

۲. بررسی سازگای انواع Lock و معرفی ماتریس سازگاری Lock ها
۳. بررسی سازگاری انواع lock با isolation level های مختلف در SQL Server
۴. بررسی جدول سیستمی sys.dm_tran_locks
۵. بررسی Lock Escalation

– آشنایی با انواع Lock Escalation
– مقایسه انواع Lock Escalation

۶. بررسی رفتار Lock ها در Extended Events
۷. معرفی Latch و کاربردهای Latch
۸. آشنایی با انواع مختلف Latch
۹. بررسی دقیق رفتار Lock و Latch
۱۰. تفاوت های Latch , Lock

۱. آشنایی با سیستم عامل SQL (SQL OS)
۲. مزایای SQL OS

– بررسی عملکرد رفتار SQL OS و Windows OS
– نحوه کار و زمانبندی SQL OS
– آشنایی با مفاهیم مرتبط با SQL OS

. Session
. Request
. Task
. Worker Thread
. Scheduler معرفی اجزای مختلف Scheduler و نحوه عملکرد آن

۳. آشنایی با مفاهیم Exectution Time , Response Time
۴. بررسی جداول سیستمی

– sys.dm_exec_requests
– sys.dm_exec_sessions
– sys.dm_os_schedulers
– sys.dm_os_sys_info
– sys.dm_os_tasks
– sys.dm_os_schedulers

۱. آشنایی با مفهوم wait type و wait time
۲. اشنایی با dmv های سیستمی

– sys.dm_os_wait_stats
– sys.dm_os_waiting_tasks
– sys.dm_exec_session_wait_stats

۳. معرفی انواع wait در SQL Server

– معرفی انواع wait مرتبط با Lock

. بررسی wait های LCK_M_S
. بررسی wait های LCK_M_X
. بررسی wait های LCK_M_U
. بررسی wait های LCK_M_SCH_M
. بررسی wait های LCK_M_Sch_S
. بررسی wait های LCK_M_I*

– معرفی انواع wait مرتبط با Latch

. آشنایی با Buffer Latch
. آشنایی با Non-Buffer Latch
. آشنایی با IO Latch

– معرفی انواع wait مرتبط با CPU

. بررسی CXPacket wait
. بررسی SOS_SCHEDULER_YIELD wait
. بررسیTHREADPOOL wait

– معرفی انواع wait مرتبط با IO

. بررسی WRITELOG wait

۴. آشنایی و بررسی مفهوم بلاکینگ
۵. بررسی Blocking Chaing

SQL Server Wait Statistic یکی از مهمترین ابزارهایی که می‌توانیم برای تحلیل کوئری‌ها و بهبود عملکرد (Performance) از آن استفاده کنیم. خیلی از برنامه نویس‌ها وDBA ها از این مقوله مهم اطلاع کافی ندارند. همچنین مستندات و مطالب کمی توی این حوزه وجود دارد. به طور کلی، هر نوع Configuration در سخت افزار سرور تا پیکربندی در کامپیوترهای کلاینت مرتبط با SQL sever روی Wait Statistic ها تاثیر می‌گذارد. با توجه به این توضیحات به عنوان یک DBA لازم است با این مفاهیم بیشتر آشنا شویم. در این بخش از دوره قصد داریم بررسی کنیم چطور می‌توانیم از Wait Statisticها برای بهبود عملکرد کوئری‌ها و دیتابیس‌ها استفاده کنیم.

۱. تعریف پارتیشن‌بندی دیتا
۲. معرفی انواع پارتیشن بندی دیتا
۳. آشنایی با Partitioned View و کاربردهای آن
۴. بررسی data partitioning در جداول SQL Server

– بررسی مفهوم Partition Function (Range Right, Range Left)
– بررسی مفهوم Partition Scheme
– بررسی نحوه ارتباط جدول پارتیشن‌بندی شده با FileGroup

۵. نحوه پارتیشن بندی جداول
۶. بررسی مثال عملیاتی پارتیشن بندی جدول در SQL Server
۷. بررسی مفهوم Partition Elimination
۸. نقش پارتیشن‌بندی و در دسترس‌پذیری دیتابیس‌ها (Database Availability) در SQL Server
۹. بررسی ساختار دیتابیس ها با پارتیشن‌بندی و بدون پارتیشن‌بندی دیتا
۱۰. آشنایی با مفهوم Partial Backup و کاربردهای آن
۱۱. بررسی مثال عملیاتی، مدیریت دیتا و انتقال Filegroup ها بین دیسک‌های مختلف
۱۲. بررسی عملکرد ایندکس های در جداول معمولی و پارتیشن‌بندی شده
۱۳. بررسی rebuild و reorganize ایندکس ها در جداول پارتیشن‌بندی شده
۱۴. آشنایی با مفهوم Statistics
۱۵. بررسی رفتار Statistics ها در جداول معمولی و پارتیشن‌بندی شده
۱۶. مقایسه Partitioned View ها و Partition Table ها
۱۷. نحوه مدیریت پارتیشن ها و آشنایی با عملیات های Merge, Split , Partition Switch
۱۸. آشنایی با ویوهای سیستمی
۱۹. sys.dm_db_partition_stats

– sys.partitions
– sys.partition_schemes
– sys.partition_functions

۲۰. بررسی Lock Escalation در جداول پارتیشن‌بندی شده

۱. آشنایی با مفهوم ذخیره سازی ستونی (Columnar)
۲. بررسی ساختار ذخیره‌سازی دیتا به صورت سطری و ستونی
۳. بررسی مزایا و معایب ساختار ذخیره‌سازی سطری و ستونی
۴. معرفی انواع ایندکس‌های Columnstore

– Nonclustered Columnstore
– Clustered Columnstore
– بررسی تفاوت ها و شباهت های Nonclustered Columnstore و Clustered Columnstore

۵. معرفی اجزای اصلی ایندکس Columnstore

– بررسی Rowgroup و ساختار آن
– بررسی و معرفی Segment

۶. آشنایی با view های سیستمی

– sys.column_store_row_groups
– Sys.dm_db_column_store_row_group_physical_stats
– sys.column_store_segments
– sys.dm_db_column_store_row_group_operational_stats

۷. بررسی مراحل ساخت ایندکس Columnstore

– آشنایی با انواع رمزگذاری در Columnstre

. Dictionary encoding
. Value-Based encoding

۸. آشنایی با مفهوم دیکشنری در Columnstore
۹. بررسی Catalog view ها

– sys.column_store_dictionaries
– sys.allocation_units

۱۰. عملکرد تکنیک های Segment Elimination , Row Group Elimination در SQL Server
۱۱. بررسی نحوه پردازش کویری در جداول Columnstore
۱۲. کاربردهای ایندکس Columnstore
۱۳. آشنایی با عملکرد پردازش دسته‌ای (Batch Processing)
۱۴. معرفی مزایای Batch Processing
۱۵. بررسی انواع بارگذاری دیتا در جداول Columnstore
۱۶. اجرای یک مثال عملیاتی و مقایسه رفتار باگذاری دیتا در حالت های مختلف روی جدول Columnstore
۱۷. بررسی اجرای موازی در حالت RowStore و Columnstore
۱۸. بررسی سناریوی Parallelism در حالت‌های اجرای Row Mode و Batch Mode در نسخه‌های مختلف SQL Server
۱۹. بررسی ساختار داخلی ایندکس Columnstore
۲۰. آشنایی با مفاهیم Delta Store و Delete Bitmap
۲۱. بررسی رفتار Insert , Update, Delete و Bulk Insert در جداول Columnstore
۲۲. آشنایی با مفهوم Tuple Mover و نحوه عملکرد آن
۲۳. بررسی محدودیت های ایندکس Columnstore
۲۴. بررسی رفتار انواع ایندکس Columnstore در نسخه های مختلف SQL Server 2014 , SQL Server 2016 و SQL Server 2017
۲۵. بررسی انواع انواع فشرده‌سازی دیتا با حالت Columnstore و Columnstore_Archive
۲۶. بررسی ویژگی Compression_Delay در ایندکس Columnstore
۲۷. بررسی فرآیند Rebuild و Reorganize ایندکس های Columnstore
۲۸. بررسی Computed Column در Columnstore

۱. آشنایی با انواع وضعیت RowGroup ها ( Open, Invisible, Closed, Tombstone, Compressed )
۲. بررسی رفتارهای Row Group ها (BulkLoad, NO_TRIM, REORG, Dictionary_Size, Memory Limitation)
۳. بررسی Memory Grant Info در اجرای کویری ها در SQL Server
۴. بررسی حافظه مورد نیاز ایندکس Columnstore
۵. بررسی عملکرد اپراتور Aggregate Window در SQL Server 2017
۶. بررسی تکنیکPredicate Pushdown و نحوه اجرای آن
۷. مدیریت و نگهداری تغییرات در جداول Columnstore

– استفاده از Change Data Capture
– استفاده از Change Tracking
– استفاده از جداول Temporal

۸. بررسی ساختار داخلی انواع ایندکس Columnstore

۹. بررسی رفتار Lock ها و Blocking در جداول Columnstore
۱۰. آشنایی با مفهوم Deadlock در SQL Server
۱۱. بررسی رفتار Deadlock در جداول Columnstore
۱۲. راه های کاهش Deadlock در جداول Columnstore
۱۳. معرفی ابزار قدرتمند SQL SentryOne
۱۴. بررسی رفتار Deadlock در SQL SentryOne

۱۵. معرفی بهترین حالات طراحی ایندکس Columnstore
۱۶. بررسی عملکرد Merge در Columnstore
۱۷. بررسی رفتار Transaction Log در جداول Columnstore
۱۸. مقایسه فایل Backup دیتابیس‌های دارای جداول rowstore و Columnstore
۱۹. بررسی Partitioning در جداول Columnstore
۲۰. برریس کویری های تحلیلی روی جداول پارتیشن شده Columnstore با جداول بدون پارتیشن Columnstore

۲۱. بررسی رفتار ایندکس Nonclustered روی جدول Columnstore
۲۲. بررسی ساختار داخلی ایندکس Nonclustered و ایندکس Columnstore

– آشنایی با مفهوم CSI Locator
– معرفی ساختار Mapping Index

۲۳. بررسی ویو سیستمی sys.internal_partitions
۲۴. آشنایی با آبجکت های داخلی Columnstore

– COLUMN_STORE_DELETE_BITMAP
– COLUMN_STORE_DELTA_STORE
– COLUMN_STORE_DELETE_BUFFER
– COLUMN_STORE_MAPPING_INDEX

۲۵. بررسی ساختار داخلی ایندکس Nonclustered Columnstore و ایندکس Clustered
۲۶. آشنایی با ساختار و عملکرد Delete Buffer
۲۷. بررسی رفتار ایندکس Columnstore در Extended Events
۲۸. بررسی رفتار ایندکس Columnstore در Performance Monitor
۲۹. بررسی مثال عملی – سناریوی Columnstore و کنترل همزمانی به منظور بررسی Blocking
۳۰. بررسی مثال عملی – شبیه‌سازی رفتار deadlock
۳۱. بررسی مثال عملی – سناریوی بکارگیری Columnstore و Partitioning

استفاده از ایندکس Columnstore در انبارداده
با استفاده از تکنولوژی Columnstore در یکی از جداول انبارداده با حدود 11 میلیارد رکورد توانستیم حجم جدول را حدود 8 برابر نسبت به حالت disk-based کاهش دهیم و نهایتا سرعت و کارایی کویری‌های جدول بشدت افزایش یافت. سرعت اکثر اجرای کویرهای تحلیلی، روی جدول مورد نظر کمتر از 2 ثانیه(2000 میلی ثانیه) است.

۱. معرفی تکنولوژی In-Memory OLTP و بررسی مزیت ها
۲. آشنایی با ویژگی‌های In-Memory OLTP Engine
۳. لزوم استفاده از تکنولوژی و جداول
۴. بررسی پروژه‌ها و سناریوهای موفق In-Memory OLTP
۵. نحوه ایجاد دیتابیس با ویژگی In-Memory OLTP‌ در SQL Server 2017
۶. بررسی معماری تکنولوژی In-Memory OLTP Engine

۷. نحوه تعریف جدول Memory-Optimized
۸. بررسی Performance جداول Memory-Optimized و Disk-Based
۹. بررسی انواع راه‌های دسترسی به آبجکت های In-Memory OLTP
۱۰. بررسی عدم استفاده از Lock و Latch در جداول memory-optimized
۱۱. بررسی ساختاری جداول Disk-Based‌ و Memory-Optimized
۱۲. ساختار داده در جداول Memory-Optimized (Data Row Format)
۱۳. معرفی تکنیک کنترل همزمانی چندنسخه‌ای (Multiversion Concurrency Control)
۱۴. بررسی Data Type های قابل استفاده در جداول Memory-Optimized‌
۱۵. معرفی ویژگی های جدید جداول Memory-Optimized در SQL Server 2017
۱۶. نحوه مهاجرت جداول Disk-Based به جداول Memory-Optimized
۱۷. بررسی محدودیت‌های جدول Memory-Optimized
۱۸. بررسی مثال عملی – سناریوی عملکرد استفاده از tvp معمولی و memory-optimized tvp
۱۹. بررسی مثال عملی – سناریوی استفاده از جداول Memory-Optimized در فرآیند ETL انبار داده
۲۰. بررسی مثال عملی – سناریوی مدیریت فضای حافظه سرور با مکانیزم آرشیو دیتای جداول Memory-Optimized
۲۱. بررسی مکانیزم مهاجرت جداول disk-based به memory-optimized

استفاده از حافظه به عنوان یک ویژگی جدید در SQL Server اضافه شده است. اما استفاده محض از این تکنیک منجر به عدم استفاده بهینه از حافظه سرور می شود. مدیریت بهینه از حافظه با استفاده از تکنیک های آرشیو دیتای جداول Memory-Optimized بسیار بااهمیت و مهم است.بسته به بیزنس و بررسی hot data ، مکانیزم آرشیو دیتا یکی از مهمترین مراحل طراحی و استفاده از تکنولوژی In-Memory OLTP به حساب می آید که با بررسی یک سناریوی عملی در این دوره مکانیزم آرشیو دیتای جداول Memory-Optimized را بررسی می کنیم.

۱. معرفی انواع ایندکس

– Hash Index
– Nonclustered Index(Range Index)
– Clustered Columnstore Index
– بررسی کاربردها و مقایسه انواع ایندکس‌ها

۲. آشنایی با Bucket Count و Hash Table Lookup
۳. بررسی عملکرد Bucket Count در جداول Memory-Optimized
Hash Indexes .۴ و SARGability
۵. نحوه ایجاد Nonclustered Indexes
۶. بررسی ساختار ایندکس های Clustered و Non-Clustered‌ در جداول Disk-Based
۷. بررسی ساختار ایندکس‌ها در جداول Memory-Optimized
۸. آشنایی با ساختار ذخیره‌سازی Nonclustered Indexes و معماری Bw-Tree
۹. معرفی Delta Records
۱۰. بررسی مثال عملی – سناریوی مقایسه عملکرد Nonclustered Indexes و Hash Indexes‌
۱۱. بررسی عملکرد ایندکس Clustered Columnstore در جداول Memory-Optimized
۱۲. بررسی مصرف کننده های حافظه با ایندکس Clustered Columnstore
۱۳. بررسی عملکرد Insert, Delete در جداول Memory-Optimized و Columnstore
۱۴. بررسی محدودیت های ایندکس Clustered Columnstore در جداول Memory-Optimized
۱۵. بررسی مثال عملی – سناریوی تعریف ایندکس بر روی جداول Memory-Optimized و بررسی عملکرد ‌ایندکس
۱۶. بررسی گزارشات قابل استفاده در SQL Server 2017 جهت بررسی جداول Memory-Optimized

۱. آشنایی با Varheaps
۲. بررسی نحوه ذخیره سازی دیتای جداول Memory-Optimized به صورت Off-Row‌ و In-Row
۳. آشنایی با انواع Memory Consumers
۴. Range Index Heap
۵. Table Heap
۶. LOB page allocator
۷. بررسی عملکرد ذخیره سازی به صورت Off-Row
۸. بررسی مثال عملی – سناریوی ایجاد Off-Row و تاثیر آن بر عملکرد حافظه و کویری ها

۱. آشنایی با Native Compilation
۲. بررسی ماژول های Native Compiled
۳. پروسیجر Natively Compiled
۴. توابع Natively Compiled
۵. تریگر Natively Compiled
۶. بررسی محدودیت ها، امکانات و دستورات قابل استفاده در Native Compiled
۷. عملگرها
Join .۸ها
۹. توابع تجمیعی
۱۰. توابع سیستمی
۱۱. دستورات کنترلی
۱۲. عملکرد ماژول های Natively Compiled
۱۳. مقایسه عملکرد پروسیجر Natively Compiled با پروسیجر عادی
۱۴. مقایسه عملکرد توابع Natively Compiled با توابع عادی

۱. مروری بر ساختار تراکنش در SQL Server
۲. بررسی Isolation Levels های قابل استفاده در In-Memory OLTP

۳. بررسی تراکنش‌های Cross-Container
۴. آشنایی با نحوه پردازش تراکنش در In-Memory OLTP
۵. بررسی ساختار ذخیره‌سازی ، Logging و Recovery
۶. بررسی با فایل های In- Memory OLTP checkpoint files Pairs))
۷. معرفی Delta Files
۸. معرفی Data Files

۹. بررسی وضعیت های مختلف CFP
۱۰. Precreated
۱۱. Under Construction
۱۲. Merge Target
۱۳. Active
۱۴. Waiting For Log Truncation
۱۵. فرآیند بازیابی دیتا در حافظه

۱۶. مقایسه فرآیند تولید Log در جداول Memory-Optimized و جداول Disk-Based
۱۷. معرفی انواع Checkpoint

– Internal
– Manual
– Automatic
– Indirect

۱۸. بررسی وضعیت Checkpoint در جداول Memory-Optimized
۱۹. استفاده از dm_db_xtp_checkpoint_files برای بررسی سایز Log , CFPs
۲۰. بررسی backup و restore در دیتابیس های In-Memory
۲۱. مرروی بر فرآیند Garbage Collection
۲۲. بررسی workflow مرتبط با Garbage Collection
۲۳. معرفی Catalog view های مرتبط با Garbage Collection

– sys.dm_xtp_gc_stats
– sys.dm_xtp_gc_queue_stats
– sys.dm_db_xtp_gc_cycle_stats
– sys.dm_db_xtp_index_stats

۲۴. بررسی یک مثال عملی – سناریوی بررسی مدیریت حافظه و Garbage Collection پس از حذف رکوردهای یک جدول

در یکی از پروژه هایی که از جداول memory-optimized بر روی SQL Server 2014 استفاده می‌کردیم. حجم ایندکس های رزرو شده در حافظه به طرز عجیبی بسیار بالا بود. با استفاده از یک تکنیک و استفاده از ایندکس های کم کاربرد، توانستیم با فراخوانی فرآیند garbage collector و بخش بسیار زیادی از حافظه رزرو شده را به حافظه سرور برگردانیم.

۱. بررسی Counter های مرتبط با In-Memory در Performance Counter
۲. بکارگیری event های مربوط به In-Memory OLTP در Extended Events
۳. معرفی و بررسی ابزارهای کمکی In_memory در حوزهSQL Server
۴. استفاده از event های مربوط به In-Memory OLTP در Extended Events
۵. بررسی مهاجرت جداول و پروسیجرها به In-Memory OLTP با استفاده از ابزار Memory Optimization Advisor