راهکار اندازه‌گیری بهبودهای عملکرد پرس و جوی SQL Server

راهکار اندازه‌گیری بهبودهای عملکرد پرس و جوی SQL Server

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

جمعه ۱ بهمن ۱۳۹۵ | | 137 بازدید

راهکار اندازه‌گیری بهبودهای عملکرد پرس و جوی SQL Server

به عنوان بخشی از پروژه بهینه سازی عملکرد برای یک از مشتریانم، چندین شاخص را تغییر دادم و می‌خواستم از این تغییرات به بهبودهایی در عملکرد برسم. مشگل این بود که هر شاخص توسط تعدادی پرس و جوی متفاوت مورد استفاده قرار می‌گرفت و شناسایی آن پرس و جوها و همچنین اندازه‌گیری معیارهای اصلی عملکرد آنها قبل و یا بعد از تغییرات شاخص، مستلزم کارهای دستی زیادی می‌شد. همانطور که احتمالاً می‌دانید، ویژگی SQL 2016 Query Store این کار را بسیار ساده‌تر کرده است، بهرحال اگر نسخه SQL Server ما قبل از ۲۰۱۶ باشد چه می‌کنیم؟ در این مقاله به شما نشان می‌دهیم که چگونه می‌توانید این کار را با استفاده از گزارشات و ابزارهای سفارشی جمع‌آوری عملکرد انجام دهید.
راهکار
بازبینی و استفاده از سناریوهای موردی
سناریوهای مختلفی وجود دارند که مستلزم دسترسی به تغییرات عملکرد پرس وجوی SQL Server هستند مثل:
• من شاخص‌های موجودم را تنظیم کردم و می‌خواهم میزان بهبود را ببینم.
• من برخی شاخص‌ها را که همپوشانی داشتند حذف کردم و می‌خواهم تاثیر عملکرد آن تغییرات را اندازه گیری کنم.
• می‌خواهم روندهای اجرایی را شناسایی کنم و بفهمم کدام پرس وجوها بهبود عملکرد و یا تنزل عملکرد داشته‌اند.
برای یافتن پاسخ‌های قطعی به این سناریوها، باید کارهای زیر را انجام دهید:
شناسایی پرس وجوهای SQL Server با استناد به جدول‌های تغییریافته
اگر پایگاه اطلاعاتی شما تنها شامل چند جدول و پرس وجو باشد،احتمالاً می‌توانید به راحتی به این سوال پاسخ دهید، اما اگر جدول‌هایی دارید که توسط ۱۲ ویو، رویه و پرس و جوی ad-hoc مورد مراجعه قرار می‌گیرند، امکان چالش وجود دارد. شما می‌توانید این اطلاعات را از طرح‌های پرس و جوی ذخیره شده در کش SQL بدست بیاورید، بهرحال از آنجایی که این کش شامل آخرین نسخه کامپایل شده هر طرح اجرایی است، آمار اجرایی شما کامل نخواهد بود. محدودیت دیگر این روش آن است که شامل اجرای پرس وجوهای تجزیه کننده XML با مصرف بالای منابع جهت استخراج نام‌های جدول از طرح‌های اجرایی است که ممکن است عملکرد سرورهای تولید را تحت تاثیر قرار دهد.
شناسایی معیارهای اجرایی SQL Server که مورد مقایسه قرار می‌گیرند
شما می‌توانید از معیارهای زیربرای اندازه‌‌گیری بهبودها استفاده نمایید:
• Query execution cost
• Total, Max, Min or Avg query elapsed time
• Total, Max, Min or Avg query worker (CPU) time
• Total, Max, Min or Avg logical reads or writes

روش شما بستگی به اهداف بهینه سازی پرس وجوی شما دارد. اگرهدف شما بهبود عملکرد کلی پرس و جو است، پیشنهاد می‌کنیم از query execution cost به عنوان معیار مقایسه استفاده کنید، چون شامل اندازه‌گیری مختلط حافظه، مصرف منابع دیسک سخت و CPU است. از سوی دیگر، اگر به کاهش طول مدت پرس و جوها علاقمندید، پرس و جوی معیارهای مرتبط با زمان تلف شده بسیار مناسب تر خواهد بود. در نهایت، اگر بدنبال حل مشکلات منابع برای حافظه ، CPU یا دیسک سخت هستید، معیارهای worker time یا logical reads or writes بهترین خواهند بود.

