پست تنها



SQL Server Profiler ( اس کیو ال سرور پروفایلر )

تصور کنید کاربر سیستم به اشتباه روی دکمه ای که کوئری کند را فراخوانی میکند کلیک کند؛ کوئری برای ساعت ها در حال اجرا باقی می ماند و در نرم افزار مربوطه هیچ راهی برای متوقف کردن آن وجود نداشته باشد. این جاست که مشکل بوجود می آید و از شما خواسته می شود هر طور که شده اجرای کوئری را متوقف کنید! چه راه حلی برای این مساله دارید؟

تصور کنید در یک نرم افزار کاربر به اشتباه روی یک دکمه کلیک می کند و یک Query کند که اجرای آن ۷ ساعت زمان میبرد اجرا می شود و در نرم افزار مربوطه هیچ راهی برای متوقف کردن آن نیست؛ چه می کنید؟

در برخی سازمان ها دیده شده که اجرای تعدادی از کوئری ها بسیار زمان بر است.حتی در بازدید هایی که داشتیم متوجه شدیم شده گاهی اجرای یک کوئری بیش از ۷ ساعت طول کشیده باشد، و این یک فاجعه است. این کوئری های کند معمولا از خارج از محیط SSMS (SQL Server Managment Studio )به SQL Server فرستاده می شوند.

کافیست تصور کنید کاربر سیستم به اشتباه روی دکمه ای که کوئری کند را فراخوانی میکند کلیک کند؛ کوئری برای ساعت ها در حال اجرا باقی می ماند و در نرم افزار مربوطه هیچ راهی برای متوقف کردن آن وجود نداشته باشد. این جاست که مشکل بوجود می آید و از شما خواسته می شود هر طور که شده اجرای کوئری را متوقف کنید! چه راه حلی برای این مساله دارید؟

این مساله ممکن است در دو حالت پیش بیاید:

شما از قبل SQL Sever را اجرا کرده اید و در حال شکار دستورات اجرایی در SQL Server هستید.

کوئری در حالی به SQL Server ارسال شده که SQL Server Profiler در حال اجرا نیست. در این حالت توجه داشته باشید که تنها دستوراتی در SQL Server Profiler شکار خواهند شد که پس از اجرای آن به SQL Server ارسال شده باشند.

در این مقاله هر دو حالت را برای کوئری های طولانی و یا به اصطلاح کند بررسی می کنیم و راه حل هایی را برای هر دو ارایه می دهیم.

بررسی و شکار Query های کند در حالت اول

منوی Tools را باز کرده و SQL Server Profiler را انتخاب کنید.

مسیر دستیابی به SQL Server Profiler

 

در پنجره اتصال به سرور که به شما نشان داده می شود سرور مورد نظر را انتخاب و به آن متصل شوید.
سپس پنجره ای با عنوان Trace Profiler به شما نشان داده ی شود. روی تب دوم این پنجره کلیک کنید و گزینه های مورد نیاز برای انتخاب را مانند زیر تیک بزنید:

پنجره ی Trace Profiler

 

سپس روی دکمه Run کلیک کنید تا پنجره ای که دستورات اجرا شده در SQL Server را نشان می دهد، باز شود. از این پس تمام دستوراتی که در SQL Server اجرا می شوند (حتی کوچکترین دستورات مانند باز کردن یک دیتابیس در Object Explorer) را در این پنجره شکار خواهید کرد. با کلیک کردن روی هریک از سطر های موجود در این پنجره، دستور اجرا شده را در پایین صفحه خواهید دید. دوستانی که بسته ۵ آشامیدنی در SQL Server را استفاده کرده اند به خوبی می دانند که استفاده از این حالت SQL Server Profiler اصلا مناسب نیست و بهتر است مطابق روشی که در این بسته گفته شده آن را به صورت Server Side اجرا کنید.

به سراغ SQL Server بروید و دستور زیر را اجرا کنید. این دستور در یک حلقه Loop می ماند و هیچ گاه متوقف نخواهد شد:

دستوری که دریک حلقه Loop می ماند و هیچ گاه متوقف نخواهد شد

declare @i int =1
while @i=1
SELECT *
FROM HumanResources.Employee

