Targeting SQL Profiler to determine performance problems

Use SQL Profiler in your production environment to target poorly performing T-SQL commands in SQL Server 2000 and SQL 2005. Microsoft database expert Michelle Gutzait guides you through SQL Trace, from how to use trace definition through incorporating SSIS/DTS packages into the process. Locate problematic T-SQL commands and send the reports to your SQL Server development team.

If you are a database administrator, you probably wish you could keep track of the badly performing commands within SQL Server instances in order to analyze them, or even send them to the development teams on a regular basis.

Do these laments sound familiar?

  • "Our SQL Server environment reaches 100% CPU for more than 10 minutes every day and we have to restart the instance."
  • "Our users complain that the applications' performance degrades every day at lunch time."
  • "The new version of the application was deployed in production and suddenly we have performance problems."
  • This article explains how to use SQL Profiler and SQL Traces to determine application inefficiencies and how to assist development teams that capture them.

    What should I trace?
    If you have a lot of activity in your SQL Server instance, you should trace only T-SQL commands that use comparably more resources (CPU, IO, Duration). This way, you will end up with a list of just the badly performing commands. Your choice of filter threads will depend on your specific environments.

    My advice is to run Profiler with initial threads of:

  • CPU > 500
  • Duration > 500 (half a second)
  • Reads > 10,000
  • Writes > 5000

    Test how many commands you are getting with these filters and change the threads accordingly until you get at least one event traced in five minutes. Also, if you get too many rows, it will be difficult to analyze the output.

    It might be necessary to adjust the threads later according to the results. For example, if the worst-performing commands were already tuned, you might want to capture commands using fewer resources than you did initially.

    Save trace definition to a file:
    It is so easy to save a trace definition. Just open SQL Profiler, choose the events, columns and filters and script trace, as described next.

    Targeting SQL Profiler to determine performance problems
     Home: Introduction
     Step 1: Tracing statements in SQL Server 2000
     Step 2: Tracing statements in SQL Server 2005
     Step 3: How to use SQL Trace definition
     Step 4: Script example for SQL Trace
     Step 5: SQL Trace results in SQL Profiler

    Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more.
    Copyright 2007 TechTarget

    More on

  • Step-by-Step Guide: How to interpret performance metrics in SQL Server 2005
  • Using SQL Server Profiler with Analysis Services
  • FAQ: DTS packages in SQL Server
  • Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning