Profiler leads to blocking

  • We are trying to reduce the CPU load on one of our database clusters, and we are trying to use Profiler to do it. However, whenever we run Profiler, even with limited event nodes, we get an obscene amount of blocking and locking and are forced to stop. I've never experienced this before with Profiler, any ideas?

    N

  • Never run Profiler on Production serers. You could end up rebooting your box. Check out PSSDiag utility from MS. Run SQLDiag, get the info and analyse it.

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • I've seen this behavior when the query execution plan was included in the profile when there was a lot of dynamic sql being run against the server.

    Hope this helps.

    Bill Mueller

  • This may sound a stupid question but how do you know profiler is causing the problem?

    Are you saving the trace to disk?

    I have a problem server which I profile, mainly for rpc and sql batch completed events but the box is maxed out 100% on all procs so is often struggling ( usually profiler gives up reporting not able to return results / out of memory )  As I also monitor for blocking I'd know if I caused this - I don't. Just interested. My server runs 99% dynamic sql.

    Agree including the query plan in the profile trace produces a large trace which often bombs even quicker!  Are you sure there are not other issues at play here?

    I've been profiling troublesome prod boxes for many years and never encountered this.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • I believe it is the Profiler, simply because after about 10 minutes after starting it (either to disk or to a DB) the site the DB is called from becomes completely unresponsive and I see a lot of blocking and locking. As soon as I kill Profiler (maybe a minute or so later) everything calms back down. One very odd thing I've noticed is that while Profiler is running, and we have all that blocking, the CPU usage drops below 50%. It normally floats around 60%. I've also never seen this with Profiler, that's why I asked here.

  • use server side traces ( try to collect only needed info though) instead of profiler and you will be fine...

  • I never write the profiler data I'm collecting to the sevrer I'm collecting from ( well generally not ) sounds as if you are slowing your disks by the profiler activity which makes your databases block.

    Try profiler from a workstation and collect the minimum data, I usually just collect rpc and sql batch completed and eliminate the sql agent, replication etc. and filter by the specific dbid

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 7 posts - 1 through 6 (of 6 total)

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