By: Ben Snaidero
Overview
Every DBA needs tools to be able to analyze the activity in their SQL Server database. Whether it's to troubleshoot a possible application or database issue or simply to monitor the overall health of their system. In this tutorial we will explore how these tasks can be accomplished using the SQL Profiler interface that can be accessed from SQL Server Management Studio or directly from the tool.
Explanation
What is SQL Profiler?
SQL Profiler is a tool that provides a user interface to the SQL Trace utility which gathers events as they occur in your SQL Server instance. It gives you the ability to monitor everything that is going on inside your SQL Server instance. SQL Profiler provides you with an easy to use interface that allows you to select the events you want to monitor and where you want the output to be saved without having to know all the system stored procedures that are a part of SQL Trace. Without this tool you would have to go through the tedious process of manually setting up each event and filter with individual stored procedure calls for every event you want configured in your trace.
When would you use SQL Profiler?
SQL Profiler has many use cases, but it's main purpose is to monitor the activity in your SQL Server instance. Whether someone has called suspecting there is an issue with your SQL Server instance or you just want to check the overall health/performance of you database, SQL Profiler gives you the ability to monitor anything from regular user activity/transactions to locks/deadlocks and system errors. You can also perform proactive maintenance on your SQL Server instance by using SQL Profiler to identify any poor performing queries so they can be analyzed and tuned or you could use it to capture a large time frame of activity so it could be used for replay on a test system. Lastly SQL Profiler can be used to perform auditing on your SQL Server instance. You have the ability to define login/logout events so you can see who is accessing your instance and what systems they are accessing it from. You can also capture all DDL events which would give you a log of all the changes that are made to your environment.
What version/editions of SQL Server is SQL Profiler available on?
SQL Profiler is only available in the Enterprise, Business Intelligence and Standard editions of SQL Server however if you have either Enterprise or Standard edition installed somewhere in your environment it can be use to profile any of the other editions i.e. Express or Web.
Also keep in mind Extended Events were released in SQL Server 2008, which is considered a replacement to SQL Server Profiler or SQL Server Trace. However, SQL Server Profiler / SQL Server Trace is still widely used and continues to provide value to address numerous DBAs needs.
Additional Information
- Read tips on using SQL Profiler
- Microsoft SQL Profiler documentation
- Microsoft SQL Trace documentation
- SQL Profiler feature information
