در BigQuery، درخواست‌های کاربران بر اساس تعداد بایت‌های خوانده شده محاسبه می‌شوند. برای تخمین هزینه‌ها قبل از اجرای query، می‌توان از یکی از روش‌های زیر استفاده نمود:

•    اعتبار سنجی Query در کنسول Cloud
•    فلگ --dry_run در ابزار خط فرمان bq
•    پارامتر dryRun هنگام ارسال یک job Query با استفاده از API
•    استفاده از Google Cloud Pricing Calculator
•    کتابخانه‌های کلاینت

هزینه‌های برآورد شده به کمک این روش‌ها ممکن است به دلیل عوامل متعددی در مقایسه با هزینه‌های واقعی متفاوت باشد. به عنوان مثال، سناریوهای زیر را در نظر بگیرید: 

•    یک عبارت Query که داده‌ها را فیلتر می‌کند، مانند یک عبارت WHERE، ممکن است تعداد بایت‌های خوانده شده را به میزان قابل‌توجهی کاهش دهد.
•    داده‌هایی که پس از برآورد، اضافه یا حذف می‌شوند، ممکن است تعداد بایت‌هایی را که هنگام اجرای Query خوانده می‌شوند، افزایش یا کاهش دهند. 

روش برآورد هزینه‌های Query با استفاده از Google Cloud Pricing Calculator به عنوان یکی از مرسوم‌ترین روش‌های برآورد هزینه کوئری در ادامه این مقاله بیان شده و همچنین چگونگی مقایسه هزینه‌ها و سایر فاکتورهای query execution plans بیان خواهد شد. در پایان نیز روش تعیین منابع مصرفی سرور در حین اجرای کوئری توضیح داده می‌شود.

 

برآورد هزینه‌های Query

برآورد هزینه‌های Query با استفاده از Google Cloud Pricing Calculator

برای تخمین هزینه‌های Query در Google Cloud Pricing Calculator، تعداد بایت‌هایی را که توسط Query پردازش می‌شوند به صورت B، KB، MB، GB، TB یا PB وارد کنید. اگر Query کمتر از 1 ترابایت پردازش می‌کند، هزینه تخمینی 0 دلار است زیراBigQuery  1 ترابایت Query را در هر ماه به صورت رایگان و بدون هزینه پردازش می‌کند. برای تخمین هزینه Query در Google Cloud Pricing Calculator مراحل زیر را دنبال کنید:

1.    Google Cloud Pricing Calculator را باز کنید،
2.    روی BigQuery کلیک کنید.
3.    روی تب On-Demand کلیک کنید.
4.    در قسمت Table Name، نام جدول را تایپ کنید. مثلاً airports.
5.    در مورد Storage Pricing، در قسمت Storage عدد 0  را وارد کنید. 
6.    برای Query Pricing، بایت‌های تخمینی خوانده شده در مرحله اجرا یا اعتبارسنجی Query را وارد کنید. 
7.    روی Add To Estimate کلیک کنید.
8.    برآورد هزینه در سمت راست ظاهر می‌شود. توجه داشته باشید که می‌توانید تخمین را ذخیره کرده یا ایمیل کنید.

به عنوان مثال در این روش، تعداد بایت‌های خوانده شده توسط Query کمتر از 1 ترابایت پردازش مورد درخواست است که از طریق free tier ارائه می‌شود در نتیجه، هزینه تخمینی 0 دلار خواهد بود.

اگر می‌خواهید برای صورت‌حساب خود قیمت‌گذاری با نرخ ثابت یا flat-rate اعمال کنید، می‌توانید روی تب Flat-Rate کلیک کنید، plan نرخ ثابت خود را انتخاب کرده و Storage costs را در این حالت به تخمین اضافه کنید.

 

محاسبه سایز Query

این بخش نحوه محاسبه تعداد بایت‌های پردازش شده توسط انواع مختلف Query را با استفاده از مدل صورتحساب مورد درخواست یا on-demand billing ارائه می‌دهد.

 

عبارات DML

اگر از صورت‌حساب مورد درخواست استفاده می‌کنید، BigQuery برای عبارات data manipulation language (DML) بر اساس تعداد بایت‌های پردازش شده توسط عبارات، هزینه دریافت می‌کند.

جداول پارتیشن‌بندی نشده یا Non-partitioned: برای جداول پارتیشن‌بندی نشده، تعداد بایت‌های پردازش شده به صورت زیر محاسبه می‌شود:

•    q - مجموع بایت‌های پردازش شده برای ستون‌های ارجاع شده در جداولی که توسط query اسکن شده‌اند.
•    t - مجموع بایت‌ها برای تمام ستون‌ها در جدول به‌روزرسانی شده در زمان شروع query، صرف‌نظر از اینکه آن ستون‌ها در query ارجاع داده شده‌اند یا تنها اصلاح آن‌ها صورت گرفته است. 

DML عبارت بایت‌های پردازش شده
INSERT    q
UPDATE    q + t
DELETE    q + t

MERGE  اگر فقط بندهای INSERT وجود داشته باشد q.
       اگر عبارت UPDATE یا DELETE وجود داشته باشد q. + t

 

جداول پارتیشن‌بندی شده یا Partitioned: برای جداول پارتیشن‌بندی شده، تعداد بایت‌های پردازش شده به صورت زیر محاسبه می‌شود:

•    q' - مجموع بایت‌های پردازش شده برای ستون‌های ارجاع شده در جداولی که توسط query اسکن شده‌اند.
•    t' - مجموع بایت‌ها برای تمام ستون‌ها در پارتیشن‌های به‌روزرسانی شده یا اسکن شده در زمان شروع query، صرف‌نظر از اینکه آن ستون‌ها در query ارجاع یا اصلاح شده‌اند.

DML عبارت بایت‌های پردازش شده

'INSERT    q
'UPDATE    q' + t
'DELETE    q' + t

MERGE    اگر فقط عبارت‌های INSERT در عبارت MERGE وجود داشته باشد 'q
         اگر یک عبارت UPDATE یا DELETE در عبارت MERGE وجود داشته باشد 'q' + t

 

عبارات DDL

اگر از on-demand billing استفاده می‌کنید، BigQuery برای query های data definition language (DDL) بر اساس تعداد بایت‌های پردازش شده توسط query  هزینه‌ دریافت می‌کند.

DDL عبارت    بایت‌های پردازش شده

CREATE TABLE    None.
CREATE TABLE ... AS SELECT ...    مجموع بایت‌های پردازش شده برای تمام ستون‌های ارجاع شده از جداولی که توسط query  اسکن شده‌اند.
CREATE VIEW    None.
DROP TABLE    None.
DROP VIEW    None.

 

اسکریپت نویسی

اگر از on-demand billing استفاده می‌کنید، BigQuery هزینه اسکریپت نویسی را بر اساس تعداد بایت‌های پردازش شده در طول اجرای اسکریپت دریافت می‌کند. تعداد بایت‌های پردازش شده توسط یک اسکریپت معمولاً قبل از اجرای آن مشخص نیست. برای جلوگیری از هزینه‌های ناخواسته query، از flat-rate pricing استفاده کنید. از طرف دیگر، می‌توانید از sandbox BigQuery برای استفاده از ویژگی محدودسازی اجرای اسکریپت به صورت رایگان استفاده نمایید.

قیمت‌گذاری زیر برای انواع عبارت‌های اسکریپت نویسی اعمال می‌شود:

•    DECLARE: مجموع بایت‌های اسکن شده برای هر جدول که در عبارت  DEFAULTارجاع داده شده است. عبارت‌های DEFAULT بدون ارجاع جدول، هزینه‌ای را در بر ندارند.
•    SET: مجموع بایت‌های اسکن شده برای هر جدول که در عبارت SET ارجاع شده است. عبارت‌هایSET  بدون ارجاع جدول، هزینه‌ای در برندارند. 
•    IF: مجموع بایت‌های اسکن شده برای هر جدول که در عبارت شرطی IF ارجاع داده شده است. عبارات شرطی IF  بدون ارجاع جدول، هزینه‌ای در برندارند. هر عبارت در بلوک IF  که اجرا نشود هزینه‌ای در برندارد.
•    WHILE: مجموع بایت‌های اسکن شده برای هر جدول که در عبارت شرطی WHILE  ارجاع داده شده است. عباراتWHILE  بدون ارجاع جدول در عبارت شرطی، هزینه‌ای در برندارد. هر عبارت در بلوکWHILE  که اجرا نشود هزینه‌ای در بر نخواهد داشت. 
•    CONTINUE  یا ITERATE: بدون هزینه.
•    BREAK یا LEAVE: بدون هزینه.
•    BEGIN  یا END: بدون هزینه.

 

