بهینه‌سازی کوئری SQL با Execution Plan و SQL Profiler

روش‌های پیشرفته دستیابی به Query Execution Plan از طریق SQL Profiler

Query Execution Plans، بدون شک مهمترین ابزار در جعبه‌ابزار هر DBA یا توسعه‌دهنده برای درک نحوه اجرای کوئری‌ها توسط SQL Server و چگونگی بهینه‌سازی آن‌ها جهت دستیابی به عملکرد مطلوب هستند. SQL Profiler احتمالاً پرکاربردترین ابزار برای ثبت اطلاعات مربوط به آنچه در یک نمونه SQL Server رخ می‌دهد، است. اگر می‌توانید با استفاده از Profiler کوئری‌هایی را پیدا کنید که زمان زیادی می‌برند، منابع زیادی مصرف می‌کنند یا باعث بن‌بست (deadlocks) می‌شوند، منطقی است که بخواهید Execution Plans آن‌ها را نیز بدست آورید تا بفهمید مشکل چیست و چگونه آن را برطرف کنید. این مقاله به شما نحوه دستیابی به Execution Plans از طریق SQL Profiler را آموزش می‌دهد.

چرا Execution Plans را از طریق SQL Profiler بدست آوریم؟

شما می‌توانید Execution Plans را با روش‌های مختلفی بدست آورید: استفاده از دستورات SET SHOWPLAN_ALL، SET SHOWPLAN_TEXT و SET STATISTICS PROFILE؛ با راست کلیک کردن روی یک کوئری در SQL Server Management Studio و انتخاب Display Estimated Execution Plan یا با فشار دادن کلید Ctrl-L؛ یا با فشار دادن کلید Ctrl-M برای فعال کردن Include Actual Execution Plan. همه این روش‌ها Execution Plans کوئری را از دیدگاه برنامه نویس یا DBA که کوئری را اجرا می‌کند، نمایش می‌دهند. با این حال، Profiler به شما این امکان را می‌دهد که ببینید کوئری‌ها چگونه توسط کاربران دیگر، برنامه‌ها یا حتی سیستم‌های خارجی اجرا می‌شوند. این قابلیت برای تحلیل عملکرد و بهینه‌سازی کوئری در محیط‌های عملیاتی بسیار حیاتی است.

اگر کاربر یا برنامه‌ای در یک محیط توسعه کوئری کندی را اجرا می‌کند، شما می‌توانید یک Trace  راه‌اندازی کنید تا فعالیت‌های آن‌ها را ضبط کرده و سپس Execution Plans مربوطه را تجزیه و تحلیل کنید. این کار به شما کمک می‌کند تا بدون نیاز به مداخله در کد برنامه، علت مشکل را پیدا و راه‌حل‌های مناسبی برای بهبود عملکرد SQL Server ارائه دهید.

راه‌اندازی یک Trace SQL Profiler برای دریافت Execution Plans

برای شروع کار، SQL Profiler را راه‌اندازی کنید و یک Trace جدید ایجاد کنید.

۱. انتخاب الگو (Template)

پس از انتخاب گزینه File > New Trace، پنجره‌ای برای تنظیم Trace Properties باز می‌شود. در این پنجره، یک نام برای Trace خود وارد کنید و الگو (template) پیش‌فرض Standard را انتخاب کنید.

۲. انتخاب رویدادها (Events)

در تب Events Selection، مطمئن شوید که گزینه Show all events و Show all columns علامت خورده باشند تا بتوانید تمام گزینه‌های موجود را مشاهده کنید.

برای بدست آوردن Execution Plans، شما نیاز به دو رویداد اصلی دارید که هر دو تحت دسته Performance قرار دارند:

• Showplan All
• Showplan Text

شما می‌توانید هم Showplan All و هم Showplan Text را برای مشاهده Execution Plans با جزئیات بیشتر انتخاب کنید. Showplan Text یک نمایش متنی از طرح اجرایی ارائه می‌دهد که معمولاً برای تحلیل سریع مفید است، در حالی که Showplan All اطلاعات دقیق‌تر و جامع‌تری را شامل می‌شود.

همچنین برای جمع‌آوری اطلاعات مفید درباره کوئری‌هایی که Trace می‌شوند، رویدادهای زیر را در زیر دسته TSQL انتخاب کنید:

• SQL:BatchCompleted
• SQL:StmtCompleted

این رویدادها به شما کمک می‌کنند تا زمان شروع و پایان هر بچ (batch) و هر دستور SQL را Trace کنید و با Execution Plans آن‌ها ارتباط دهید.

۳. انتخاب ستون‌ها (Columns)

برای تحلیل دقیق، ستون‌های زیر را انتخاب کنید:

• TextData: برای مشاهده متن کامل کوئری.
• Duration: برای مشاهده مدت زمان اجرای کوئری.
• StartTime: برای مشاهده زمان شروع اجرای کوئری.
• EndTime: برای مشاهده زمان پایان اجرای کوئری.
• Reads: برای مشاهده تعداد خواندن‌های منطقی.
• Writes: برای مشاهده تعداد نوشتن‌ها.
• CPU: برای مشاهده زمان CPU مصرف شده.
• ApplicationName: برای شناسایی برنامه‌ای که کوئری را اجرا کرده است.
• DatabaseName: برای شناسایی پایگاه داده.
• HostName: برای شناسایی هاست.
• LoginName: برای شناسایی کاربر.

۴. تنظیم فیلترها (Filters)

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

ذخیره و مشاهده داده‌های Trace

۱. ذخیره Trace در فایل

همیشه توصیه می‌شود که Trace را در یک فایل ذخیره کنید (گزینه Save to file را در تب General فعال کنید). این کار به شما امکان می‌دهد تا در آینده داده‌ها را بازبینی و تجزیه و تحلیل کنید، و از دست رفتن اطلاعات در صورت قطع شدن Profiler جلوگیری می‌کند. مطمئن شوید که مسیر ذخیره‌سازی و حداکثر اندازه فایل مناسبی را تنظیم کرده‌اید.

۲. مشاهده Execution Plans

پس از اجرای Trace، برای مشاهده طرح اجرایی یک کوئری خاص، ستون TextData را در رویدادهای Showplan All یا Showplan Text بررسی کنید. این ستون حاوی اطلاعات طرح اجرایی به صورت XML یا متنی است. با کلیک کردن بر روی سلول مربوطه، می‌توانید جزئیات طرح اجرایی را در پنل پایین مشاهده کنید و آن را به صورت گرافیکی (در صورت Showplan XML) یا متنی تجزیه و تحلیل کنید.

اتومیشن Trace‌ها با استفاده از T-SQL

برای اتومیشن فرآیند Trace و اجرای آن به صورت برنامه‌ریزی شده، می‌توانید از اسکریپت‌های T-SQL استفاده کنید. این روش به شما امکان می‌دهد تا Trace‌ها را بدون نیاز به رابط کاربری گرافیکی Profiler اجرا کنید و برای نظارت مداوم بر عملکرد SQL Server مفید است. در اینجا یک نمونه اسکریپت برای ایجاد و شروع یک Trace ارائه شده است:

DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
SET @maxfilesize = 5

EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'C:\trace', @maxfilesize, NULL

این بخش از کد T-SQL یک Trace جدید ایجاد می‌کند. @rc یک متغیر برای ذخیره کد بازگشتی تابع است. @TraceID شناسه منحصر به فرد Trace را ذخیره می‌کند. @maxfilesize حداکثر اندازه فایل Trace (در مگابایت) را تعیین می‌کند. N’C:\trace’ مسیر و نام پایه فایل Trace را مشخص می‌کند (فایل‌هایی مانند C:\trace.trc، C:\trace_1.trc و غیره ایجاد می‌شوند).

برای اضافه کردن رویدادهای Showplan All و Showplan Text به Trace، از دستورات زیر استفاده کنید:

exec sp_trace_setevent @traceid, 90, 1, 1 -- Showplan All - TextData
exec sp_trace_setevent @traceid, 90, 12, 1 -- Showplan All - SPID
exec sp_trace_setevent @traceid, 90, 14, 1 -- Showplan All - StartTime
exec sp_trace_setevent @traceid, 90, 15, 1 -- Showplan All - EndTime
exec sp_trace_setevent @traceid, 90, 16, 1 -- Showplan All - Duration

exec sp_trace_setevent @traceid, 68, 1, 1  -- Showplan Text - TextData
exec sp_trace_setevent @traceid, 68, 12, 1 -- Showplan Text - SPID
exec sp_trace_setevent @traceid, 68, 14, 1 -- Showplan Text - StartTime
exec sp_trace_setevent @traceid, 68, 15, 1 -- Showplan Text - EndTime
exec sp_trace_setevent @traceid, 68, 16, 1 -- Showplan Text - Duration

این کدها با استفاده از رویه سیستمی sp_trace_setevent، رویدادها و ستون‌های مورد نیاز را به Trace اضافه می‌کنند. عدد 90 شناسه رویداد Showplan All و 68 شناسه رویداد Showplan Text است. عدد دوم (مثلاً 1، 12 و غیره) شناسه ستون است، و عدد نهایی (1) نشان دهنده فعال‌سازی آن ستون برای رویداد مورد نظر است.

برای اضافه کردن رویدادهای SQL:BatchCompleted و SQL:StmtCompleted به Trace و ستون‌های مربوطه:

exec sp_trace_setevent @TraceID, 13, 1, 1 -- SQL:BatchCompleted - TextData
exec sp_trace_setevent @TraceID, 13, 12, 1 -- SQL:BatchCompleted - SPID
exec sp_trace_setevent @TraceID, 13, 14, 1 -- SQL:BatchCompleted - StartTime
exec sp_trace_setevent @TraceID, 13, 15, 1 -- SQL:BatchCompleted - EndTime
exec sp_trace_setevent @TraceID, 13, 16, 1 -- SQL:BatchCompleted - Duration
exec sp_trace_setevent @TraceID, 13, 17, 1 -- SQL:BatchCompleted - Reads
exec sp_trace_setevent @TraceID, 13, 18, 1 -- SQL:BatchCompleted - Writes
exec sp_trace_setevent @TraceID, 13, 34, 1 -- SQL:BatchCompleted - ApplicationName
exec sp_trace_setevent @TraceID, 13, 35, 1 -- SQL:BatchCompleted - DatabaseName
exec sp_trace_setevent @TraceID, 13, 8, 1  -- SQL:BatchCompleted - HostName
exec sp_trace_setevent @TraceID, 13, 11, 1 -- SQL:BatchCompleted - LoginName
exec sp_trace_setevent @TraceID, 13, 13, 1 -- SQL:BatchCompleted - CPU

exec sp_trace_setevent @TraceID, 40, 1, 1 -- SQL:StmtCompleted - TextData
exec sp_trace_setevent @TraceID, 40, 12, 1 -- SQL:StmtCompleted - SPID
exec sp_trace_setevent @TraceID, 40, 14, 1 -- SQL:StmtCompleted - StartTime
exec sp_trace_setevent @TraceID, 40, 15, 1 -- SQL:StmtCompleted - EndTime
exec sp_trace_setevent @TraceID, 40, 16, 1 -- SQL:StmtCompleted - Duration
exec sp_trace_setevent @TraceID, 40, 17, 1 -- SQL:StmtCompleted - Reads
exec sp_trace_setevent @TraceID, 40, 18, 1 -- SQL:StmtCompleted - Writes
exec sp_trace_setevent @TraceID, 40, 34, 1 -- SQL:StmtCompleted - ApplicationName
exec sp_trace_setevent @TraceID, 40, 35, 1 -- SQL:StmtCompleted - DatabaseName
exec sp_trace_setevent @TraceID, 40, 8, 1  -- SQL:StmtCompleted - HostName
exec sp_trace_setevent @TraceID, 40, 11, 1 -- SQL:StmtCompleted - LoginName
exec sp_trace_setevent @TraceID, 40, 13, 1 -- SQL:StmtCompleted - CPU

این کدها رویدادهای اتمام بچ و دستور SQL را با تمام ستون‌های حیاتی برای تحلیل عملکرد به Trace اضافه می‌کنند. عدد 13 شناسه رویداد SQL:BatchCompleted و 40 شناسه رویداد SQL:StmtCompleted است. هر ستون با شناسه مخصوص خود اضافه می‌شود.

تنظیم فیلترها (اختیاری)

برای اضافه کردن فیلترها، از sp_trace_setfilter استفاده کنید. این مثال، Trace را به پایگاه داده Northwind محدود می‌کند:

exec sp_trace_setfilter @TraceID, 35, 0, 0, N'Northwind'

این دستور یک فیلتر روی ستون DatabaseName (شناسه 35) اعمال می‌کند تا فقط فعالیت‌های مربوط به پایگاه داده Northwind ثبت شوند.

شروع Trace

پس از تعریف تمام رویدادها، ستون‌ها و فیلترها، Trace را با دستور زیر شروع کنید:

exec sp_trace_setstatus @TraceID, 1

این دستور وضعیت Trace را به 1 (شروع) تغییر می‌دهد و Trace شروع به جمع‌آوری داده می‌کند.

برای توقف Trace:

exec sp_trace_setstatus @TraceID, 0

این دستور وضعیت Trace را به 0 (توقف) تغییر می‌دهد.

برای بستن و حذف Trace از سیستم:

exec sp_trace_setstatus @TraceID, 2

این دستور وضعیت Trace را به 2 (بستن) تغییر می‌دهد و Trace از SQL Server حذف می‌شود.

جمع‌بندی

دستیابی به Execution Plans کوئری از طریق SQL Profiler یک ابزار قدرتمند برای DBA‌ها و توسعه‌دهندگان است که به دنبال بهینه‌سازی عملکرد SQL Server هستند. با استفاده از رویدادهای Showplan All و Showplan Text، می‌توان دید عمیقی از نحوه اجرای کوئری‌ها بدست آورد و گلوگاه‌های عملکردی را شناسایی کرد. چه از طریق رابط کاربری گرافیکی Profiler و چه از طریق اسکریپت‌های T-SQL برای اتومیشن، این روش به شما کمک می‌کند تا تصمیمات آگاهانه‌ای برای بهبود کارایی و سرعت اجرای کوئری‌های خود بگیرید. با استفاده از این تکنیک، می‌توانید به طور موثرتری مشکلات عملکردی را عیب‌یابی و برطرف کنید و تجربه کاربری بهتری ارائه دهید.