شناسایی ابزار برای مقایسه
شما می‌توانید برای مقایسه ساده از Excel استفاده کنید، بهرحال آنهم مسلتزم کارهای دستی زیاد برای ساختن فرمول‌های مناسب مقایسه است. روش دیگر استفاده از گزارشات SSRS خواهد بود که مستلزم کار توسعه‌ای است.
راهکاری که در ادامه شرح می‌دهیم به شما امکان می‌دهد تا آیتم‌های بالا را مخاطب قرار دهید.
چگونه از ذخیره سفارشی پرس وجو برای اندازه‌گیری بهبودهای پرس و جو استفاده کنیم؟
ابزاری وجود دارد به نام ابزار سفارشی جمع آوری پرس وجو. این ابزار به شما امکان می‌دهد تا آمارهای اجرای پرس و جو را از چندین سرور تولید جمع آوری کرده و آن‌ها را در یک مخزن مرکزی ذخیره نموده و بر اساس آن مخزن گزارشات اختصاصی تهیه کنید.
مزایا این روش:
۱- این راهکار به شما امکان ذخیره کردن چندین طرح اجرایی و آمار عملکرد آن‌ها را می‌دهد که در عوض می‌توانید تغییرات عملکرد را برای پرس و جوهای انتخابی ردیابی نمایید.
۲- آمار عملکرد جمع‌آوری شده از سرورهای تولید در سرور مرکزی کپی می‌شوند، جایی که می‌توانید پرس وجوهای تجزیه کننده XML با مصرف منابع بالا را اجرا کنید تا اطلاعات مهم ذخیره شده در طرح‌های اجرایی را استخراج نمایید بدون آن که تاثیری روی سرورهای تولید شما داشته باشد.
۳- فرآیند جمع‌آوری کاملاً خودکار است و دارای حداقل تاثیر بر روی سرورهای تولید می‌باشد.
۴- شما می‌توانید مجموعه‌ای از گزارشات /داشبوردها را برای تحلیل عملکرد مربوط به پرس و جوهای برآمده از چندین سرور را بسازید.
ما مجموعه‌ای از گزارشات SSRS را ساختیم، آن هم مبتنی بر راهکاری که در بالا توضیح دادیم، که کار مقایسه عملکرد را بسیار آسان‌تر کرد.

گزارشات مقایسه عملکرد
مجموعه گزارشات مقایسه عملکرد شامل دو گزارش است- یکی گزارش اصلی (Performance Stats Analysis) و یک گزارش فرزند (Performance Stats Analysis-details) که می‌تواند تنها از گزارش اصلی قابل دسترس باشد. یک تصویر ساده از گزارش اصلی:

این گزارش شامل پارامترهای ورودی زیر است:
• SQL Instance name – نام SQL server که پرس‌جوها از آن سرچشمه می‌گیرند
• Database name: نام پایگاه اطلاعاتی که پرس و جوها از آن سرچشمه می‌گیرد
• Table name : برای فیلتر کردن پرس وجوها بر اساس جدول مشخص شده
• Min Subtree Cost : از این فیلتر می‌توان برای شناسایی پرس وجوهای پرهزینه با هزینه بیش از مقدار مشخص شده استفاده نمود.
• Min # of execution plans : از این فیلتر می‌تواند برای شناسایی پرس وجوهای که دارای چندین طرح اجرایی هستند، استفاده کرد. ممکن است بخواهید از مقدار بالای صفر برای این پارامتر استفاده کنید تا لیست پرس وجوهایی که طرح‌های اجرایی خود را تغییر داده‌اند را بدست بیاورید.
وقتی لیست پرس و جوها را براساس جدول اصلی بدست آورید، می‌توانید به سراغ آمار اجرایی سطح بالای هر پرس و جو بروید همانطور که نشان داده شده است:

این ویو به شما امکان می‌دهد تا تعداد طرح‌های اجرایی جمع‌آوری شده برای این پرس وجو ( هر طرح با یک رنگ متفاوت نشان داده شده است) و همچنین عملکرد مربوط به آن‌ها را ببینید. در این مثال بخصوص، یک نگاه اجمالی به این ویو نشان می‌دهد که دومین طرح اجرایی دارای عملکرد بسیار بهتری است. ما معیارهای اجرایی زیر را به این ویو افزوده‌ایم:
• Query execution cost
• Max, Avg query elapsed time
• Max, Avg query worker (CPU) time
• Max, Avg logical reads

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

شما می‌توانید محتوای XML هر طرح اجرایی را با گسترده کردن ستون PlanHash ببینید. به این صورت:

استقرار گزارش
می‌توانید مراحل زیر را برای پیکربندی منابع داده‌های مربوط به گزارشات دنبال کنید. این فرآیند شامل جمع‌آوری داده‌ها در هر سرور تولید و سپس کپی کردن داده‌ها در یک سرور مرکزی است که کار تحلیل و گزارش گیری در آنجا انجام می‌شود.
۱- اسکریپت زیر را در هر سرور تولید جایی که می‌خواهید داده‌ها را جمع‌آوری کنید، اجرا نمایید. این اسکریپت آمار سطر جدول را جمع‌آوری می‌کند.
USE [PerfStats]
GO

CREATE TABLE [dbo].[TableRowStats](
[RecID] [int] IDENTITY(1,1) NOT NULL,
[SQLInstanceName] [varchar](200) NULL,
[DbName] [varchar](100) NULL,
[TableName] [varchar](100) NULL,
[ObjId] [bigint] NULL,
[IndId] [int] NULL,
[IndName] [varchar](200) NULL,
[RowCnt] [bigint] NULL,
[DateInserted] [datetime] NULL CONSTRAINT [DF_IndexRowStats_DateInserted] DEFAULT (getdate()),
CONSTRAINT [PK_IndexRowStats] PRIMARY KEY CLUSTERED ([RecID] ASC))
GO

CREATE PROCEDURE [dbo].[GetRowCounts]
@MinRows int=1,
@vDbName varchar(200)
AS
IF EXISTS (Select 1 FROM tempdb.sys.objects where name=’##IndList’)
DROP TABLE ##IndList
Declare @SqlStr varchar(max)=N’
SELECT SO.Name as TableName,SI.id as ObjId,indid,SI.name as IndName ,rowcnt
INTO ##IndList
FROM ‘+@vDbName +’.sys.sysindexes (nolock) SI
JOIN ‘+@vDbName +’.sys.sysobjects (nolock) SO ON SI.id=So.id
WHERE SO.TYPE =”U” AND Indid <2 and rowcnt >‘+RTRIM(STR(@MinRows))
EXEC (@SqlStr)
MERGE TableRowStats RS
USING ##IndList I ON RS.SQLInstanceName=@@SERVERNAME AND RS.DbName=@vDbName
AND RS.ObjId=I.ObjId AND RS.IndId=I.Indid
WHEN MATCHED THEN
UPDATE Set RS.RowCnt=I.RowCnt
WHEN NOT MATCHED THEN
INSERT (SQLInstanceName,DbName,TableName,ObjId,IndId,IndName,RowCnt)
VALUES(@@SERVERNAME,@vDbName,I.TableName,I.ObjId,I.IndId,I.IndName,I.RowCnt);
GO;

/*
CollectPerfStats – shell procedure to trigger performance collection procedures
Author:Fikrat Azizov
Date:April, 2016
*/
ALTER PROCEDURE [dbo].[CollectPerfStats]
@DbList varchar(200),
@GetQueryStats bit=1,
@TopN int,
@OrderBy varchar(200) =’TotalElapsedTime_MSec’,
@FilterStr varchar(max) =NULL,
@CollectStatsInfo bit=0,
@GetWaitStats bit=0
AS
IF @GetQueryStats=1
EXEC [CollectQueryStats] @vTopNRows=@TopN,@vDbList=@DbList,@vOrderBy=@OrderBy,@vFilterStr=@FilterStr,@vCollectStatsInfo=@CollectStatsInfo;
IF @GetWaitStats=1 exec GetWaitStats 10,5;
EXEC [dbo].[GetRowCounts] 1,@DbList;
GO

۲- جدول جدید TableRowStats را به یک publication بیافزایید تا امکان کپی داده‌ها در سرور مرکزی فراهم گردد. باید این کار را برای هر سروری که داده‌ها را در آن جمع‌آوری می‌کنید، انجام دهید.
۳- در سرور مرکزی، اسکریپت زیر را اجرا کنید تا یک رویه ذخیره شده بسازید که همانند یک منبع داده‌ها برای گزارشات جدید عمل می‌کند:
USE [PerfStats]
GO

CREATE PROCEDURE GetQueryStatsByDBTable
@DbName varchar(200),
@TableName varchar(200),
@MinSubtreeCost decimal (10,2)=0,
@MinPlans int=1
AS
DECLARE @vTableName AS NVARCHAR(200) =QUOTENAME(@TableName)
;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’)
,QueryStatsCTE As (
SELECT [SQLInstanceName]
,[DbName]
,Left([ParentQueryTxt],200) as ParentQueryTxt
,LEFT([QueryTxt],200) as QueryTxt
,[TotalElapsedTime_Msec]
,[MaxElapsedTime_Msec]
,[MinElapsedTime_Msec]
,[Avg_elapsedTime_Msec]
,[TotalWorkerTime_Msec]
,[MaxWorkerTime_Msec]
,[MinWorkerTime_Msec]
,[Avg_WorkerTime_Msec]
,[TotalLogicalReads]
,[MaxLogicalReads]
,[MinLogicalReads]
,[Avg_Logical_Reads]
,[ExecutionCount]
,[QueryHash]
,[PlanHash]
,cast(CollectionDateTime as date) as CollectionDateTime
,stmt.value(‘(@StatementSubTreeCost)[1]’, ‘decimal(10,2)’) AS SubtreeCost
FROM [dbo].[vQueryStats_Plans] (NOLOCK) cp
CROSS APPLY cp.queryplan.nodes(‘//ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[@QueryHash=sql:column(“QueryHash”)]’) AS batch(stmt)
CROSS APPLY stmt.nodes(‘//Object[@Table=sql:variable(“@vTableName”)]’) AS vObj(obj)
WHERE DbName=@DbName AND QueryHash IN (Select QueryHash FROM [dbo].vQueryStats_Plans
GROUP BY QueryHash HAVING COUNT(1)>=@MinPlans)
)
SELECT * from QueryStatsCTE QS Where SubtreeCost >=@MinSubtreeCost ORDER BY SubtreeCost Desc ;
GO

۴- در سرور مرکزی اسکریپت زیر را اجرا کنید تا شاخص‌های XML را در جدول QueryPlanفعال نمایید.
USE [PerfStats]
GO

ALTER INDEX [PXML_QueryPlans] ON [dbo].[QueryPlans] REBUILD
ALTER INDEX [IXML_QueryPlans_Path] ON [dbo].[QueryPlans] REBUILD
ALTER INDEX [IXML_QueryPlans_Value] ON [dbo].[QueryPlans] REBUILD
GO

لطفا زمان‌هایی که این شاخص‌ها موجب ارتقاء عملکرد گزارشاتتان می‌شود را یادداشت کنید؛ فعال کردن آن‌ها در سرور مرکزی موجب می‌گردد که در سرورهای تولید فعال نشوند.
۵- در سرور گزارش سرور مرکزی، فولدرهای Data Source و Reports را بسازید همانطور که در شکل می‌بینید:

۶- در سرور مرکزی، منبع داده‌های PerfStatsDS را در داخل فولدر Data Sources با تنظیمات زیر بسازید:

۷- پکیج زیپ شده گزارشات را دانلود کنید. آن را unzip کنید و گزارشات ‘Performance stats analysis.rdl’ و ‘Performance stats analysis-details.rdl’ را در فولدر Reports آپلود نمایید.
کادر انتخابی Hide in tile view را برای گزارش ‘Performance stats analysis-details تیک بزنید تا آن را از کاربران پنهان نمایید. این گزارش باید تنها از طریق گزارش اصلی (Performance stats analysis) در دسترس قرار بگیرد.

استفاده از گزارشات سفارشی برای اندازه گیری تغییرات عملکرد
برای اندازه‌گیری تغییرات عملکرد باید برنامه CollectQueryPerfStats را در سرور تولید خود بعد از اجرای پرس و جو یا گزارشاتی که می‌خواهید تجزیه تحلیل شوند، اجرا نمایید. تغییرات اساسی را در پرس و جوها و یا گزارشات انجام دهید و همین برنامه را دوباره اجرا نمایید تا طرح‌های اجرایی جدید جمع‌آوری شوند. از کپی شدن داده‌ها در سرور مرکزی مطمئن شوید و سپس گزارشات مقایسه را برای اندازه‌گیری بهبودها اجرا نمایید.
نتیجه
تنظیم عملکرد یک فرایند خلاق است که مستلزم توجه و کار دستی زیاد می‌باشد. بهرحال، با ابزارهای درست و مناسب می‌توانید کارهای دستی مربوط به اندازه‌گیری‌ نتایج را کم کنید و روی فرایند تنظیم تمرکز نمایید.

دیدگاه خود را بیان کنید