به SQL Server Profiler باز گردید. دستور اجرا شده را به چند روش می توانید در بین انبوهی از دستورات اجرا شده پیدا کنید.بهتر است برای ساده کردن کار ،کلید های Ctrl+F را بفشارید . در صورتی که می دانید دستور شما در کدام پایگاه داده اجرا شده،از لیست باز شونده ی پنجره Find ، DatabaseName را انتخاب کنید و بخشی از نام پایگاه داده یا نام کامل آن را وارد کنید. سپس با کلیک کردن روی دکمه ها Find Next و یا Find Previous می توانید دستورات اجرا شده روی آن پایگاه داده را پیدا کنید.

 

جستجوی پایگاه داده ای که دستور در آن اجرا شده است

 

در تصویر زیر مشاهده می کنید که دستور مورد نظر را پیدا کردم.

پیدا کردن دستور مورد نظر

 

حال SPID آن را در دستور KILL استفاده کنید تا بتوانید کوئری در حال اجرا را متوقف کنید:

 

دستور متوقف کردن یک کوئری در حال اجرا

  KILL 52

دقت داشته باشید که بجای ۵۲ حتما از SPID مربوط به کوئری خود استفاده کنید.

شکار کوئری های کند در حالت دوم

مانند تصویر دستور زیر را در SSMS بنویسید و اجرا کنید، تا یک کوئری کند داشته باشید که اجرای آن هرگز متوقف نخواهد شد :

اجرای یک Query کند

Declare @i int =1
while @i=1
SELECT *
FROM HumanResources.Employee

کوئری که هرگز متوقف نخواهد شد

 

حال دستور زیر را در یک صفحه دیگر بنویسید و اجرا کنید تا بتوانید دستورات در حال اجرا بر روی SQL Server را پیدا کنید:

پیدا کردن دستورات در حال اجرا بر روی SQL Server

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

نتیجه اجرای این دستور به صورت زیر خواهد بود :

نتیجه اجرای دستور فوق

حال از بین دستوراتی که در سرور در حال اجرا هستند، دستور مورد نظر خود را پیدا کرده و مقدار ستون SPID آن را در کنار دستور KILL قرار داده و اجرا کنید.
نتیجه عالی خواهد بود
براحتی و در چند گام ساده توانستید اجرای کوئری را متوقف کنید.
اما ممکن است سوالی در ذهن شما بوجود بیاید. اینکه در یک محیط عملیاتی هر لحظه ممکن است میلیون ها تراکنش در سرور رخ دهد . چگونه می توانیم از میان آنها کوئری که ما آن را اجرا کردیم را پیدا کنیم؟ پاسخ سوال بسیار ساده است. کافیست تا شما دستوری که برای پیدا کوئری در حال اجرا می نویسید را بر اساس پارامتر های مورد نیاز خود فیلتر کنید. و یا از دستور زیر استفاده و طوری آن رافیلتر کنید که براحتی کوئری خود را پیدا کنید:

فیلتر کردن دستور برای شکار کوئری کند

select P.cmd ,
P.spid
, right(convert(varchar,
dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),
۱۲۱), ۱۲) as 'batch_duration'
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in ('background', 'sleeping')
and P.cmd not in ('AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER')
برای فیلتر کردن نتایج بدست آمده، چند پارامتر (ستون) زیر بسیار پرکاربرد خواهند بود که میتوانید آنها را در ادامه شرط دستور فوق وارد کنید:

NTUserName : در صورتی که برای اتصال به سرور و اجرای کوئری از روش احراز هویت ویندوز استفاده شده باشد، نام کاربری ویندوز در آن قرار میگیرد، در غیر این صورت مقدار تهی خواهد داشت.

HostName : نام سیستمی که دستور را به SQL Server ارسال کرده

program_name : نام برنامه ای که دستور را در SQL Server اجرا کرده است.

command : نام دستور

در دستور زیر تنها دستوراتی که از کامپیوتری با نام CodeTejariPC به SQL Server وارد شده اند و در حال اجرا هستند، نشان داده خواهند شد:

جستجوی دستورات در حال اجرا که از کامپیوتری با نام CodeTejariPC به SQL Server وارد شده اند

select P.cmd ,
P.spid
, right(convert(varchar,
dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),
۱۲۱), ۱۲) as 'batch_duration'
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in ('background', 'sleeping')
and P.cmd not in ('AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER')
and hostname = 'CodeTejariPC'

حال کافیست تا SPID مورد نظر خود را در دستور KILL قرار داده و کوئری را موقف کنید .

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *