بهینه سازی کوئری (بخش دوم، MSSQL Server)

نویسنده اسماعیل شیدایی

شنبه 17 آبان 1393 - 10:04

بهینه سازی کوئری (بخش دوم، MSSQL Server)

بسم الله الرحمن الرحیم

آموزش بهینه سازی کوئری در MSSQL Server

یکی از جذاب ترین کارها برای من تعمیر و نگهداری و بهینه سازی سیستم ERP برای کارهای بزرگ است.در یک سیستم که ابتدا برای تعداد محدودی از وظایف طراحی شده بود مشکلاتی ایجاد شد و در طول زمان این سیستم رشد بسیار زیادی کرد و از حد انتظار فراتر رفت.

در این برنامه هنگامی که چند کاربر به طور همزمان از سیستم استفاده می کردند کار با این سیستم عملا غیر ممکن می شد. افزایش ظرفیت عملیاتی در سرور به طور کامل نمی تواند این چنین مشکلاتی را حل کند. پس باید از راه حلهای دیگری استفاده شود، بعد از اصلاح کردن business-functionality و بهینه سازی پرس وجو ها در استفاده از منابع، مشکل حل و فصل شد.
قبل از این که به این مقاله بپردازم در اینجا مقاله ای در خصوص نحوه بهینه سازی و چگونگی انتخاب یک Query توسط SQL Server توضیحاتی را ارائه داده ام. برای اجرا هر دستور چندین Plan توسط SQL Server طراحی می شود و بر اساس الگورتیم های متفاوت بهترین روش که کمترین هزینه را دارد، انتخاب و مورد استفاده قرار می گیرد.

همچنین شما باید بدانید که یک روش (Plan) به چه صورتی ایجاد می شود از این رو بهتر است مقاله ای که در این زمینه قبلا ارائه داده شد را مطالعه کنید.

برای رسیدن به حداکثر سرعت در اجرای یک دستور query optimizer (بخش های مختلف کدهای SQL Server را از یکدیگر تفکیک می کند) همیشه تلاش می کند تا یک روش با مجموعه از فعالیت ها که کمترین میزان ممکن از منابع را اشغال می کنند تولید کند.
هنگام ارزیابی هر روش قابل اجرا، بهینه ساز پرس و جو (query optimizer) در حساب های متفاوت فاکتورهای متفاوتی را ایجاد می کند: اشیا پایگاه داده دخالت داده شده، شرایط join شدن آنها با یکدیگر، لیست های بازیابی شده، حضورشاخص ها، در دسترس بود Index ها و آمارهای واقعی و غیره.
با این حال گاهی اوقات بهینه ساز پرس و جو نمی تواند با داده های واقعی کار کند و به همین دلیل ممکن است در زمان استفاده از روش های مختلف بیشتر یا کمتر از مقدار ارزیابی شده هزینه خود را محاسبه کند. همین امر موجب می شود در گاهی اوقات یک روش بهینه سازی نشده انتخاب شود!

SSMS اجازه می دهد روش

بسم الله الرحمن الرحیم

آموزش بهینه سازی کوئری در MSSQL Server

query profile با استفاده از dbForge Studio For SQL Server، برای دانلود این ابزار از طریق لینک زیر اقدام کنید.

دانلود

معرفی این ابزار

در این آموزش از این ابزار استفاده خواهیم کرد.

ما می توانیم یک روش اجرایی با مدت زمان اجرای آن مشاهده کنیم. برای اینکار باید به profiling mode برویم.

همچنین ما می توانیم با اجرای Generate Execution Plan بدون نیاز به اجرای پرس و جو از این ویژگی استفاده کنیم.

چندین مثال وجود دارد که ما می توانیم به وسیله آنها نکاتی را در مورد بهینه سازی پرس و جو ها با استفاده از profiler بدست آوریم.

1- به حداقل رساندن connection های ضمنی
برای بیشتر پرس و جو های پیچیده SQL من به دنبال استفاده از view ها وابسته به شرایط هستم، زمانی که آنها تعریف می شوند ممکن است

since their declaration might be abundant, and the server will be consuming more time for reading and analyzing this data
و سررو زمان زیادی برای خواندن و تحلیل این داده ها صرف کند، من نمی خواهم بگویم که نباید از این ویژگی اصلا استفاده کنید اما منظورم این است که باید به صورت رعایت شده از آن استفاده کنید. مثلا در شرکت خودمان در مباحث این چنینی خیلی باید وقت زیادی صرف می کردیم و یکی از دوستان ما همیشه به View ها به عنوان یک راه حل نگاه می کرد در صورتی که من شخصا تمایل زیادی برای استفاده از آن ها ندارم.
برای مثال، ما باید یک لیست از جدول Customer بدست آوریم. تمام اشیا داده لازم می تواند از system view دریافت شوند.

SELECT *
FROM sys.tables t

ما طرح زیر را برای بدست آوردن این پرس و جو خواهیم داشت:

در profile عملیاتی که بیشترین هزینه استفاده از منابع را دارند با رنگ قرمز مشخص شده اند و بعد از آنها با رنگ زرد مشخص شده اند. درصد استفاده از منابع در هر بخش به ازای عملیاتها نمایش داده شده است.

خوب SQL query خود را تغییر می دهیم. ما باید فقط نام جداول خود را انتخاب کنیم.

SELECT t.name
FROM sys.tables t

در حال حاضر طرح اجرایی ما ساده شده است و مشخص است که طرح اجرایی ما به منابع کمتری نیاز دارد.

اینکار چطور انجام شد؟ باید نگاهی به محتوای sys.tables داشته باشیم. متاسفانه، SSMS اجازه نمی دهد view DLL های سیستمی را مشاهده کنید، پس ما مجبور می شویم یک ترفند برای اینکار به کار ببریم.

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = OBJECT_DEFINITION(OBJECT_ID('sys.tables'))
PRINT @SQL

ما نتایج زیر را دریافت خواهیم کرد:

CREATE VIEW sys.tables
AS
SELECT o.name, o.OBJECT_ID, o.principal_id, o.schema_id, o.parent_object_id,
o.TYPE, o.type_desc, o.create_date, o.modify_date,
o.is_ms_shipped, o.is_published, o.is_schema_published,
ISNULL(ds.indepid, 0) AS lob_data_space_id,
rfs.indepid AS filestream_data_space_id,
...
ts.name AS lock_escalation_desc,
o.is_filetable
FROM sys.objects$ o
LEFT JOIN sys.sysidxstats lob ON lob.id = o.OBJECT_ID AND lob.indid &amp;lt;= 1
LEFT JOIN sys.syssingleobjrefs ds ON ds.depid = o.OBJECT_ID AND ...
LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.OBJECT_ID AND ...
LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ...
WHERE o.TYPE = 'U'

بهینه ساز پرس و جو به ستونهایی که عملا در پرس و جو SQL ما استفاده شده اند نگاه می کند و join های بی مورد را حذف می کند. حالا ما می توانیم ببینیم چرا یک "*" در پرس و جو می تواند نتیجه و کارایی پرس و جو کاهش دهد.
با این حال همان داده ها را می توان در یک روش کارامدتر دریافت کرد. همه جداول اشیا هستند و این اشیا در sys.objects موجود می باشند.

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = OBJECT_DEFINITION(OBJECT_ID('sys.objects))
PRINT @SQL
'

ما خروجی زیر را دریافت می کنیم.

CREATE VIEW sys.objects AS
SELECT name,
OBJECT_ID,
principal_id,
schema_id,
...
FROM sys.objects$

واضح است که این view خیلی ساده است و بنابراین سریعتر از sys.tables است. ما باید جداول را در میان تمامی اشیا
به وسیله اضافه کردن فیلترهای شرطی select کنیم (U – USER_TABLE, V – VIEW).

SELECT t.name
FROM sys.objects t
WHERE t.[type] = 'U'

برای مقایسه پرس و جو ها نیاز داریم که روش اجرایی خود را انتخاب کنیم و از بخش منوها Compare Selected Results را انتخاب کنیم.

2- حذف داده هایی که بازخوانی می شوند

معمولا بازخوانی داده ها یک از عملیات هایی هستند که منابع را مشغول می کنند. به همین دلیل است که پرس و جو ها باید در روشی نوشته شوند که تنها یک بار اجازه خواندن داده ها را بدهند.
برای مثال:

SELECT
(
SELECT COUNT(*)
FROM sys.objects o
WHERE o.[type] = 'U')
,(
SELECT COUNT(*)
FROM sys.objects o
WHERE o.[type] = 'V')

هنگامی شما از طریق این طرح خروجی را بررسی می کنید، می توانید مشاهده کنید که داده های این جدول دوبار خوانده می شوند.

مثال زیر روش حل و فصل این مشکل را نمایش می دهد:

;WITH cte AS
(
SELECT o.[type], [count] = COUNT(*)
FROM sys.objects o
WHERE o.[type] IN ('U', 'V')
GROUP BY o.[type]
)
SELECT (
SELECT [count]
FROM cte
WHERE [type] = 'U'
)
, (
SELECT [count]
FROM cte
WHERE [type] = 'V'
)

بیاد داشته باشید که CTE فقط یک تولید کننده کد است و این ابزار به شما اجازه نمی دهد که بازخوانی داده ها را حذف کنید.

می بایست پرس و جو SQL را به وسیله aggregation باز نویسی کنیم.

SELECT
COUNT(CASE WHEN o.[type] = 'U' THEN 1 END)
, COUNT(CASE WHEN o.[type] = 'V' THEN 1 END)
FROM sys.objects o
WHERE o.[type] IN ('U', 'V')

بهینه سازی صورت گرفته کاملا مشخص است.

بیایید تصور کنیم ما بیش از دو aggregation نیاز داریم. در این شرایط اجرای پرس و جو زمان بیشتری را مصرف می کند. اما در واقعیت، ما می توانیم این پرس و جو را بیشتر از این ساده کنیم.

SELECT *
FROM (
SELECT o.OBJECT_ID, o.[type]
FROM sys.objects o
WHERE o.[type] IN ('U', 'V')
) t
PIVOT (
COUNT(t.OBJECT_ID) FOR [type] IN (U, V)
) p

پرس و جو ها را مقایسه کنید:

فکر کنم دیگر نیازی به توضیح نباشد.
در اینجا می خواستیم به این نکته اشاره کنیم که ساده سازی منطق پرس و جو SQL کلید کار بهینه ساز پرس و جو است. چیزی که همیشه سعی کردم به دیگران نیز اتقال بدهم زیرا روش های نوشتن یک پرس وجو ساده می تواند نتایج مختلفی را از لحاظ هزینه استفاده از منابع برای ما داشته باشد.
هنگامی که تحلیل و بررسی روش های اجرایی با کمک query profiler شما می توانید بحران ها و فشارها را در افزایش کارایی پایگاه داده تعیین کنید و همچنین پرس و جو های خودتان را کارامدتر کنید.