June 28, 2003 at 2:32 am
Hi All,
I'm tuning my database due to a few recent instances of users being delayed do to locking. Using SysMon I see counts in the Compilations/Sec under SQL. I have reviewed BOL and the excellent material on this site and, as best as I can see, my procs are ok, eg:
No Set statements
All qualified with dbo.
No excessive activity
No temporary tables
etc.
However, all my procs have parameters passed into them at runtime.
I have used Profilier to try and identify the recompiles but the event SP:Recompile is never shown.
I'm at a loss now. It seems to me that either SysMon or Profiler are reporting incorrect data.
Any ideas anyone?
Cheers, Peter
ps environment is: SQL Server 2000 SP3 running under W2K Server with a VB6 app.
Cheers, Peter
June 29, 2003 at 11:27 pm
Is "Compilations/Sec" different to "re-Compilations/Sec"? The latter may refer to SPs whilst the former to any ad hoc SQL batches?
Cheers,
- Mark
June 30, 2003 at 9:05 pm
Hi,
Thanks for the hint - I hadn't noticed that Profiler just has Recompiles and not Compiles so I wasn't comparing like with like.
My staus is both SysMon and Profiler agree on zero Recompiles.
How can I find out what are the procs that are being Compiled according to SysMon?
Why aren't Compiles are trackable event in the Profiler?
Cheers, Peter
June 30, 2003 at 9:26 pm
Personally I'd track any DML that's not within an SP. Starting with TSQL:SQL:StmtStarting and then filtering out obvious garbage (SET statements etc). That may come close, but then again I may be way off track.
Cheers,
- Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply