SQL SERVER – Introduction to SQL Server 2008 Profiler – Complete

Introduction

SQL Server Profiler is a powerful tool that is available with SQL Server since a long time; however, it has mostly been underutilized by DBAs. SQL Server Profiler can perform various significant functions such as tracing what is running under the SQL Server Engine’s hood, and finding out how queries are resolved internally and what scripts are running to accomplish any T-SQL command. The major functions this tool can perform have been listed below:

  • Creating trace
  • Watching trace
  • Storing trace
  • Replaying trace

Trace includes all the T-SQL scripts that run simultaneously on SQL Server. As trace contains all the T-SQL scripts running on SQL Server, it often tends to become considerably huge. Hence, it is always a good practice to capture only those data that are actually required for analysis.

Dilemma to use on Production Server

SQL Server Profiler has a lot of potential as a tool and offers several advantages. Unfortunately, developers have been keeping their hands off from this tool and usually give two major excuses.

  • Reason 1: Profiler adds too much overhead to production server.
  • Reason 2: Profiler accumulates plethora of data making it extremely intricate to analyze them afterwards.

It cannot be denied that both the above excuses are valid reasons and play a significant role in discouraging developers from using the profiler. When a profiler is run over a production server it adds additional load on the CPU. However, the real question is – How much load does the profiler add to CPU? It may be possible that the production server’s CPU is not used more than 50% at all, and running the profiler over that database will not degrade the performance.

If the profiler is configured appropriately to trace only relevant data for analysis, it will diminish the additional load that is introduced while it is running. If the profiler is run for a longer duration, then to capture the event of longer duration it is very important to collect selective data, otherwise the collected data can outgrow the space on the server.

Profiler Terminology

It is important to understand profiler terminology before digging into its details and finding how it works.

Event – An event is an action within an instance of SQL Server Database Engine. Some good examples of event are running any T-SQL or performing any operation in the SSMS that is related to database; running stored procedures, and creating jobs.

Event Class – An event class can be defined as a type of event that can be traced. Examples of event classes are SP:Starting and RPC:Completed.

Event Category – Group of events are called an event category. Examples of event category are Stored Procedure and Locks. In fact, there can be multiple event categories that can be selected for single trace.

Data Column – Data column is an attribute of an event class captured in the trace. Data column contains value of an event class.

Filter –Filters are used to create selectiveness in data that are collected in trace.

Trace – A trace captures data based on the selected event classes, data columns, and filters. A trace devoid of filter can be very hefty. Columns that are indispensable for the analysis should be selected only in trace. Trace can be saved in the database as well as in trace files.

Template – A template defines the default configuration for a trace. Templates can be saved, imported and exported between SQL Server instances. Templates from one SQL Server version cannot be imported to a different SQL Server version. SQL Server 2009 comes with 9 pre-created templates. The default template is a standard template that captures all SP and T-SQL batches. It additionally logs general database server activity.

Some very basic examples are presented below where data has been captured.

Starting the Profiler

SQL Server profiler is a component of client tools and can be installed independently from SQL Server Database Engine. It is mandatory for the user to have “system admin” rights to start the profiler. It can be started using the following methods.

1. Going to Start >> All Programs >> Microsoft SQL Server 2008 >> Performance Tools >> SQL Server Profiler

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler1

2. From SQL Server Management Studio >> Go to Tools >> SQL Server Profiler

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler2

3. For SQL Server 2008, type profiler in the command prompt.

4. For SQL Server 2005, type profiler90 in the command prompt.

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler3

Collecting Data

Once the profiler is started, connect it to any database. As discussed earlier, a profiler is not restricted to connecting only to the local database. With appropriate authentication and system admin role, it can connect to any database and capture the data.

Once the profiler is started click on Menu >> File >> New Trace.

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler4

This will display a login prompt very similar to SQL Server Database Engine. Connect with the appropriate username and password.

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler5

It will display the ‘Trace Properties’ screen. On this screen you can enter the trace name. You will notice that trace provider name, type and versions are pre-populated and cannot be altered. These are set based on which SQL Server instance you are connected with.

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler6

The ‘Trace Properties’ screen contains a drop-down for template selection, which is labeled with ‘Use the template’. As mentioned earlier, the default template selected is standard. We can see what events are included in the Standard template in the next tab, ‘Events Selection’.

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler7

The ‘Event selection’ screen contains a checkbox that can be configured as per the requirement of data to be collected. The same screen contains a ‘Column Filters’ button, which when clicked brings up another screen that can be used to further filter trace data.

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler8

There is another button named ‘Organize Columns’ on the same screen. This screen can be used to organize and group columns. Please note that columns cannot be grouped or ordered once the process of trace collection has begun.

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler9

The ‘Trace Properties’ screen contains a ‘Run’ button. When you click on this button, it runs the trace. The trace can be paused or stopped as required using the play menu on top.

SQL SERVER - Introduction to SQL Server 2008 Profiler - Complete sql2008_profiler10

This data can be saved in database and trace file or saved as XML. Trace file can be imported back in profiler, and profiler can re-run the whole scenario. Once the trace file is saved in database table, it can be queried by using T-SQL.

Summary

This article is a prologue to SQL Server Profiler. In the next article, I will talk about filtering and organizing columns. In addition, I will include storing trace file in the database table and querying table.

Please write to me at pinal@sqlauthority.com or contact me using form on my blog https://blog.sqlauthority.com/ if you have any doubt, big or small, regarding this article.

Reference : Pinal Dave (https://blog.sqlauthority.com), DotNetSlackers

Previous Post
SQLAuthority News – Proposed eGov Standards Policy – Benefit for All or Only A Chosen Few
Next Post
SQLAuthority News – Author Visit – Virtual Tech Days August 2009

Related Posts

43 Comments. Leave new

  • Sumit, why did you changed your name?

    Reply
  • Vishnu Rana
    May 1, 2012 4:35 pm

    really very good and informative article on sql profiler.

    Reply
  • great..

    Reply
  • good article. can anyone say that how to select the databases which we want to trace using profiler

    Reply
  • Hi Pinal
    I have create own template, and run successfully also
    Thanks
    Arulraj.M

    Reply
  • Devesh Kumar
    July 9, 2012 4:10 pm

    Hello Sir,

    I need info on the below points. Please mail me answers as soon as possible.
    Thanks in advance!

    1-Setting up indexes properly for database in SQ L Server 2008 R2

    2- Monitoring and enhancing speed of the SQL Server 2008 R2 Version

    3- Setting up user authentication for accessing database, but it should not create problem for Live users, it should only ask for authentication if there are any changes to be made to live database.

    4- Blocking changes to SQL database or server from some other machine, RDP or third party softwares like Teamviewer, etc. it should only accept if changes are done from authorized machines.

    5- Securing database w.r.t maintaining .it should not crash at any time.

    6- Setting up proper backup policies for database.

    7- Setting up recovery mechanism if anything goes wrong.

    8- Creating logs for changes that are done to database. But these log should acquire only a small amount of space of hard disk/ virtual memory.

    9- When data is queued to other server it should function smooth without slowing down other live processes.

    10- Setting up mirror server with same database, It will be help full for recovery.(if possible, I am not sure if this can happen).

    Reply
  • is it posible to trace SSIS Package

    Reply
  • Hi Friends..
    really it’s very god post and informative article on sql server…can any one tell me how can i trace particular database.

    Reply
    • Go to tab in Trace Properties, check “show all columns”, click on .
      In “Edit Filter” window, select DatabaseName and on the right hand side type the databasename under “Like”

      Reply
  • Quit using Chrome Frame… I cant right click and create a short cut to desktop… that’s how I operate.

    Reply
  • Hi,
    Thanks for the nice introduction about SQL Server Profiler. Can you tell me what is the differences between duration and CPU? If I want to compare execution time of an MDX query and and SQL query that retrieve same results which one should be used(duration or CPU)? However for MDX the profiler shows duration and cpu time for subcube, query end and some other steps. Should I add them to get the total cpu time and total duration?

    Reply
  • Hi Pinal
    I need a help, its urgent.
    I have 3 servers like S1,S2,S3.
    I have same database in these 3 servers.
    My requirement is
    if S1 will busy then the data will be fetched from S2/S3 or
    if S2 will busy then the data will be fetched from S1/S3 or
    if S3 will busy then the data will be fetched from S1/S2

    and any updation in any server will update the other 2 simultaneously.

    Please do reply in my mail.

    Reply
  • Good article.

    Reply
  • Excellent Post ….. It is very use full to all….

    Reply
  • You have explained the concept of SQL Profiler very beautifully and simply. Thanks!

    Reply
  • i install sql mamagmant noe getting performance tools so what i doing….i tired about this problem and i not start profiler

    Reply
  • Hello All,
    (1)How to change default trace size ,
    (2)How to kept more then 5 trace file ?

    Reply
  • Thank you this is very nice articale. I need the link about filtering and organizing columns..
    Many thanks again for your help!
    Khalid

    Reply
  • Nice Article, Thanks alot.
    Would appreciated if you could suggest me on this.
    Am working on Reports for Database which is very vast and don’t have any documentation related to it. Database in not designed properly column names are different in related tables. (eg: EmplD is IDType in Dept tbl and same is NO in another tbl) creating much trouble to find relationship. After understanding alot am try with same dataypes which might be wrong later.
    Please help me or suggest me to work on this. Are there any tools like activity monitor or suggestions to analyse database and work on it. Thanks in advance.

    Reply
  • Please anyone tell me how to add sql server agent in sql server 2008 R2

    Reply

Leave a Reply

Menu