Step-by-Step Guide

Targeting SQL Profiler to determine performance problems

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

    • Requires Free Membership to View

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to: