خانه SQL Server اشنایی با Query Store | بخش ششم SQL Server افزایش سرعت SQL Server نوشته شده توسط: تیم فنی نیک آموز تاریخ انتشار: ۱۷ اسفند ۱۴۰۰ آخرین بروزرسانی: ۱۵ مهر ۱۴۰۲ زمان مطالعه: 22 دقیقه ۴ (۱) مقدمه در بخش اول با Query Store با روشها و متدهایی که برای انالیز و بررسی Query ها وجود داشتند، آشنا شدیم و مزایا و معایب هر کدام را نیز شرح دادیم. در بخش دوم با معماری داخلی Query Store و روش منحصر به فردی که برای جمع آوری اطلاعات کوئریها و سایر اطلاعات مرتبط با آنها بود نیز آشنا شدیم و همچنین پارامترهایی که برای تنظیمات مرتبط با این قابلیت بود نیز به صورت کلی تشریح شد. در بخش سوم مقاله آشنایی با Query Store نکاتی ارائه گردید که بتوانید در محیطهای عملیاتی به شیوه بهتری نسبت به جمع آوری اطلاعات از پلنهای اجرایی اقدام کنید. در بخش چهارم مقاله آشنایی با Query Store با Catalog viewهای بیشتری از Query Store آشنا خواهیم شد و همچنین کاربردهای Query Store در دنیای واقعی را بهتر خواهید شناخت. در قسمت پنجم از سلسله مقالات مرتبط با Query Store، در خصوص پلن های اجرایی صحبت کردیم و نکات مرتبط با آنها ارایه گردید . مسلما در بیزینس های مختلف و کوئری های متفاوت ساختار پلن ها عمدتا مشترک هست و اپراتور هایی که استفاده می شود در بیشتر مواقع یکسان هستند . به همین دلیل شناخت اپراتور ها شاید مشکل خاصی نداشته باشد . موضوع اصلی ارتباط بین این اپراتور ها و ورودی ها و خروجی هایی هست که این اپراتور ها با یکدیگر رد و بدل می کنند . در این خصوص یکی از بهترین کتاب ها برای بررسی پلن های اجرایی کتاب SQL Server Execution Plans: Third Edition هست که در فصل های مختلف ، دید بسیار جامع و کلی از کار با اپراتور ها را به شما نمایش می دهد . همچنین یکی از بهترین ابزار ها برای بررسی پلن های اجرایی ، ابزاری به اسم SentryOne هست که در زمینه های مختلف ، نرم افزار های کاربردی ارایه کرده است . SentryOne ابزاری برای انالیز و تحلیل پلن های اجرایی هست که می توانید از جنبه های مختلف ، پلن ایجاد شده رو مورد بررسی قرار دهید. در این مقاله، می خواهیم پلن های اجرایی را در Query Store دقیق تر بررسی کنیم و ببینیم با چه قابلیت هایی می توانیم به صورت خودکار، بهینه ترین پلن ها را همیشه اجرا کنیم . همچنین نگاهی عمیق تر به ساختار کوئری ها خواهیم داشت که در قالب داشبورد های تحلیلی به شما ارایه می شود . شاید نیاز داشته باشید که منطبق با سلیقه یا نیاز خود این داشبورد ها را سفارشی سازی کنید . پس باید بتوانیم Baseline ایی که در این سری از مقالات، بررسی شد را بسته به نیاز خودمان در هر بخش شبیه سازی کنیم و در اصلاح تحلیل هایی دقیق تر و جزیی تری بر روی این فرایند ها داشته باشیم . در قسمت قبل، در گزارش های مرتبط با پسرفت کوئری که خدمت شما ارایه شد، متوجه شدید که چندین پلن اجرایی ممکن است برای یک کوئری در نظر گرفته شود . اما قابلیت بسیار خوبی که در این گزارش هست ، مقایسه پلن های اجرایی با یکدیگر هست. مطابق با عکسی که در شکل زیر مشاهده می کنید می توانید این مقایسه را انجام دهید: پس از اعمال گزینه بالا ، می توانیم پلن ها را با یکدیگر مقایسه کنیم: زمانی که دو پلن اجرایی با یکدیگر مقایسه می شوند ، دو قسمت مجزا به شما نمایش داده خواهد شد. در سمت چپ، اپراتور ها و در سمت راست ویژگی های مرتبط با هر کدام به شما نمایش داده می شود. زمانی که بر روی هر اپراتور کلیک کنیم ، قسمت منتخب با کاور صورتی شکل مطابق با عکس بالا نمایش داده می شود. در سمت راست در قسمت Properties ، در واقع اطلاعات کلی هر پلن قابل نمایش هست. عبارت هایی که ایکون زرد رنگ و نامساوی در کنار آن ها قرار دارند ، نشان دهنده این هست که این مقادیر در دو پلن با یکدیگر مساوی نیستند. این مقایسه ما بین ویژگی ها در دو پلن به ما نشان خواهد داد که مشکل دقیقا در چه بخشی هست. برای این که بتوانیم این مثال را عینا مشاهده کنیم می توانید کوئری های زیر را اجرا کنید: CREATE OR ALTER PROC [dbo].[AddressByCity] @City NVARCHAR(30) AS SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE a.City = @City; GO در این قسمت بر اساس مقادیر مختلفی که در این جدول هستند می خواهیم Store proc را فرخوانی کنیم: EXEC dbo.AddressByCity @City = N'London'; ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; EXEC dbo.AddressByCity @City = N'Mentor'; در این قسمت بر اساس مقادیر مختلفی که در این جدول هستند می خواهیم Store proc را فرخوانی کنیم با این تفاوت که در قسمت اول با یک پلن کوئری ما ساخته می شود و در قسمت دوم کلیه پلن ها به ازای دیتابیس مورد نظر حذف شده و بر اساس مقدار شهر N’Mentor’ پلن اجرایی ایجاد می شود . لطفا کلیه این کوئری را به ترتیب اجرا کنیدو در هر مرحله پلن های ان ها را با یکدیگر مقایسه کنید. --Establish baseline behavior EXEC dbo.AddressByCity @City = N'London'; GO 100 --Remove the plan from cache ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; --Compile a new plan EXEC dbo.AddressByCity @City = N'Mentor'; GO --Execute the code to show query regression EXEC dbo.AddressByCity @City = N'London'; GO 100 این مثال در قسمت قبل نیز بررسی شده بود. زمانی که قابلیت Query Store بر روی دیتابیس شما فعال باشد کلیه این موارد ذخیره شده و پلن های هر یک به شما نمایش داده می شود . سپس می توانید از طریق این قابلیت، دو پلن فوق را مورد بررسی قرار دهید . دقت داشته باشید در شکل اول اپراتور هایی مورد بررسی قرار گرفت که در دو پلن اجرایی شبیه یکدیگر بودند . عملا قابلیت مقایسه پلن های اجرایی زمانی می تواند مفید و موثر باشد که عمده اپراتور ها در دو پلن با یکدیگر یکسان باشند . در این خصوص می توانید بهترین خروجی ها را از مقایسه دو پلن دریافت کنید . با توجه به سناریو ها و شرایطی که در مقاله های قبل بررسی شد ، مشاهده کردیم که مشکلاتی نظیر Parameter Sniffing باعث ایجاد اختلال در عملکرد سیستم خواهند شد . در این شرایط باید چه کاری انجام دهیم؟ چه تغییری در سیستم اعمال شود که بتوانیم بهینه ترین پلن را پیدا کنیم؟ یکی از روش های پیشنهادی این کتاب، روش Force کردن این گونه پلن ها و مقایسه امار آنها نسبت به وضعیتی هست که از قبل تنظیم نشده بود . در واقع اعمال یک پلن و اجبار به تحلیل کوئری از روی این مسیر اجرایی که برای ان مشخص شده است ، بازخوردی که برای ما به همراه دارد از گزارشات تحلیلی Query Store به راحتی قابل ارزیابی هست. همچنین قابلیت دیگری که Query Store را در این شرایط نسبت به سایر روش های مشابه ممتاز می کند، قابلیت ردیابی پلن های Force شده هست که می توانیم انالیز دقیق بر روی عملکرد هر پلن داشته باشیم. شرایطی را در نظر بگیرید که سیستم عملیاتی را به شما تحویل داده اند و قرار هست کلیه این کوئری ها را رصد کنید. مطابق با Baseline مسلما به این نتیجه خواهید رسید که انتخاب بعضی از پلن ها در حین اجرا باعث می شود که مصرف منابع توسط برخی از کوئری های سنگین به شدت کاهش پیدا کند یا فرایند های روتین سیستم که شاید شامل گزارشات مختلف و متنوعی باشد را به راحتی از روی جداول عملیاتی تشخیص دهید. پیشنهاد می کنم که برای Force کردن پلن های اجرایی حتما سناریو بالا را در یک محیط تستی یا توسط نرم افزار هایی مانند Query Stress به مراتب و در تعداد درخواست های بالا اجرا کنید و میزان Wait ها و منابع سرور را دقیق تر بررسی نمایید. متوجه خواهید شد، زمانی که سیستم های عملیاتی زیر بار درخواست ها و گزارشات سنگین هستند عملا عدم نادیده گرفتن همین موارد و مشکلاتی جزیی به شدت کارایی سیستم را زیر سوال خواهد برد. در Query Store این کار به راحتی قابل انجام هست. مطابق با شکل پایین می توانید پلن های منتخب را Force کنید که در کوئری های مرتبط با آنها اجرا شوند: EXEC sys.sp_query_store_force_plan 262,273; همچنین زمانی که می خواهید شرایط را به حالت اول برگردانید می توانید از کوئری زیر استفاده کنید: EXEC sys.sp_query_store_unforce_plan 262,273; می توانید امار پلن هایی که بین شکل انتخاب شده است را با استفاده از اسکریپت زیر از سیستم استخراج کنید: SELECT qsq.query_id, qsp.plan_id, qsp.is_forced_plan Chapter 7 ForCing plans166 FROM sys.query_store_query AS qsq JOIN sys.query_store_plan AS qsp ON qsp.query_id = qsq.query_id WHERE qsq.query_id = 262 زمانی که کوئری بالا اجرا می شود خروجی اول به ما کلیات مرتبط با پلن های Force شده را نمایش می دهد . همچنین در عکس دوم زمانی که کوئری را بررسی کنیم پارامتری وجود دارد که متوجه خواهیم شد از یک پلن Force شده استفاده شه است یا خیر. تا این جا در خصوص قابلیت های ارزشمند Query Store مطالب مختلفی خدمت شما ارائه شد. اما یکی از قابلیت های بی نظیری که در این قسمت قرار هست معرفی کنیم ، در خصوص بحث بهینه سازی خودکار پلن اجرایی هست. در واقع کلیه مواردی که در این مقاله توضیح داده شد را قرار هست SQL SERVER برای ما به صورت هوشمند انجام بدهد و بر اساس افت عملکرد کوئری ها بهترین تصمیم را اتخاد کند. این قابلیت تحت عنوان Automatic Plan Correction شناخته می شود . کلیه این فرایند ها تحت شرایطی انجام می شود که پلن های ایجاد شده طی الگوریتم هایی که برای آن نوشته شده است تشخیص داده شود که کارایی آن در حال حاضر اصلا به نفع سیستم نیست و می تواند جایگزین خوبی داشته باشد. برای این که جزییات این موضوع را دقیق تر بررسی کنیم می خواهیم دیتابیس ایی داشته باشیم که شامل رکورد های بیشتری نسبت به دیتابیس های معمولی هست. بدین منظور می توانید از اسکریپت های زیر جهت درج اطلاعات بیشتر در دیتابیس Adventurework استفاده کنید. لطفا اسکریپت های زیر را اجرا کنید: /* * ایجاد جدول کالاها با رکورد های بیشتر */ USE AdventureWorks2019 GO SELECT p.ProductID + (a.number * 1000) AS ProductID, p.Name + CONVERT(VARCHAR, (a.number * 1000)) AS NAME, p.ProductNumber + '-' + CONVERT(VARCHAR, (a.number * 1000)) AS ProductNumber, p.MakeFlag, p.FinishedGoodsFlag, p.Color, p.SafetyStockLevel, p.ReorderPoint, p.StandardCost, p.ListPrice, p.Size, p.SizeUnitMeasureCode, p.WeightUnitMeasureCode, p.Weight, p.DaysToManufacture, p.ProductLine, p.Class, p.Style, p.ProductSubcategoryID, p.ProductModelID, p.SellStartDate, p.SellEndDate, p.DiscontinuedDate INTO bigProduct FROM Production.Product AS p CROSS JOIN MASTER..spt_values AS a WHERE a.type = 'p' AND a.number BETWEEN 1 AND 50 GO ALTER TABLE bigProduct ALTER COLUMN ProductId INT NOT NULL GO ALTER TABLE bigProduct ADD CONSTRAINT pk_bigProduct PRIMARY KEY(ProductId) GO SELECT ROW_NUMBER() OVER( ORDER BY x.TransactionDate, ( SELECT NEWID() ) ) AS TransactionID, p1.ProductID, x.TransactionDate, x.Quantity, CONVERT( MONEY, p1.ListPrice * x.Quantity * RAND(CHECKSUM(NEWID())) * 2 ) AS ActualCost INTO bigTransactionHistory FROM ( SELECT p.ProductID, p.ListPrice, CASE WHEN p.productid % 26 = 0 THEN 26 WHEN p.productid % 25 = 0 THEN 25 WHEN p.productid % 24 = 0 THEN 24 WHEN p.productid % 23 = 0 THEN 23 WHEN p.productid % 22 = 0 THEN 22 WHEN p.productid % 21 = 0 THEN 21 WHEN p.productid % 20 = 0 THEN 20 WHEN p.productid % 19 = 0 THEN 19 WHEN p.productid % 18 = 0 THEN 18 WHEN p.productid % 17 = 0 THEN 17 WHEN p.productid % 16 = 0 THEN 16 WHEN p.productid % 15 = 0 THEN 15 WHEN p.productid % 14 = 0 THEN 14 WHEN p.productid % 13 = 0 THEN 13 WHEN p.productid % 12 = 0 THEN 12 WHEN p.productid % 11 = 0 THEN 11 WHEN p.productid % 10 = 0 THEN 10 WHEN p.productid % 9 = 0 THEN 9 WHEN p.productid % 8 = 0 THEN 8 WHEN p.productid % 7 = 0 THEN 7 WHEN p.productid % 6 = 0 THEN 6 WHEN p.productid % 5 = 0 THEN 5 WHEN p.productid % 4 = 0 THEN 4 WHEN p.productid % 3 = 0 THEN 3 WHEN p.productid % 2 = 0 THEN 2 ELSE 1 END AS ProductGroup FROM bigproduct p ) AS p1 CROSS APPLY ( SELECT transactionDate, CONVERT(INT, (RAND(CHECKSUM(NEWID())) * 100) + 1) AS Quantity FROM ( SELECT DATEADD(dd, number, '20050101') AS transactionDate, NTILE(p1.ProductGroup) OVER(ORDER BY number) AS groupRange FROM MASTER..spt_values WHERE TYPE = 'p' ) AS z WHERE z.groupRange % 2 = 1 ) AS x ALTER TABLE bigTransactionHistory ALTER COLUMN TransactionID INT NOT NULL GO ALTER TABLE bigTransactionHistory ADD CONSTRAINT pk_bigTransactionHistory PRIMARY KEY(TransactionID) GO CREATE NONCLUSTERED INDEX IX_ProductId_TransactionDate ON bigTransactionHistory(ProductId, TransactionDate) INCLUDE(Quantity, ActualCost) GO دقت داشته باشید که اسکریپت های فوق بر روی دیتابیس AdventureWorks2019 اجرا شده است . لذا در صورتی که از نسخه های پایین تر استفاده می کنید حتما دقت داشته باشید که دیتابیس مورد نظر به درستی انتخاب شده باشد. پس از اجرای اسکریپت های بالا ، حالا باید رویه ایی ایجاد کنیم: CREATE OR ALTER PROCEDURE dbo.ProductByCost (@ActualCost MONEY) AS SELECT bth.ActualCost FROM dbo.bigTransactionHistory AS bth JOIN dbo.bigProduct AS p ON p.ProductID = bth.ProductID WHERE bth.ActualCost = @ActualCost; GO --ensuring that Query Store is on and has a clean data set ALTER DATABASE AdventureWorks2019 SET QUERY_STORE = ON; ALTER DATABASE AdventureWorks2019 SET QUERY_STORE CLEAR; GO حالا می خواهیم شرایطی را شبیه سازی کنیم که در آن افت عملکرد در پلن های اجرایی را مشاهده کنیم: /* * قسمت اول */ EXEC dbo.ProductByCost @ActualCost = 8.2205; GO 30 /* * قسمت دوم */ DECLARE @PlanHandle VARBINARY(64); SELECT @PlanHandle = deps.plan_handle FROM sys.dm_exec_procedure_stats AS deps WHERE deps.object_id = OBJECT_ID('dbo.ProductByCost'); IF @PlanHandle IS NOT NULL BEGIN DBCC FREEPROCCACHE(@PlanHandle); END GO /* * قسمت سوم */ EXEC dbo.ProductByCost @ActualCost = 0.0; GO /* * قسمت چهارم */ EXEC dbo.ProductByCost @ActualCost = 8.2205; GO 15 همان طور که در کوئری بالا مشاهده می کنید ، ۴ بخش نوشته شده است که هر بخش برای هدف خاصی در نظر گرفته شده . در بخش اول با اجرای رویه مورد نظر میخواهیم یک Baseline از کوئری مورد نظر ایجاد کنیم و در سیستم داشته باشیم . در بخش دوم کلیه پلن های ایجاد شده را از Plan Cache حذف می کنیم که از این به بعد کوئری های اجرا شده با پلن جدید ، ایجاد شوند. حتما بخش دوم را اجرا کنید، چرا که در صورت عدم اجرا، پلن های قبلی در حافظه باقی می ماند و عملکرد Automatic Plan Correction را نمی توانیم به خوبی بررسی کنیم .در قسمت سوم، رویه را با یک مقدار دیگری اجرا می کنیم که توزیع اطلاعات برای این مقدار با مقدار قسمت اول مقداری متفاوت است .در قسمت چهارم نیز این رویه با مقدار دیگری به تعداد ۱۵ مرتبه اجرا شده است که در خروجی مشاهده خواهید کرد.در نهایت اسکریپت پایین را اجرا کنید : SELECT DDTR.TYPE ,DDTR.REASON ,DDTR.LAST_REFRESH ,DDTR.STATE ,DDTR.SCORE ,DDTR.DETAILS FROM SYS.DM_DB_TUNING_RECOMMENDATIONS AS DDTR; در قسمت اول شاهد این پلن اجرایی هستیم: و در قسمت دوم زمانی که با مقدار @ActualCost = 0.0; رویه را اجرا کردیم شاهد پلن زیر هستیم: دقت داشته باشید زمانی که قسمت سوم را اجرا می کنید، در حدود ۱۲ میلیون رکورد، دیتا وارد پلن اجرایی می شود. لذا در اینجا عملکرد اپراتور ها باید نسبت به ظرفیت داده های ورودی، با ظرفیت بالاتر و اپراتور های سنگین تر عملیاتی شود. دلیل این موضوع هم بدین خاطر هست که به نسبت الگوریتم هایی که در پشت هر کدام از این بخش ها نوشته شده است ، هر اپراتور بسته به اطلاعاتی ورودی مختلف ، عملکرد متفاوتی را از خود نشان خواهد داد. به عنوا مثال زمانی که از Merge Join در پلن های اجرایی استفاده شود نشان دهنده این موضوع هست که دو طرف ورودی به این اپراتور باید اطلاعات مرتب شده ایی باشند که منطبق با الگوریتم Merge Join پردازش بر روی ان انجام شود. در فصل دوم و سوم کتابی که در اول مقاله خدمت شما معرفی شد کلیه این جزییات مطرح شده است و با الگوریتم های مختلفی که پشت سر هر اپراتور هست اشنا خواهید شد. زمانی که خروجی اسکریپت بالا را ملاحظه کنید بدین شکل پیغامی به شما نمایش داده می شود: Average query CPU time changed from 0.16ms to 4909.41ms پیامی که در بالا مشاهده می کنید توسط این DMV به ما نمایش داده شده. مسلما تغییر پلن در این حالت اصلا به نفع سیستم نیست که بخواهیم این پلن اجرایی را بر روی کلیه کوئری ها با مقادیر مختلف Force کنیم. همچنین می توانید با استفاده از اسکریپت زیر در صورتی که در این DMV پیغامی را مشاهده کردید جزییات مختلف با متن آنرا مشاهده کنید. خروجی این قسمت به شکل Json هست و اطلاعات خوبی در اختیار شما قرار خواهد داد. WITH DbTuneRec AS ( SELECT ddtr.reason, ddtr.score, pfd.query_id, pfd.regressedPlanId, pfd.recommendedPlanId, JSON_VALUE(ddtr.state, '$.currentValue') AS CurrentState, JSON_VALUE(ddtr.state, '$.reason') AS CurrentStateReason, JSON_VALUE(ddtr.details, '$.implementationDetails.script') AS ImplementationScript FROM sys.dm_db_tuning_recommendations AS ddtr CROSS APPLY OPENJSON(ddtr.details, '$.planForceDetails') WITH ( query_id INT '$.queryId', regressedPlanId INT '$.regressedPlanId', recommendedPlanId INT '$.recommendedPlanId' ) AS pfd ) WITH DbTuneRec AS ( SELECT ddtr.reason, ddtr.score, pfd.query_id, pfd.regressedPlanId, pfd.recommendedPlanId, JSON_VALUE(ddtr.state, '$.currentValue') AS CurrentState, JSON_VALUE(ddtr.state, '$.reason') AS CurrentStateReason, JSON_VALUE(ddtr.details, '$.implementationDetails.script') AS ImplementationScript FROM sys.dm_db_tuning_recommendations AS ddtr CROSS APPLY OPENJSON(ddtr.details, '$.planForceDetails') WITH ( query_id INT '$.queryId', regressedPlanId INT '$.regressedPlanId', recommendedPlanId INT '$.recommendedPlanId' ) AS pfd ) همچنین با استفاده از اسکریپت های زیر می توانید این قابلیت را با استفاده از دستورات T-sql فعال یا عیر فعال کنید: ALTER DATABASE AdventureWorks2019 SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON); ALTER DATABASE AdventureWorks2019 SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF); این قابلیت که با دستورات T-sql بتوانیم این کار را انجام دهیم از نسخه ۲۰۱۷ به بعد شاهد هستیم. پیشنهاد کتاب بدین شکل هست که اجازه دهید این قابلیت بر روی دیتابیس های شما فعال باشد که فرایند بهینه سازی خودکار انجام شود. همچنین جهت تست مجدد بر روی این قابلیت می توانید از اسکریپت زیر استفاده کنید: ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; GO ALTER DATABASE AdventureWorks2019 SET QUERY_STORE CLEAR; GO EXEC dbo.ProductByCost @ActualCost = 8.2205; GO 30 DECLARE @PlanHandle VARBINARY(64); SELECT @PlanHandle = deps.plan_handle FROM sys.dm_exec_procedure_stats AS deps WHERE deps.object_id = OBJECT_ID('dbo.ProductByCost'); IF @PlanHandle IS NOT NULL BEGIN DBCC FREEPROCCACHE(@PlanHandle); END GO EXEC dbo.ProductByCost @ActualCost = 0.0; GO EXEC dbo.ProductByCost @ActualCost = 8.2205; GO 15 در این قسمت ، اسکریپت هایی که در زمینه Query Store و جداول مرتبط می توانید استفاده کنید را معرفی خواهیم کرد که انعطاف گزارش سازی های مرتبط با این موضوع را بالاتر ببرید. با توجه به کلیه بخش هایی که به تفکیک در مقالات قبلی به ان اشاره شد می توانید از طریق کوئری زیر امار مرتبط با پلن های اجرایی، متن کوئری ها و اطلاعات زمان اجرا را علاوه بر داشبورد های تحلیلی Query Store به تفکیک داشته باشید. گاهی نیاز هست که تحلیل ها در سایر نرم افزار ها ها انجام شود . به همین خاطر اسکریپت زیر به شما کمک خواهد کرد که اطلاعات زمان اجرا را بصری سازی کنید. DECLARE @HoursBack smallint = 8; DECLARE @StartDate datetime2 = DATEADD(hour, -@HoursBack, GETUTCDATE()); WITH QueryRuntimeStats AS ( SELECT p.plan_id ,q.query_id ,q.query_hash ,SUM(rs.count_executions) AS total_executions ,SUM(rs.count_executions * rs.avg_duration) / 1000 AS total_duration_ms ,SUM(rs.count_executions * rs.avg_cpu_time) / 1000 AS total_cpu_ms ,SUM(rs.count_executions * rs.avg_clr_time) / 1000 AS total_clr_time_ms ,SUM(rs.count_executions * rs.avg_query_max_used_memory) AS total_query_max_used_memory ,SUM(rs.count_executions * rs.avg_logical_io_reads) AS total_logi_reads ,SUM(rs.count_executions * rs.avg_logical_io_writes) AS total_logi_writes ,SUM(rs.count_executions * rs.avg_physical_io_reads) AS total_phys_reads ,SUM(rs.count_executions * rs.avg_rowcount) AS total_rowcount ,SUM(rs.count_executions * rs.avg_log_bytes_used) AS total_log_bytes_used ,SUM(rs.count_executions * rs.avg_tempdb_space_used) AS total_tempdb_space_used from sys.query_store_plan p join sys.query_store_query q on q.query_id = p.query_id join sys.query_store_runtime_stats rs on rs.plan_id = p.plan_id join sys.query_store_runtime_stats_interval rsi on rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id where rsi.start_time > @StartDate group by p.plan_id ,q.query_id ,q.query_hash ) ,QueryWaitStats AS ( SELECT p.plan_id ,q.query_id ,q.query_hash ,ws.wait_category_desc ,SUM(ws.total_query_wait_time_ms) AS total_wait_time_ms FROM sys.query_store_plan p JOIN sys.query_store_query q ON q.query_id = p.query_id JOIN sys.query_store_wait_stats ws ON ws.plan_id = p.plan_id JOIN sys.query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = ws.runtime_stats_interval_id WHERE rsi.start_time > @StartDate GROUP BY p.plan_id ,q.query_id ,q.query_hash ,ws.wait_category_desc ) ,QueryWaitStatsByCategory AS ( SELECT * FROM QueryWaitStats PIVOT ( SUM(total_wait_time_ms) FOR wait_category_desc IN ( [Unknown] ,[CPU] ,[Worker Thread] ,[Lock] ,[Latch] ,[Buffer Latch] ,[Buffer IO] ,[Compilation] ,[SQL CLR] ,[Mirroring] ,[Transaction] ,[Idle] ,[Preemptive] ,[Service Broker] ,[Tran Log IO] ,[Network IO] ,[Parallelism] ,[Memory] ,[User Wait] ,[Tracing] ,[Full Text Search] ,[Other Disk IO] ,[Replication] ,[Log Rate Governor] ) ) AS pvt ) ,QueryWaitStatsTotals AS ( SELECT plan_id ,query_id ,query_hash ,SUM(total_wait_time_ms) AS total_wait_time_ms FROM QueryWaitStats GROUP BY plan_id ,query_id ,query_hash ) SELECT rs.plan_id , rs.query_id , rs.query_hash , rs.total_executions , rs.total_duration_ms , rs.total_cpu_ms , rs.total_clr_time_ms , rs.total_query_max_used_memory , rs.total_logi_reads , rs.total_logi_writes , rs.total_phys_reads , rs.total_rowcount , rs.total_log_bytes_used , rs.total_tempdb_space_used , ws.total_wait_time_ms , wsc.[CPU] , wsc.[Lock] , wsc.[Latch] , wsc.[Buffer Latch] , wsc.[Buffer IO] , wsc.[Memory] , wsc.[Tran Log IO] , wsc.[Network IO] , wsc.[Worker Thread] , wsc.[Unknown] , wsc.[Compilation] , wsc.[SQL CLR] , wsc.[Mirroring] , wsc.[Transaction] , wsc.[Idle] , wsc.[Preemptive] , wsc.[Service Broker] , wsc.[Parallelism] , wsc.[User Wait] , wsc.[Tracing] , wsc.[Full Text Search] , wsc.[Other Disk IO] , wsc.[Replication] , wsc.[Log Rate Governor] FROM QueryRuntimeStats rs LEFT JOIN QueryWaitStatsTotals AS ws ON rs.plan_id = ws.plan_id AND rs.query_id = ws.query_id LEFT JOIN QueryWaitStatsByCategory AS wsc ON rs.plan_id = wsc.plan_id AND rs.query_id = wsc.query_id با استفاده از اسکریپت پایین می توانید کلیه امار ثبت شده در Query Store را مشاهده کنید .با توجه به حجم رکورد هایی که ثبت شده است ممکن است این کوئری زمان زیادی را برای ا جرا نیاز داشته باشد. به طور معمول در صورتی که قصد دارید Baseline سفارشی برای خود درست کنید می توانید از این کوئری استفاده کنید: SELECT * FROM sys.query_store_query LEFT JOIN sys.query_store_query_text ON query_store_query.query_text_id = query_store_query_text.query_text_id LEFT JOIN sys.query_store_plan ON query_store_query.query_id = query_store_plan.query_id LEFT JOIN sys.query_store_runtime_stats ON query_store_plan.plan_id = query_store_runtime_stats.plan_id LEFT JOIN sys.query_store_runtime_stats_interval ON query_store_runtime_stats.runtime_stats_interval_id = query_store_runtime_stats_interval.runtime_stats_interval_id با استفاده از اسکریپت زیر می توانیم در یک بازه زمانی مورد نظر امار مرتبط با زمان اجرای هر کوئری را مشاهده کنیم. در صورتی که قصد داشتید از رویه های سفارشی استفاده کنید می توانید از کوئری زیر استفاده کنید . همان طور که مشاهده کردید در داشبورد های تحلیلی Query Store نیز با فیلتر کردن بر روی هر بازه ایی که مد نظر داشتیم ، اطلاعات فوق را به صورت کلی مشاهده می کردیم . همین کار را نیز می توانید به روش دیگری انجام دهید : SELECT TOP 50 query_store_query_text.query_sql_text, CAST(query_store_plan.query_plan AS XML) AS query_plan_xml, query_store_runtime_stats.first_execution_time, query_store_runtime_stats.last_execution_time, query_store_runtime_stats.count_executions, query_store_runtime_stats.avg_duration AS avg_duration_microseconds, query_store_runtime_stats.last_duration AS last_duration_microseconds, query_store_runtime_stats.avg_cpu_time AS avg_cpu_time_microseconds, query_store_runtime_stats.last_cpu_time AS last_cpu_time_microseconds, query_store_runtime_stats.avg_logical_io_reads, query_store_runtime_stats.last_logical_io_reads, query_store_runtime_stats.avg_query_max_used_memory AS avg_query_max_used_memory_8k_pages, query_store_runtime_stats.last_query_max_used_memory AS last_query_max_used_memory_8k_pages, query_store_runtime_stats.avg_rowcount, query_store_runtime_stats.last_rowcount, query_store_runtime_stats_interval.start_time AS interval_start_time, query_store_runtime_stats_interval.end_time AS interval_end_time, query_store_query.query_id, query_store_query_text.query_text_id, query_store_plan.plan_id FROM sys.query_store_query LEFT JOIN sys.query_store_query_text ON query_store_query.query_text_id = query_store_query_text.query_text_id LEFT JOIN sys.query_store_plan ON query_store_query.query_id = query_store_plan.query_id LEFT JOIN sys.query_store_runtime_stats ON query_store_plan.plan_id = query_store_runtime_stats.plan_id LEFT JOIN sys.query_store_runtime_stats_interval ON query_store_runtime_stats.runtime_stats_interval_id = query_store_runtime_stats_interval.runtime_stats_interval_id WHERE query_store_runtime_stats_interval.start_time BETWEEN ‘۱۱/۱۷/۲۰۱۵ ۲۲:۰۰:۰۰’ AND ‘۱۱/۱۷/۲۰۱۵ ۲۳:۰۰:۰۰' ORDER BY query_store_runtime_stats.avg_cpu_time DESC ORDER BY query_store_runtime_stats.avg_duration DESC ORDER BY query_store_runtime_stats.count_executions DESC ORDER BY query_store_runtime_stats.avg_logical_io_reads DESC ORDER BY query_store_runtime_stats.avg_rowcount DESC با استفاده از کوئری زیر می توانید وضعیت یک جدول خاص را مورد بررسی قرار دهید. همچنین زمان اجرا بسته به بازه ایی که در نظر دارید را می توانید مشخص کنید. از این کوئری نیز میتوانید به عنوان یک رویه ایی که صرفا با یک متن خاص اجرا می شود به خوبی استفاده کنید: SELECT TOP 50 query_store_query.query_id, query_store_query_text.query_sql_text, CAST(query_store_plan.query_plan AS XML) AS query_plan_xml, query_store_runtime_stats.first_execution_time, query_store_runtime_stats.last_execution_time, query_store_runtime_stats.count_executions, query_store_runtime_stats.avg_duration AS avg_duration_microseconds, query_store_runtime_stats.last_duration AS last_duration_microseconds, query_store_runtime_stats.avg_cpu_time AS avg_cpu_time_microseconds, query_store_runtime_stats.last_cpu_time AS last_cpu_time_microseconds, query_store_runtime_stats.avg_logical_io_reads, query_store_runtime_stats.last_logical_io_reads, query_store_runtime_stats.avg_query_max_used_memory AS avg_query_max_used_memory_8k_pages, query_store_runtime_stats.last_query_max_used_memory AS last_query_max_used_memory_8k_pages, query_store_runtime_stats.avg_rowcount, query_store_runtime_stats.last_rowcount FROM sys.query_store_query LEFT JOIN sys.query_store_query_text ON query_store_query.query_text_id = query_store_query_text.query_text_id LEFT JOIN sys.query_store_plan ON query_store_query.query_id = query_store_plan.query_id LEFT JOIN sys.query_store_runtime_stats ON query_store_plan.plan_id = query_store_runtime_stats.plan_id LEFT JOIN sys.query_store_runtime_stats_interval ON query_store_runtime_stats.runtime_stats_interval_id = query_store_runtime_stats_interval.runtime_stats_interval_id WHERE query_store_runtime_stats_interval.start_time BETWEEN ‘۱۱/۱۷/۲۰۱۵ ۲۲:۰۰:۰۰’ AND ‘۱۱/۱۸/۲۰۱۵ ۲۲:۰۰:۰۰' AND query_store_query_text.query_sql_text LIKE ‘%SELECT * FROM Person.Person%’ ORDER BY query_store_runtime_stats.last_execution_time DESC با استفاده از اسکریپت زیر می توانید ، کوئری هایی که از یک ایندکس خاص استفاده می کنند را به راحتی شناسایی کنید . در بعضی از سناریو ها و دیتابیس های عملیاتی شاهد هستیم که به دلایلی ، در سمت کد برنامه، ایندکس هایی استفاده شده است که حتما باید پلن های اجرایی باید مطابق با ان اجرا شود . لذا این روش به شما این امکان را می دهد که در صورتی که نیاز داشتید ایندکس های مختلف را جهت این موضوع یا دلایل دیگر مورد بررسی قرار دهید، به شکل زیر عمل کنید: SELECT TOP 50 query_store_query.query_id, query_store_query_text.query_sql_text, CAST(query_store_plan.query_plan AS XML) AS query_plan_xml, query_store_runtime_stats.avg_logical_io_reads FROM sys.query_store_query LEFT JOIN sys.query_store_query_text ON query_store_query.query_text_id = query_store_query_text.query_text_id LEFT JOIN sys.query_store_plan ON query_store_query.query_id = query_store_plan.query_id LEFT JOIN sys.query_store_runtime_stats ON query_store_plan.plan_id = query_store_runtime_stats.plan_id LEFT JOIN sys.query_store_runtime_stats_interval ON query_store_runtime_stats.runtime_stats_interval_id = query_store_runtime_stats_interval.runtime_stats_interval_id WHERE query_store_runtime_stats_interval.start_time BETWEEN '11/17/2015 22:00:00' AND '11/18/2015 22:00:00' AND query_store_plan.query_plan LIKE '%IX_Person_LastName_FirstName_MiddleName%' ORDER BY query_store_runtime_stats.avg_logical_io_reads DESC با استفاده از اسکریپت زیر می توانیم پلن های اجرایی که مشکل مغایرت در نوع دیتا تایپ ها را دارند را به راحتی شناسایی و استخراج کنیم . عبارت convert_implicit در این قسمت نشان دهنده همین مشکل هست که در قسمت های قبلی در این خصوص توضیحات لازم ارایه شد که ممکن است پیغام هایی بر روی پلن هایی اجرایی دریافت کنیم که عملکرد پلن ها دستخوش تغییر شود . لذا این موارد را می توانیم به این روش شناسایی و برطرف کرد SELECT TOP 50 query_store_query.query_id, query_store_query_text.query_sql_text, query_store_plan.query_plan AS query_plan_text, CAST(query_store_plan.query_plan AS XML) AS query_plan_xml, query_store_runtime_stats.last_execution_time FROM sys.query_store_query LEFT JOIN sys.query_store_query_text ON query_store_query.query_text_id = query_store_query_text.query_text_id LEFT JOIN sys.query_store_plan ON query_store_query.query_id = query_store_plan.query_id LEFT JOIN sys.query_store_runtime_stats ON query_store_plan.plan_id = query_store_runtime_stats.plan_id LEFT JOIN sys.query_store_runtime_stats_interval ON query_store_runtime_stats.runtime_stats_interval_id = query_store_runtime_stats_interval.runtime_stats_interval_id WHERE query_store_runtime_stats_interval.start_time BETWEEN '11/17/2015 22:00:00' AND '11/18/2015 22:00:00' AND query_store_plan.query_plan LIKE '%convert_implicit%' ORDER BY query_store_runtime_stats.last_execution_time DESC با استفاده از اسکریپت زیر نیز می توانیم کلیه پلن هایی اجرایی که برای یک کوئری ایجاد شده است را مشاهده کنیم. با توجه به گرافی که Query store در اختیار ما قرار میداد و همچنین اماری که از پلن های اجرایی دریافت می کردیم، تصمیم میگرفتیم که بر چه اساسی یک پلن انتخاب شود که بتوانیم کلیه کوئری ها را از طریق ان اجرا کنیم و حتما اجبار به استفاده کنیم . لذا این امار را می توانید از طریق اسکریپت زیر به دست آورید: WITH Query_MultPlans AS ( SELECT COUNT(*) AS cnt, q.query_id FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON p.query_id = q.query_id GROUP BY q.query_id HAVING COUNT(distinct plan_id) > 1 ) SELECT q.query_id, object_name(object_id) AS ContainingObject, query_sql_text, plan_id, p.query_plan AS plan_xml, p.last_compile_start_time, p.last_execution_time FROM Query_MultPlans AS qm JOIN sys.query_store_query AS q ON qm.query_id = q.query_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id ORDER BY query_id, plan_id; همچنین اسکریپت دیگری برای این کار وجود دارد که اقای برنت اوزار در سایت خود به شکل پایین معرفی کرده است و می توانید اطلاعاتی خروجی قبلی را در این اسکریپت نیز مشاهده کنید: جمع بندی در این مقاله سعی کردیم که قسمت های کاربردی از پلن های اجرایی را در که بخشی از ان در مقاله قبلی معرفی شده بود را خدمت شما عزیزان ارایه دهیم . همچنین اسکریپت هایی معرفی شد که می توانید از طریق ان امار پلن ها و کارکرد کوئری ها و امار مرتبط با وضعیت اجرای آنها را نیز خارج از داشبوردها به دست اوردیم. در فصل اخر این کتاب دو ابزار DBA Tools و SpBlitz معرفی شده است. ابزار اول به صورت دستورات پاورشل قابل استفاده است و می توانید دستورات را در ان محیط استفاده کنید. ابزار دوم نیز تحت عنوان پکیجی به اسم SQL-Server-First-Responder-Kit معرفی شده است که می توانید امار جمع اوری شده توسط Query Store را از طریق اسکریپت های آن تحلیل کنید . در واقع SQL-Server-First-Responder-Kit مجموعه ایی از رویه های مختلفی هست که در ر رویه مجموعه ایی از پارامتر های کاربردی را مشاهده خواهید کرد. به عنوان مثال sp_BlitzQueryStore.sql را زمانی که بر روی دیتابیس Master نصب می کنید قابلیت تحلیل بر روی اطلاعات جمع اوری شده را به راحتی خواهید داشت. حتما جزییات مرتبط با این Package را از سایت گیت هاب مطالعه بفرمایید تا با قابلیت های بی نظیری که به شما ارایه می دهد به خوبی آشنا شوید. منتظر نظرات شما عزیزان در جهت بهبود کیفیت و کمیت مقالات اموزشی هستیم. لیست منابع https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-remove-query-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-flush-db-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/query-store-stored-procedures-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints?view=azuresqldb-current&viewFallbackFrom=sql-server-ver15 https://www.red-gate.com/simple-talk/author/enrico-van-de-laar/ https://www.red-gate.com/hub/university/courses/sql-server-query-performance-tuning/built-in-sql-server-tools-make-query-tuning-easier/built-in-sql-server-tools-make-query-tuning-easier/week1 چه رتبه ای میدهید؟ میانگین ۴ / ۵. از مجموع ۱ اولین نفر باش دانلود مقاله اشنایی با Query Store | بخش ششم فرمت PDF 26 صفحه حجم 3 مگابایت دانلود مقاله معرفی نویسنده مقالات 366 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تیم فنی نیک آموز معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