معرفی کتاب‌های تخصصی SQL Server | بخش چهارم

مقدمه

در بخش اول ، بخش دوم  و بخش سوم تعدادی از کتاب‌ها و سرفصل‌های مرتبط معرفی گردید. در چهارمین قسمت از سلسله مقالات مرتبط با بررسی کتاب‌های تخصصی در حوزه SQL SERVER به یکی از مهمترین مباحث که در زمینه Performance با آن رو به رو هستیم خواهیم پرداخت. مبحثی به اسم Wait Statisticsها که بسیار مباحث مهمی در این زمینه به شمار می‌روند. ولی چرا این مبحث دارای اهمیت زیادی هست و باید به خوبی بر روی ان مسلط شد؟ جواب این سوال در این است که متوجه خواهیم شد که SQL Server از سیستم عاملی تحت عنوان SQL OS در دورن خود استفاده می کند. البته اسم سیستم عامل شاید به آن وسعت و بزرگی که در ذهن دارید نباشد ولی این موضوع به عملکرد داخلی Engine اشاره مستقیم دارد و نحوه پردازش درخواست‌ها، تخصیص منابع، ارسال به سایر Stateها جهت پردازش و مدیریت کلیه این فرایندها همگی تحت این عامل در SQL Server اجرا می‌شوند. به همین دلیل کتاب Pro SQL Server 2019 Wait Statistics را در این مقاله با جزییات دقیق و همراه با معرفی منابع مرتبط معرفی خواهیم کرد و سرفصل‌های آن را تشریح می‌کنیم. مبحث Wait Statistics همواره در هر سیستمی که  با آن کار می کنید ، با مقیاس های مختلف بهترین سرنخ‌ها را به شما ارایه می‌دهد. دلیل این موضوع هم این هست که در واقع داریم SQL OS را مورد بررسی قرار می‌دهیم و متوجه می شویم در چه بخشی از این فرایند‌ها به اصلاح با وقفه‌هایی همراه هستیم . هدف از ارایه این مقاله بدین شکل هست که در کنار معرفی بخش‌های مختلف کتاب، با نمونه اسکریپت‌هایی مرتبط با هر بخش تا حدودی آشنایی پیدا کنید. به عنوان مثال در بخش‌هایی از این کتاب ابزارهایی معرفی شده است که نیاز به اجرای اسکریپت خاصی ندارند. در بعضی از سرفصل‌ها، ماهیت بحث بدین شکل هست که واقعا باید نتیجه نهایی را مشاهده کنید و نسبت به خروجی نمایش داده شده، دید مناسبی دریافت کنید.

در راستای معرفی سرفصل‌های این مرجع آموزشی، کلیه کتاب‌هایی که انتشارات Apress منتشر می‌کند، سورس کدهای ان را در سایت گیت‌هاب و در مخزن مرتبط با همین انتشارات برای عموم به اشتراک می‌گذارد. لذا سورس کدهای این کتاب در خصوص اسکریپت‌‌‌هایی که در فصل‌های مختلف مورد بررسی قرار گرفته است را می‌توانید از آدرس زیر دریافت کنید:

اهداف کتاب

مطابق با توضیحاتی که برای این کتاب نوشته شده است ، بعد از مطالعه آن می‌توانید به این اهداف دست یابید :

• شناسایی و تشخیص resource bottlenecks‌ها در سرور های عملیاتی
• آشنایی با ساختار معماری مرتبط با Waitها جهت درک دقیق آمار آنها
• آشنایی بیشتر با DMVها و اطلاعات Query Store در خصوص Waitها
• عملکرد سیستم‌ها را تجزیه و تحلیل کنید
• تشخیص مسائل مرتبط با I/O و مسائل locking contention
• آشنایی با طبقه بندی Waitها و اولویت های هریک جهت بررسی دقیق تر مشکلات
• کاهش زمان wait timeها نسبت به هر کدام از wait typesها

 درباره کتاب

در ابتدای مقاله برای این که تصویر شفاف و دقیق‌تری نسبت به مطالعه این کتاب به دست آورید به چند نکته دقت نمایید . طبقه بندی Waitها در این کتاب بدین شکل هست که بسته به معماری داخلی SQL SERVER ، اشکال مختلفی از Waitها را شاهد هستیم. لذا باید در  مطالعه این کتاب به نکاتی دقت شود که با یک تصویر سازی مناسب تری  مطالب کتاب برای شما به خوبی شفاف شود . این نکات به شرح زیر هستند :

• نکته اول: زمانی که یک Query را اجرا می‌کنیم، پشت پرده اجرای نهایی آن کلیه اتفاقی که رخ می‌دهد شامل بخش‌های مختلفی هست که برای هر بخش Wait‌های مختلف خواهیم داشت. به عنوان مثال در سرفصل مرتبط با CPU، Wait‌هایی مورد بررسی قرار میگیرد که از اهمیت و اولویت بالاتری نسبت به سایر Wait‌های این دسته قرار دارند.عملکرد این قابلیت‌ها کاملا مرتبط با معماری داخلی Engine طراحی شده است. به عبارتی سرفصل‌های این کتاب در چندین بخش طبقه‌بندی شده که هر بخش شامل مهمترین Wait‌هایی خواهد بود که باعث ایجاد مشکلات بیشتری برای ما خواهد شد .
• نکته دوم : در مباحث مرتبط با Waitها مطابق با توضیحات کتاب بیش از ۹۰۰ ویت مختلف معرفی شده. مسلما چه در این کتاب و چه در سایر مراجعی که این مباحث را با دقت بیشتری مورد بررسی قرار می‌دهند، بررسی کلیه این Wait‌ها امکان‌پذیر نیست. بحث Wait‌ها، همواره بد نیست و عملا باید به ازای مواردی ما اعداد و اماری را شاهد باشیم که واقعا جزو ماهیت اصلی سرور به حساب می‌آید. لذا با این دید به این مبحث نگاه نکنید که تک به تک Wait‌ها را به صورت ریز و با جزییات دقیق‌تری مورد بررسی قرار دهید. اما چرا این موضوع مطرح می‌شود؟ جواب این سوال در این هست که اگر مباحث این کتاب را در نظر نگیریم، اسکریپت‌هایی که برای بررسی مباحث Tuning در این زمینه وجود دارند همواره مهمترین Wait‌ها مورد بررسی قرار داده‌اند. حتما در ادامه نمونه‌هایی از این اسکریپت‌ها را با هم مشاهده می‌کنیم . در این خصوص می توانید با عناوین SQL Server Wait Types Library این طبقه بندی‌ها رو پیدا کرده و مطالعه کنید .
• نکته سوم: برای هر کدام از Wait‌هایی که در این کتاب معرفی شده است یا شاید در این کتاب مشاهده نکنید، روش‌ها و راه حل‌های مختلفی ارایه شده است. لذا برطرف کردن مشکلاتی که یک Waitممکن است برای ما ایجاد کند، نیازمند بررسی چند راه حل هست که در این کتاب و در مقاله‌ها و سایت‌های مختلف بر اساس تجربه‌ها، این روش‌ها ارایه شده است. شاید بتوان به این موضوع اشاره داشت که یکی از اصولی‌ترین طراحی‌ها برای این مبحث بدین شکل بوده هست که نامگذاری Wait‌ها و حتی دیدن امار مرتبط با آن‌ها شما را با عمده مشکلات سرور به خوبی آشنا می‌کند .
• نکته چهارم: با استفاده از آماری که سایت‌های تخصصی در این زمینه ارایه می‌دهند میتوانید درجه اهمیت هر Wait را قبل از بررسی مشخص پیدا کنید سپس به بررسی آن بپردازید . یکی از سایت‌ها به اسم …. هست که می توانید نوع Wait ها را بررسی کنید .به عنوان مثال نقطه قرمزی که در عکس پایین مشاهده می‌کنید، برای Waitایی به اسم CXPACKET هست . به صورت کلی هر چه قدر نقاطی که در این نمودار به سمت راست و بالا صفحه متمایل شده باشد، درجه اهمیت بالایی خواهد داشت .

یا به عنوان مثال عکس زیر در خصوص PAGEIOLATCH_SH  است:

درباره نویسنده

این کتاب توسط آقای Enrico van de Laar  نوشته شده است. ایشان یکی از MVPهای ماکروسافت هستند و با بیش از ۱۵ سال تجربه در زمینه علوم مرتبط با داده فعالیت می کنند. تمرکز ایشان بیشتر بر روی مباحث Performance ایی بوده و به همین دلیل، باعث شده که یکی از مهمترین بحث های این حوزه را در قالب این کتاب تالیف کنند. از طرفی بر روی پلتفرم Azure و Machine learning نیز فعالیت می‌کنند.

کتاب فوق، در ۱۳ فصل مختلف، مباحث مرتبط با Wait را مورد بررسی قرار داده است . سرفصل‌های این کتاب به شکل زیر ارایه شده است:

• Wait Statistics Internals
• Querying SQL Server Wait Statistics
• The Query Store
• Building a Solid Baseline
• CPU-Related Wait Types
• IO-Related Wait Types
• Backup-Related Wait Types
• Lock-Related Wait Types
• Latch-Related Wait Types
• High-Availability and Disaster-Recovery Wait Types
• Preemptive Wait Types
• Background and Miscellaneous Wait Types
• In-Memory OLTP–Related Wait Types

هدف نویسنده این کتاب، بدین شکل بوده که به صورت علمی و دقیق‌تری، خوانندگان این کتاب چرایی و چیستی این Waitها را به خوبی متوجه شده، بررسی‌های لازم را انجام دهند و مشکلات را برطرف کنند. ولی این نکته را در نظر داشته باشید، این کتاب تنها مقدمه ایی بر این موضوع است که با این مفاهیم پایه اشنا شویم. در واقع اصل مطلب این هست که، ارتباط بین این Wait‌ها را به خوبی درک کنیم. درک این موضوع تنها با دیدن مثال‌های متعدد از محیط‌های مخلتف و بیزینس‌های متفاوت حاصل می‌شود. به عنوان مثال سناریو‌هایی را در نظر بگیرید که حجم تراکنش‌ها به صورت ثانیه ایی در حال افزایش هست و به صورت پیوسته اطلاعات در سرور‌های عملیاتی درج می‌شود. در این سناریو، مسلما طیف وسیعی از Wait‌ها را شاهد هستیم. در همین سناریو موضوع Backup/Restore به عنوان یکی از چالش‌های اصلی این بیزینس مطرح می‌شود. حتی مجددا در این بحث نیز ممکن است طیف مختلفی از Wait‌ها را مشاهده کنیم که در بعضی از موارد عملکرد سیستم، با اختلال همراه است. در همین بیزینس مسلما Down شدن سرور یکی از مهمترین مسائل به شمار می‌رود و همواره باید سرویس‌هایی که در اختیار دارید را در دسترس قرار دهید و کمترین میزان Down Time را داشته باشید. از طرفی شاید در شرکتی مشغول به فعالیت باشید که از یک نرم‌افزار حسابداری ساده استفاده می‌شود ولی مجددا همان کندی‌ها در این سناریو دیده می‌شود. پس می‌توان حتی به این موضوع رسید که یکی از مهمترین مقیاس‌هایی که می‌توانید برای بررسی این گونه پروژه‌ها داشته باشید، بررسی همین امار مرتبط خواهد بود.

در ادامه این سرفصل ها ، هر کدام از این Wait ها ، مورد بررسی قرار گرفته است. این لیست جزو اصلی ترین و مهمترین Waitها را تشکیل داده است که می‌توانید به تفکیک در مورد هر کدام نیز مطالعه کنید و تجربه‌های مختلف را برای برطرف کردن آنها مطالعه کنید . این لیست شامل موارد زیر هست :

CXPACKET

PAGEIOLATCH_XX

ASYNC_NETWORK_IO

WRITELOG

BROKER_RECEIVE_WAITFOR

MSQL_XP

OLEDB

BACKUPIO

LCK_M_XX

ONDEMAND_TASK_QUEUE

BACKUPBUFFER

IO_COMPLETION

SOS_SCHEDULER_YIELD

DBMIRROR_EVENTS_QUEUE

DBMIRRORING_CMD

PAGELATCH_XX

LATCH_XX

PREEMPTIVE_OS_PIPEOPS

THREADPOOL

BROKER_TRANSMITTER

SQLTRACE_WAIT_ENTRIES

DBMIRROR_DBM_MUTEX

RESOURCE_SEMAPHORE

PREEMPTIVE_OS_AUTHENTICATIONOPS

PREEMPTIVE_OS_GENERICOPS

SLEEP_BPOOL_FLUSH

MSQL_DQ

RESOURCE_SEMAPHORE_QUERY_COMPILE

DAC_INIT

MSSEARCH

PREEMPTIVE_OS_FILEOPS

PREEMPTIVE_OS_LIBRARYOPS

PREEMPTIVE_OS_LOOKUPACCOUNTSID

PREEMPTIVE_OS_QUERYREGISTRY

SQLTRACE_LOCK

در فصل اول کتاب می خوانیم

به صورت کلی همواره در کتاب‌های تخصصی که مطالعه می‌کنید، فصل‌های ابتدایی کتاب، در خصوص معماری داخلی قابلیتی صحبت می‌کند که در ادامه در لایه‌های دیگر، به صورت سیستمی به این قسمت‌ها رجوع کرده و بر‌اساس این معماری، منطقی که برای ان در نظر گرفته شده است را پیاده‌سازی می‌کند. در این کتاب هم بدین گونه هست. مقدمه کتاب بدین شروع می‌شود، که در نسخه‌های پایین‌تر و قدیمی SQL Server، مفهوم زمانبندی به شکل دیگری پیاده‌سازی شده بود که این موضوع باعث ایجاد مشکلات زیادی می‌شد. طراحی این موضوع با بحثی به اسم SQL OS دستخوش تغییر شد و معماری آن به گونه‌ایی تغییر کرد که کلیه این فرایند‌ها خارج از زمانبندی سیستم عامل اجرایی شود. در قسمت بعدی، نویسنده بر روی مباحث زیر، توضیحات لازم را ارایه می‌کند. این موارد شامل:

• Sessions
• Requests
• Tasks
• Worker Threads
• Schedulers

عناوینی که در لیست بالا عنوان شد در واقع به عنوان سنگ بنای بحث SQL OS معرفی می‌شود و باید به خوبی بر روی تک تک این موارد تسلط داشته باشید. در واقع هر کدام از این موارد به صورت یک درختواره ایی در نظر گرفت می‌شود که شامل مجموعه‌هایی از عنوان‌های بعد خودشان هستند. به عنوان مثال زمانی که Sessions را ایجاد می‌کنیم، ممکن است یک یا چند Requests برای ان در نظر بگیریم و به همین شکل زمانی که یک Requests داریم ممکن است چندین Tasks به موازات آن در نظر گرفته شود. لذا برای انالیز کردن این ساختار می‌توانید از DMV‌های مرتبط با هر کدام استفاده کنید. در این کتاب Query ‌هایی که بتوانید وضعیت درخواست‌ها و سشن‌ها را مشاهده کنید، معرفی شده است. این گونه Query عمدتا شامل DMV‌های مرتبط با یکدیگر هستند که بتوانید وضعیت آنلاین درخواست‌ها را مشاهده کنید.

به عنوان مثال نمونه کوئری‌ایی که می‌توانیم برای معرفی این بخش ارایه دهیم بدین شکل هست:

SELECT r.session_id         AS 'Session ID',
              r.command            AS 'Type of Request',
              qt.[text]            AS 'Query Text',
              t.task_address       AS 'Task Address',
              t.task_state         AS 'Task State',
              w.worker_address     AS 'Worker Address',
              w.[state]            AS 'Worker State',
              s.scheduler_address  AS 'Scheduler Address',
              s.[status]           AS 'Scheduler State'
       FROM   sys.dm_exec_requests r
              CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
       INNER JOIN sys.dm_os_tasks t
                   ON  r.task_address = t.task_address
              INNER JOIN sys.dm_os_workers w
                   ON  t.worker_address = w.worker_address
              INNER JOIN sys.dm_os_schedulers s
                   ON  w.scheduler_address = s.scheduler_address
       WHERE  r.session_id = 53

در فصل دوم کتاب می‌خوانیم

همان طور که از عنوان این فصل مشخص است، اسکریپت‌هایی مرتبط با بحث Wait ها را در این قسمت مشاهده می‌کنیم . مبحث Wait Statistics متشکل از چندین DMV یا ویوهای سیستمی هستند که اطلاعات جامعی در این خصوص به ما ارایه می‌دهند. در این بخش DMV‌های زیر مورد بررسی قرار گرفته است:

• dm_os_wait_stats
• dm_os_waiting_tasks
• dm_exec_requests
• dm_exec_session_wait_stats

نویسنده به مبحثی در خصوص Extended Events پرداخته است. این موضوع روش جایگزین برای SQL SERVER Profiler به حساب می‌آید چرا که سربار استفاده از ان به شدت کاهش یافته و براساس یک معماری اصولی و ساختار یافته‌ایی Eventها را جمع‌آوری و ذخیره می‌کند. شروع کتاب بدین شکل هست که Extended Eventsها شامل اجزایی هستند که هر کدام پیش نیاز وجود دیگر اجزا به حساب می‌آید. این اجزا شامل موارد زیر هستند:

• Packages
• Events
• Targets
• Actions
• Predicates

در این فصل، برای این که بیشتر با انواع Event‌ها اشنایی پیدا کنید، مثال‌های بسیار خوبی در خصوص مهمترین Eventها ارایه شده است که می‌توانید در سرورهای عملیاتی از ان به خوبی استفاده کنید . همچنین گروه بندی کردن Eventها و انتخاب هر یک بسته به فیلتر هایی که باید برای ان در نظر گرفته شود و محل ذخیره سازی هر یک از Eventها بسته به Target‌ایی که برای آن مشخص می‌کنید. در این فصل مثال‌های متنوعی ارایه شده است. در مبحث Extended Eventsها بیش از ۹۰۰ Event مختلف وجود دارد که در زمینه‌های مختلف طبقه بندی شده‌اند . لذا حتما اسکریپت‌ها و مثال‌هایی که در این فصل ارایه شده است را اجرا کنید تا مفاهیم این بخش به خوبی برای شما نمایان شود. هر کدام از DMVهایی که در لیست بالا مطرح شده است، شامل فیلد ها و پارامتر هایی هست که در بررسی مشکلاتی که وجود دارند به ما کمک می‌کنند. به همین دلیل قبل از این که سایر فصل‌ها را مورد بررسی قرار دهید حتما نسبت به پارامتر ها و کاربرد هر یک به خوبی مطالعه داشته باشید . در ادامه کتاب سایر روش‌هایی که می‌توانید برای جمع اوری و ذخیره این امار و اطلاعات استفاده کنید معرفی شده است . به عنوان مثال ، استفاده از Extended event یکی دیگر از روش هایی هست که منجر به ذخیره و جمع اوری اطلاعات این امار کاربردی خواهد شد . همان طور که در مقالات قبلی، سایر منابع تخصصی در این حوزه معرفی شدند ، این مبحث یکی از کاربردی مباحث در بحث مدیریت پایگاه داده به شمار می‌رود. در واقع ابزاری هست که می‌توانید از ان برای عیب‌یابی و مشکلاتی که در سرور وجود دارد به خوبی استفاده کنید .

در ادامه کتاب ، روش های دیگری برای استفاده از امار مرتبط با Wait statisticsها معرفی شده است . شما با استفاده از ابزاری تحت عنوان Perfmon می توانید گروه بندی مرتبط با SQLServer:Wait Statistics را مشاهده کرده و Event های مرتبط با آن را مورد بررسی قرار دهید.

نمونه کوئری‌ایی که می‌توانیم برای این بخش ارایه دهیم بدین شکل هست:

SELECT blocking.session_id      AS blocking_session_id,
       blocked.session_id       AS blocked_session_id,
       waitstats.wait_type      AS blocking_resource,
       waitstats.wait_duration_ms,
       waitstats.resource_description,
       blocked_cache.text       AS blocked_text,
       blocking_cache.text      AS blocking_text
FROM   sys.dm_exec_connections  AS blocking
       INNER JOIN sys.dm_exec_requests blocked
            ON  blocking.session_id = blocked.blocking_session_id
       CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats
            ON  waitstats.session_id = blocked.session_id

به عنوان یکی از مهمترین DMV‌هایی که برای در این فصل معرفی شده، می‌توانیم آمار جالبی نسبت به مهمترین Wait‌های به دست اوردیم. این کار را می‌توانید با استفاده از کوئری زیر انجام دهید:

WITH [Waits]
     AS (
         SELECT [wait_type],
                [wait_time_ms] / 1000.0  AS [WaitS],
                ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
                [signal_wait_time_ms] / 1000.0 AS [SignalS],
                [waiting_tasks_count]    AS [WaitCount],
                ۱۰۰.۰ * [wait_time_ms] / SUM([wait_time_ms]) OVER() AS [Percentage],
                ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
         FROM   sys.dm_os_wait_stats
         WHERE  [wait_type] NOT IN (N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', 
                                   N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', 
                                   N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT')
     )

SELECT [W1].[wait_type]    AS [WaitType],
       CAST([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
       CAST([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
       CAST([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
       [W1].[WaitCount]    AS [WaitCount],
       CAST([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
       CAST(([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL(14, 4)) AS [AvgWait_S],
       CAST(([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL(14, 4)) AS [AvgRes_S],
       CAST(([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL(14, 4)) AS [AvgSig_S]
FROM   [Waits]             AS [W1]
       INNER JOIN [Waits]  AS [W2]
            ON  [W2].[RowNum] <= [W1].[RowNum]
GROUP BY
       [W1].[RowNum],
       [W1].[wait_type],
       [W1].[WaitS],
       [W1].[ResourceS],
       [W1].[SignalS],
       [W1].[WaitCount],
       [W1].[Percentage]
HAVING SUM([W2].[Percentage]) - [W1].[Percentage] < 95;
GO     

در فصل سوم کتاب می‌خوانیم

ویژگی Query store  یکی از قابلیت‌های بی‌نظیری هست که از نسخه  SQL SERVER 2016 شاهد آن هستیم. با استفاده از این ویژگی، می‌توانید الگوی‌هایی را کشف کنید که باعث اختلال در عملکرد سیستم می‌شود. اما قبل از این که وارد این مبحث شویم نیاز هست که برخی از تعاریف را با دقت بررسی کنیم که آیا قابلیت‌هایی فعلی که در نسخه‌های قبل نیز وجود داشته است، چه نقاط قوت و ضعفی نسبت به این ویژگی دارند و چرا ماکروسافت تاکید دارد که استفاده از این قابلیت در برطرف مشکلات سیستم نسبت به سایر روش‌ها از اولویت بالاتری برخوردار هست. ماهیت جمع آوری  و ذخیره‌سازی اطلاعاتی که توسط Query store ذخیره می‌شود بر اساس یک Baseline است. اما نکته ایی که هست، در خصوص جمع آوری دیتا و اطلاعات، این هست که این ذخیره و جمع آوری دیتا‌ها باید با یک معماری خوبی طراحی شده باشد که حداقل سربار را بر روی سیستم تحمیل کند.

استفاده از Query Store تنظیمات خاصی دارد که باید به درستی انجام شود. همچنین گزارشاتی که به شما ارایه می‌دهد، که بتوانید در کوتاه‌ترین زمان، مشکلات کوئری‌ها را پیدا کنید. با توجه به این که در این فصل به صورت مختصر این موضوع ارایه شده است و این مبحث بسیار جزییات زیادی به همراه دارد لذا می‌توانید از مقاله‌هایی که قبلا در سایت نیک اموز منتشر شده است برای تکمیل محتوای این بخش استفاده کنید. در هر مقاله بخشی از قابلیت‌هایی این موضوع اشاره شده است که می‌توانید از سناریو‌های عملیاتی از آن به خوبی استفاده کنید: در مقاله‌های زیر، علاوه بر این که این قابلیت با جزییات کامل معرفی شده است، مبحث پلن‌های اجرایی نیز مورد بررسی قرار گرفته است. پلن‌های اجرایی جزو لاینفک Query Store محسوب می‌شود. در واقع اساس و پایه این تکنولوژی برای این هست که که بر اساس جمع آوری داده‌هایی که از کوئری‌های اجرا شده حاصل می‌شود، بتوانیم تحلیل‌های مناسبی بر روی پلن‌های اجرایی داشته باشیم. البته لازم به ذکر هست که ابن گونه پلن‌ها به شکل پلن‌های تخمینی مشاهده می‌شود. یکی از مهمترین موضوعاتی که در مقالات قبلی بدان اشاره شد استفاده از Catalog View‌هایی هست که صرفا برای Query Store می‌توانیم داشته باشیم. این View‌ها به صورت زیر هستند:

• database_query_store_options (Transact-SQL)
• query_context_settings (Transact-SQL)
• query_store_plan (Transact-SQL)
• query_store_query (Transact-SQL)
• query_store_query_text (Transact-SQL)
• query_store_wait_stats (Transact-SQL)
• query_store_runtime_stats (Transact-SQL)
• query_store_runtime_stats_interval (Transact-SQL)
• query_store_query_hints (Transact-SQL)

در هر Catalog View ایی که مشاهده می‌کنید اطلاعات مختلفی جهت تحلیل مورد استفاده قرار می‌گیرد. زمانی که هر کدام از Catalog View‌های بالا را اجرا کنید ستون‌های را مشاهده می‌کنید که جزییات مرتبط با آن بخش به صورت کامل نمایش داده می‌شود. به عنوان مثال در sys. query_store_plan می‌توانیم ستون‌های مختلفی را مشاهده کنیم که یکی از آن‌ها مرتبط با بحث compatibility_level دیتابیس هست که مشخص می‌کند کوئری‌ها در چه مدی در حال اجرا هستند. یا ستونی را مشاهده می‌کنیم که با is_parallel_plan مشخص می‌شود و می‌توانیم به راحتی کلیه پلن‌هایی که به سمت موازی‌سازی هدایت شده‌اند را از سیستم استخراج کنیم. پس حتما به صورت مجزا این Catalog View‌ها را به ترتیب اجرا کنید تا با اطلاعات سفارشی شده که در هر بخش است آشنا شوید. همچنین یکی از مهمترین ستون‌هایی که در این کتاب با تاکید زیاد بر روی ان اشاره شده ستونی به اسم count_compiles هست که در دو Catalog Views به sys. query_store_query و sys. query_store_planمی توانید مشاهده کنید. اطلاعات این ستون مشخص می‌کند که به چه دفعاتی Query ‌هایی که نوشته شده است compiled یا recompiled شده است. استفاده از پارامتر‌هایی در کوئری باعث می‌شود که Query به ازای هر اجرا، recompiled شود که این به تنهایی ممکن است سرباری بر روی سیستم اعمال کند و در بعضی از موارد Performance کوئری را نیز بهبود دهد. پس کلیه این پارامتر‌ها با جزییات دقیقی از این Catalog View‌ها قابل استخراج هست .

ایجاد یک Baseline برای آنالیز و تحلیل اطلاعات کوئری‌ها وظیفه اصلی یک DBA هست که بر اساس ان سرباری که به سیستم عملیاتی اعمال می شود را تحلیل و بررسی کند. مسلما در محیط‌های عملیاتی زمان‌هایی وجود دارد که نرخ این سربار از سایر زمان‌ها به مراتب بیشتر بوده و به اصلاح در زمان‌های مختلف، دیتابیس شما ریتم‌های مختلفی را برای شما می‌نوازد. لذا باید دقت داشته باشید که این ریتم را به صورت مرتب بررسی کنید. در صورتی که این Baseline را داشته باشید مسلما هر زمانی که اتفاق مشکوکی در سیستم رخ دهد با روز قبل، هفته یا حتی ماه یا سال قبل هم قابل مقایسه هست که آیا این سرباری که بر سیستم تحمیل شده است عادی بوده یا اتفاق دیگری در حال رخ دادن هست. به عنوان مثال گزارشی که در خصوص پسرفت کوئری‌ها بود را مشاهده کردیم که در چه زمان‌هایی کوئری‌ها عملکرد بهینه خود را از دست خواهند داد یا زمان‌هایی که از یک نسخه پایین تر به نسخه بالاتر مهاجرت می‌کنیم باید دقت داشته باشیم که کندی و سربار اضافی به سیستم به هر دلیل اعمال نشود. لذا Query Store در این زمینه می‌تواند ارزش افزوده بسیار خوبی برای اطلاعات تحلیلی شما ایجاد کند. در ادامه به بعضی از این سناریو‌ها با جزییات بیشتری خواهیم پرداخت .

کلیه این موارد به صورت کامل در مقالاتی که خدمت شما ارایه شد ، توضیحات هر بخش را به صورت مستقل داراست لذا صرفا به محتوای این فصل از کتاب نباید بسنده کرد چرا که درحد معرفی به ان پرداخته شده و جزییات هر کدام از این موارد ، اشاره نشده است.

در فصل چهارم کتاب می‌‌‌خوانیم

یکی از نقاط ضعفی که در بحث Waitها شاهد هستیم بدین شکل هست که آمار مرتبط با آنها پس از ریست شدن سرور از بین می‌رود به همین دلیل باید روشی وجود داشته باشد که بتوانیم ، این آمار را در بازه های زمانی مختلف مورد بررسی قرار دهیم. یکی از نکات مهمی که در این کتاب بدان اشاره شده، در این خصوص است که تحلیل امار مرتبط با Waitها همواره باید به صورت یک چرخه‌ایی از اجرای مرتب فرایند ها باشد. بدین شکل که اول باید با استفاده از اسکریپت هایی که در قسمت های قبلی معرفی شد ، به این نکته برسید که چه مشکلی در حال حاضر در سرور شما وجود دارد؟ در مرحله بعد باید این موضوع را بررسی کنیم که آمار مرتبط با این مشکلات به چه شکل است و چه ارتباطی بین هر کدام دیده می‌شود؟  در مرحله بعد ، روش‌ها و راه حل هایی که برای رفع کردن ان وجود دارد به چه شکل است و در مرحله اخر ، پس از پیاده سازی روش مورد نظر یا اصلاح آن، مجددا وضعیت سرور را مورد بررسی قرار دهیم. در این چرخه ، عملا باید علاوه بر داشتن آمار قبلی و مقایسه با آمار جدید ، همان طور که در قسمت های قبلی نیز مطرح شد  ، باید ارتباط بین این مشکلات را به خوبی متوجه شویم . روش پیشنهادی کتاب استفاده از منطق Baseline هست که جداولی ساخته شود و Job هایی به صورت مرتب ، در بازه های زمانی مختلف ، این آمار و اطلاعات را در این جداول ذخیره کنند . سپس تحلیل ها و امار را بر روی هر کدام از ان ها انجام دهیم.

اگر به خاطر داشته باشید، در مقالاتی که مبحث Query Store را مورد بررسی قرار دادیم، به این نکته اشاره شد که معماری Query Store در لایه‌ایی از نرم افزار، اطلاعات لازم را مطابق با منطقی که توضیح داده شد، به صورت خودکار ذخیره و جمع اوری می کند و در ادامه مشاهده کردیم که پارامترهایی در این خصوص وجود دارد که بتوانید نسبت به حالت‌های ذخیره سازی و ارشیو کردن اطلاعات و حتی زمان حذف اطلاعات قدیمی تنظیمات مختلفی را انجام دهیم.

برای این که مروری بر روی این موضوع داشته باشیم، در مقاله مرتبط با این موضوع، این موارد مطرح شدند:

در صورتی که از نسخه SQL Server ۲۰۱۶ استفاده می‌کنید می‌توانید از تنظیمات زیر استفاده کنید:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
        OPERATION_MODE = READ_WRITE,
        CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
        DATA_FLUSH_INTERVAL_SECONDS = 900,
        QUERY_CAPTURE_MODE = AUTO,
        MAX_STORAGE_SIZE_MB = 1000,
        INTERVAL_LENGTH_MINUTES = 60
    );

در صورتی که از نسخه  SQL Server2017 استفاده می‌کنید می توانید از تنظیمات زیر استفاده کنید:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
        OPERATION_MODE = READ_WRITE,
        CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
        DATA_FLUSH_INTERVAL_SECONDS = 900,
        QUERY_CAPTURE_MODE = AUTO,
        MAX_STORAGE_SIZE_MB = 1000,
        INTERVAL_LENGTH_MINUTES = 60,
        SIZE_BASED_CLEANUP_MODE = AUTO,
        MAX_PLANS_PER_QUERY = 200,
        WAIT_STATS_CAPTURE_MODE = ON
    );

در صورتی که از نسخه  SQL Server2019 استفاده می‌کنید می‌توانید از تنظیمات زیر استفاده کنید:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
        OPERATION_MODE = READ_WRITE,
        CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
        DATA_FLUSH_INTERVAL_SECONDS = 900,
        MAX_STORAGE_SIZE_MB = 1000,
        INTERVAL_LENGTH_MINUTES = 60,
        SIZE_BASED_CLEANUP_MODE = AUTO,
        MAX_PLANS_PER_QUERY = 200,
        WAIT_STATS_CAPTURE_MODE = ON,
        QUERY_CAPTURE_MODE = CUSTOM,
        QUERY_CAPTURE_POLICY = (
            STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
            EXECUTION_COUNT = 30,
            TOTAL_COMPILE_CPU_TIME_MS = 1000,
            TOTAL_EXECUTION_CPU_TIME_MS = 100
        )
    );

تمامی این پارامترها و کاربردهای آنها نیز در مقالات جداگانه ایی مورد بررسی قرار گرفته است که می توانید آن ها دنبال کنید . هدف از یاداوری این موضوع ، این هست که در صورتی که پیاده سازی یک Baseline ، به مراتب چالشهای خاص خودش را دارد و باید خیلی از پارامتر ها و تنظیمات را در نظر داشته باشید . روش پیشنهاد کتاب برای این موضوع یکی از ساده ترین روش هایی هست که می توانید استفاده کنید . ابزار های مختلفی هم برای این موضوع وجود دارند نظیر ابزاری تحت عنوان SQL-Server-First-Responder-Kit که بخشی از این ابزار ، دقیقا برای ایجاد Baseline بوده که بتوانید امار و اطلاعات لازم را از سرور های عملیاتی استخراج و ثبت و ضبط کنید . بررسی این پکیچ ، مسلما خارج از حوصله این مقاله است چرا که شامل پارامتر ها و اسکپریت های مختلفی هست که هر کدام در سناریو خاصی مورد بررسی قرار میگیرد . پس علاوه بر مطالعه این فصل از کتاب و اشنایی با ماهیت و هویت Baseline حتما از سایر روش ها و ابزار هایی که می توانید این اطلاعات را ذخیره و جمع اوری کنید استفاده نمایید . مسلما جمع اوری این متریک ها و ارایه آن ها در داشبورد های مدیریتی نظیر Power Bi یا سایر ابزار هایی که برای تحلیل اطلاعات مورد استفاده قرار میگیرد ، دید بسیار خوبی از روند کاری شما ارایه خواهد کرد و همچنین می توانید قبل از این که کاربران از کندی ها و مشکلاتی در سیستم شکایت کنند ، این مشکلات را خیلی قبل تر از آنها متوجه شده ، بررسی و برطرف کنید.

برای این که نسبت به اسکریپت ها و ساخت یک پلن مرتبط دید شفاف تری پیدا کنید می توانید از اسکریپت زیر استفاده نمایید . در این اسکریپت با ساخت یک جدول ، امار مرتبط با Wait Statistics ها را قرار هست ذخیره کنیم . این امار باید به گونه ایی باشد که به صورت پیوسته توسط جاب هایی که برای ان نوشته می شود ، ذخیره ونگهداری شود . همچنین رویه هایی در این بیس لاین ایجاد شده است که بتوانیم امار را به صورت گزارش سفارشی سازی شده برای خودمان تبدیل کنیم . به عنوان یک ایده اولیه می توانید از این روش استفاده کنید و مطابق با Baseline  ایی که در این فصل صحبت شد این کار را انجام دهید.

IF NOT EXISTS (
       SELECT *
       FROM   sys.tables
       WHERE  OBJECT_ID = OBJECT_ID('dbo.WaitStats')
   )
BEGIN
    CREATE TABLE dbo.WaitStats
    (
      WaitType              NVARCHAR(60)
       ,WaitingTasksCount     BIGINT
       ,WaitTimeMs            BIGINT
       ,MaxWaitTimeMs         BIGINT
       ,SignalWaitTimeMs      BIGINT
       ,CollectionDate        DATETIME
       ,CONSTRAINT PK_WaitStats
        PRIMARY KEY CLUSTERED(WaitType ,CollectionDate)
    );
END
GO
CREATE OR ALTER VIEW dbo.ImportantWaits
AS
SELECT *
FROM   dbo.WaitStats
WHERE  WaitType NOT IN (N'BROKER_EVENTHANDLER'
                       ,N'BROKER_RECEIVE_WAITFOR'
                       ,N'BROKER_TASK_STOP'
                       ,N'BROKER_TO_FLUSH'
                       ,N'BROKER_TRANSMITTER'
                       ,N'CHECKPOINT_QUEUE'
                       ,N'CHKPT'
                       ,N'CLR_AUTO_EVENT'
                       ,N'CLR_MANUAL_EVENT'
                       ,N'CLR_SEMAPHORE'
                       ,N'CXCONSUMER'
                       ,N'DBMIRROR_DBM_EVENT'
                       ,N'DBMIRROR_EVENTS_QUEUE'
                       ,N'DBMIRROR_WORKER_QUEUE'
                       ,N'DBMIRRORING_CMD'
                       ,N'DIRTY_PAGE_POLL'
                       ,N'DISPATCHER_QUEUE_SEMAPHORE'
                       ,N'EXECSYNC'
                       ,N'FSAGENT'
                       ,N'FT_IFTS_SCHEDULER_IDLE_WAIT'
                       ,N'FT_IFTSHC_MUTEX'
                       ,N'HADR_CLUSAPI_CALL'
                       ,N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
                       ,N'HADR_LOGCAPTURE_WAIT'
                       ,N'HADR_NOTIFICATION_DEQUEUE'
                       ,N'HADR_TIMER_TASK'
                       ,N'HADR_WORK_QUEUE'
                       ,N'KSOURCE_WAKEUP'
                       ,N'LAZYWRITER_SLEEP'
                       ,N'LOGMGR_QUEUE'
                       ,N'MEMORY_ALLOCATION_EXT'
                       ,N'ONDEMAND_TASK_QUEUE'
                       ,N'PARALLEL_REDO_DRAIN_WORKER'
                       ,N'PARALLEL_REDO_LOG_CACHE'
                       ,N'PARALLEL_REDO_TRAN_LIST'
                       ,N'PARALLEL_REDO_WORKER_SYNC'
                       ,N'PARALLEL_REDO_WORKER_WAIT_WORK'
                       ,N'PREEMPTIVE_OS_FLUSHFILEBUFFERS'
                       ,N'PREEMPTIVE_XE_GETTARGETSTATE'
                       ,N'PWAIT_ALL_COMPONENTS_INITIALIZED'
                       ,N'PWAIT_DIRECTLOGCONSUMER_GETNEXT'
                       ,N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
                       ,N'QDS_ASYNC_QUEUE'
                       ,N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
                       ,N'QDS_SHUTDOWN_QUEUE'
                       ,N'REDO_THREAD_PENDING_WORK'
                       ,N'REQUEST_FOR_DEADLOCK_SEARCH'
                       ,N'RESOURCE_QUEUE'
                       ,N'SERVER_IDLE_CHECK'
                       ,N'SLEEP_BPOOL_FLUSH'
                       ,N'SLEEP_DBSTARTUP'
                       ,N'SLEEP_DCOMSTARTUP'
                       ,N'SLEEP_MASTERDBREADY'
                       ,N'SLEEP_MASTERMDREADY'
                       ,N'SLEEP_MASTERUPGRADED'
                       ,N'SLEEP_MSDBSTARTUP'
                       ,N'SLEEP_SYSTEMTASK'
                       ,N'SLEEP_TASK'
                       ,N'SLEEP_TEMPDBSTARTUP'
                       ,N'SNI_HTTP_ACCEPT'
                       ,N'SOS_WORK_DISPATCHER'
                       ,N'SP_SERVER_DIAGNOSTICS_SLEEP'
                       ,N'SQLTRACE_BUFFER_FLUSH'
                       ,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
                       ,N'SQLTRACE_WAIT_ENTRIES'
                       ,N'VDI_CLIENT_OTHER'
                       ,N'WAIT_FOR_RESULTS'
                       ,N'WAITFOR'
                       ,N'WAITFOR_TASKSHUTDOWN'
                       ,N'WAIT_XTP_RECOVERY'
                       ,N'WAIT_XTP_HOST_WAIT'
                       ,N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
                       ,N'WAIT_XTP_CKPT_CLOSE'
                       ,N'XE_DISPATCHER_JOIN'
                       ,N'XE_DISPATCHER_WAIT'
                       ,N'XE_TIMER_EVENT');
GO
CREATE OR 
ALTER PROCEDURE dbo.s_CollectWaitStats (@WaitThresholdInMs BIGINT)
AS
  INSERT dbo.WaitStats
    (
      WaitType
     ,WaitingTasksCount
     ,WaitTimeMs
     ,MaxWaitTimeMs
     ,SignalWaitTimeMs
     ,CollectionDate
    )
  SELECT wait_type
        ,waiting_tasks_count
        ,wait_time_ms
        ,max_wait_time_ms
        ,signal_wait_time_ms
        ,GETUTCDATE()
  FROM   sys.dm_os_wait_stats
  WHERE  wait_time_ms>@WaitThresholdInMs;
  
  -- Clean up
  DELETE dbo.WaitStats
  WHERE  CollectionDate<DATEADD(DAY ,-7 ,GETUTCDATE());
GO


CREATE OR 
ALTER PROCEDURE dbo.s_WaitStatsHistogram
  @HistogramBucketSizeInMinutes INT=60
AS
  WITH WaitStatsHistogram AS
  (
      SELECT W.WaitType
            ,C.GroupedCollectionDate  AS CollectionDate
            ,MAX(WaitTimeMs)          AS WaitTimeMs
      FROM   ImportantWaits W
             CROSS APPLY (
          SELECT DATEADD(
                     MINUTE
                    ,(
                         DATEDIFF(MINUTE ,0 ,W.CollectionDate)/@HistogramBucketSizeInMinutes
                     )*@HistogramBucketSizeInMinutes
                    ,۰
                 )
      )                               AS C(GroupedCollectionDate)
      GROUP BY
             W.WaitType
            ,C.GroupedCollectionDate
  )
  SELECT WaitType
        ,CollectionDate
        ,WaitTimeMs- 
         LAG(WaitTimeMs ,1 ,NULL) OVER(PARTITION BY(WaitType) ORDER BY(CollectionDate)) AS WaitTimeMs
  FROM   WaitStatsHistogram;
GO


DECLARE @DBName SYSNAME=DB_NAME();
DECLARE @JobName SYSNAME='CollectWaitStats';
DECLARE @JobDesc SYSNAME
       =' Source:   '+'https://github.com/mjswart/CollectWaitStats. ;' 
       +' Created:   '+CAST(GETDATE() AS VARCHAR(20))+';'
       +' By:    '+SUSER_NAME()+';';

IF NOT EXISTS 
   (
       SELECT *
       FROM   msdb.dbo.sysjobs
       WHERE  NAME = @JobName
   )
BEGIN
    EXEC msdb.dbo.sp_add_job 
         @job_name=@JobName
        ,@description=@JobDesc;
    
    EXEC msdb.dbo.sp_add_jobstep 
         @job_name=@JobName
        ,@step_name=N'Collect Wait Stats'
        ,@command=N'exec s_CollectWaitStats @WaitThresholdInMs = 1000;'
        ,@database_name=@DBName;
    
    EXEC msdb.dbo.sp_add_jobserver 
         @job_name=@JobName
        ,@server_name=N'(local)';
    
    EXEC msdb.dbo.sp_add_jobschedule 
         @job_name=@JobName
        ,@name=N'Minutely'
        ,@enabled=1
        ,@freq_type=4
        ,@freq_interval=1
        ,@freq_subday_type=4
        ,@freq_subday_interval=1;
END
GO

در فصل پنجم کتاب می‌خوانیم

در فصل‌های قبلی ، تمرکز نویسنده عمدتا بر روی معماری، معرفی روش ها و ابزارها، و همچنین اشنا کردن مخاطبان با تکنیک‌هایی هست که بتوانند این اطلاعات را ذخیره کنند و به خوبی تحلیل کنند. اما از این فصل به بعد،  هدف نویسنده ، بررسی عمیق تر مشکلاتی هست که در گروه بندی‌های مختلف از Wait typeها شاهد هستیم. به همین دلیل، هر فصل از کتاب، به دسته بندی خاصی از Wait type ها اشاره می‌کند و در ابتدا مهمترین گروه Wait ها را در هر دسته معرفی کرده، سپس مشکلاتی که هر کدام ممکن است به وجود اوردند را بررسی می‌کند و در نهایت، روش‌های پیشنهادی برای برطرف کردن آن‌ها را به مخاطبان، معرفی می‌کند . این فصل موضوع CPU-Related Wait Types را مورد بررسی قرار می‌دهد.

در فصل ششم کتاب می‌خوانیم

در این فصل ، مباحث مرتبط با IO-Related Wait Types ها یا مشکلات مرتبط با I/O مورد بررسی قرار گرفته است .مسلما بحث I/O یکی از مهمترین مواردی هست که همواره باید عملکرد آن را از جنبه های مختلف زیر نظر داشته باشید . به همین دلیل هم هست که در این لیست نسبت به سایر بخش ها ، تعداد بیشتری از Wait ها را شاهد هستیم . در سرور های عملیاتی به دلیل درگیری زیادی که با دیسک جهت خواندن و یا ثبت اطلاعات وجود دارد ، ممکن است کندی هایی داشته باشیم . گاهی اوقات نیز ، بهینه نبودن اطلاعات و لود شدن اطلاعات اضافه و  انتقال در بستر شبکه ، باعث کندی های خاصی خواهد شد . از طرفی Best practice هایی برای استقرار و نحوه تنظیم Data File  ها و Log file ها وجود دارد که باید بسته به شرایط سرور به درستی تنظیم شود . در این بخش ، عوامل دیگری نیز می تواند مشکلاتی که در بالا مطرح شده است را کمتر کند . به عنوان مثال شناخت دقیق پلن‌های اجرایی کوئری ها ، سیاست های ایندکس گذاری بر روی جداول ، استقرار صحیح Data/LOG File ، بررسی صحت و سلامت دیسک ها ، نوع RAID بندی دیسک‌ها و عوامل دیگر در این بخش به تفصیل مورد برسی قرار گرفته است . همان طور که مشاهده می کنید ، در این بخش بحث IO مطرح شده است ولی ارتباط نزدیکی با بخش های مختلف دارد . لذا جهت درک بهتر این موضوع باید نسبت به مواردی که در بالا گفته شد نیز به صورت دقیق تری ، مطالعه داشته باشید . مهمترین Waitهایی که در این سرفصل مورد بررسی قرار میگیرند به شرح زیر هستند :

• ASYNC_IO_COMPLETION
• ASYNC_NETWORK_IO
• CMEMTHREAD
• IO_COMPLETION
• LOGBUFFER and WRITELOG
• RESOURCE_SEMAPHORE
• RESOURCE_SEMAPHORE_QUERY_COMPILE
• SLEEP_BPOOL_FLUSH
• WRITE_COMPLETION

در فصل هفتم کتاب می‌خوانیم

در این فصل ، مباحث مرتبط با Backup-Related Wait Types مورد بررسی قرار گرفته است .بحث Backup گرفتن از دیتابیس ، همان طور که در شکل های زیر نیز مشاهده می کنید ، شامل فرایند هایی مختلفی هست که برای Engine تعریف شده است . این فرایند ها از دید کاربر نهایی مسلما مخفی شده است ولی می توانید لاگ هر بخش را با دستوراتی به تفکیک مشاهده کنید . چون به بحث از دید فرایندی نگاه می کنیم مسلما هر بخشی باید از بخش قبلی خود اطلاعات را دریافت ، پردازش و اجرا کند که در نتیجه فرایند به درستی انجام شود . اما گاها با مشکلات مختلفی در این بحث رو به رو هستیم . به عنوان مثال سناریو ایی را در نظر بگیرید که حجم اطلاعات بسیاز زیاد هست و بخواهیم در کمترین زمان از این اطلاعات بکاپ تهیه کنیم. همین پروسه را وقتی در نظر بگیرید مسلما بخشی از منابع سیستم برای این فرایند خرج می شد . اما چرا باید اجازه دهیم که این قدر زمان بکاپ طولانی شود ؟ دقیقا مشکل اصلی کجاست و به چه شکل می توانیم بررسی کنیم ؟ پس همه این عوامل از منابع سرور که درگیر اجرای این فرایند هستند تا تک به تک اجزای این فرایند را می توانیم توسط Wait هایی شناسایی کرده و دقیقا مشکل اصلی را پیدا کنیم . برای هر کدام از این عوامل در این فصل ، به همراه روش ها و راهکار های مختلف ، این مشکلات نیز بررسی شده است . مهمترین Wait هایی که در این سرفصل مورد بررسی قرار میگیرند به شرح زیر هستند :

• BACKUPBUFFER
• BACKUPIO
• BACKUPTHREAD

 در فصل هشتم کتاب می‌خوانیم

در این فصل ، مباحث مرتبط با Lock-Related Wait Types مورد بررسی قرار گرفته است . همان طور که مشاهده می کنید ، سرفصل های این کتاب که در هر بخش بررسی می شود ، به تنهایی برای خودشان رفرنس ها و کتاب های جدایی دارند که به تفصیل در مورد ساختار آن ها صحبت شده است . مبحث Lock ها نیز به انواع مختلف ، در شرایط مختلف و حتی در سطوح مختلف در دیتابیس اتفاق می افتد. عمده ترین مسائل مرتبط با این مشکلات ، عدم بهینه بودن کوئری ها ، واکشی اطلاعات اضافی از دیتابیس ، ایندکس های نامناسب و چندین عامل دیگر هست که در این فصل به این وارد اشاره شده است . همان طور که در اول این مقاله توضیح داده شد ، هر فصلی که در این کتاب بررسی می شود ، ارتباط مستقیم با سایر Wait ها خواهد داشت . مهمترین نکته و برداشتی که می توان از این کتاب داشت بدین شکل هست که در واقع مجموعه ارتباط و یا زنجیره ایجاد این Wait ها را به خوبی تشخیص دهیم و مشکل را به شکلی برطرف کنیم که عملا سربار بانک اطلاعاتی به حد قابل قبولی کاهش یابد . لذا در هر سرفصل که دنبال می کنیم ، دید جدیدی نسبت به این ارتباطات حاصل می شود . البته مهمترین بعد این موضوع بدین شکل هست که با اجرای اسکریپت هایی که در همین کتاب معرفی شد ، در شرایط مختلف بتوانید این ارتباطات را کشف کرده و مشکل آن را ریشه یابی کنید . مهمترین Wait هایی که در این سرفصل مورد بررسی قرار میگیرند به شرح زیر هستند

• LCK_M_S
• LCK_M_U
• LCK_M_X
• LCK_M_I[xx]

در فصل نهم کتاب می‌خوانیم:

در این فصل ، مباحث مرتبط با Latch-Related Wait Typesمورد بررسی قرار گرفته است .ساختار Latch ها کاملا مرتبط با فضای Ram بوده و برای این که اطلاعاتی که در حافظه بارگزاری شده است ، طی فرایند هایی مجددا در اختیار Engine قرار گیرد ، باید مکانیزمی داشته باشیم که اطلاعات این بخش نیز دستخوش تغییر نشود یا کنترل هایی بر روی ان انجام شود تا یکپارچکی اطلاعات حفظ شود . به عنوان مثال PAGEIOLATCH برای سناریو ایی هست که اطلاعاتی از دیسک قرار هست به رم منتقل شود . این فرایند انتقال ، در شرایطی ممکن هست با کندی همراه باشد . پس باید عامل دیسک هم در این شرایط در نظر گرفته شود که دقیقا چه مشکلی در ان قسمت وجود دارد . در این فصل نیز ، همانند فصل های قبل ، مشکلاتی که هر کدام از این Wait ها نشان می دهد با روش های مختلف ارایه شده است . همان طور که مجددا میبینید ، ارتباطی تنگاتنگی بین بخش های مختلف و حتی Wait های مختلف وجود دارد که باید زنجیره این Wait ها برای برطرف کردن مشکل اصلی سیستم شناسایی کنیم .  مهمترین Wait هایی که در این سرفصل مورد بررسی قرار میگیرند به شرح زیر هستند :

• PAGELATCH_[xx]
• LATCH_[xx]
• PAGEIOLATCH_[xx]

در فصل دهم کتاب می‌خوانیم:

در این فصل، مباحث مرتبط با High-Availability and Disaster-Recovery Wait Types  مورد بررسی قرار گرفته است . بحث AlwaysOn Availability Groups به عنوان یکی از مهمترین مباحث در زمینه در دسترس بودن سرور های عملیاتی مطرح می شود . پیاده سازی مبحث HA یا High Availability بعضا به روش های مختلفی امکان پذیر هست . هر روش هم از رش خاصی برای کپی اطلاعات در سرور ثانویه استفاده می کند . به همین دلیل ، هر کدام از انها برای پیاده سازی روش هایی که برای آن در نظر گرفته شده است ، منابعی از سرور را درگیر می کنند . به همین دلیل وجود resource bottleneck در این روش ها خود عاملی برای کندی یا افت Performance در این موضوع خواهد بود . یا به عنوان مثال سینک شدن طولانی اطلاعات بین دو یا چند سرور در بعضی از شرایط ریسک بسیار زیادی به همراه دارد که باید این کندی ها و مشکلات توسط اماری که در این Wait ها مشاهده می کنید مورد بررسی قرار گیرد. مهمترین Wait هایی که در این سرفصل مورد بررسی قرار می‌گیرند به شرح زیر هستند :

• DBMIRROR_SEND
• HADR_LOGCAPTURE_WAIT and HADR_WORK_QUEUE
• HADR_SYNC_COMMIT
• REDO_THREAD_PENDING_WORK

در فصل یازدهم کتاب می‌خوانیم:

در این فصل، مباحث مرتبط با Preemptive Wait Types مورد بررسی قرار گرفته است. این دسته از Wait ها کاملا مستقیم با سیستم عامل ایی هستند که دیتابیس شما بر روی ان استقرار پیدا کرده است .  به عنوان مثال فرض کنید از قابلیتی استفاده می کنید که نیاز به دسترسی به Active Directory برای اعمال سطح دسترسی ها دارد . پس باید سیگنالی از سمت Engine به بیرون از این محیط ارسال شود تا تاییده های لازم برای مجوز های مرتبط دریافت سپس ادامه فرایند انجام شود . از این دست موارد که Engine نیاز به ارتباط با خارج از خود دارد، بعضا ممکن هست باعث بالا رفتن آمار مرتبط با Wait های زیر شود . مهمترین Wait هایی که در این سرفصل مورد بررسی قرار میگیرند به شرح زیر هستند :

• PREEMPTIVE_OS_ENCRYPTMESSAGE and PREEMPTIVE_OS_DECRYPTMESSAGE
• PREEMPTIVE_OS_WRITEFILEGATHER
• PREEMPTIVE_OS_AUTHENTICATIONOPS
• PREEMPTIVE_OS_GETPROCADDRESS

در فصل دوازدهم کتاب می‌خوانیم:

در این فصل، مباحث مرتبط با Background and Miscellaneous Wait Types مورد بررسی قرار گرفته است .این دسته از Wait Typeها مرتبط با فرایندهایی هست که در پشت صحنه SQL OS اجرا می‌شوند و عملا جزو فرایندهای داخلی سیستم هستند. همان طور که در شکل بالا نیز مشاهده می‌کنید عمدتا بعضی از فرایند ها باید به صورت داخلی در محیط Engine انجام شود که زیر مجموعه فرایندهای بزرگتر هستند . این گونه فرایندها به اسم background processes شناخته می‌شوند و باید دید بسیار عمیقی از کارکرد Engine  داشته باشید. در این کتاب در لایه‌های مختلف این طبقه بندی‌ها انجام شده است که نسبت به فرایند های مختلف Wait‌های مختلف نیز مورد بررسی قرار گیرد .

• CHECKPOINT_QUEUE
• DIRTY_PAGE_POLL
• LAZYWRITER_SLEEP
• MSQL_XP
• OLEDB
• TRACEWRITE
• WAITFOR

در فصل سیزدهم کتاب می‌خوانیم:

در این فصل ، مباحث مرتبط با In-Memory OLTP–Related Wait Types مورد بررسی قرار گرفته است . مبحث In-Memory OLTP از نسخه ۲۰۱۴ وارد Engine شد و هدف آن نیز بدین شکل بود که Performance سیستم به طور قابل توجهی افزایش یابد . این افزایش عملکرد نیز در پردازش تراکنش ها ، انتقال داده ها, بارگزاری داده و به صورت کلی هر فرایندی که مستقیما با این داده ها سروکار دارد تاثیر گذار خواهد بود . در واقع جداولی که داریم یا بر روی دیسک ایجاد می شود که به اصطلاح به آنها disk-based table گفته می شود و جداولی نیز وجود دارند که مستقیما بر روی حافظه ایجاد می شودکه به اصطلاح به آنها memory-optimized tables گفته می شود. البته در این قسمت نمی خواهیم به صورت تخصصی کلیه فرایند این تکنولوژی را بررسی کنیم . در همین حد که بدانیم هر قابلیت جدیدی که اضافه می شود مسلما Wait های مختص به خود را داراست ، می توانیم در سناریو های مختلف آنها را بررسی کنیم . مهمترین Wait هایی که در این سرفصل مورد بررسی قرار میگیرند به شرح زیر هستند :

• WAIT_XTP_HOST_WAIT
• WAIT_XTP_CKPT_CLOSE
• WAIT_XTP_OFFLINE_CKPT_NEW_LOG

جمع بندی:

این کتاب، به عنوان منبعی ارزشمند دید بسیار خوبی از ساختار Waitها به شما ارایه می دهد که اول مشکلات اصلی سیستم را ریشه یابی کنید سپس طبق راه حل‌هایی که ارایه شده است بتوانید مشکلات را مرحله به مرحله بررسی و رفع و رجوع کنید . البته در فروم‌های مختلف ، تجربه های بیشتری از کاربران مختلف نیز در این خصوص مشاهده می‌کنید که از چه تکنیک ها و روش هایی برای برطرف کردن این مشکلات استفاده کردن. به عنوان یک DBA برای برطرف کردن مشکلاتی از این نوع، سعی کنید که نسبت به پلن‌های اجرایی و طرز درست نوشتن کوئری ها نظارت صحیح و دقیق تری داشته باشید . چرا که اصول صحیح کوئری نویسی ، عملا درگیری شما را با بیشتر این Wait ها کمتر خواهد کرد . در ادامه لطفا نظرات ارزشمند خود را از با ما به اشتراک بگذارید .

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/