آموزش جامع sys.dm_exec_text_query_plan در Microsoft SQL Server
این مقاله یک راهنمای عملی از سطح مقدماتی تا حرفهای است و روی استفاده واقعی در عیبیابی، تحلیل Performance و مانیتورینگ SQL Server تمرکز دارد. هدف فقط معرفی Syntax نیست؛ بلکه یاد میگیریم چگونه داده خروجی را تفسیر کنیم، آن را با DMVهای دیگر ترکیب کنیم و از نتیجهگیری شتابزده جلوگیری کنیم.
بازگشت به راهنمای مادر: راهنمای جامع توابع Execution Plan در SQL Server
در محیط Production قبل از اجرای Queryهای عیبیابی، سطح دسترسی، حجم Plan Cache، تعداد Sessionها و هزینه احتمالی جمعآوری داده را بررسی کنید. Diagnostic Query نامناسب میتواند در زمان Incident فشار اضافه ایجاد کند.
تعریف و کاربرد اصلی
sys.dm_exec_text_query_plan برای بازیابی Showplan به صورت متن برای کل Batch یا یک Statement مشخص با استفاده از Offsetها طراحی شده است. این ابزار بخشی از مجموعه Dynamic Management Objects موتور Database Engine است و معمولاً زمانی استفاده میشود که DBA یا Developer میخواهد از علائم کلی مانند CPU بالا، کندی، Blocking یا Recompile به شواهد دقیقتری برسد.
در عمل هیچ DMV یا DMF بهتنهایی پاسخ کامل نمیدهد. بهترین تحلیل زمانی شکل میگیرد که اطلاعات این آبجکت با sys.dm_exec_requests، sys.dm_exec_query_stats، sys.dm_exec_cached_plans، Query Store، Wait Statistics و متن SQL کنار هم قرار گیرد. این نگاه ترکیبی از اشتباه رایج «دیدن یک Plan و صدور حکم قطعی» جلوگیری میکند.
کاربرد شاخص این ابزار: بازیابی Showplan به صورت متن برای کل Batch یا یک Statement مشخص با استفاده از Offsetها. بنابراین قبل از اجرا مشخص کنید سؤال فنی شما چیست و دقیقاً دنبال کدام Session، Plan یا Query هستید.
Syntax، ورودی و خروجی
نحو پایه
SELECT *
FROM sys.dm_exec_text_query_plan(@plan_handle, @statement_start_offset, @statement_end_offset);
پارامتر یا Context موردنیاز
پارامتر اول plan_handle است. دو پارامتر بعدی Offset شروع و پایان Statement بر حسب بایت هستند؛ 0 و -1 برای کل Batch استفاده میشوند.
نوع خروجی
ستون query_plan از نوع nvarchar(max) برگردانده میشود و Showplan متنی را ارائه میکند. برخلاف خروجی XML، محدودیت عمق XML مطرح نیست و میتوان Statement مشخصی را هدف گرفت.
| ستون یا مفهوم | کاربرد |
|---|
| dbid | شناسه دیتابیس Compile |
| objectid | شناسه شیء مرتبط |
| encrypted | وضعیت رمزگذاری |
| query_plan | Showplan متنی nvarchar(max) |
مقادیر Handle مانند plan_handle و sql_handle شناسههای باینری موقت هستند. آنها را بهعنوان شناسه دائمی کسبوکار ذخیره نکنید. Recompile، Eviction، Restart سرویس یا تغییرات Cache میتواند اعتبار عملی آنها را تغییر دهد.
پیشنیازهای امنیتی و نکات نسخه
برای SQL Server 2008 و نسخههای بعدی در دسترس است و برای عیبیابی طرحهای بسیار پیچیده یا Batchهای چنددستوری کاربرد ویژه دارد.
در بسیاری از نسخهها برای مشاهده اطلاعات سراسری Server به مجوزهای خانواده VIEW SERVER STATE یا در نسخههای جدیدتر VIEW SERVER PERFORMANCE STATE نیاز دارید. برخی DMVهای Database-scoped نیز مجوزهای Database Performance State میخواهند. حساب Service یا Application را صرفاً برای راحتی مانیتورینگ عضو sysadmin نکنید؛ اصل Least Privilege را رعایت کنید.
در Azure SQL Database و Managed Instance دامنه دید Sessionها و نام Permissionها میتواند متفاوت باشد. اسکریپت Production باید نسخه، Edition و محیط اجرا را تشخیص دهد و در صورت نبود مجوز، خطای قابل فهم برگرداند.
مثالهای عملی
مثال 1: نمایش Showplan متنی کل Batch
از Offsetهای 0 و -1 برای گرفتن کل Plan یک Batch استفاده میکنیم.
DECLARE @plan_handle varbinary(64);
SELECT TOP (1) @plan_handle = plan_handle
FROM sys.dm_exec_query_stats
ORDER BY last_execution_time DESC;
SELECT query_plan
FROM sys.dm_exec_text_query_plan(@plan_handle, 0, -1);
| query_plan |
|---|
| (Showplan text) |
نکته کاربردی: این حالت برای دیدن کل Batch مناسب است.
مثال 2: نمایش Plan یک Statement مشخص
Offsetهای واقعی Query Stats را مستقیماً به تابع میدهیم.
SELECT TOP (10)
qs.statement_start_offset,
qs.statement_end_offset,
tp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan
(
qs.plan_handle,
qs.statement_start_offset,
qs.statement_end_offset
) AS tp
ORDER BY qs.last_execution_time DESC;
| statement_start_offset | statement_end_offset | query_plan |
|---|
| 0 | 184 | (Statement Showplan) |
نکته کاربردی: مزیت اصلی تابع، هدفگیری Statement در Batch چنددستوری است.
مثال 3: طرح متنی Queryهای پرCPU
Plan متنی Queryهایی با بالاترین متوسط CPU را استخراج میکنیم.
SELECT TOP (5)
qs.total_worker_time / NULLIF(qs.execution_count, 0) AS avg_cpu,
tp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan
(
qs.plan_handle,
qs.statement_start_offset,
qs.statement_end_offset
) AS tp
ORDER BY avg_cpu DESC;
| avg_cpu | query_plan |
|---|
| 8200 | (Statement Showplan) |
نکته کاربردی: ترکیب Avg CPU و Plan برای Tuning هدفمند مفید است.
مثال 4: Plan درخواست فعال با Offset
برای Request جاری از Offset همان Request استفاده میکنیم.
SELECT
r.session_id,
r.status,
tp.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_text_query_plan
(
r.plan_handle,
r.statement_start_offset,
r.statement_end_offset
) AS tp
WHERE r.session_id <> @@SPID;
| session_id | status | query_plan |
|---|
| 64 | suspended | (Statement Showplan) |
نکته کاربردی: این Query برای بررسی Request طولانی در همان لحظه مناسب است.
مثال 5: جستجوی Operator در متن Plan
از LIKE روی خروجی متنی برای پیدا کردن عبارتهای خاص استفاده میکنیم.
SELECT TOP (20)
qs.execution_count,
tp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 0, -1) AS tp
WHERE tp.query_plan LIKE N'%Index Scan%'
ORDER BY qs.execution_count DESC;
| execution_count | query_plan |
|---|
| 55 | ...Index Scan... |
نکته کاربردی: جستجوی متنی برای غربال اولیه مفید است ولی جای تحلیل ساختاری XML را نمیگیرد.
مثال 6: اندازه خروجی Plan متنی
طول Showplan را برای تشخیص Planهای بسیار بزرگ اندازه میگیریم.
SELECT TOP (20)
DATALENGTH(tp.query_plan) AS plan_bytes,
cp.usecounts
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, 0, -1) AS tp
ORDER BY plan_bytes DESC;
| plan_bytes | usecounts |
|---|
| 760000 | 2 |
نکته کاربردی: Planهای بسیار بزرگ میتوانند برای انتقال، ذخیره و تحلیل هزینه بیشتری داشته باشند.
مثال 7: مقایسه Plan کل Batch با Statement
در یک Query هر دو نمای کل Batch و Statement جاری را میگیریم.
SELECT TOP (5)
LEN(fullp.query_plan) AS full_plan_chars,
LEN(stmtp.query_plan) AS statement_plan_chars
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 0, -1) AS fullp
CROSS APPLY sys.dm_exec_text_query_plan
(
qs.plan_handle,
qs.statement_start_offset,
qs.statement_end_offset
) AS stmtp;
| full_plan_chars | statement_plan_chars |
|---|
| 18500 | 6200 |
نکته کاربردی: برای Batchهای بزرگ، Plan Statement معمولاً متمرکزتر و خواناتر است.
مثال 8: بررسی خروجی NULL
مواردی که Plan متنی در دسترس نیست جدا میشوند.
SELECT TOP (50)
cp.objtype,
cp.usecounts,
tp.query_plan
FROM sys.dm_exec_cached_plans AS cp
OUTER APPLY sys.dm_exec_text_query_plan(cp.plan_handle, 0, -1) AS tp
WHERE tp.query_plan IS NULL;
| objtype | usecounts | query_plan |
|---|
| Adhoc | 1 | NULL |
نکته کاربردی: قبل از نتیجهگیری خطا، وضعیت Cache و نوع Query را بررسی کنید.
مثال 9: فیلتر Planهای پرتکرار
فقط Planهای با Usecount بالا را به متن تبدیل میکنیم.
SELECT TOP (20)
cp.usecounts,
tp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, 0, -1) AS tp
WHERE cp.usecounts >= 10
ORDER BY cp.usecounts DESC;
| usecounts | query_plan |
|---|
| 230 | (Showplan text) |
نکته کاربردی: فیلتر Usecount حجم تحلیل را کاهش میدهد.
مثال 10: ترکیب متن SQL و Showplan متنی
Statement واقعی و Plan متنی آن را کنار هم نشان میدهیم.
SELECT TOP (10)
SUBSTRING
(
st.text,
(qs.statement_start_offset / 2) + 1,
(
(CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2
) + 1
) AS statement_text,
tp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan
(
qs.plan_handle,
qs.statement_start_offset,
qs.statement_end_offset
) AS tp
ORDER BY qs.last_execution_time DESC;
| statement_text | query_plan |
|---|
| SELECT ... | (Statement Showplan) |
نکته کاربردی: این الگو برای گزارش فنی Tuning بسیار کاربردی است.
خطاهای رایج و روش عیبیابی
اشتباه در Offsetها باعث میشود بخش موردنظر بازیابی نشود. همچنین اگر plan_handle دیگر معتبر نباشد، خروجی طرح میتواند NULL باشد.
- Handle یا Session قدیمی را بدون کنترل وضعیت فعلی استفاده نکنید.
- خروجی NULL یا خالی را فوراً بهعنوان خرابی SQL Server تعبیر نکنید؛ ابتدا Lifecycle داده و Permission را بررسی کنید.
- در Queryهای CROSS APPLY روی کل Plan Cache یا همه Sessionها، ابتدا دامنه را با TOP، WHERE و معیارهای Performance محدود کنید.
- برای مقایسه دو Snapshot، زمان جمعآوری و Context بار سیستم را ثبت کنید تا تغییرات طبیعی با Regression اشتباه نشود.
- پاکسازی Plan Cache، Restart سرویس یا تغییر SET Option را فقط برای «دیدن نتیجه» انجام ندهید؛ این اقدامات میتوانند اثر سراسری داشته باشند.
ملاحظات Performance و بهینهسازی
بهتر است ابتدا Queryهای هدف را با sys.dm_exec_query_stats محدود کنید و سپس تابع را فقط برای همان plan_handleها فراخوانی کنید؛ اجرای گسترده روی کل Cache هزینه اضافی دارد.
برای ابزارهای مانیتورینگ سازمانی یک الگوی دو مرحلهای مناسب است: مرحله اول Query سبک برای پیدا کردن کاندیداها و مرحله دوم جمعآوری جزئیات فقط برای همان کاندیداها. این روش هم سربار را پایین میآورد و هم داده قابل استفادهتری تولید میکند.
اگر خروجی شامل XML یا متن بسیار بزرگ است، آن را در هر Poll ذخیره نکنید. میتوان Hash، اندازه، Handle، زمان و چند متریک کلیدی را ثبت کرد و فقط در صورت عبور از آستانه یا وقوع Incident جزئیات کامل را گرفت. این رویکرد برای داشبوردهای داخلی و سامانههای Alerting بسیار مقیاسپذیرتر است.
Best Practices برای محیط Production
- مسئله را با یک معیار قابل اندازهگیری تعریف کنید؛ مانند CPU، Duration، Logical Reads، Blocking یا Plan Regression.
- ابتدا Session یا Query هدف را با یک DMV سبک شناسایی کنید.
- سپس sys.dm_exec_text_query_plan را فقط برای همان هدف اجرا کنید و خروجی را همراه Timestamp ذخیره کنید.
- متن SQL، Database Context، SET Options و پارامترهای مؤثر را تا حد امکان کنار شواهد نگه دارید.
- قبل از تغییر Index، Hint یا Configuration، فرضیه را در محیط Test با داده نزدیک به Production آزمایش کنید.
- اسکریپت عیبیابی را Version Control کنید تا تغییرات و نتایج قابل بازبینی باشند.
در پروژههای بزرگ بهتر است این Queryها بهصورت Runbook استاندارد تهیه شوند. آموزش تیم عملیات و مستندسازی اینکه هر خروجی چه معنایی دارد، ارزش بیشتری از داشتن مجموعهای از اسکریپتهای پراکنده و بدون Context ایجاد میکند.
کاربرد واقعی در سناریوهای سازمانی
فرض کنید سامانه فروش در ساعات اوج با افزایش ناگهانی زمان پاسخ مواجه شده است. بهجای Restart یا پاکسازی Cache، ابتدا Queryهای فعال و پرهزینه شناسایی میشوند، سپس از sys.dm_exec_text_query_plan برای بازیابی Showplan به صورت متن برای کل Batch یا یک Statement مشخص با استفاده از Offsetها استفاده میشود. داده حاصل با Wait Type، Blocking، Query Store و شاخصهای CPU و I/O تطبیق داده میشود.
در یک سناریوی دیگر ممکن است دو Application با Connection Setting متفاوت یک SQL مشابه را اجرا کنند و Planهای متفاوت بسازند. ابزارهای Execution-related به DBA کمک میکنند تفاوت Context، Plan و متن را مستند کند و به جای حدس، علت قابل اثبات ارائه دهد.
برای تیمهای DevOps نیز میتوان Snapshotهای محدود و زماندار ساخت و هنگام Alert فقط دادههای ضروری را ثبت کرد. این الگو برای Postmortem بسیار ارزشمند است؛ زیرا چند ساعت بعد از Incident ممکن است Plan از Cache خارج شده یا Session پایان یافته باشد.
سؤالات متداول
sys.dm_exec_text_query_plan دقیقاً چه کاری انجام میدهد؟
sys.dm_exec_text_query_plan برای بازیابی Showplan به صورت متن برای کل Batch یا یک Statement مشخص با استفاده از Offsetها استفاده میشود. ارزش اصلی آن زمانی مشخص میشود که اطلاعات خام Plan Cache یا Session را با Context مناسب مانند متن SQL، آمار اجرا و وضعیت Request ترکیب کنید. در آموزشهای حرفهای SQL Server این تابع معمولاً بخشی از یک زنجیره عیبیابی است، نه یک Query مستقل.
از کجا ورودی یا Context مناسب برای sys.dm_exec_text_query_plan را پیدا کنیم؟
پارامتر اول plan_handle است. دو پارامتر بعدی Offset شروع و پایان Statement بر حسب بایت هستند؛ 0 و -1 برای کل Batch استفاده میشوند. در عمل بهتر است ورودی را از DMVهای همان لحظه یا Plan Cache بهصورت پویا استخراج کنید تا Handleهای قدیمی، Session اشتباه یا دادههای نامعتبر وارد تحلیل نشوند.
آیا استفاده از sys.dm_exec_text_query_plan برای پروژههای سازمانی ارزش دارد؟
بله، مخصوصاً در سامانههایی که کندی Query، Blocking، مصرف CPU یا رفتار Plan Cache باید سریع ریشهیابی شود. تیم DBA میتواند خروجی را در Runbookها و ابزارهای مانیتورینگ داخلی قرار دهد و در پروژههای بهینهسازی SQL Server از آن برای مستندسازی شواهد فنی استفاده کند.
در چه شرایطی مشاوره تخصصی برای تحلیل خروجی sys.dm_exec_text_query_plan مفید است؟
وقتی خروجی با Queryهای پیچیده، Parallelism، Parameter Sensitivity، Plan Cache Bloat یا Incidentهای Production گره میخورد، تفسیر صرف یک ستون کافی نیست. مشاوره SQL Server کمک میکند داده این ابزار با Wait Stats، Query Store، Indexها و الگوی بار واقعی کنار هم قرار گیرد.
تفاوت sys.dm_exec_text_query_plan با نگاه کردن ساده به Activity Monitor چیست؟
Activity Monitor یک نمای عمومی و رابط گرافیکی ارائه میکند، اما sys.dm_exec_text_query_plan داده فنی دقیقتری برای Queryهای اسکریپتی و اتوماسیون فراهم میکند. برای تحلیل قابل تکرار، Queryهای DMV معمولاً شفافتر، قابل ذخیرهتر و مناسبتر برای مقایسه هستند.
چطور یک اسکریپت آماده مبتنی بر sys.dm_exec_text_query_plan را در محیط واقعی اجرا کنیم؟
ابتدا Permission لازم را بررسی کنید، سپس Query را در محیط آزمایشی یا با فیلتر محدود اجرا کنید و بعد Session، Database یا Planهای هدف را انتخاب کنید. در سرویسهای حساس بهتر است اسکریپت مانیتورینگ با TOP، شرط زمان و ثبت حداقلی خروجی ساخته شود.
رایجترین خطا هنگام کار با sys.dm_exec_text_query_plan چیست؟
اشتباه در Offsetها باعث میشود بخش موردنظر بازیابی نشود. همچنین اگر plan_handle دیگر معتبر نباشد، خروجی طرح میتواند NULL باشد. خطای رایج دیگر این است که یک Snapshot را بهعنوان حقیقت دائمی در نظر بگیریم؛ Plan Cache و وضعیت Session پویا هستند و ممکن است چند ثانیه بعد تغییر کنند.
آیا sys.dm_exec_text_query_plan میتواند روی Performance اثر بگذارد؟
بهتر است ابتدا Queryهای هدف را با sys.dm_exec_query_stats محدود کنید و سپس تابع را فقط برای همان plan_handleها فراخوانی کنید؛ اجرای گسترده روی کل Cache هزینه اضافی دارد. اصل مهم این است که Diagnostic Query هم باید خودش بهینه باشد؛ خواندن بیقید حجم زیادی XML، متن یا ردیفهای Profiling میتواند در زمان Incident فشار اضافی ایجاد کند.
Best Practice استفاده از sys.dm_exec_text_query_plan چیست؟
بهترین روش، شروع از یک سؤال مشخص است: کدام Session، Query یا Plan مشکل دارد؟ سپس کمترین داده لازم را جمع کنید، Timestamp و Context را ثبت کنید، خروجی را با معیارهای دیگر تطبیق دهید و از پاکسازی Cache یا تغییر تنظیمات بدون شواهد کافی خودداری کنید.
sys.dm_exec_text_query_plan با کدام نسخههای SQL Server سازگار است؟
برای SQL Server 2008 و نسخههای بعدی در دسترس است و برای عیبیابی طرحهای بسیار پیچیده یا Batchهای چنددستوری کاربرد ویژه دارد. همیشه مستندات نسخه دقیق SQL Server و سطح Permission را بررسی کنید، زیرا نام Permissionها و برخی جزئیات Profiling یا دسترسی DMVها بین نسخهها و سرویسهای ابری تفاوت دارد.
سؤالات مصاحبه SQL Server
در مصاحبه چگونه کاربرد sys.dm_exec_text_query_plan را توضیح میدهید؟
میگویم این ابزار برای بازیابی Showplan به صورت متن برای کل Batch یا یک Statement مشخص با استفاده از Offsetها است و سپس توضیح میدهم چگونه آن را با DMVهای مرتبط ترکیب میکنم تا از یک شناسه خام به شواهد قابل اقدام برسم.
اگر خروجی sys.dm_exec_text_query_plan خالی یا NULL باشد چه میکنید؟
ابتدا اعتبار Handle یا Session، وجود Query در حال اجرا، وضعیت Plan Cache، Permission و محدودیت نسخه را بررسی میکنم؛ سپس از یک منبع جایگزین مانند Query Store، Actual Plan یا Extended Events استفاده میکنم.
چطور از سربار مانیتورینگ با sys.dm_exec_text_query_plan جلوگیری میکنید؟
با فیلتر دقیق، TOP، انتخاب بازه زمانی کوتاه، جلوگیری از Polling بیوقفه و جمعآوری فقط ستونهای ضروری. در Production هر Diagnostic Query باید با همان دقت Queryهای کسبوکار طراحی شود.
چه دادههایی را همراه خروجی sys.dm_exec_text_query_plan ذخیره میکنید؟
زمان جمعآوری، نام Instance و Database، session_id یا plan_handle مرتبط، متن Query، معیارهای CPU و I/O، Wait Type و توضیح Incident را ذخیره میکنم تا تحلیل بعداً قابل بازسازی باشد.
چرا یک Plan یا Snapshot بهتنهایی برای نتیجهگیری کافی نیست؟
زیرا رفتار SQL Server به پارامترها، حجم داده، Statistics، Concurrency، Memory Grant، Waitها و لحظه اجرای Query وابسته است. شواهد باید از چند منبع مستقل کنار هم قرار گیرند.
چکلیست نهایی
- نسخه SQL Server و Permission لازم را مشخص کردهام.
- Session، Query یا Plan هدف را قبل از جمعآوری جزئیات محدود کردهام.
- خروجی را همراه زمان، Database Context و متن SQL ثبت کردهام.
- نتیجه را با حداقل یک منبع دیگر مانند Query Store، Wait Stats یا Actual Plan تطبیق دادهام.
- هیچ اقدام پرریسک مانند Clear Cache را بدون دلیل و برنامه Rollback انجام ندادهام.
- اسکریپت را طوری نوشتهام که در صورت نبود داده یا Permission، رفتار قابل پیشبینی داشته باشد.
جمعبندی
sys.dm_exec_text_query_plan یک ابزار تخصصی برای بازیابی Showplan به صورت متن برای کل Batch یا یک Statement مشخص با استفاده از Offsetها است. ارزش واقعی آن در ترکیب با Context و متریکهای دیگر نمایان میشود. با فیلتر هدفمند، ثبت Timestamp، رعایت Permission و تحلیل چندمنبعی میتوان از آن برای Troubleshooting سریع و مستند در محیطهای جدی SQL Server استفاده کرد.
برای دیدن ارتباط این ابزار با سایر توابع، مقاله مادر توابع Execution Plan در SQL Server را مطالعه کنید.