SQL server comprises of an inbuilt powerful tool i.e., SQL Server Profiler. This feature or tool is embedded with the server since a long time and is generally used by database administrators. It render various important functions like examining what is operating under SQL server Engine’s hood, determining the ways by which queries were fixed, what T-SQL scripts are running for accomplishing tasks, etc.
The trace involves all the T-SQL scripts, which are running simultaneously on the server. Since the trace comprises of all the T-SQL server scripts, it tends to become substantially large. Therefore, it is recommended to server users that they should capture only those data file that is required for analysis.
Basically, SQL Server profiler is a utility, which execute on the server in order to monitor and log specified activities for producing an activity list occurring on the server. With help of this SQL server profiler, users can capture only the event that they want to examine. It will create a customizable view of the event (which is to be monitored) for server user.
Until now in this blog, we are continuously seeing a term ‘trace’ but now a question arises ‘What exactly a trace is?’
A trace catches a data which is related to a selected event classes, data columns, and filters. One should select only those trace where data columns are essential for analysis. This trace can be stored either within the database or in the trace files.
NOTE: Event classes are basically an event type, which can be traced and filter is an option for creating a particular data that is to be gathered in trace.
Launching the Server Profiler Window
To launch profiler window from Microsoft SQL server management studio, you will have to navigate to Tools >> SQL Server Profiler
Navigate towards Start button of Windows on your machine and then go to Programs >> Microsoft SQL Server >> Performance Tools >> SQL Server Profiler
Open Run window on your operating system and run the following command for SQL server 2008 and all latest server versions, in order to launch profiler in SQL Server window
Launch the Run window on your machine and run the following command for SQL server 2005 for opening SQL server profiler
Figure Representing Window of SQL Server Profiler
After successfully launching the SQL server profiler, initially one will create a new trace (if there does not exist any trace for analysis). Hence, to do so go through following set of steps and configure a new trace:
TIP: You can directly press Ctrl + N from keyboard on SQL Server profiler window to perform Step 1
There are two more options available within this tab i.e., Column Filters and Organise Columns
NOTE: Once the trace collection procedure gets started, no changes like grouping and ordering, can be performed within the column
The blog gives a brief introduction about SQL server profiler with its role in the server. Here, user can find ways to perform common operation on SQL server profiler.