sql profiler trace

  • Hi,

    Please help me out in understanding,how does running the sql profiler trace on production database can cause performance overheads..

    Thanks,

    Deepak

  • Running profiler means you are capturing the information related to specific database / activity. For that SERVER has to collect the details and any activity on the server affect CPU/Memory/IO etc.

    That is the reason if you need accurate data of load of your database/server you need to run the profiler from the other machine however that will increase the network traffic.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • You don't ever want to use Profiler gui against a production server. Use a server-side trace and trace to a fast local drive instead.

    http://scarydba.wordpress.com/2008/12/18/profiler-research/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This is the key page for understanding how SQL Trace works and feeds into the I/O providers (rowset/SMO/Profiler is one of them).

    SQL Trace

    The rowset provider, on the other hand, is not designed to make any data loss guarantees. If data is not being consumed quickly enough and its internal buffers fill, it waits up to 20 seconds before it begins jettisoning events in order to free buffers to get things moving. The SQL Server Profiler client tool will send a special error message if events are getting dropped, but you can also find out if you’re headed in that direction by monitoring SQL Server’s TRACEWRITE wait type, which is incremented as threads are waiting for buffers to free up.

    Profiler impacts performance when implementing larges traces due to the number of events being fired. This would impede CPU/Memory[/IO] if there large number of events occuring.

    Once an event fires, its data is routed into a global event sink, which queues the event data for distribution to each trace that is actively listening. The trace controller routes the data to each listening trace based on its internal list of traces and watched events

    Hope this helps.

  • GilaMonster (10/19/2010)


    You don't ever want to use Profiler gui against a production server. Use a server-side trace and trace to a fast local drive instead.

    FWIW I've found lightweight traces are fine on high volume database environments. I believe its a combination of myth and/or poorly configured traces that have spread this across a multitude of articles.

  • MysteryJimbo (10/20/2010)


    I believe its a combination of myth and/or poorly configured traces that have spread this across a multitude of articles.

    I have personally brought down a busy production server with a lightweight trace via the profiler GUI. It's no myth.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks to every one for your input's.

    Thanks,

    Deepak

  • GilaMonster (10/20/2010)


    MysteryJimbo (10/20/2010)


    I believe its a combination of myth and/or poorly configured traces that have spread this across a multitude of articles.

    I have personally brought down a busy production server with a lightweight trace via the profiler GUI. It's no myth.

    I'm not denying it isnt possible to bring down a server. Just its not impossible to use profiler if you are careful. We have two profiler traces and a server side trace running on a busy website server with between 5000 and 20000 concurrent and running connections. The database itself performs at least 40 million transactions/queries per day. So it is possible to successfullly use profiler on a prod db server so long as you are selective.

  • MysteryJimbo (10/21/2010)


    GilaMonster (10/20/2010)


    MysteryJimbo (10/20/2010)


    I believe its a combination of myth and/or poorly configured traces that have spread this across a multitude of articles.

    I have personally brought down a busy production server with a lightweight trace via the profiler GUI. It's no myth.

    I'm not denying it isnt possible to bring down a server. Just its not impossible to use profiler if you are careful. We have two profiler traces and a server side trace running on a busy website server with between 5000 and 20000 concurrent and running connections. The database itself performs at least 40 million transactions/queries per day. So it is possible to successfullly use profiler on a prod db server so long as you are selective.

    I've had similar positive experiences on very busy servers by implementing traces prudently. I have never locked up a server by using Profiler and executing traces if the server wasn't already overloaded.

    LC

  • use data collection under management

    easy and clear output with reports.

    profiler is always a headache for me from my exp.

    http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-performance-data-collector/

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply