Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    22

    Question Unanswered: SQL Recompilations - PerfMon different from Trace

    Ok, I'm really stumped on this one and I'm hoping someone can shed some light on this. I was using PerfMon to monitor SQL Recompilations (SQL Re-Compilations/Sec counter), and found that there were small spikes of about 3 or 4 re-compilations every minute. Probably nothing to lose sleep over, but I thought I would investigate further to see if I could eliminate the cause.

    So I ran a profiler trace, which included the SP:Recompile Event, but it came up empty. Kind of odd, so I ran both PerfMon and Profiler concurrently to see if the event would show up in Profiler when the graph spiked in PerfMon. I saw the PerfMon graph spike, but Profiler didn't pick up anything. I double-checked my Profiler settings by running a test script that would cause a recomplile (interleaved DDL and DML statetments) and Profiler correctly picked up the event.

    Does anyone have any idea why the two tools would report different results?

    Thanks,
    Roby2222

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    in a word - M$

    why are you monitoring recompilations? Do you have a heavily used database?

  3. #3
    Join Date
    Dec 2003
    Posts
    22
    The database isn't under particulary heavy use, but it is our primary DB. I was tuning the database over the past few weeks, and was using PerfMon to monitor the effect of the changes. The recompilations were happening before I began, but I was hoping to minimize them. I realize there will always be a few, and that's ok, but I'm just stumped because I can't seem to find anything with a Profiler trace.

    Roby2222

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This white paper may become your ultimate reference for this kind of issues.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Dec 2003
    Posts
    22
    Thanks for the link. Looks like a good read.

    Roby2222

Posting Permissions

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