Setting up a sql trace profiler

There are some references to other links on performance tuning on this page. This page serves  as a reminder for me on how to start the process going for setting up a Sql trace.  Here are my basic steps and the articles with some details on the whole process and logic are where I Go next.

profiler1

  1. Capture a profiler trace for a short period while the server is active. This will identify the queries that constitute a typical workload.
  2. Import the trace results into a database table and analyze them to find the queries or stored procedures that are having the most impact on the server.
  3. Run those stored procedures in Management Studio against a test database, and examine the execution plans and query statistics.
  4. Use the execution plan and statistics to identify queries that need tuning and indexes that need creating.
    1. 5.        Implement the changes and review the effects.
 

The below provides how to generate the first script. However, I would like to get one for each of the following catagories at some point:

ANALYZING AND DEBUGGING SQL STATEMENTS AND STORED PROCEDURES.

Monitoring slow performance.

Stress analysis.

General debugging and troubleshooting.

Fine-tuning indexes.

Auditing and reviewing security activity.

 

Analyzing Stored Procedures:

  1. Use Profiler to get us a “script”.
    1. Start Sql Profiler
    2. File-> New Trace
    3. Name it and – Chose Blank
    4. Click the events selection tab

i.      Chose Stored Procedures-> RPC Completed

ii.      Chose TSQL->SQL:Batch Completed

It will look like the below picture. Then Run it and then stop it.

profiler2

Then Choose File->Export->Script Trace Definition->For SQL 2005 – 2008

Finally you can open the trace up using SSMS. However, there are a couple of changes that need to be made.  Review:

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/community/blogs/brad_mcgehee/archive/2008/09/25/69712.aspx

http://www.simple-talk.com/community/blogs/brad_mcgehee/archive/2008/09/25/69712.aspx

Exporting a SQL Trace from Profiler to create a Performance data collector~! – This cannot be removed on the customer machine as of SQL 2008 SP2. Beware!

Short I know but these are my first two posts so hopefully i will have many more to come and  I will get better at it> 🙂

Louis

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s