مثالی برای قیمت‌گذاری اسکریپت نویسی

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

-- No cost, since no tables are referenced.
DECLARE x DATE DEFAULT CURRENT_DATE();
-- Incurs the cost of scanning string_col from dataset.table.
DECLARE y STRING DEFAULT (SELECT MAX(string_col) FROM dataset.table);
-- Incurs the cost of copying the data from dataset.big_table.  Once the
-- table is created, you are not charged for storage while the rest of the
-- script runs.
CREATE TEMP TABLE t AS SELECT * FROM dataset.big_table;
-- Incurs the cost of scanning column1 from temporary table t.
SELECT column1 FROM t;
-- No cost, since y = 'foo' doesn't reference a table.
IF y = 'foo' THEN
  -- Incurs the cost of scanning all columns from dataset.other_table, if
  -- y was equal to 'foo', or otherwise no cost since it is not executed.
  SELECT * FROM dataset.other_table;
ELSE
  -- Incurs the cost of scanning all columns from dataset.different_table, if
  -- y was not equal to 'foo', or otherwise no cost since it is not executed.
  UPDATE dataset.different_table
  SET col = 10
  WHERE true;
END IF;
-- Incurs the cost of scanning date_col from dataset.table for each
-- iteration of the loop.
WHILE x < (SELECT MIN(date_col) FROM dataset.table) DO
  -- No cost, since the expression does not reference any tables.
  SET x = DATE_ADD(x, INTERVAL 1 DAY);
  -- No cost, since the expression does not reference any tables.
  IF true THEN
    -- LEAVE has no associated cost.
    LEAVE;
  END IF;
  -- Never executed, since the IF branch is always taken, so does not incur
  -- a cost.
  SELECT * FROM dataset.big_table;
END WHILE;

 

مقایسه  Query Execution Plan

برای انتخاب یک query، می‌بایست هزینه‌ها و execution plane را در SQL Server برای کوئری‌های مختلف با هم مقایسه کرد. به عنوان مثال می‌توان از SQL Server 2016 استفاده کرد. SQL Server 2016 قابلیت‌های برتری را برای عیب‌یابی و شناسایی planها فراهم می‌کند. برخی از ویژگی‌های مهم آن عبارت‌اند از:

•    فروشگاه Query
•    بهبود رویدادهای طولانی
•    query execution plan به صورت لایو
•    تجزیه‌وتحلیل زمان واقعی
•    In-Memory OLTP (پردازش تراکنش آنلاین)
•    بهبود عملکرد استودیوی مدیریت مانند Compare Showplan

ویژگی Compare Showplan امکان مقایسه دو مجموعه از query execution planها را فراهم می‌کند. برای توضیح بیشتر باید گفت که SQL Server بر اساس یک سری عوامل مانند تعداد ردیف‌ها، شاخص، آمار، حافظه، CPU و غیره، query execution plans بهینه شده را تولید می‌کند. برای مقایسه هزینه کوئری‌ها می‌توان از این ویژگی استفاده کرد. تا پیش از ارائه SQL Server 2016، انجام این نوع مقایسه بسیار دشوار بود، اما اکنون می‌توان از این ویژگی برای شناسایی تفاوت‌ها، تغییرات و هزینه‌های کوئری‌ها استفاده نمود. 

 

کدام کوئری برای اجرا بهتر است؟

هنگام تجزیه‌وتحلیل query plans، درصد هزینهquery  (که در بالای هرquery  در query plans برآورد شده و ظاهر می‌شود) از اهمیت بالایی برخوردار است. درصد هزینهquery  نشان‌دهنده منبع مصرف شده توسط هر کوئری نسبت به کوئری‌هایی است که اجرا شده‌اند.

نکته‌ای که باید به آن توجه داشت این است که هزینه بیشتر کوئری به این معنی نیست کهquery  زمان بیشتری را برای اجرا صرف کرده است، بلکه به این معنی است کهquery  بیشترین مقدار منابع (CPU، I/O یا حافظه) را مصرف می‌کند.query  با بالاترین هزینه ممکن است همیشه طولانی‌ترینquery  نباشد. لطفاً به این مثال توجه کنید. 

SET NOCOUNT ON
Declare @dt Datetime
SET @dt = getdate()
WAITFOR DELAY '00:00:10'
select * from sys.databases where database_id = 5
Print 'time spent in First Query - '
Print Datediff(ms,@dt,getdate())
Print char(10)
/*************** End of First Batch ******************/
SET @dt = getdate()
select syscolumns.name,sysobjects.name,syscolumns.*
from syscolumns, sysobjects where syscolumns.id = sysobjects.id
and sysobjects.xtype = 'u'
order by sysobjects.crdate
Print 'time spent in Second Query - '
Print Datediff(ms,@dt,getdate())

در این مثال از query plans برای مقایسه هزینه کوئری‌های اول و دوم استفاده می‌کنیم. متغیر @dt مورد استفاده در اسکریپت برای ردیابی زمان صرف شده برای اجرای هر کوئری استفاده می‌شود. مجموعه کوئری‌های اول، 10 ثانیه تأخیر دارند و کمی بیش از 10 ثانیه به پایان می‌رسند. در حالی که کوئری‌های دوم در 140 میلی‌ثانیه تمام می‌شود. در نتیجه با مقایسه query plans، اولین کوئری فقط 12% از کل هزینهquery  را مصرف می‌کند، در حالی که کوئری دوم کمی پیچیده است و 88% از کل هزینهquery  را مصرف می‌کند، علیرغم اینکه تکمیل کوئری اول بیشتر طول می‌کشد.

 

تعیین منابع مورد نیاز سرور در حین اجرای کوئری

SET STATISTICS IO و SET STATISTICS TIME دو روش تنظیم هستند که به شما در تعیین منابع مورد نیاز سرور در هنگام اجرای کوئری کمک می‌کنند. SET STATISTICS IO آمار مربوط به میزان عملکرد دیسک را نشان می‌دهد که توسط کوئری به نمایش گذارده می‌شود. SET STATISTICS TIME نیز مقدار زمان مورد نیاز برای تجزیه، کامپایل و اجرای هر عبارت در کوئری را نشان می‌دهد. با نوشتن یک کوئری به چند روش مختلف می‌توانید آن‌ها را از نظر منابع مورد نیاز سرور با هم مقایسه کرده و بهترین کوئری را انتخاب نمایید.

فعال کردن این تنظیمات یعنی تنظیم SET STATISTICS IO ON و SET STATISTICS TIME ON به یکی از دو روش زیر قابل انجام است. در روش اول این کار را می‌توان با استفاده از دستورات SET برای فعال و غیرفعال کردن این گزینه‌ها انجام داد یا می‌توان با استفاده از تنظیمات گزینه در Query Analyzer این قابلیت‌ها را فعال یا غیرفعال نمود.

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

در ادامه یک مثال برای فعال و غیرفعال کردن STATISTICS IO ارائه شده است.

-- turn on statistics IO
SET STATISTICS IO ON 
GO -- your query goes here
SELECT * FROM Employee
GO
-- turn off statistics IO
SET STATISTICS IO OFF 
GO
همچنین مثالی برای فعال و غیرفعال‌سازی STATISTICS TIME نیز در ادامه آورده شده است.
-- turn on statistics IO
SET STATISTICS TIME ON 
GO -- your query goes here
SELECT * FROM Employee
GO
-- turn off statistics IO
SET STATISTICS TIME OFF 
GO

جمع بندی

در این مقاله روش‌های برآورد هزینه کوئری، انتخاب و مقایسه Execution plan و روش‌های تعیین منابع مورد نیاز سرور در حین اجرای کوئری بیان شد. تعیین منابع مورد استفاده سرور به کمک دو ابزار تنظیم یعنی SET STATISTICS IO و SET STATISTICS TIME انجام می‌شوند که به راحتی به کمک خط دستور، قابل فعال‌سازی و یا غیر فعال‌سازی هستند.

 

     مدرس : استادغلامحسین عبادی