How to use SQL Profiler in SQL 2008

By | November 19, 2012
rx online

SQL Server Profiler is a rich interface to create and manage traces and analyze and replay trace results. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem.

To know more about SQL Profiler please read article sql-server-profiler


Permissions Required to Run SQL Server Profiler

By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission. For more information

Permissions Used to Replay Traces

Replaying traces also requires that the user who is replaying the trace have the ALTER TRACE permission.

However, during replay, SQL Server Profiler uses the EXECUTE AS command if an Audit Login event is encountered in the trace that is being replayed. SQL Server Profiler uses the EXECUTE AS command to impersonate the user who is associated with the login event.

If SQL Server Profiler encounters a login event in a trace that is being replayed, the following permission checks are performed:

  1. User1, who has the ALTER TRACE permission, starts replaying a trace.
  2. A login event for User2 is encountered in the replayed trace.
  3. SQL Server Profiler uses the EXECUTE AS command to impersonate User2.
  4. SQL Server attempts to authenticate User2, and depending on the results, one of the following occurs:
    1. If User2 cannot be authenticated, SQL Server Profiler returns an error, and continues replaying the trace as User1.
    2. If User2 is successfully authenticated, replaying the trace as User2 continues.
  5. Permissions for User2 are checked on the target database, and depending on the results, one of the following occurs:
    1. If User2 has permissions on the target database, impersonation has succeeded, and the trace is replayed as User2.
    2. If User2 does not have permissions on the target database, the server checks for a Guest user on that database.
  6. Existence of a Guest user is checked on the target database, and depending on the results, one of the following occurs:
    1. If a Guest account exists, the trace is replayed as the Guest account.
    2. If no Guest account exists on the target database, an error is returned and the trace is replayed as User1.

The following diagram shows this process of checking permission when replaying traces:

SQL Server Profiler replay trace permissions


Steps to Use SQL Profiler

  • Open SQL Management Studio
  • Click –> Tools –> SQL Profiler
  • Login with you SQL/Window Authentication
  • Enter Trace Name (Not Mandatory)
  • Select Event to trace from “Event Selection” tab and Click on Run button to open Trace Window
  • Execute any SQL Statement on SQL Query Analyzer
  • Trace the event on SQLeasy references.



Where we can use SQL Profiler

It is very useful to trace the SQL Script running on website/site without debugging the code. Suppose on live site you are getting error, from log you come to know it’s SQL error. To trouble shoot the error first you need to find the SQL script which is executing on SQL server to process the client request. If you will start debugging the site then it will be more time consuming and also it is not possible to debug the live site. But with the help of SQL Profiler you will easily able to trace any types of SQL statement executing on server.

In some scenario we are passing 10-15 parameter to procedure and it is very difficult to get the SQL script from debug also because parameter which is passed to Store Procedure is in array and you can’t execute the same on SQL Query Analyzer with proper formation. If you get the final script which will execute on statement then your are easily able to resolve the issue. You can easily achieve this task using SQL Profiler. I have used the same in many live projects and saved my valuable time.

Time is very precious and technology exist to save the time and achieve the task in short time. Hopes this post will help you to save your time in future. If so then please don’t forget to comment on same.



2,710 total views, 2 views today

Category: SQL