SQL Server Profiler چیست و چه کاربردی دارد؟

SQL Server Profiler چیست و چه کاربردی دارد؟‌ اگر شما هم از SQL Server برای مدیریت پایگاه‌های داده استفاده می‌کنید، بهتر است کار با آن را یاد بگیرید؛ چراکه می‌تواند امکاناتی برای نظارت بر پایگاه داده و رفع خطاهای آن را در اختیار شما قرار دهد. در این مطلب می‌خواهیم SQL Server Profiler را معرفی کرده و با مزایای آن آشنا شویم. همچنین نحوه پیکربندی و استفاده از آن را هم توضیح می‌دهیم. با ما همراه باشید.

SQL Server Profiler چیست؟

SQL Server Profiler یک ابزار مبتنی‌بر GUI (رابط کاربری گرافیکی یا Graphical User Interface) است که در برنامه Microsoft SQL Server وجود دارد و هدف آن، نظارت بر پایگاه‌های داده و تحلیل آن‌ها است. از این ابزار می‌توان برای خطایابی و شناسایی مشکلات پایگاه‌های داده نیز استفاده کرد. 

این ابزار اولین بار در نسخه سال ۲۰۰۰ SQL Server معرفی شد. SQL Server Profiler تمامی عملیات‌های انجام‌شده روی یک پایگاه داده را دنبال می‌کند؛ بنابراین، با استفاده از آن می‌توان تمامی کوئری‌های اعمال‌شده روی جداول پایگاه داده را Trace کرد (منظور از Trace، ثبت تمامی وقایع و دستورالعمل‌های اعمال‌شده روی پایگاه داده است).

مزایای استفاده از SQL Server Profiler چیست؟‌

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

مزایای SQL Server Profiler برای مدیر پایگاه داده

مدیر پایگاه داده می‌تواند به لطف قابلیت‌هایی مثل ثبت وقایع، اجرای آزمون پایداری، شناسایی کوئری‌های زمان‌بر و… ، مشکلات پایگاه داده را شناسایی و برطرف کند. مزایای کلی SQL Server Profiler برای مدیر پایگاه داده عبارت است از:

• نظارت بر تمامی وقایع پایگاه داده
• تحلیل سرویس‌ها
• تحلیل کوئری‌ها
• بررسی رفتار کاربران
• گروه‌بندی نتایج Trace
• ساخت Trace های شخصی‌سازی‌شده
• امکان برنامه‌نویسی برای Trace کردن وقایع
• امکان ذخیره نتایج Trace به‌صورت جدولی یا فایل XML

مدیر پایگاه داده می‌تواند با ذخیره Trace ها و تحلیل آن‌ها، عملکرد پایگاه داده در شرایط مختلف را باهم مقایسه کند.

مزایای SQL Server Profiler برای توسعه دهندگان

توسعه‌دهندگان می‌توانند به کمک Profiler ، نحوه تعامل پایگاه داده با سایر کلاینت‌ها را به‌طور دقیق و شفاف بررسی کنند. سایر مزایای استفاده از SQL Server Profiler برای توسعه‌دهندگان عبارت است از:

• نظارت‌بر T-SQL (مجموعه افزونه‌هایی که قابلیت‌های مختلفی را به SQL اضافه می‌کنند) و رویه‌های ذخیره‌شده
• اجرای آزمون پایداری
• اجرای آزمون تضمین کیفیت

استفاده از SQL Server Profiler برای پیداکردن و رفع مشکلات عملکرد در SQL Server با استفاده از ویژگی‌های پنهان SQL Server Profiler برای کاربران پیشرفته، بسیار راحت است؛ بنابراین، سعی کنید استفاده از این ابزار و قابلیت‌های کلیدی آن را به‌صورت حرفه‌ای یاد بگیرید.

قابلیت های کلیدی SQL Server Profiler چیست؟

مهم‌ترین قابلیت‌های کلیدی SQL Server Profiler عبارت‌اند از:

• ثبت وقایع: امکان ثبت بازه گسترده‌ای از وقایع رخ‌داده برای هر نمونه SQL Server وجود دارد. این وقایع را می‌توان در دسته‌های مختلفی مثل رویه‌ها (Procedures)، خطاها، هشدارها و… تقسیم‌بندی کرد.

• امکان استفاده از ستون‌های اطلاعات: به ازای هر واقعه‌ای که ثبت می‌شود، می‌توان یک ستون حاوی اطلاعات مرتبط با آن واقعه را هم ثبت کرد. این اطلاعات شامل مواردی مثل متن کوئری، زمان اجرای آن و… است. 

• امکان استفاده از فیلتر: فیلترها امکان تمرکز روی یک دستورالعمل یا فعالیت خاص را فراهم می‌کنند. فیلترها را می‌توان براساس مواردی مثل نام پایگاه داده یا مدت‌زمان اجرای کوئری و… ایجاد کرد.

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

• امکان ذخیره و اجرای Trace: شما می‌توانید هر Trace را پس از پیکربندی، به‌صورت یک فایل ذخیره کنید. فایل Trace را می‌توانید در آینده تحلیل کرده یا آن را با سایر همکاران، به اشتراک بگذارید.
• امکان نظارت به‌صورت بلادرنگ: ابزار SQL Server Profiler به شما امکان نظارت بلادرنگ بر پایگاه داده را می‌دهد. درواقع شما می‌توانید تمامی وقایع رخ داده در پایگاه داده به‌صورت در لحظه، مشاهده کنید.

نحوه عملکرد SQL Server Profiler 

برای پرداختن به نحوه عملکرد SQL Server Profiler بهتر است ابتدا با اصطلاحات رایجی که هنگام کار با این ابزار مواجه می‌شوید را معرفی کنیم. البته تا اینجا با بعضی از اصطلاحات مثل Trace، فیلتر و… آشنا شده‌ایم، ولی در این قسمت، مفصل‌تر به آن‌ها می‌پردازیم.

اصطلاح Event

اول از همه به این که Event در SQL Server Profiler چیست اشاره می‌کنیم. یک Event، عبارت است از عملی که روی نمونه‌های ایجادشده توسط SQL Server انجام می‌شود. این اعمال، شامل موارد زیر می‌شود:

• ورود موفق یا ناموفق به حساب کاربری مدیریت پایگاه داده
• اجرای دستوراتی مثل SELECT و INSERT و UPDATE و DELETE 
• اجرای Remote Procedure Call (درخواست سرویس از کامپیوترهای دیگر از راه دور)
• شروع یا پایان فراخوانی یک Procedure
• شروع یا پایان اجرای کوئری‌ها
• وقوع یک خطا
• بررسی اجازه دسترسی
• شروع یا پایان دستورالعمل‌های موجود در یک رویه

تمامی داده‌های تولیدشده توسط یک Event به‌صورت یک ردیف در جدول Trace، قابل مشاهده هستند.

اصطلاح EventClass

اصطلاح EventClass به نوعی از Event اشاره دارد که می‌توان تغییرات آن را رصد کرد. مثال‌هایی از EventClass را مشاهده کنید:

• SQL:BatchCompleted: اشاره به این که اجرای T-SQL به پایان رسیده است.
• Audit Login: اشاره به ورود موفق کاربر به اکانت خود در SQL Server
• Audit Logout: اشاره به خروج موفق کاربر از اکانت خود در SQL Server
• Lock: Acquired: اشاره به قفل‌شدن موفق یک منبع (به‌معنای سلب امکان ویرایش داده‌ها)
• Lock: Released: اشاره به بازشدن قفل از روی یک منبع

اصطلاح EventCategory

این اصطلاح، گروه‌بندی Event ها توسط SQL Server Profiler را توصیف می‌کند. مثلاً، تمامی Event های Lock (همان قفل‌شدن)، در دسته‌بندی Locks قرار می‌گیرند، باید بدانیم که این اصطلاح، به نحوه گروه‌بندی Evenet ها توسط موتور SQL Server اشاره نمی‌کند.

اصطلاح DataColumn 

DataColumn در SQL Server Profiler چیست؟ این اصطلاح، به مشخصه‌ای از یک Event اشاره می‌کند که حاوی اطلاعات خاصی از آن است. DataColumn های مختلفی وجود دارد که مقادیر آن‌ها با توجه به Event موردنظر تغییر می‌کند. مثلاً Lock Acquired را درنظر بگیرید. در این Event، می‌توانیم مقدار مربوط به سطر قفل‌شده را در ستون Binary Data مشاهده کنیم. ولی در همین Event، ستون Integer Data فاقد داده است؛ چراکه نمی‌توان این DataColumn را به Lock Acquired نسبت داد.

اصطلاح Template

این اصطلاح، به تنظیمات ازپیش‌تعیین‌شده برای Trace اشاره می‌کند. هر قالب یا Template حاوی Event Class هایی است که قصد رصد آن‌ها با SQL Server Profiler را دارید. هر Template به‌صورت یک فایل و با پسوند tdf. ذخیره می‌شود. پس از ذخیره‌شدن، Template می‌تواند داده‌های یک Trace که قرار است براساس آن Template انجام شود را کنترل کند.

اصطلاح Trace

یک Trace داده‌ها را براساس Event Class ها، DataColumn ها و فیلترها، جمع‌آوری می‌کند. مثلاً شما می‌توانید یک Trace را تنها برای نظارت بر خطاهای استثنا (Exception Error) بسازید. برای این کار، باید Exception event Class و ستون‌های حاوی داده Error ،State و Severity را انتخاب کنید. حضور این سه DataColumn برای جمع‌آوری داده‌های معنادار، ضروری است. 

اصطلاح Filter

فیلتر در SQL Server Profiler چیست؟ از فیلتر می‌توان هنگام ساخت Template یا Trace به منظور رصد داده‌هایی که مدنظر شما هستند، استفاده کرد. مثلاً شما می‌توانید اطلاعات Event را محدود به یک کاربر خاص ویندوز کنید و از جمع‌آوری اطلاعات مربوط به سایر کاربران، خودداری کنید. این کار باعث کم حجم شدن فایل Trace می‌شود؛ در نتیجه تجزیه و تحلیل آن در آینده آسان‌تر خواهد بود. 

پس از آشنایی با اصطلاحات رایج SQL Server Profiler بهتر است با معماری آن آشنا شویم.

معماری SQL Server Profiler

آشنایی با معماری SQL Server Profile باعث درک بهتر نحوه استفاده از آن برای پیداکردن و رفع مشکلات عملکرد در SQL Server خواهد شد. در تصویر زیر، تمامی مؤلفه‌های اصلی این ابزار و نحوه عملکرد آن‌ها، آورده شده است:

اما این تصویر چه چیزی به ما می‌گوید؟ نحوه عملکرد SQL Server Profiler را طبق این تصویر، می‌توان در مراحل زیر خلاصه کرد:

1. ایجادکنندگان Event (مثل انبار داده عملیاتی یا ODS، مدیر Lock، پردازش‌گر کوئری‌ها و…) آن را در SQL Server Profiler ثبت می‌کنند.
2. فیلترها، اطلاعاتی که SQL Server Profiler قرار است رصد کند را مشخص می‌کنند. در صورت عدم تعریف یک Event در فیلتر، آن Event به SQL Server Profiler ارسال نخواهد شد.
3. تمامی Event ها توسط SQL Server Profiler در صف قرار می‌گیرند.
4. حالا SQL Server Profiler، تمامی Event ها را در فرمت ازپیشتعیین‌شده (که می‌تواند فایل ساده، جدول، پنجره Profiler و… باشد) می‌نویسد.

علاوه‌بر Event هایی که در مرحله اول ایجاد می‌شود، می‌توان SQL Profiler را طوری پیکربندی کرد که از داده‌های وقایعی که قبلاً ذخیره شده‌، استفاده کند.

راه‌ اندازی و پیکربندی SQL Server Profiler برای مانیتورینگ

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

1. ابتدا نحوه اجرای برنامه SQL Server Profiler را بررسی می‌کنیم. برای این کار، منوی استارت را باز کنید. سپس عبارت SQL Server Profiler را جستجو کنید. پس از مشاهده برنامه در قسمت نتایج، آن را اجرا کنید:

راه دیگری نیز برای اجرای این برنامه وجود دارد. ابتدا برنامه Microsoft SQL Server را باز کنید. سپس از منوی Tools، گزینه SQL Server Profiler را انتخاب کنید:

2. پس از اجرای SQL Server Profiler ، پنجره احزار هویت برای شما باز می‌شود: 

پس از وارد کردن نام کاربری و گذرواژه، روی دکمه Connect کلیک کنید.

3. پس از اتصال موفق به پایگاه داده، پنجره پیکربندی تنظیمات Trace به شما نشان داده می‌شود. این پنجره شامل دو زبانه است؛ زبانه General و زبانه Events Selection. در زبانه General، ابتدا باید Trace را نام‌گذاری کنید. همچنین نحوه ذخیره‌کردن Trace (در فایل یا جدول) را هم می‌توانید در این زبانه، مشخص کنید. امکان انتخاب Template هم در زبانه General وجود دارد:

حالا به‌سراغ زبانه Events Selection می‌رویم. در این قسمت باید Event هایی که می‌خواهید Trace شوند را مشخص کنید. نگاهی به تصویر زیر بیندازید:

بخش شماره یک این تصویر، Event ها را نشان می‌دهد. شما می‌توانید براساس نیاز خود، تیک هر یک از Event ها که می‌خواهید را بزنید. در بخش شماره ۲ تصویر، می‌توانید جزئیات Event هایی که انتخاب کرده‌اید را مطالعه کنید. 

در بخش شماره سه تصویر، می‌توانید گزینه نمایش تمامی Event ها و ستون‌های انتخاب‌شده در بخش ۱ تصویر را انتخاب کنید. در بخش ۴ تصویر هم می‌توانید دکمه اعمال فیلتر روی ستون را مشاهده کنید. با زدن این دکمه، پنجره‌ای مثل شکل زیر نمایش داده می‌شود:

در این قسمت، می‌توانید شرایط خاص خود را برای اعمال روی ستون، مشخص کنید. همچنین با کلیک روی دکمه Organize Column هم می‌توانید ترتیب ‌Event ها در صف Trace را عوض کنید. 

4. حالا باید روی دکمه Run کلیک کنید تا Trace شروع شود.
5. حالا باید Trace را متوقف کنید. دکمه‌های مربوط به شروع و توقف Trace را در تصویر زیر مشاهده می‌کنید:

6. حالا باید گزارش Trace را به‌صورت یک فایل ذخیره کنید:

برای این کار، مسیر زیر را طی کنید:

File > Save As >  Trace File

7. حالا باید برنامه database engine tuning advisor را اجرا کنید. این برنامه براساس فایل Trace که در مرحله قبل ذخیره کردید، عمل می‌کند و می‌تواند پیشنهاداتی را در راستای بهبود عملکرد پایگاه داده در اختیار شما قرار دهد. برای اجرای database engine tuning advisor ، مسیر زیر را طی کنید:

Tools > database engine tuning advisor

حالا مسیر فایلی که قبلاً ذخیره کردید را به database engine tuning advisor نشان دهید:

حالا دکمه شروع تحلیل را بزنید:

نحوه استخراج Query با استفاده از SQL Server Profiler

برای استخراج Query از SQL Server Profiler ابتدا باید فایل Trace یا جدولی که حاوی وقایع اجراشده توسط دستورات SQL Server است را باز کنید. به‌منظور بازکردن فایل Trace، وارد منوی File شده و گزینه Trace File را انتخاب کنید. سپس گزینه Open File را انتخاب کرده و به محل ذخیره فایل Trace بروید. حالا فایل را انتخاب و آن را باز کنید. سپس باید از منوی File، مسیر زیر را طی کنید:

Export > Extract SQL Server Events > Extract Transact SQL Events

حالا باید گزینه Save As را انتخاب کنید. درنهایت، یک نام برای فایل Transact SQL خود انتخاب کرده و سپس روی گزینه Save کلیک کنید. این فایل، حاوی کوئری‌هایی است که باعث رخ دادن وقایع شده.

معرفی ابزارهای جایگزین SQL Server Profiler

اما نکته منفی کار با SQL Server Profiler چیست؟ باید بدانیم که این ابزار، به‌زودی از دسترس خارج و با ابزارهای مدرن و کارآمدتر، جایگزین خواهد شد. درواقع، نسخه‌های SQL Server در آینده فاقد ابزارهای SQL Trace و SQL Server Profiler خواهند بود. بنابراین، برای توسعه پروژه‌های جدید، نباید از این ابزار استفاده کنیم. همچنین باید تغییراتی را در پروژه‌هایی که قبلاً در آن‌ها از این ابزار استفاده شده، ایجاد کنیم. مایکروسافت درعوض، کاربران را تشویق به استفاده از Extended Events کرده است. Extended Events یا XEvents، گزینه‌های بسیار زیادی برای پیکربندی دارند، سبک هستند و مقیاس‌پذیری بسیار بالایی هم دارند.

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

• Prefix: یکی از بهترین جایگزین‌های SQL Server Profiler که با استفاده از آن می‌توانید کوئری‌های SQL Server را حین نوشتن کد، مشاهده کنید. Prefix یک جایگزین رایگان و قوی برای SQL Server Profiler است که جزئیات بسیار زیادی را هنگام Trace در اختیار شما قرار می‌دهد.
• ExpressProfiler: یک ابزار قوی که رابط کاربری بسیار روان، ساده و سریعی دارد.
• Neor Profile SQL: ابزاری که قابلیت‌های مشابه SQL Server Profiler را در اختیار کاربران MySQL قرار می‌دهد.

جمع بندی: SQL Server Profiler چیست؟

در این مطلب، متوجه شدیم که SQL Server Profiler چیست و چه کاربردهایی دارد. همچنین معماری این ابزار را هم به‌طور مفصل، بررسی کردیم. گفتیم که هدف این ابزار، بررسی عملکرد پایگاه داده و ثبت وقایع رخ داده در آن است. به کمک آن می‌توان خطاهای رخ داده در پایگاه داده را کشف و آن‌ها را برطرف کرد. ابزار SQL Server Profiler به ما کمک می‌کند تا تأثیر کوئری‌ها در عملکرد پایگاه داده و تعامل آن با کلاینت‌ها را بررسی کنیم.