Tag: Attentions

  • Profiler events for different scenarios

    A few moons back I had blogged about the high frequency events in SQL Server. In this post, I shall talk about the common scenarios where profiler traces are collected for SQL Performance issues and what kind of events need to be captured in the server side traces that are configured for the data collection.

    While configuring a profiler trace, always include all data columns for that event as that doesn’t have an overhead as much as the number of events being captured during the trace capture.

    In this post, I shall cover two of the most common issues that CSS collects profiler data for addressing SQL Performance issues: Attentions/Timeouts and High CPU usage/Long Running queries.

    Attentions or Timeouts

    Attentions or Timeouts can occur due to client side application timeout setting (mostly this is the case) or due to database engine side issue (like failure to acquire memory, problem on the server side connectivity layer etc.). During this time, you will have a lot of queries starting but not completing successfully. If you are dealing with such a scenario, then you would need to capture the Starting events using a profiler trace to find out which statements didn’t complete and encountered the Attention event.

    The events that need to be captured are:

    All Database Events

    Log File Auto Shrink
    Data File Auto Shrink
    Log File Auto Grow
    Data File Auto Grow

    All Errors and Warnings

    Attention (Important for tracking the timeouts)
    ErrorLog
    EventLog
    Exception
    Hash Warning
    Execution Warnings
    Sort Warnings
    Missing Column Statistics
    Missing Join Predicate
    Exchange Spill Event
    Blocked process report
    User Error Message
    Background Job Error
    Bitmap Warning
    Database Suspect Data Page
    CPU threshold exceeded

    Lock:Deadlock Chain and Lock:Timeout (timeout > 0)

    Performance: Auto Stats

    Showplan All or Showplan XML (As execution plan will not be available for queries which didn’t complete)

    Showplan Statistics Profile or Showplan XML Statistics Profile (For comparing estimated and actual query plan)

    Security Audit: Audit Login, Audit Login Failed and Audit Logout

    Stored Procedures events:

    RPC:Starting
    SP:Recompile
    SP:Starting
    SP:Completed
    SP:StmtStarting
    SP:StmtCompleted
    RPC:Completed

    Transactions: SQLTransaction is required for tracing back the transaction if there are nested transactions). DTCTransaction is required only if DTC transactions are getting timed out.

    TSQL events:

    SQL:BatchCompleted
    SQL:BatchStarting
    SQL:StmtStarting
    SQL:StmtCompleted
    SQL:StmtRecompile

    Cursor events: CursorRecompile and CursorImplicitConversion (Required for checking if the requested cursor is being implicitly converted or if the cursor is being frequently recompiled).

    The above events should be sufficient to track down any query timeouts and the common root causes for the same.

    High CPU usage/Long Running queries

    Another set of issues that are commonly dealt with while troubleshooting SQL Performance issues are Long Running Queries or Queries causing high CPU usage on the server. In such a case, the above set of events would be definitely be useful. If you are certain that the profiler trace can be captured for the entire duration of the query execution, then for highly transactional servers, you can omit the capture of the following events:

    SQL: BatchStarting
    SQL: StmtStarting
    SP: StmtStarting
    SP: Starting
    RPC: Starting

    The above logic of configuring a profiler trace will also help you capture trace data when you are trying to track down queries which are causing excessive amount of IO on the SQL instance.

    Reference for capturing blocking information on a SQL instance: SQL 2000 Blocker Script output/SQL 2005 PerfStats

    Keep in mind that the profiler data captured may not be sufficient for root cause analysis if you are not capturing SQL blocking information and performance monitor data in conjunction with the profiler traces.

    In case you need additional events like deadlock events or lock events, then refer to my previous post and find out if they fall in the high frequency event category and when to capture the particular event.

    TIP: As much as possible, avoid adding Text filters to your profiler trace configuration as it can lead to high CPU usage for highly active OLTP environments during filter evaluation.

    Hope that the above information will help you reduce the excessive amount of data collection for troubleshooting SQL performance issues.