Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Question Unanswered: SQL Profiler - How does it affect SQL performance?

    Everywhere I read, it states that running SQL Profiler can affect performance of your SQL Server. My question is - how much of an impact will it really make? Will I see a 1% degredation in peformance? 5%? 50%? I haven't been able to find a good answer. We currently have SQL Profiler running all day long for almost 3 years, and the databases are still humming.

    Is it the amount of data you are requesting from the trace that affects performance? There are some compliance tools out there (Idera Compliance Manager, IPLocks, etc) that run a profiler trace to get data. There are other DBAs in my organization who don't want to use them because "profiler traces will degrade my SQL Server performance". How true is this really.

    Any help I can get would be extremely appreciated.

    Thanks.

  2. #2
    Join Date
    Dec 2004
    Posts
    46
    From what I have seen, 10% is a good bet. Actually, you wouldn't request too much information from profiler, because you end up very difficult to analyse them. Unless you are debugging, you normally would go down to store procedure level.

  3. #3
    Join Date
    Nov 2003
    Posts
    6

    Published statistics?

    Has anyone ever actually done a study on this, or seen published statistics? I haven't seen anything that actually proves this. I'm not noticing any degredation on our servers, and the Profiler trace runs all day long, every day.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    It really depends on the amount of information the trace is capturing.

    For instance, we keep three low-level traces running 24/7, capturing information on long running queries, deadlocks, and failed logins for high level users. Each of the files generated is small (avg size 10M for long-running, less than 128K for security and deadlocks). These traces have very low impact on the system.

    If, however, we were to try and capture sql start and stop + text + database + username + <other information based on events> for every spid and every process, it would probably occupy a good 20 - 25 % of resources (cpu, disk, memory) thereby adversly affecting the performance of the system.

    Start with 'event classes' in BOL and follow the links from there regarding the different event classes, columns, and categories from there.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Dec 2004
    Posts
    46
    U need to stress the system to saturate the CPU to notice the 10% degrade. I have tried to capture profile at store preocedure level and u would notice the throughput degrade by 10%. This may varies depends on ur machine I believe, mine was a 4 X 2.4G Xeon.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •