ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده

Database Engine Tuning Advisor یکی از ابزارهای ضروری برای مدیریت پایگاه داده و نظارت درست روی آن است که در این مطلب با تمامی نکات مربوط به آن آشنا خواهیم شد. این ویژگی SQL Server کمک خواهد کرد تا با دید بهتری بتوان نحوه اجرای کوئری‌ها را در دیتابیس تحلیل کرد. با وجود این تحلیل‌ها، مدیران پایگاه داده می‌توانند پردازش و عملکرد کوئری‌ها را بهبود بخشند و در شرایط مختلف روی بخش‌های دیتابیس و جداول بهینه سازی‌هایی را اعمال کنند. 

Database Engine Tuning Advisor چیست؟‌‌‌

اگر تا به حال با هر یک از دیتابیس‌ها کار کرده باشید، مطمئنا می‌دانید که ارزیابی و آنالیز بخش‌های مختلف و در اختیار داشتن تحلیل‌های عملکردی و بهینه سازی از اهمیت بالایی برخوردار است. شاید در دیتابیس‌های کوچک این ارزیابی‌ها زیاد هم مهم نباشند چرا که دیتای عظیمی قرار نیست توسط پایگاه داده SQL Server ذخیره‌سازی، تحلیل و حتی فراخوانی شود. با این حال، در دیتابیس‌های بزرگ که شاید بیشتر در فروشگاه‌های اینترنتی و سازمان‌ها کاربرد دارند، اهمیت زیادی دارد تا به ابزارهایی مانند Microsoft Database Engine Tuning Advisor دسترسی داشته باشیم. 

کار اصلی این مدل ابزارها تحلیل و ارزیابی عملکرد است. با این حال، قابلیت‌های اضافی نیز در دنیای امروزی به همراه DETA ارائه شده است که با نصب آن به تمامی این ویژگی‌های مهم دسترسی خواهید داشت. در بخش‌های بعد با این قابلیت‌های ارزشمند و ضروری آشنا خواهیم شد. 

اهمیت و کاربرد های Database Engine Tuning Advisor در مدیریت پایگاه داده

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

پس از اجرای SSMS می‌توانید طبق تصویر زیر به دیتابیس مربوطه متصل شوید:

کاربرد این ابزارها که Database Engine Tuning Advisor نیز یکی از بهترین آن‌ها است، در موارد زیر است:

بهینه سازی عملکرد دیتابیس

بهینه‌سازی عملکرد کوئری‌ها بدون داشتن درک درست از ساختار دیتابیس و مدل جدول‌ها کاری بسیار دشوار است. کاربرد اصلی DETA همین‌جا مشخص می‌شود. این ابزار، قدرت تحلیل کش کوئری‌ها و بار کاری جاری را دارد تا بر اساس آن پیشنهاداتی را به مدیر دیتابیس ارائه دهد. 

ارزیابی قبل از تغییرات

یک مزیت مهم DETA قدرت ارزیابی عملکرد تغییرات، قبل اعمال نهایی است. به این صورت که شما یک تحلیل عملکرد به دست خواهید آورد و با استفاده از آن بهتر می‌توانید در مورد تغییر یا modify کردن دیتابیس تصمیم گیری کنید.  

مستندسازی و گزارش گیری

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

در کل، همه چیز در این بخش دقیق و جزئی ارائه شده است تا کاربر با خیالی آسوده به هر آن چیزی که نیاز است، دسترسی داشته باشد. 

در بخش گزارش‌ها به صورت جزئی به دیتای موردنیاز دسترسی دارید:

با انتخاب هر یک از گزارش‌ها از بخش select report به اطلاعات بیشتری در مورد توصیه‌های DETA دسترسی خواهید داشت:

نحوه عملکرد Database Engine Tuning Advisor

بهتر است در همین ابتدا اشاره کنیم، Database Engine Tuning Advisor یک ابزار اضافی است که روی بسته SSMS مایکروسافت ارائه شده است. SSMS برای مدیریت و نظارت بهتر روی دیتابیس SQL Server در اختیار کاربران قرار گرفته است و عملکرد آن به صورتی است که تحت یک رابط گرافیکی سرعت عمل و بهینه‌سازی بالایی را برای استفاده از پایگاه داده در اختیار کاربر قرار می‌دهد. 

DETA روی کوئری‌ها عمل می‌کند و پس از اجرای هر کوئری یا دسته‌های مختلف کد، می‌توانید آنالیز آن را نیز با این ابزار انجام دهید. 

با اجرای کد زیر می‌‌توان یک جدول جدید با نام Employee در دیتابیس test-db ایجاد کرد. با این کار، تنها کافی است تا داخل کوئری مورد نظر راست کلیک کرده و آنالیز DETA را انتخاب کنید تا اطلاعات مورد نیاز در مورد آنالیز آن در اختیارتان قرار بگیرد. در صورت وجود پیشنهادهای بهینه‌سازی، DETA آن‌ها را در اختیار شما قرار می‌دهد. 

USE [test-db]
GO
DROP TABLE IF EXISTS HR.dbo.Employee; --drop table if already exists
CREATE TABLE Employee
(  
    ID int IDENTITY(1,1) PRIMARY KEY,
    FName nvarchar(50) NOT NULL,  
    LName nvarchar(50) NOT NULL, 
    Email nvarchar(50),
    Phone varchar(15),
    HireDate date,
    Salary Money
);

پس از ایجاد یک دیتابیس می‌توانید روی آن راست کلیک کرده و یک کوئری جدید بسازید:

کوئری به صورت زیر است که یک جدول با مشخصات مربوطه خواهد ساخت:

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

در صفحه جدید نیاز است تا دیتابیس مربوطه انتخاب شود. سپس با فشردن کلید Start analysis کار آنالیز برای کوئری مورد نظر شروع خواهد شد:

بر اساس حجم کوئری و پیچیدگی آن، شاید زمان بیشتری برای آنالیز نیاز باشد. بنابراین، بهتر است با صبر و حوصله منتظر نتیجه بررسی‌ها بمانید:

نصب و پیکربندی Database Engine Tuning Advisor

نصب و پیکربندی DETA بسیار آسان است و در صورتی که قبلا با SQL Server شرکت مایکروسافت کار کرده باشید، تنها ۵ یا ۱۰ دقیقه برای این کار زمان نیاز دارید. البته، قبل از هر کاری نیاز است تا نسخه مورد نیاز از SQL Server را از طریق این لینک دریافت کنید که ما در این مقاله از نسخه ۲۰۲۲ استفاده کرده‌ایم. 

• ابزار DETA برای نسخه EXPRESS ارائه نشده است و برای استفاده از این ابزار نیاز به نسخه Developer دیتابیس مایکروسافت یا نسخه‌های بهتر است. 

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

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

اکنون در صفحه اصلی SSMS می‌توانید روی DETA کلیک کنید و از آن برای آنالیز کوئری‌ها استفاده کنید:

استفاده از Database Engine Tuning Advisor در بهینه سازی کوئری ها

بهتر است تا بدانید معمولا برای کوئری‌های ساده و جزئی پیشنهاد بهینه‌سازی از DETA دریافت نخواهید کرد. این ابزار برای زمان‌هایی کاربردی است که با جداول پیچیده و دیتای زیاد سروکار دارید. بنابراین، در زمان اجرای کوئری‌های سنگین بهتر است روی Analyze with DETA در SSMS کلیک کنید تا پیشنهادهای مورد نظر را مشاهده کنید. در بسیاری از موارد این پیشنهادها به عملکرد بهتر در ذخیره‌سازی و فراخوانی دیتا کمک زیادی خواهد کرد. 

در این بخش با استفاده از اسکریپت زیر ابتدا یک دیتابیس ایجاد می‌کنیم تا جداول مورد نیاز را داخل آن تولید کنیم. حجم دیتا در این مورد بالا است تا بتوانیم در آینده پیشنهادات DETA را برای بهینه‌سازی بیشتر دریافت کنیم. 

-- Create a new database
CREATE DATABASE TestDB;
GO
-- Switch to the new database
USE TestDB;
GO
-- Create a sample table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50),
    Salary INT,
    HireDate DATE
);
GO
-- Insert a large amount of sample data into the table
DECLARE @i INT = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
    VALUES 
        (@i, 'First' + CAST(@i AS NVARCHAR(50)), 'Last' + CAST(@i AS NVARCHAR(50)), 
         CASE WHEN @i % 4 = 0 THEN 'HR' WHEN @i % 4 = 1 THEN 'IT' 
              WHEN @i % 4 = 2 THEN 'Finance' ELSE 'Marketing' END, 
         ۵۰۰۰۰ + (@i % 10000), 
         DATEADD(DAY, -@i, GETDATE()));
    SET @i = @i + 1;
END
GO
-- Create additional tables for more complexity
CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName NVARCHAR(100),
    StartDate DATE,
    EndDate DATE
);
GO
CREATE TABLE EmployeeProjects (
    EmployeeID INT,
    ProjectID INT,
    Role NVARCHAR(50),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
);
GO
-- Insert sample data into Projects
INSERT INTO Projects (ProjectID, ProjectName, StartDate, EndDate)
VALUES
    (۱, 'Project A', '2018-01-01', '2018-12-31'),
    (۲, 'Project B', '2019-01-01', '2019-12-31'),
    (۳, 'Project C', '2020-01-01', '2020-12-31');
GO
-- Insert a large amount of sample data into EmployeeProjects
DECLARE @j INT = 1;
WHILE @j <= 30000
BEGIN
    INSERT INTO EmployeeProjects (EmployeeID, ProjectID, Role)
    VALUES 
        ((@j % 10000) + 1, ((@j % 3) + 1), 
         CASE WHEN @j % 3 = 0 THEN 'Manager' WHEN @j % 3 = 1 THEN 'Developer' ELSE 'Tester' END);
    SET @j = @j + 1;
END
GO

این اسکریپت جداولی با نام Employees، Projects و EmployeeProjects ایجاد خواهد کرد. در نهایت، دیتای جداول به صورت زیر هستند: 

اکنون با کوئری زیر می‌توان پیشنهادات مدنظر توسط DETA را دریافت کرد:

-- Run these queries multiple times to generate the recommendations
SELECT * FROM Employees WHERE Department = 'IT';
SELECT * FROM Employees WHERE Salary > 60000;
GO

با ایجاد کوئری، داخل آن راست کلیک کرده و آنالیز با DETA را انتخاب کنید:

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

پیشنهادها و تحلیل‌های DETA به صورت زیر هستند:

تحلیل نتایج Database Engine Tuning Advisor

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

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

به این صورت، شما می‌توانید روی Definition هر یک از پیشنهادها کلیک کنید تا توصیه موردنظر را مشاهده کرده و در صورت نیاز از آن استفاده کنید:

*** با کلیک روی see reports for sizes of existing objects، امکان تحلیل آماری فضاهای اشغال شده و قابلیت‌های بهینه‌سازی بیشتر در اختیار شما خواهد بود.

گزارش‌های اختصاصی برای هر recommendation به صورت زیر است:

نکات و بهترین روش ها برای استفاده از DETA

رسیدن به بازدهی حداکثری با DETA منوط به حرفه‌ای بودن توسعه‌دهنده و تسلط وی بر بخش‌های مختلف این ابزار است. شاید بهتر باشد چندین آنالیز از یک کوئری انجام دهید و شرایط مختلف را روی آن تست کنید تا بتوانید به بهترین بهینه‌سازی برسید. مزیت این ابزار سرعت عمل آن است و با انجام این آنالیزهای مکرر زمان زیادی از شما تلف نخواهد شد. 

برای رسیدن به بهترین نتیجه با این ابزار می‌توانید نکات زیر را در نظر داشته باشید:

• تنظیمات دقیق: با مراجعه به صفحه DETA بخش‌های مختلفی را مشاهده خواهید کرد که برای افزایش بازدهی و بهبود عملکرد آن طراحی شده است. در این بخش‌ها امکان تغییر و اعمال تنظیمات بیشتر در اختیار کاربر است. 
• ارزیابی و پیاده‌سازی پیشنهادات: قبل از اعمال پیشنهادات بهتر است آن‌ها را نیز تحلیل کنید تا مطمئن شوید پس از اعمال به پیشرفت قابل ملاحظه‌ای در عملکرد دست پیدا خواهید کرد.
• ابزارهای مانیتورینگ: در بخش‌ بعدی چند ابزار مانیتورینگ را معرفی خواهیم کرد که با استفاده از آن‌ها می‌توانید به صورت بصری روی این تغییرات نظارت داشته باشید. بهتر است در کنار Database Engine Tuning Advisor از این ابزارهای مانیتورینگ نیز برای رسیدن به بهترین نتایج استفاده کنید.   

مقایسه DETA با ابزارهای مشابه

ابزار Database Engine Tuning Advisor به صورت اختصاصی از طرف شرکت مایکروسافت در اختیار توسعه‌دهندگان قرار گرفته است. با این حال، شاید نیاز داشته باشید تا از ابزارهای حرفه‌ای‌تر و کامل‌تر نیز استفاده کنید. 

ابزار SolarWinds Database Performance Analyzer

ابزار آنالیز SolarWinds یکی از محبوب‌ترین محصولات نرم افزاری است که برای تحلیل عملکرد دیتابیس مایکروسافت می‌توان از آن استفاده کرد. فهرست بزرگی از ویژگی‌ها را در این ابزار در اختیار دارید و به صورت گرافیکی به تمامی تحلیل‌ها در این ابزار دسترسی دارید. SolarWinds DPA دارای پلن رایگان و پولی است. 

ابزار Logic Monitor 

ابزار Logic Monitor دیگر بسته نرم افزاری مدرن است که با یک رابط گرافیکی بی‌نظیر بسیاری از قابلیت‌ها را در اختیار شما قرار خواهد داد. در این ابزار امکان استفاده متریک‌های مختلف وجود دارد تا به راحتی بتوانید روی بخش مورد نظر در دیتابیس نظارت و مدیریت داشته باشید. لاجیک مانیتور یکی از کامل‌ترین بسته‌های تیونینگ و نظارت SQL Server است و با نصب آن تنها چند کلیک کافی است تا نظارت روی پایگاه داده را شروع کنید. 

در آخر

Database Engine Tuning Advisor چند سال قبل بود که با عرضه SQL Server 2005 در اختیار مدیران پایگاه داده قرار گرفت تا فهرستی از ویژگی‌ها را ارائه دهد. ازآن‌زمان پیشرفت‌های زیادی در فهرست ویژگی‌های این ابزار دیده شده است و امروزه DTA را می‌توان یک پلاگین کامل برای تحلیل و آنالیز کوئری‌ها به حساب آورد. در حالتی که حجم جداول دیتابیس و میزان ردیف‌ها بسیار بالا باشد، لزوما نیاز است تا از یک ابزار جانبی مانند DTA استفاده شود تا از مشکلات کاهش سرعت و آسیب به عملکرد جلوگیری کنیم.